Řešení jednoduché SQL hádanky

Tomáš Herceg       11. 8. 2009       SQL, Databáze       7798 zobrazení

Před dvěma týdny jsem na svém blogu uveřejnil SQL hádanku. Jednalo se o to, jak v SQL vybrat z určité tabulky se sloupci Datum a Skupina pět nejnižších dat z každé skupiny, přičemž skupinou se myslí záznamy, kterémají stejnou hodnotu ve sloupci Skupina.

Během pěti minut po zprávičce na mém Twitteru mi přistály v e-mailové schránce 3 správná řešení. Bylo to samozřejmě od lidí, které znám a od kterých jsem to čekal. Evidentně už podobný dotaz někdy museli psát také (anebo četli Altairův článek), proto věděli, jak se to dělá.

Během následujících hodin a dní mi už začaly chodit spíše řešení, kde čtenáři ranking functions v MS SQL Serveru neznali, anebo je nenapadlo, že by se daly použít. Já sám jsem na blog tuto hádanku napsal a až po příchodu prvního řešení jsem si vzpomněl na to, že znám klíčové slovo PARTITION BY, akorát mě nenapadlo ho použít. Ono se to dá i bez něj (že by další hádanka?), ale ideální řešení to není, i když bude pravděpodobně skoro stejně rychlé.

Rozhodně není správné řešení toto, protože tady pro každý řádek pouštíte jeden SELECT dotaz. Pokud řádků bude sto tisíc, děláte sto tisíc a jeden SELECT. Časová náročnost dotazů se sice nedá počítat jen podle počtu prováděných selectů, ale jako takový základní odhad to většinou stačí. Ona si konec konců databáze ty dotazy dost optimalizuje, na druhou stranu na to není dobré vždy spoléhat. Takhle tedy rozhodně ne.

SELECT * 
  FROM @t [t] 
  WHERE 
    [t].[Number] IN (SELECT TOP 4 [Number] FROM @t WHERE [CategoryId] = [t].[CategoryId] ORDER BY [Number]) 
  ORDER BY [t].[CategoryId], [t].[Number]

Správné řešení je využít funkci ROW_NUMBER, která umí záznamy v sadě výsledků očíslovat podle pořadí dle nějakého klíče. Je nutné ji ještě spojit s klíčovým slovem PARTITION BY, které řekne, že se má číslovat v rámci skupin určených stejnou hodnotou nějakého výrazu.

SELECT [t].[CategoryId], [t].[Number]
  FROM 
   (SELECT ROW_NUMBER() OVER (PARTITION BY [CategoryId] ORDER BY [Number]) AS [RowNumber], [CategoryId], [Number] FROM @t) [t] 
  WHERE [t].[RowNumber] <= 4

To by tedy mělo být ideální a nejrychlejší řešení. U vnořených SELECTů je nutné dávat dobrý pozor. Někdo říká, že by se používat vůbec neměly, někdo na to kašle a používá je všude, protože nezná klíčové slovo JOIN, což rozhodně správné není.

Je nutné si uvědomit, jestli se subselect spouští jednou anebo pro každý řádek. Pokud je v sekci FROM, pak se vytáhne pouze jednou a pak se s ním pracuje. Pokud je v sekci SELECT nebo WHERE, je to problém, protože se spouští pro každý řádek. Pokud je řádků pár a server není zatížený, tak to ani nepoznáte. Pokud ale máte na krku velkou databázi, rozdíly jsou dosti dramatické.

 

hodnocení článku

0       Hodnotit mohou jen registrované uživatelé.

 

Nový příspěvek

 

Diskuse: Řešení jednoduché SQL hádanky

Teď koukám, že máš v kódu i textu (včetně předchozího článku) malou nekonzistenci, která by mohla někoho zmást - občas mluvíš o prvních čtyřech záznamech ze skupiny, občas o pěti.

Pak tenhle koment klidně smaž, ať tu nestraší ;)

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Diskuse: Řešení jednoduché SQL hádanky

Dobrý den,

narazil jsem na Vaši hádanku a snažím se inspirovat pro řešení mého problému:

Mám tabulku kontakty ( kontakty) a tabulku kontaktní osoby (kon_os )- každý kontakt má více kontaktních osob. Potřeboval bych v gridu s údaji o kontaktu zobrazit jen nejnovější( nebo nejstarší) kontaktní osobu ( jméno a přijmení )např. dle primárního klíče kon. os. - Můžete mi poradit kudy na to ?

Pokouším se nějak upravit pro tento účel dotaz :

SELECT Kontakty.Kod, Kon_osoba.Kod_kon_osoba, Kontakty.spolecnost, Kon_osoba.prijmeni, Kon_osoba.jmeno

FROM Kontakty INNER JOIN

Kon_osoba ON Kontakty.Kod = Kon_osoba.Kod

Děkuji a přeji hezký den

PB

nahlásit spamnahlásit spam -1 / 1 odpovědětodpovědět

Diskuse: Řešení jednoduché SQL hádanky

... tak to je zrada, já myslel, že to musí fungovat i v MSSQL 2000, které ROW_NUMBER nemá. Co to dát znova a s podmínkou kompatibility s T-SQL co je v MSSQL 2000?

nahlásit spamnahlásit spam 0 odpovědětodpovědět

SQL Server 2000 ranking functions neumí. Je pravda, že v zadání to nebylo, za což se omlouvám. Na druhou stranu proč se omezovat deset let starou verzí databázového enginu, když máme SQL Server 2008?

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Přece proto, že by to pak bylo těžší dát do kupy. ;-) A opravdovým vítězem by se mohl stát ten, kdo to napíše ještě pro MySQL, PostgreSQL a Firebird.

Vážně: Dosud některé projekty na MSSQL 2000 udržuji - přestože MSSQL je co do dopředné kompatibility na tom opravdu dobře. Ale například SQL 2005 už vyžadoval ke své instalaci .NET dvojku. A navíc nemá SQLServerAgenta, což je prostě krok zpátky ;-(

nahlásit spamnahlásit spam 0 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback