IdentifiantMot de passe
Mot de passe oublié ?Je m'inscris ! (gratuit)

Roulement de Sommes dans SQL - un Exemple Pratique

Image non disponible

Roulement de Sommes dans SQL - un Exemple Pratique

Date de publication : 05/08/2008


 

III. Observation intéressante l'utilisation de l'auto-jointure


III. Observation intéressante l'utilisation de l'auto-jointure

fr Damien Seguy a une observation intéressante sur mon utilisation de l'auto-jointure pour la génération du fr 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 auto-jointure. Cependant, il y a quelques complications.
Premièrement, jetons un oeil à 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(*) recquiert 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 oeil à 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 constatez 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 auto-joiture est entièrement de votre avis. Les deux manière 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'auto-jointure, et ceci pour les situations où vous voulez plus qu'une somme courante, mais aussi si vous voulez d'autres aggrégats courants. Un exemple courant serait si je voudrais obtenir la moyennne courante des flux ajoutés à PlanetMySQL sur la durée de vie de l'historique Planet. Utilisant une auto-jointure, 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 ene une version légèrement modifiée des totaux courants SQL avec une auto-jointure 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 en 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 auto-jointure é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'auto-jointure 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 cette article, Jay accepte les commentaire sur son blog, en 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.