Mějme modelový projekt, předpokládejme, že máme články a k těm je možno přidávat komentáře. Součástí aplikace je zároveň funkce, která vrací poslední tři komentované články – nikoliv poslední 3 komentáře, ale poslední články.
Jak na to SQL příkazem, abychom nemuseli třídit data externě?
V tabulce article máme 3 články:
| id | name |
|---|---|
| 1 | Nový článek |
| 2 | Jiný článek |
| 3 | Další článek |
V tabulce comment máme 4 komentáře z různých dní:
| id | id_article | date | author |
|---|---|---|---|
| 1 | 3 | 2007-03-02 | xom |
| 2 | 1 | 2007-03-03 | myff |
| 3 | 2 | 2007-03-04 | xom |
| 4 | 2 | 2007-03-05 | lada |
Nelezl jsem dvě řešení, přesně nevím, které z nich je optimálnější, možná mi pomůžete v komentářích nebo vás napadne další možnost řešení.
První řešení je následující:
Použijeme funkci MAX, která nám vybere nejnovější datum a zároveň výsledek zgroupujeme podle názvu článku a srovnáme podle data.
SELECT
MAX(comment.date) AS date,
article.name
FROM
article LEFT JOIN comment ON article.id = comment.id_article
GROUP BY
article.name
ORDER BY
date DESC
LIMIT
3
| 2007-03-05 | Jiný článek |
| 2007-03-03 | Nový článek |
| 2007-03-02 | Další článek |
No jo, ale co když potřebujeme vybrat více položek z komentáře, pokud bychom chtěli primární klíč – id, tak není problém, jelikož se předpokládá, že bude mít nejvyšší číslo klíče pole, které má zároveň nejnovější – nejvyšší datum.
Funkci MAX(comment.id) musíme uvést, jelikož chceme filtrovat výsledky dle tabulky article, pokud bychom selectovali čistý comment.id, tak bychom ho museli zahrnout do definice GROUP BY. Tím by se nám výsledku znehodnotili na něco podobného:
| 2007-03-05 | Jiný článek | 4 |
| 2007-03-04 | Jiný článek | 3 |
| 2007-03-03 | Nový článek | 2 |
S použítím MAX(comment.id) a nezahrnutím do GROUP BY je výsledek optimální:
| 2007-03-05 | Jiný článek | 4 |
| 2007-03-03 | Nový článek | 2 |
| 2007-03-02 | Další článek | 1 |
Problém by nastal, pokud bychom chtěli vybrat jméno komentujícího comment.author, při použití funkce MAX by databáze přiřazovala k výsledku jméno, které by nemuselo patřit k vracenému datu komentáře.
Dostali jsme se nejspíš do slepé uličky v modifikaci této metody.
V SQL existuje deklarace DISTINCT, která odstraňuje duplicitní hodnoty. V příkladu:
SELECT DISTINCT
id_article
FROM
comment
Tento select by nám vrátil
| 1 |
| 2 |
| 3 |
Jenže při použití na náš příklad by nespatřoval žádný výsledek jako duplicitní, protože vybíráme hodnoty, které se nikdy nepřekrývají úplně.
Postgre SQL má funkci DISTINCT ON, která umožňuje filtrování duplicit přes definované sloupce.
Jasnou volbou je, že nechceme mít duplicitní hodnotu id_article, která je vzdáleným klíčem tabulky article.
SELECT
DISTINCT ON (comment.id_article)
comment.date,
article.name,
comment.author
FROM
article JOIN comment ON article.id = comment.id_article
ORDER BY
comment.id_article,
comment.date DESC
LIMIT
3
Nevýhodou tohoto řešení je nutnost uvést parametr funkce DISTINCT ON na prvním místě v deklaraci ORDER BY, což znamená, že máme ve výsledku 3 poslední komentáře 3 různých článků, ale neseřazených dle data.
| 2007-03-03 | Nový článek | myff |
| 2007-03-05 | Jiný článek | lada |
| 2007-03-02 | Další článek | xom |
Seřazení dle data jsem provedl vnořeným selectem..
SELECT
*
FROM (
SELECT
DISTINCT ON (comment.id_article)
comment.date,
article.name,
comment.author
FROM
article JOIN comment ON article.id = comment.id_article
ORDER BY
comment.id_article,
comment.date DESC
LIMIT 3
) AS tabulka
ORDER BY
date DESC
LIMIT
3
A výsledek je:
| 2007-03-05 | Jiný článek | lada |
| 2007-03-03 | Nový článek | myff |
| 2007-03-02 | Další článek | xom |
Vybrali jsme z databáze 3 nejnovější příspěvky ke 3 různým článkům v pořadí od nejnověji přidaného.
Znáte nějaký lepší způsob? Komentáře jsou k dispozici..
xom 1.6.07 11:56
|1| - reakce na Stanislav Duben: Jasně, tento způsob je jednodušší, ale neřeší to o čem jsem psal v článku.
Vaše popsané řešení vrací všechny poslední komentáře, nikoliv poslední komentované články..
Kvantifikátor TOP je navíc implementován v Microsoft SQL a uživatelé MySQL či Postgre SQL ho nevyužijí.
Andrew 6.6.07 13:31
xom 6.6.07 14:30
|3| - reakce na Andrew: To ano, ten jeho příklad projde, ale pokud rozšíříme vybírané sloupce třeba o datum komentáře, tak to již nefunguje, proto jsem psal o těch dvou metodách nahoře..
Tento kód: SELECT distinct article.name, comment.date FROM
article LEFT JOIN comment ON article.id= comment.id_article ORDER BY date DESC
LIMIT 3 již vybírá poslední komentáře (nikoliv poslední
články) a to jde – vybrat všechno najednou..
Andrew 6.6.07 15:05
Andrew 6.6.07 15:14
Tak mám dílčí úspěch. Předpokládám, že je to podle normy SQL, ale je to hnusná neoptimální fujka :-) Předpokládám, že pro každý článek to bude dělat extra subselect a jednotlivé komentáře s ním porovnánvat.
select * from article a join comment c on (c.id = (select max(id) from comment where id_article = a.id))
xom 7.6.07 02:17
Tak jsem provedl 10 měření s výběrem 3 sloupců comment.date, comment.author, article.name Andrewovou metodou a mou vskutku nestandardní metodou DISTINCT ON popisovanou výše..
Výběr 3 nejnověji komentovaných článků prováděla moje metoda v průměrném čase 3,241 ms (s extrémy 3,052 a 3,402), Andrewova varianta si vedla trochu hůře za průměrný čas 3,310 ms (s extrémy 3,210 a 3,482).
Tímto jsem chtěl vytvořit závěr, že jsou obě metody výkonnostně podobné, ale neučiním tak, protože mi to nedalo a změřil jsem si výkon s výběrem všech sloupců nad testovacími tabulkami, které měly v součtu 23 sloupců..
Při zachování rovnání dle data komentáře (vnořený select u metody v článku) byly výsledky obou metod o poznání jiné! xomova metoda dosáhla průměrného času 6,837 ms, přičemž Andrewova 6,192 ms!
Předpokládal jsem, že odstraněním rovnání dle data publikace se hodnoty přiblíží, ale opak byl pravdou a výsledky byly 4,704 ms pro xoma a 3,679 ms pro Andrewa. Rozdíl jedné milisekundy je již celkem znatelný.
Měření bylo prováděno na Postgre SQL 7.4.7
Andrew 7.6.07 18:58
No a jaké z toho plyne ponaučení? Že optimalizace dotazů je docela alchymie. Nikdy bych netipoval, že ta moje zrůdička bude rychlejší než tvůj dotaz :)
Stanislav Duben 1.6.07 10:48
Tohle je obojí zbytečně složité. Mnohem jednodušší cesta z hlediska zatížení DB serveru je:
SELECT distinct TOP 3 article.name FROM article LEFT JOIN comment ON article.id= comment.id_article ORDER BY date DESC
resp.
SELECT distinct article.name FROM article LEFT JOIN comment ON article.id= comment.id_article ORDER BY date DESC LIMIT 3