Roulement de Sommes dans SQL - un Exemple Pratique

Aujourd'hui Lenz réunissait quelques stats sur les commentaires de PlanetMySQL depuis janvier de cette année, et a demandé dans un courrier électronique s'il devrait inclure des totaux dans les stats. J'ai répondu oui, et j'ai proposé une solution SQL rapide pour sortir ses données de la base. J'ai pensé que cela pourrait être utile pour d'autres, donc allons-y.

Les roulements de sommes (ou moyennes) sont une façon d'inclure le courant ou roulement d'agrégat de colonnes dans la sortie de votre SQL. Par exemple disons que vous voulez lister des totaux de commandes, groupées par catégories de produits, mais dans chaque ligne de la sortie finale, vous voulez inclure le total courant de toutes les lignes dans la sortie avant et incluant la ligne courante. Une somme courante devrait vous autoriser à faire un tel calcul.

Dans l'exemple spécifique d'aujourd'hui, Lenz a la commande suivante qui liste le mois et le nombre de commentaires pour chaque mois :

SELECT 
  MONTHNAME(created) AS Month
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created);

+----------+-------+
| Month    | Added |
+----------+-------+
| January  |     1 |
| February |     1 |
| March    |    11 |
| April    |     8 |
| May      |    18 |
| June     |     3 |
+----------+-------+
6 rows in set (0.00 sec)

Bien sûr, cette sortie est bonne, mais si nous voulions montrer le nombre total de commentaires sur la Planète, avec le nombre de nouveaux commentaires dans le mois ? Ici, une somme courante entre en jeu. Pour collecter une somme courante, une technique qui joint un jeu de résultats à lui-même est utilisée.
Essentiellement, vous voulez joindre une table (ou un jeu de résultat groupé) à lui-même a l'aide d'une condition de jointure « Supérieur ou égal ».

La configuration basique se fait comme ceci :

SELECT 
  x1.key
, x1.some_column
, AGGREGATE_FN(x2.some_column) AS running_aggregate
FROM x AS x1
INNER JOIN x AS x2
ON x1.key >= x2.key
GROUP BY x1.key;

En joignant le jeu à lui-même le long du prédicat de rang, l'agrégat sur le second jeu de some_column AGGREGATE_FN(x2.some_column) inclut seulement les lignes avant et celle courante dans la sortie primaire.

Pour revenir sur notre exemple terre à terre de PlanetMySQL, nous allons traiter avec la seule chose qui fait des sommes courantes rusées : quand vous produisez des sommes courantes sur des jeux déjà agrégés. Dans le code original de Lenz vous noterez que nous agrégeons déjà sur MONTH(created). L'observateur parmi vous aura déjà vu le problème en essayant de produire une somme courante sur le jeu d'informations déjà groupé. Vous ne pouvez pas avoir deux GROUP BY dans un seul SELECT.
Ce qui veut dire, si nous devons suivre la configuration basique de la commande 2, que nous devrions avoir quelque chose comme ceci :

SELECT 
  MONTHNAME(x1.created) AS Month
, COUNT(x1.*) AS Added
, SUM(COUNT(x2.*)) AS running_sum
FROM feeds x1
INNER JOIN feeds x2
ON x1.Month >= x2.Month
WHERE x1.created >= '2007-01-01'
GROUP BY MONTH(x2.created)
GROUP BY MONTH(x1.created);

bien sûr, la commande 3 n'a aucun sens du point de vue SQL. Vous ne pouvez évidemment pas avoir plus qu'un niveau d'agrégat (GROUP BY) dans une seule expression SELECT.

Donc, comment allons-nous contourner le problème ? En utilisant une table dérivées ou une sous-requête dans la close FROM.