Roulement de Sommes dans SQL - un Exemple Pratique

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

Damien SeguySite de Damien Seguy a une observation intéressante sur mon utilisation de l'autojointure pour la génération du roulement de sommesArticle 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 :

Sélectionnez

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 :

Sélectionnez

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

Sélectionnez

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…

Sélectionnez

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.

Sélectionnez

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)

Sélectionnez

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 :

Sélectionnez

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)

Sélectionnez

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 :

Sélectionnez

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')Définition de la fonction DATEFORMAT 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 :

Sélectionnez

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

Sélectionnez

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

Sélectionnez

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

Sélectionnez

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 :

Sélectionnez

, @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.

Copyright © 2008 developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site ni 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.