[SQL Server]Saga transact-SQL Episode 4 : Grouper et organiser les données
Parfois l’ensemble de résultat doit être présenté selon les souhaits du client et donc certaine mise en pages sont nécessaires.
Lister les n premières valeurs
Il s’agit de la clause TOP n dans la commande SELECT. On peut avoir ainsi les n premières lignes ou les n premier % des lignes.
On a les 10 premières lignes de la table en fonction de leur ProductID et de leur Nom de façon croissante sur les 2 colonne globalement, c’est pour ça qu’il est recommandé d’associer une clause ORDER BY aux commandes SELECT comprenant une clause TOP.
On a les 10 premières lignes correspondant aux 10 premiers ProductId de la table.
Ici on a utilisé la clause PERCENT donc on a les 10 premiers pourcent de la table soit non plus 10 lignes mais 55 lignes ordonnées par ProductID.
La clause WITH TIES est particulière :
- Elle se place après le TOP n
- Elle est fortement liée à la clause ORDER BY
- Elle permet d’ajouter, à l’ensemble de résultat, les lignes qui correspondent à la dernière ligne du TOP et dont la valeur de la colonne signalée dans la clause ORDER BY correspond.
Je crois qu’il vaut mieux un exemple :
On fait un top simple qui nous ramène 10 lignes.
On prend exactement la même requête et on ajoute la clause WITH TIES.
On a 94 lignes soit toutes les lignes de la table qui ont la valeur « color=’Black’ ». (J’espère que c’est plus clair sinon essayez le !! ça peut être utile.).
Les fonctions d’agrégation
Ce sont des fonctions SQL server intégrées que vous pouvez utiliser dans la clause SELECT ou en combinaison dans la clause GROUP BY.
A l’exception de la fonction COUNT(*) toutes les fonctions renvoient NULL si aucune ligne ne répond aux conditions.
Voici les fonctions d’agrégations les plus communes
AVG: Calcule la moyenne d'une colonne
COUNT: Calcule le nombre de lignes correspondant aux critères de la requête
MAX: Calcule la valeur maximale d'une colonne
MIN: Calcule la valeur minimale d'une colonne
SUM: Effectue la somme des valeurs d'une colonne
Il y a certain spécificité pour ces fonctions:
- COUNT est la seule fonction d’agrégation avec laquelle on peut utiliser des types de colonne text, ntext, ou image.
- MIN et MAX vous ne pouvez pas utiliser ces clauses avec des types bit.
- SUM et AVG vous pouvez les utiliser seulement avec des colonnes de type int, smallint, tinyint, decimal, numeric, float,real, money, et smallmoney.
Prix moyen des produits.
Prix total des produits.
Prix minimum et prix maximum.
Utiliser des fonctions d’agrégation avec des valeurs NULL
Les valeurs NULL peuvent fausser l’ensemble de résultats par exemple si on utilise la fonction COUNT dans une instruction SELECT sur une table qui compte 10 ligne et que la colonne que l’on compte contient 2 valeurs NULL, la requête retourne 8 comme résultat.
Ici nous avons une table qui contient 4 lignes avec des colonnes nom et prénom, mais un des prénom est NULL donc on a 4 noms et 3 prénoms.
Les fondamentaux du GROUP BY
Utilisez la clause GROUP BY sur des colonnes ou des expressions pour organiser les lignes et pour résumer ces groupes. Par exemple, utilisez la clause GROUP BY pour déterminer la quantité de chaque produit qui a été commandé pour chaque commande.
Lorsque vous utilisez la clause GROUP BY, examinez les directives suivantes:
- Toutes les colonnes qui sont spécifiées dans la clause GROUP BY doivent être incluses dans la commande SELECT.
- Si vous incluez une clause WHERE, SQL Server groupes uniquement les lignes qui satisferont la clause WHERE.
- Ne pas utiliser la clause GROUP BY sur des colonnes qui contiennent plusieurs null parce que les valeurs NULL sont traitées comme un groupe.
- Utilisez le mot-clé ALL avec la clause GROUP BY pour afficher toutes les lignes avec les valeurs NULL dans l'ensemble des colonnes, indépendamment de savoir si les lignes de satisferont la clause WHERE.
Exemple :
On a la table qui contient entre autre les données suivantes
Maintenant si on utilise la clause GROUP BY pour avoir la quantité en stock de chaque produit.
Group BY et HAVING
Utilisez la clause HAVING sur des colonnes ou des expressions pour définir les conditions sur le groupe inclus dans un ensemble de résultats. La clause HAVING définit les conditions sur la clause GROUP BY, au même titre que la clause WHERE interagit avec la commande SELECT.
Lorsque vous utilisez la clause HAVING, examiner les directives suivantes:
- Utilisez la clause HAVING uniquement avec la clause GROUP BY pour restreindre le groupement. Utilisation de la clause HAVING sans la clause GROUP BY est n’a pas de sens.
- Vous pouvez référencer l'une des colonnes qui apparait dans le SELECT.
Ici nous avons les produits dont la quantité en stock est comprise entre 1090 et 1300.
Génération de valeurs globales Dans Ensembles de résultats
Utiliser la clause GROUP BY avec l’opérateur CUBE et ROLLUP pour générer des valeurs globales dans les ensembles de résultats. Les opérateurs CUBE ou ROLLUP peuvent être utile pour les références croisées (cross-referencing) dans une table sans avoir à écrire
d'autres scripts.
Lorsque vous utilisez les opérateurs ROLLUP ou CUBE, utilisez la fonction GROUPING pour identifier le détail et la synthèse des valeurs dans les résultats.
Utilisez l’opérateur ROLLUP avec la clause GROUP BY pour résumer les valeurs groupées.
Exemple :
Ici on a le nombre d’article commandé par commande pour les produits 897 et 898.
Donc on a 4 fois le produit 897 et 15 fois le produit 898 soit 19 articles en tout. Ce que l’on voudrait c’est ne pas calculer ça à la main… WITH ROLLUP est là pour ça !!
On a juste ajouté la clause WITH ROLLUP entre le GROUP BY et le ORDER BY et ici la première ligne de l’ensemble de résultat est la somme totale, la deuxième (surlignée) est la somme des articles 897 ensuite le détail comme précédemment puis la somme des articles 898 et le détail.
La commande CUBE quand à elle permet de résumer les données comme le ROLLUP le fait mais ici on aura toutes les combinaisons possibles.
Un exemple avec la même requête que précédemment :
Ligne 1 : total des produits
Ligne 2 à 10 : somme des produits commandés par commande
Ligne 11 : somme des articles 897 commandés
Ligne 12 et 13 détail de l’article 897
Ligne 14 : somme des articles 898 commandés
Ligne 15 à la fin détail de l’article 898
Attention ici le problème de la lisibilité des résultats devient on problème car si on a n colonnes dans le GROUP BY, SQL retournera 2n combinaisons dans l’ensemble de résultat.
Pour remédier au risque de lecture difficile la fonction GROUPING est la pour distinguer les lignes résumées des lignes détails.
Exemple :
La colonne « résumé » met un flag à 1 sur les lignes résumées de « productid » et la colonne « resum » met un flag à 1 sur les colonnes résumées de « saleorderid ». Donc toutes les lignes avec un flag à 1 sont des lignes résumées et pas les autres.
Utilisation des clauses COMPUTE et COMPUTE BY
Les clauses COMPUTE et COMPUTE BY génèrent des lignes résumées supplémentaires dans un format non-relationnelles qui ne sont pas aux normes ANSI. Si c’est utile pour la visualisation, la sortie n'est pas bien adaptée pour la production d'ensembles de résultats à utiliser avec d'autres applications.
On ne peut pas inclure de colonne text, ntext ou image dans ces clauses.
Exemple :
Ici on a 2 ensembles de résultats un pour le détail de la requête et un pour la somme de la quantité globale.
Un exemple en ajoutant un COMPUTE BY :
Ici on a un résumé par produit, le détail de chaque produit et le résumé total.