TSQL - Les agrégations

Cette quête t'apprend à maîtriser les agrégations pour tirer pleine puissance des requêtes SQL

Objectifs

• Comprendre le principe d'agrégation de données

• Connaître quelques fonctions formant un agrégat

• Utiliser GROUP BY pour former des agrégats

Méchant ransomware

Un magasin de chaussettes s'est fait cambrioler ses données par un pirate malveillant. Le pirate demande une raçon au client afin qu'il restitue ses données. Le magasin, bien embarassé, décide de ne pas payer la rançon. Premièrement, le gérant de la boutique n'est pas sûr que le pirate lui restitue les données à l'issue du paiement. Deuxièmement, il n'y a aucune assurance que les données restituées soient intègres. Troisièmement, payer ne met pas à l'abri de nouvelles attaques.

Les fonctions d'agrégation

Les bases de données relationnelles sont des outils puissants. Beaucoup de tâches peuvent être exécutées côté SGBD. Une des fonctionnalités fondamentales des SGBDR est la possibilité de former des agrégats. Un agrégat est le regroupement de plusieurs entités en un seul enregistrement.

SQL Server met à disposition des fonctions permettant d'obtenir des agrégats. Par exemple, la fonction SUM() renvoie la somme des champs de plusieurs entités distinctes en un seul enregistrement. Dans SQL Server - ainsi que dans d'autres SGBDR - la fonction AVG() renvoie la moyenne d'un ensemble d'éléments.

GROUP BY

La clause GROUP BY permet de regrouper plusieurs entités partageant la même valeur du même champ. GROUP BY s'utilise conjointement aux fonctions d'agrégation pour obtenir des informations sur des sous-ensembles. Par exemple, il est possible de faire la somme des prix pour tous les produits par catégorie. Une requête réalisant cela pourrait ressembler à:

SELECT SUM(pd.product_price), ct.category_name
FROM Product AS pd
INNER JOIN Category AS ct ON ct.id = pd.category_id 
GROUP BY product_category;

On peut rajouter une clause WHERE pour exclure tous les produits ne faisant pas parti de la catégorie ayant le nom Chaussette:

SELECT SUM(pd.product_price), ct.category_name
FROM Product AS pd
INNER JOIN Category AS ct ON ct.id = pd.category_id
GROUP BY product_category
WHERE ct.name = "Chaussette";

Voici un autre exemple d'utilisation de GROUP BY où la somme des salaires des employés par département est retournée:

Dans beaucoup de cas le choix d'utiliser la puissance des requêtes SQL est bon puisqu'elles sont performantes et concises - la plupart du temps. De plus, le code du SGBD est optimisé pour que les traitements soient suffisament rapides sur un très grand ensemble de données, ce qu'un code écrit dans un langage de programmation ne prend pas forcément en compte.

Challenge

Un client rassuré

Le magasin a une organisation particulière que ton modèle de données doit respecter. Il y a plusieurs produits, chacun a un prix, un nom et appartient à une catégorie de produits. Une catégorie de produit un nom qui l'identifie. Chaque achat d'un produit doit pouvoir être stocké. La date de chaque achat doit être disponible.

Critères de validation

• Fournis un fichier SQL créant la base de données

• Le fichier SQL fournit un jeu de données de test. Le jeu de données de test contient au moins:

  • 3 catégories

  • 9 articles

  • 18 achats entre 2019 et 2020

• Un produit appartient forcément à une catégorie, sinon il ne peut pas exister

• Ecris une requête de sélection renvoyant:

  • La somme du nombre d'achats de chaque catégorie

  • La somme des prix des produits achetés de chaque catégorie

• Ecris une requête qui renvoie la somme du nombre d'achats entre 2019 et 2020