Roulement de Sommes dans SQL - un Exemple Pratique

Image non disponible


précédentsommairesuivant

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 donné 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
Sélectionnez
SELECT 
  MONTHNAME(created) AS Month
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created);

Quoiqu'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èse et donnons lui un alias.

Commande sql 4
Sélectionnez
(
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
Sélectionnez
(
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 pseudo-code, quelque chose comme ceci :

Commande sql 6
Sélectionnez
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 pseudo code 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érieure et remplacer «key» par le vrai nom de champs.
  • Remplacer le pseudo-code 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
Sélectionnez
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 tout les mois précédent et incluant le mois en cour dans x1.
De plus, j'ai aussi mis une dépendance (sur le champs 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
Sélectionnez
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
Sélectionnez
+---------+-----------+-------+--------------+
| 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
Sélectionnez
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
Sélectionnez
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)

précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

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.