Roulement de Sommes dans SQL - un Exemple Pratique

| Jay Pipes | Joris Crozier (Traducteur) | Arnaud Feltz (Traducteur) |

Cet article est la traduction de Rolling Sums in SQL - A Practical Example (disponible ici) ainsi que de Follow Up on Rolling Sums - Self Joins vs User Variables (disponible ici) et a pour but de vous montrer les sommes courantes en SQL avec un exemple pratique. ♪

- Titre : Roulement de Sommes dans SQL - un Exemple Pratique

- Auteurs : Jay Pipes, Joris Crozier (Traducteur), Arnaud Feltz (Traducteur)

- Parution : 5 août 2008

- Licence : Copyright ® 2008 Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- Introduction

- Utiliser des sous-requêtes dans la clause FROM pour doubler les agrégats

- Observation intéressante sur l'utilisation de l'autojointure

Introduction

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 :

Commande sql 1

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

Listing 1

+----------+-------+
| 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 :

Commande sql 2

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 :

Commande sql 3

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.

Utiliser des sous-requêtes dans la clause FROM pour doubler les agrégats

Les sous-requêtes dans la clause FROM sont parfois mentionnées comme des tables virtuelles ou dérivées. Elles représentent un jeu d'informations, dans son ensemble, qui peut être joint et sélectionné depuis une table normale. Pour faire une sous-requête dans la clause FROM, entourez simplement l'expression SELECT avec des parenthèses et donnez un alias ou un nom pour la table dérivée.

Retournons à notre exemple, ceci est le jeu d'informations que nous voulons éventuellement joindre à notre rand de prédicat.

Commande sql

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

Quoi qu'il en soit, comme constaté précédemment, puisque notre SELECT contient déjà un GROUP BY, nous devons utiliser une table dérivée pour générer une somme courante. Pour faire ceci, nous allons construire la commande SQL complexe par étapes.

D'abord, entourons notre expression SELECT avec des parenthèses et donnons-lui un alias.

Commande sql 4

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

Ensuite, en suivant la formule générale donnée dans la commande 2, nous allons créer un second jeu identique pour joindre au premier jeu. Donc nous utilisons une autre table dérivée et nous lui donnons un alias x2 cette fois :

Commande sql 5

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

Ensuite nous voulons appliquer la condition de jointure, pour ce faire, bloquons une expression SELECT extérieure, et une clause FROM contant les deux tables dérivées et une condition de jointure.

En pseudocode, quelque chose comme ceci :

Commande sql 6

SELECT ...
FROM
( x1 SELECT... ) AS x1
INNER JOIN ( x2 SELECT ) AS x2
ON x1.key >= x2.key
GROUP BY x1.key;

En écrivant le SQL en pseudocode comme dans la commande 6, vous vous faites une meilleure idée de comment les pièces fonctionnent entre elles, sans s'enliser dans une expression SQL plus longue, plus complexe.

Les seules choses qu'il reste à faire à partir de maintenant sont :

- inclure les champs SELECT dans le SELECT extérieur et remplacer « key » par le vrai nom de champs ;

- remplacer le pseudocode utilisé pour la table dérivée de l'expression SELECT par le vrai code SQL.

Dans l'ordre, allons-y.
D'abord incluant les champs SELECT dans l'expression SELECT extérieure.

Commande sql 7

SELECT
x1.MonthNo
, x1.MonthName
, x1.Added
, SUM(x2.Added) AS RunningTotal
FROM
( x1 SELECT... ) AS x1
INNER JOIN ( x2 SELECT ) AS x2
ON x1.MonthNo >= x2.MonthNo
GROUP BY x1.MonthNo;

L'expression extérieure SELECT doit contenir le champ clé depuis x1 (MonthNo), le champ Added depuis x1 (qui contient le nombre de commentaires par mois), et la somme des jeux de valeurs de Added depuis x2, qui contient le nombre de commentaires pour tous les mois précédents et incluant le mois en cours dans x1.
De plus, j'ai aussi mis une dépendance (sur le champ onMonth), MonthName , depuis x1 dans un but décoratif.

