Šuplík Honzy Hučína

Výpočet pořadí jedním SQL dotazem

Někdy je potřeba přiřadit každému záznamu v tabulce pořadí podle hodnoty určitého pole, případně vypsat jen záznam s N-tou největší hodnotou. Nepochybuji o tom, že pro databázové profesionály se jedná o známou věc; možná ale pro začátečníky bude mít můj návod nějakou hodnotu.

Podívejme se nejdřív na vypsání záznamu s N-tou největší hodnotou. Budeme pracovat s tabulkou tab, v níž máme pole s primárním klíčem id a pole s hodnotou, podle které třídíme, například score. Pro zjednodušení budeme vypisovat místo celého záznamu jen pole id.

Nabízí se využít v příkazu SELECT klauzuli ORDER BY, která seřadí výstup podle určitého výrazu; výstup pak omezíme na jediný požadovaný řádek klauzulí LIMIT, do níž dosadíme za N požadované pořadí:

select id from tab order by score desc limit N,1

Problém nastane ve chvíli, kdy více záznamů bude mít stejnou hodnotu pole score. V tom případě bychom potřebovali vypsat všechny záznamy, které „dělí” N-té místo. Jelikož ale předem nevíme, kolik jich bude, LIMIT nám nepomůže. Musíme na to jít jinak.

Nejprve si vypočteme pořadí pro každý záznam. Pořadí záznamu podle pole score je definováno jako počet ostatních záznamů s vyšší hodnotu score zvětšený o jedničku. Jak ale SQL příkazem porovnávat každý záznam se všemi ostatními? Pomůžeme si trikem: do příkazu zahrneme tabulku tab dvakrát a tyto dva exempláře propojíme podmínkou, že hodnota pole je v prvním exempláři menší než ve druhém. Tak se porovná každý záznam s každým a výsledek bude tvořen řádky, kde ke každému záznamu v tabulce tab budou uvedeny všechny záznamy ze stejné tabulky s vyšší hodnotou pole score.

select a.id as id
from tab as a, tab as b
where a.score<b.score...

Dopustili jsme se ovšem jisté chyby. K záznamu s nejvyšší hodnotou score samozřejmě neexistují záznamy s vyšší hodnotou, a tak by se tento záznam ve výsledku vůbec neobjevil (propojovací podmínka v části WHERE by nebyla splněna). Obejdeme to použitím neostré nerovnosti:

select a.id as id
from tab as a, tab as b
where a.score<=b.score...

Nyní máme pro každý záznam tabulky vygenerováno tolik řádků, kolik záznamů z tabulky má vyšší nebo stejnou hodnotu. Řádky tedy seskupíme pro každý záznam (pomocí klíče id) a spočítáme jejich počet:

select a.id as id, count(*) as počet
from tab as a, tab as b
where a.score<=b.score
group by id;

Pokud by každý záznam nabýval různé hodnoty pole score, jsme u cíle – v poli pocet je pro každý záznam přesné pořadí. Abychom vypsali jen záznam s pořadím N, stačí připojit omezující klauzuli HAVING:

select a.id as id, count(*) as pocet
from tab as a, tab as b
where a.score<=b.score
group by id
having pocet=N;

Ale co když víc záznamů nabývá stejné hodnoty? Pak by se v poli pocet číslo N vůbec nemuselo objevit, mohly by tam být například hodnoty 2, 2, 5, 5, 5, 9, 9, 9, 9, ... Navíc se nám posune číslování pořadí: budou-li dva záznamy mít maximální hodnotu score, dosáhnou v poli pocet hodnoty 2 a ne 1, jak bychom chtěli.

Řešením je zjistit ještě počet záznamů, který má stejnou hodnotu jako aktuální záznam. To se dá spočítat tak, že pro každý řádek výsledku otestujeme, zda platí rovnost, počet je pak dán součtem logických jedniček a nul:

select a.id as id, count(*) as pocet, sum(a.score=b.score) as stejne
from tab as a, tab as b
where a.score<=b.score
group by id;

Teď už můžeme spočítat správné pořadí: je to počet záznamů s vyšší nebo stejnou hodnotou (pocet) minus počet záznamů se stejnou hodnotou (stejne) plus 1:

select a.id as id, count(*) as pocet, sum(a.score=b.score) as stejne,
count(*)–sum(a.score=b.score)+1 as poradi
from tab as a, tab as b
where a.score<=b.score
group by id;

A jak nyní vyřešit výpis záznamu (či záznamů), který (které) jsou v pořadí N-tý (N-té)? Pro takové záznamy platí zároveň dvě podmínky:

• mají pořadí menší nebo rovno N
• pořadí plus počet záznamů se stejnou hodnotou je větší než N

Připojíme tedy opět HAVING s těmito dvěma podmínkami – a máme hotovo:

select a.id as id, count(*) as pocet, sum(a.score=b.score) as stejne,
count(*)–sum(a.score=b.score)+1 as poradi
from tab as a, tab as b
where a.score<=b.score
group by id
having (poradi<=N and poradi+stejne>N);

Poznamenávám, že propojení tabulek by bylo efektivnější pomocí JOIN, ale takhle je to trochu názornější, proto jsem zvolil uvedený způsob.