Funkce pro určení pořadí řádků ve výsledku - Ranking function

Velmi často potřebujeme v praxi stanovit pořadí záznamů ve výsledku. Pro setřídění výsledků nám standard SQL nabízí klíčové slovo ORDER BY umísťované na konec SELECT dotazu. Očíslovat výsledek můžeme v prostředí ORACLE pomocí pseudosloupce ROWNUM. 

     SELECT ROWNUM poradi, jmeno, prijmeni FROM student

Pseudosloupec ROWNUM přiřazuje číslo výsledným řádkům. Bohužel přiřazení probíhá ještě před finálním setříděním podle výrazu za ORDER BY, a proto následujícím způsobem pořadí studentů dle abecedy nezískáme:

   SELECT ROWNUM poradi, jmeno, prijmeni FROM student 
            ORDER BY prijmeni, jmeno

Pokud chceme číslovat až setříděný seznam, musíme dotaz zanořit:

   SELECT ROWNUM poradi, jmeno, prijmeni
            FROM (
                        SELECT jmeno, prijmeni FROM student 
                        ORDER BY prijmeni, jmeno
            )

Na další omezení narazíme, pokud potřebujeme vybrat záznamy podle pořadí, například třetího studenta dle abecedy. V takovém případě musíme podmínku vložit až do třetí vrstvy nadřazeného dotazu. Nelze napsat:

     SELECT ROWNUM poradi, jmeno, prijmeni FROM student 
            WHERE rownum  = 3
            ORDER BY prijmeni, jmeno 

ani

     SELECT * FROM (
                        SELECT ROWNUM poradi, jmeno, prijmeni FROM student 
                        ORDER BY prijmeni, jmeno 
            ) WHERE poradi = 3
 

Důvodem je, že databáze pseudosloupec ROWNUM  nastavuje, až když řádek splní podmínku za WHERE,  první řádek je vždy ROWNUM = 1, a protože tento řádek nesplňuje podmínku ROWNUM = 3, na výstup se nedostane. Databáze  tak projde všechny záznamy v tabulce, ale žádný podmínku nesplní. Druhý problém je, že ROWNUM se nastavuje ještě před setříděním přes ORDER BY. Databáze vybere tři řádky z tabulky, které teprve následně setřídí.  Výsledkem druhého dotazu je třetí řádek, který databáze našla, ale nikoliv nutně třetí dle  příjmení studenta. Náhoda často způsobí, že při letmém testování se může zdát, že dotaz funguje, teprve při hlubší kontrole se ukáže, že jde o chybu. 

Správně je:

     SELECT * FROM (
                        SELECT ROWNUM poradi, jmeno, prijmeni FROM (
                                    SELECT jmeno, prijmeni FROM student 
                                    ORDER BY prijmeni, jmeno 
                        )
            )

     WHERE poradi = 3

Tedy nejprve setřídit, pak očíslovat a teprve ve třetí vrstvě filtrovat. Jak je vidět tato konstrukce je dost komplikovaná a navíc neřeší variantu, kdy máme studenty se stejným příjmením a tyto bychom chtěli označit stejným pořadovým číslem. Proto je pro tento typ úloh výhodnější použít některou z tzv. ranking function. ORACLE i POSTGRESQL nabízí tři funkce:

• RANK()
• DENSE_RANK()
• ROW_NUMBER() 

Funkce se liší způsobem, jakým řádky číslují v případě, kdy se objeví ve výsledku stejné, dle pravidel třídění rovnocenné hodnoty. Funkce RANK() a DENSE_RANK() číslují stejné hodnoty stejným pořadovým číslem. Funkce ROW_NUMBER přiděluje každému řádku unikátní číslo, u shodných hodnot rozhoduje o pořadí náhoda. Rozdíl mezi RANK a DENSE_RANK spočívá v tom, jaké pořadové číslo následuje po sérii shodných řádků. Funkce DENSE_RANK pokračuje nepřerušenou číselnou řadou, funkce RANK přeskočí odpovídající počet čísel. Vše osvětlí následující tabulka.

Tab. 1: Srovnání výsledků funkcí RANK, DENSE_RANK a ROW_NUMBER

Syntaxe všech tří funkcí je následující:

     RANK () OVER (ORDER BY sloupec)

Za názvem funkce následují prázdné závorky, dále klíčové slovo OVER, za kterým následuje definice třídění, podle kterého chceme určovat pořadí řádků. Definice za ORDER BY v ranking funkci nemá žádnou vazbu k ORDER BY klauzuli na konci celého SQL dotazu. Můžeme určovat pořadí zcela nezávisle na finálním setřídění výsledku. 

V případě, kdy potřebujeme stanovit pořadí v jednotlivých kategoriích výsledku, lze  výraz OVER dále rozšířit o klauzuli PARTITION BY. Například pokud chceme číslovat pořadí vyšetření jednotlivých pacientů podle data vyšetření. 

     SELECT patient_id, datum_vysetreni,
            RANK() OVER (PARTITION BY patient_id ORDER BY datum_vysetreni) poradi
            FROM vysetreni

Tab. 2: Číslování výsledku s klauzulí PARTITION BY

Ranking funkce lze v dotazu umístit mezi klíčová slova SELECT a FROM nebo jako výraz pro třídění za závěrečné ORDER BY. Naopak nelze je umístit do podmínky za WHERE či HAVING. Pokud chceme pomocí nich definovat podmínku, musíme použít vnořený SQL dotaz.