L'étape finale est de remplacer la table dérivée par le code SQL actuel.

Commande sql 8

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

Voila , maintenant nous avons une somme courante.

Listing 9

+---------+-----------+-------+--------------+
| MonthNo | MonthName | Added | RunningTotal |
+---------+-----------+-------+--------------+
|       1 | January   |     1 |            1 |
|       2 | February  |     1 |            2 |
|       3 | March     |    11 |           13 |
|       4 | April     |     8 |           21 |
|       5 | May       |    18 |           39 |
|       6 | June      |     3 |           42 |
+---------+-----------+-------+--------------+
6 rows in set (0.00 sec)

Il y a juste un petit problème dans la requête, elle ne montre pas en réalité le nombre total de commentaires sur PlanetMySQL, seulement ceux ajoutés en 2007.

Et si nous voulions afficher le nombre total de commentaires ? Facile, nous pouvons utiliser une variable utilisateur contenant le nombre de commentaires non ajoutés en 2007 et l'ajouter au total courant.

Voici la commande SQL finale et son résultat. En espérant que cela aidera d'autres personnes.

Commande sql 10

SELECT COUNT(*) INTO @2006total FROM feeds WHERE created < '2007-01-01';

SELECT
x1.MonthNo
, x1.MonthName
, x1.Added
, SUM(x2.Added) + @2006total AS RunningTotal
FROM
(
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x1
INNER JOIN (
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x2
ON x1.MonthNo >= x2.MonthNo
GROUP BY x1.MonthNo;

Résultat final

mysql> SELECT @2006total;
+------------+
| @2006total |
+------------+
| 159        |
+------------+
1 row in set (0.00 sec)
<snip>
+---------+-----------+-------+--------------+
| MonthNo | MonthName | Added | RunningTotal |
+---------+-----------+-------+--------------+
|       1 | January   |     1 |          160 |
|       2 | February  |     1 |          161 |
|       3 | March     |    11 |          172 |
|       4 | April     |     8 |          180 |
|       5 | May       |    18 |          198 |
|       6 | June      |     3 |          201 |
+---------+-----------+-------+--------------+
6 rows in set (0.00 sec)

Observation intéressante sur l'utilisation de l'autojointure

Damien Seguy a une observation intéressante sur mon utilisation de l'autojointure pour la génération du roulement de sommes.

Qu'en est-il à propos de l'utilisation d'une variable utilisateur pour faire le roulement de sommes depuis le début? Quelque chose de simple comme ceci :

set @sum := 159;
// Numéro initial du post, ou 0 si vous préférez.
set @mn := 0;
// Numéro du mois
select @mn := @mn + 1 as MonthNumber Month, Added, @sum := @sum + Added as RunningTotal from ResultListing1;

Damien a, bien sûr, raison sur le fait que j'aurais pu utiliser une variable utilisateur comme ci-dessus pour générer le roulement de sommes au lieu d'utiliser une autojointure. Cependant, il y a quelques complications.
Premièrement, jetons un œil à ce qu'il arrive si je réécris la requête originelle GROUP BY de Lenz afin d'y inclure une variable utilisateur pour calculer les totaux courants :

mysql> SELECT COUNT(*) INTO @running_total
-> FROM feeds WHERE created < '2007-01-01';
Query OK, 1 row affected (0.00 sec)

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

+----------+-------+--------------+
| Month    | Added | RunningTotal |
+----------+-------+--------------+
| January  |     1 |          160 |
| February |     1 |          160 |
| March    |    11 |          170 |
| April    |     8 |          167 |
| May      |    18 |          177 |
| June     |     4 |          163 |
+----------+-------+--------------+
6 rows in set (0.00 sec)

Comme vous pouvez le constater, nous avons quelques problèmes. Clairement, la variable utilisateur @running_total n'est pas calculée correctement quand elle est utilisée à la fois comme sortie et comme affectation dans la même colonne. Observons maintenant ce qu'il se passe si je sors la variable utilisateur d'affectation dans une colonne séparée…

mysql> SELECT COUNT(*) INTO @running_total
-> FROM feeds WHERE created < '2007-01-01';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @running_total;

+----------------+
| @running_total |
+----------------+
| 159            |
+----------------+
1 row in set (0.00 sec)

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

+----------+-------+---------------------------------------------+--------------+
| Month    | Added | @running_total := @running_total + COUNT(*) | RunningTotal |
+----------+-------+---------------------------------------------+--------------+
| January  |     1 |                                         160 |          159 |
| February |     1 |                                         160 |          159 |
| March    |    11 |                                         170 |          159 |
| April    |     8 |                                         167 |          159 |
| May      |    18 |                                         177 |          159 |
| June     |     4 |                                         163 |          159 |
+----------+-------+---------------------------------------------+--------------+
6 rows in set (0.00 sec)

Encore un coup de dé. Cepdenant, dans la sortie précédente, vous pouvez voir plus clairement ce qui arrive à la requête : le calcul du COUNT(*) est réalisé durant la phase du GROUP BY, qui intervient après la sélection des lignes de données.
Malheureusement, le calcul d'une variable utilisateur d'affectation est réalisé pendant la phase de sélection des lignes, et depuis que l'affectation de @running_total à lui-même plus le groupement du COUNT(*) requiert des informations non disponibles durant la phase de sélection des lignes, l'affectation n'est pas faite contrairement à nos attentes. Pour démontrer ceci plus loin, jetons un œil à ce qui arrive si nous avons ajoutons un nombre constant, et non le résultat du COUNT(*) à la variable @running_total, dans une colonne séparée.

mysql> SELECT COUNT(*) INTO @running_total
-> FROM feeds WHERE created < '2007-01-01';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @running_total;

+----------------+
| @running_total |
+----------------+
| 159            |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT
->   MONTHNAME(created) AS Month
-> , COUNT(*) AS Added
-> , @running_total := @running_total + 1
-> , @running_total AS RunningTotal
-> FROM feeds
-> WHERE created >= '2007-01-01'
-> GROUP BY MONTH(created);

+----------+-------+--------------------------------------+--------------+
| Month    | Added | @running_total := @running_total + 1 | RunningTotal |
+----------+-------+--------------------------------------+--------------+
| January  |     1 |                                  160 |          159 |
| February |     1 |                                  161 |          159 |
| March    |    11 |                                  162 |          159 |
| April    |     8 |                                  163 |          159 |
| May      |    18 |                                  164 |          159 |
| June     |     4 |                                  165 |          159 |
+----------+-------+--------------------------------------+--------------+
6 rows in set (0.01 sec)

Comme vous pouvez le constater ci-dessus, ajouter une constante à une variable utilisateur fonctionne comme voulu, résultant en une incrémentation du total courant. Mais depuis que l'expression COUNT(*) est évaluée pendant la phase GROUP BY, l'affectation du total courant est faite par groupe, et par conséquent ne correspond pas à nos attentes d'un total courant.

Donc, comment pouvons-nous sortir de cette impasse tout en utilisant une variable utilisateur pour faire les sommes courantes ?
Bien, nous pourrions revenir à notre vieille amie, la table dérivée, pour forcer le calcul du GROUP BY à être réalisé en premier, et ensuite appliquer la technique de Damien. Et, comme vous pouvez le constater ci-dessous, ceci fonctionne très bien :

mysql> SELECT COUNT(*) INTO @running_total
-> FROM feeds WHERE created < '2007-01-01';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @running_total;

+----------------+
| @running_total |
+----------------+
| 159            |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT
->   Month
-> , Added
-> , @running_total := @running_total + Added AS RunningTotal
-> FROM (
-> SELECT
->   MONTHNAME(created) AS Month
-> , COUNT(*) AS Added
-> FROM feeds
-> WHERE created >= '2007-01-01'
-> GROUP BY MONTH(created)
-> ) AS t;

+----------+-------+--------------+
| Month    | Added | RunningTotal |
+----------+-------+--------------+
| January  |     1 |          160 |
| February |     1 |          161 |
| March    |    11 |          172 |
| April    |     8 |          180 |
| May      |    18 |          198 |
| June     |     4 |          202 |
+----------+-------+--------------+
6 rows in set (0.00 sec)

Le fait que vous considérez l'exemple précédent comme plus simple ou plus lisible que la technique de autojoiture est entièrement de votre avis. Les deux manières donnent des résultats identiques.

Eh! Mais qu'en est-il si je voulais une moyenne courante?

Mais, il y a plus d'une raison qui fait que je m'en tienne à la technique de l'autojointure, et ceci pour les situations où vous voulez plus qu'une somme courante, mais aussi si vous voulez d'autres agrégats courants. Un exemple courant serait si je voulais obtenir la moyenne courante des flux ajoutés à PlanetMySQL sur la durée de vie de l'historique Planet. Utilisant une autojointure, le code ressemble à ceci :

mysql> SELECT
->   x1.YearMonth
-> , x1.MonthName
-> , x1.Added
-> , SUM(x2.Added) AS RunningTotal
-> , FLOOR(AVG(x2.Added)) AS RunningAvg
-> FROM
-> (
-> SELECT
->   DATE_FORMAT(created, '%Y-%m') AS YearMonth
-> , MONTHNAME(created) AS MonthName
-> , COUNT(*) AS Added
-> FROM feeds
-> GROUP BY  DATE_FORMAT(created, '%Y-%m')
-> ) AS x1
-> INNER JOIN (
-> SELECT
->   DATE_FORMAT(created, '%Y-%m') AS YearMonth
-> , MONTH(created) AS MonthNo
-> , MONTHNAME(created) AS MonthName
-> , COUNT(*) AS Added
-> FROM feeds
-> GROUP BY  DATE_FORMAT(created, '%Y-%m')
-> ) AS x2
-> ON x1.YearMonth >= x2.YearMonth
-> GROUP BY x1.YearMonth;

+-----------+-----------+-------+--------------+------------+
| YearMonth | MonthName | Added | RunningTotal | RunningAvg |
+-----------+-----------+-------+--------------+------------+
| 2005-07   | July      |    32 |           32 |         32 |
| 2005-08   | August    |    10 |           42 |         21 |
| 2005-09   | September |    15 |           57 |         19 |
| 2005-10   | October   |     6 |           63 |         15 |
| 2005-11   | November  |     5 |           68 |         13 |
| 2005-12   | December  |     7 |           75 |         12 |
| 2006-01   | January   |     1 |           76 |         10 |
| 2006-02   | February  |     4 |           80 |         10 |
| 2006-03   | March     |    20 |          100 |         11 |
| 2006-04   | April     |     7 |          107 |         10 |
| 2006-05   | May       |     5 |          112 |         10 |
| 2006-06   | June      |    12 |          124 |         10 |
| 2006-07   | July      |     5 |          129 |          9 |
| 2006-08   | August    |    12 |          141 |         10 |
| 2006-09   | September |     6 |          147 |          9 |
| 2006-10   | October   |     5 |          152 |          9 |
| 2006-11   | November  |     4 |          156 |          9 |
| 2006-12   | December  |     3 |          159 |          8 |
| 2007-01   | January   |     1 |          160 |          8 |
| 2007-02   | February  |     1 |          161 |          8 |
| 2007-03   | March     |    11 |          172 |          8 |
| 2007-04   | April     |     8 |          180 |          8 |
| 2007-05   | May       |    18 |          198 |          8 |
| 2007-06   | June      |     4 |          202 |          8 |
+-----------+-----------+-------+--------------+------------+
24 rows in set (0.00 sec)

La requête SQL précédente est une version légèrement modifiée des totaux courants SQL avec une autojointure que j'ai démontrée dans l'article d'hier. Les seuls changements sont que j'ai supprimé la clause WHERE dans la sous-requête qui limite le jeu de données aux seuls flux de 2007 et j'ai changé MONTH(created) par DATE_FORMAT(created, '%Y-%m') afin d'y inclure également l'année.

Notez qu'il n'y a pas que ce qui est cité précédemment qui change, tout ce que j'avais à faire pour obtenir une moyenne courante avec la même autojointure était d'ajouter cette ligne à la clause SELECT :

, FLOOR(AVG(x2.Added)) AS RunningAvg

La fonction FLOOR() tronque simplement à partir du point décimal de la moyenne et procède à un arrondi inférieur. Mais, il est tellement simple d'ajouter un calcul de moyenne courante à ce bout de requête. Comment aurions-nous procédé en utilisant les variables utilisateurs?
Bien, ça devient légèrement compliqué, puisque les moyennes se basent sur le nombre total d'objets étant moyennés, ce qui nécessite dans une variable utilisateur séparée. Voici comment procéder en utilisant les variables utilisateurs :

mysql> SET @num_rows := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @running_total := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
->   YearMonth
-> , Added
-> , @running_total := @running_total + Added AS RunningTotal
-> , @running_avg := FLOOR(((@running_total - Added) + Added)
->   / (@num_rows := @num_rows + 1)) AS RunningAvg
-> FROM (
-> SELECT
->   DATE_FORMAT(created, '%Y-%m') AS YearMonth
-> , COUNT(*) AS Added
-> FROM feeds
-> GROUP BY DATE_FORMAT(created, '%Y-%m')
-> ) AS t;

+-----------+-------+--------------+------------+
| YearMonth | Added | RunningTotal | RunningAvg |
+-----------+-------+--------------+------------+
| 2005-07   |    32 |           32 |         32 |
| 2005-08   |    10 |           42 |         21 |
| 2005-09   |    15 |           57 |         19 |
| 2005-10   |     6 |           63 |         15 |
| 2005-11   |     5 |           68 |         13 |
| 2005-12   |     7 |           75 |         12 |
| 2006-01   |     1 |           76 |         10 |
| 2006-02   |     4 |           80 |         10 |
| 2006-03   |    20 |          100 |         11 |
| 2006-04   |     7 |          107 |         10 |
| 2006-05   |     5 |          112 |         10 |
| 2006-06   |    12 |          124 |         10 |
| 2006-07   |     5 |          129 |          9 |
| 2006-08   |    12 |          141 |         10 |
| 2006-09   |     6 |          147 |          9 |
| 2006-10   |     5 |          152 |          9 |
| 2006-11   |     4 |          156 |          9 |
| 2006-12   |     3 |          159 |          8 |
| 2007-01   |     1 |          160 |          8 |
| 2007-02   |     1 |          161 |          8 |
| 2007-03   |    11 |          172 |          8 |
| 2007-04   |     8 |          180 |          8 |
| 2007-05   |    18 |          198 |          8 |
| 2007-06   |     4 |          202 |          8 |
+-----------+-------+--------------+------------+
24 rows in set (0.00 sec)

Comme vous pouvez le voir, il est possible d'obtenir des moyennes courantes avec des variables utilisateurs, mais il s'agit d'une délicate double affectation de variable utilisateur dans la colonne de calcul de la colonne RunningAvg :

, @running_avg := FLOOR(((@running_total - Added) + Added)
/ (@num_rows := @num_rows + 1)) AS RunningAvg

La première affectation est actuellement faite pour la variable utilisateur @num_rows dans une parenthèse interne. La seconde affectation (externe) est faite par la variable utilisateur @running_avg. Nous devons faire cela, car pour calculer la moyenne correctement, nous avons besoin de diviser le total des flux ajoutés par le nombre de mois (stockés dans la variable incrémentale @num_rows)…

Personnellement, pour des raisons telles que celles-ci, je préfère utiliser la technique de l'autojointure, car elle est plus lisible sur le long terme. Dans un article à venir, je vais regarder un benchmark de performances pour chaque méthode afin de voir s'il y a une différence notable…

Si vous désirez commenter cet article, Jay accepte les commentaires sur son blog, http://jpipes.com.