MySQL group by trik

Představte si, že máme aplikaci jako #stokliku, kde si uživatel může zaznamenávat, kolik udělal kliků.

Data jsou uložena ve dvou jednoduchých tabulkách:

user:     id, name
exercise: userId, date, count

Někde budeme chtít vypsat seznam všech zúčastněných společně s celkovým počtem a maximem kliků. To není žádný problém, bude nám stačit jednoduchý dotaz:

select
  u.name,
  sum(e.count),
  max(e.count)
from user u
join exercise e on e.userId = u.id
group by u.id

Ale teď někdo dostane nápad, že by bylo skvělé, kdyby se ukazovalo i maximum v posledních čtyřech týdnech.

A tady nastává maličký problém.

Pokud dotaz omezíme where datediff(now(), e.date) < 28, pak vypíše jenom data v posledních 4 týdnech. Ale my bychom potřebovali, aby vypsal souhrn všech cvičení a zároveň souhrn za poslední 4 týdny.

Potřebovali bychom něco jako:

select
  u.name,
  sum(e.count),
  max(e.count)
  max(e.count where datediff(now(), e.date) < 28) -- tohle by se nám hodilo
from user u
join exercise e on e.userId = u.id
group by u.id

Ale to v MySQL nejde. Skoro to vypadá, jako bychom se museli uchýlit ke dvěma dotazům.

Právě teď je čas vytáhnout z rukávu jeden trik:

select
  u.name,
  sum(e.count),
  max(e.count)
  max((datediff(now(), e.date) < 28) * e.count) -- magic!
from user u
join exercise e on e.userId = u.id
group by u.id

Kouzlo spočívá v tom, že SQL je věrné tradici céčka a boolovské hodnoty jsou špatně maskované integery. True je ve skutečnosti 1 a false 0.

A to je celé.

Pokud se necítíte dost odvážně, je možné použít o něco příčetnější variantu:

max(if(datediff(now(), e.date) < 28, e.count, 0))

Takhle je možné v jednom dotazu provádět agregace na celých datech a zároveň na jejich libovolných částech.