Šuplík Honzy Hučína

Skok na navigaci (menu)

Tento blog je v současné době uzavřen.
Pokračování blogu na bloguje.cz jsem zrušil. Pokud budu někdy pokračovat, bude to spíš tady.

Komentovaný článek

Internet a vše kolem
5. 10. 2004

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.

Vložit vlastní komentářNávrat k článkuRSS komentářů tohoto článku

Komentáře

[1] 5. 10. 2004, 13:54 – dgx (Odkaz)

SELECT score FROM tab GROUP BY score (ASC|DESC)

seek (7), přečteme záznam a víme, že 7. nejnižší|nejvyšší skore je "N"

SELECT * FROM tab WHERE score = N

[2] 5. 10. 2004, 18:34 – MaD

Jo, jo. jden z duvodu, proc misto MySQL pouzit databazi ;-), tohle je jasnej ukol pro poddotaz. Verze pro MSSQL: select * from tab where score=(select top 1 score from (select top N score from tab order by score) a order by score desc)

A to jeste MSSQL neumi slusnej LIMIT, takze se jednoho Ntyho skore musim dobrat pres dva topy. V jinejch databazich by to mohlo bejt jeste o to jednodussi.

[3] 5. 10. 2004, 19:41 – Honza Hučín (Odkaz)

Ad [1] Jenže to není jedním SQL dotazem :-)

Ad [2] Ano, poddotazy to samozřejmě řeší také. BTW MySQL od jakési verze (4.1? 5? teď nevím přesně) poddotazy umí také.
Navržené řešení ad 2 nepočítá pro každý záznam pořadí, jenom hledá N-tý záznam.

[4] 7. 10. 2004, 00:58 – dgx (Odkaz)

[3] máš pravdu, jedním dotazem to není. V "zadání" (první odstavec článku) se to ani nepožadovalo :-)

Je třeba trošku uvažovat nad tím, kolik strojového času úkol zabere. Aby režie dramaticky nepřevýšila samotné řešení ;-)

[5] 30. 7. 2012, 23:39 – Honza

Pěkný článek , jako vždy škoda že jsi s blogováním skončil.

K tomuto článku není možné vkládat komentáře.

© Honza Hučín 2004–6

Šuplík běží na PIPNI.CZ. Díky!

sber.cermat.cz

RSS Šuplíku

RSS komentářů – souhrnně

U každého článku je samostatný RSS kanál pro komentáře.

Výběr článků

Posledních 10 článků

nebo podle data:

nebo hledání fulltextem:

Archiv všech článků

Poslední komentáře

Zabili mě, parchanti [2]

8. 8. 16:48 | Pepa

Cestou kolem blogu [2]

7. 8. 21:26 | Honza Hučín

Cestou kolem blogu [1]

7. 8. 21:02 | Honza

Vrtulník nad hlavou [3]

6. 8. 14:29 | Pepa

Taková hra na volby [1]

3. 8. 18:29 | Honza

Nejčtenější

Cestou kolem blogu (1)

O mně

*1967, absolvent MFF UK v Praze (1991)

statistik, analytik, programátor, učitel, hudebník

nyní Ústav pro informace ve vzdělávání

Životopis (RTF)

Napište mi