GROUP BY

Školení, která pořádám

Nejčastější SQL příkaz SELECT má kromě obvyklých komponent FROM, WHERE, ORDER BY a LIMIT také méně obvyklé části GROUP BY a HAVING. GROUP BY slouží k seskupení řádek do jedné skupiny podle daného kritéria, HAVING slouží k omezení skupin, které mají být vráceny. Vhodným použitím GROUP BY lze vytvořit elegantnější kód, HAVING jsem naproti tomu u webových aplikací v praxi použil jen zřídka. Seskupování se obvykle používá spolu s agregačními funkcemi.

Dejme tomu, že chceme vypsat všechny články spolu s počtem diskusních příspěvků, které se k nim vztahují, a datem posledního z nich.

<?php
// neefektivní kód
$result = mysql_query("SELECT id, nadpis FROM clanky");
while ($row = mysql_fetch_assoc($result)) {
    $pocet = mysql_result(mysql_query("SELECT COUNT(*) FROM diskuse WHERE clanek = '$row[id]'"), 0);
    $posledni = mysql_result(mysql_query("SELECT MAX(datum) FROM diskuse WHERE clanek = '$row[id]'"), 0);
    echo "<li><a href='?id=$row[id]'>$row[nadpis]</a> - diskuse: $pocet" . (isset($posledni) ? ", poslední: $posledni" : "") . "</li>\n";
}
mysql_free_result($result);

// s využitím GROUP BY
$result = mysql_query("
    SELECT clanky.id, clanky.nadpis, COUNT(diskuse.id) AS pocet, MAX(diskuse.datum) AS posledni
    FROM clanky
    LEFT JOIN diskuse ON clanky.id = diskuse.clanek
    GROUP BY clanky.id
");
while ($row = mysql_fetch_assoc($result)) {
    echo "<li><a href='?id=$row[id]'>$row[nadpis]</a> - diskuse: $row[pocet]" . (isset($row["posledni"]) ? ", poslední: $row[posledni]" : "") . "</li>\n";
}
mysql_free_result($result);
?>

První kód je neefektivní proto, že uvnitř cyklu se opakovaně kladou SQL dotazy, což je relativně pomalá operace. V kódu si všimněte podmínky vypisování data posledního příspěvku – pokud totiž žádný příspěvek do diskuse k článku ještě vložen nebyl, vrátí MAX hodnotu NULL, která je převedena na PHP hodnotu null.

Sluší se zmínit, že v SQL lze podle normy vybírat jen sloupce, podle kterých se seskupuje nebo na které je aplikována agregační funkce (tedy místo SELECT id, nazev … GROUP BY id je nutné psát SELECT id, nazev … GROUP BY id, nazev nebo SELECT id, MAX(nazev) … GROUP BY id). MySQL tak přísné není a dovoluje vybírat jakékoliv sloupce, do kterých dosadí náhodnou hodnotu ze skupiny – pokud tedy seskupujeme podle unikátního klíče, můžeme se na správnou hodnotu sloupce spolehnout, v jiném případě je lepší se této zkratce vyhnout.

Od MySQL 4.1.1 je k dispozici modifikátor ROLLUP, který vy mně vyvolává dojem, že si ho někdo v MySQL zaplatil.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Výuka, 4.7.2005, diskuse: 4 (nové: 0)

Diskuse

cita:

Jakým způsobem se řeší v aplikační vrstvě funkce modifikátoru ROLLUP?

4.7.2005 13:27:45

Jakub Vrána :

Třeba takhle:

<?php
$groupby = array("year", "country", "product");
$data = "SUM(profit)";
$rollup = array_fill(0, count($groupby), 0); // nasčítané hodnoty
$last = array(); // poslední viděné hodnoty

$result = mysql_query("SELECT " . implode(", ", $groupby) . ", $data FROM sales GROUP BY " . implode(", ", $groupby) . "");
echo implode("\t", $groupby) . "\t$data\n";
while (true) {
$row = mysql_fetch_row($result);
// zobrazení ROLLUP údajů
$print = ""; // větší skupiny se přidávají až za menší
for ($i=1; $i < count($groupby); $i++) {
if ($print || !isset($last[$i-1]) || $last[$i-1] !== $row[$i-1]) {
if (isset($last[$i-1])) {
$print = $rollup[$i] . "\n$print";
for ($j = count($groupby); $j--; ) {
$print = ($j < $i ? $last[$j] : "NULL") . "\t$print";
}
$rollup[$i] = 0;
}
}
}
echo $print;
if (!$row) {
break;
}
// aktualizace ROLLUP údajů
for ($i=0; $i < count($groupby); $i++) {
$last[$i] = $row[$i];
$rollup[$i] += $row[count($groupby)];
}
echo implode("\t", $row) . "\n";
}
mysql_free_result($result);
echo str_repeat("NULL\t", count($groupby)) . $rollup[0] . "\n";
?>

Funguje to pouze pro SUM a COUNT, pro ostatní agregační funkce by se kód musel upravit.

7.7.2005 15:19:09

dgx:

> ...vyvolává dojem, že si ho někdo v MySQL zaplatil.

Vždyť to je v pořádku, z něčeho se vývoj MySQL financovat musí.

5.7.2005 20:00:03

spaze:

taky jsem chtěl reagovat, ale pak jsem si nebyl úplně jist, jestli ten _dojem_ je negativní. Takovej qmailadmin svoje sponzory taky uvádí "Thanks to PinkRoccade Public Sector for sponsoring this feature." ;)

6.7.2005 17:58:47

Diskuse je zrušena z důvodu spamu.