Du SQL applicatif au SQL d'analyse

Tu sais écrire un SELECT, un WHERE et une jointureVocabulaire · jointureRapprocher deux tables sur une colonne commune, pour lire ensemble des informations qui sont stockées séparément.. La difficulté commence quand le résultat ne représente plus une ligne de la table, mais un groupe à interpréter. Cette fiche travaille ce changement de maille.

Le TP demande ce passage dès la partie 1, et il vaut pour le reste de la formation. Côté référentiel : BC01 pour l'extraction, BC02 pour l'analyse.

1. La granularité : ce qu'une ligne du résultat représente

En applicatif, une requête renvoie des lignes de la table. La commande 42, les clients de Lille, les produits en rupture. Une ligne en entrée, une ligne en sortie.

En analyse, une requête renvoie une ligne par groupe. Un chiffre d'affaires par mois, un panier moyenVocabulaire · panier moyenLe montant moyen d'une commande, pas le nombre d'articles qu'elle contient. On additionne ce qu'une commande a coûté, puis on fait la moyenne sur l'ensemble des commandes. par catégorie, un taux de retard par État. La sortie ne ressemble plus à la table. Ce que représente une ligne du résultat s'appelle la granularitéVocabulaire · granularitéLe niveau de détail d'une ligne de table : une ligne par commande, ou une ligne par article de la commande, ce n'est pas la même chose et ça change tous les comptes..

Observe d'abord un petit résultat groupé :

SELECT seller_state, COUNT(*) AS vendeurs
FROM sellers
WHERE seller_state IN ('BA', 'CE')
GROUP BY seller_state
ORDER BY seller_state;

Avant d'exécuter, prédis le nombre de lignes. Le résultat en contient deux, une par État retenu. Une jointure peut changer cette maille : une commande rapprochée de trois articles occupe trois lignes, et COUNT(*) compte alors des articles.

Devant chaque requête d'analyse, pose-toi la question : une ligne de mon résultat, ça représente quoi ? Une commande, un article, un paiement, un client ? Tant que tu ne peux pas répondre en une phrase, ne calcule rien.

2. GROUP BY : ce que le SELECT peut contenir

SELECT seller_state, COUNT(*) AS vendeurs
FROM sellers
GROUP BY seller_state
ORDER BY vendeurs DESC;

Cette requête renvoie une ligne par État présent dans sellers.

Voici une erreur courante :

SELECT seller_state, seller_id, COUNT(*)
FROM sellers
GROUP BY seller_state;

PostgreSQL refuse :

ERROR:  column "sellers.seller_id" must appear in the GROUP BY clause
        or be used in an aggregate function

La règle : après un GROUP BY, chaque colonne du SELECT doit être soit dans le GROUP BY, soit dans une fonction d'agrégation. Une fonction d'agrégation résume un groupe de lignes en une seule valeur : COUNT, SUM, AVG, MIN, MAX.

La raison : le groupe SP contient 1 849 vendeurs. Demander seller_id revient à demander 1 849 valeurs pour une seule ligne. La base ne peut pas choisir.

Si tu viens de MySQL, ce paragraphe te concerne. Dans certaines configurations, MySQL accepte cette requête et renvoie un seller_id pris parmi les 1 849, sans prévenir. Le résultat a l'air correct et il ne l'est pas. PostgreSQL refuse, et ce refus t'évite un résultat faux.

3. WHERE et HAVING : avant ou après le regroupement

Les deux filtrent. Ils ne filtrent pas la même chose.

• WHERE s'applique aux lignes, avant le regroupement.
• HAVING s'applique aux groupes, après.

SELECT seller_state, COUNT(*) AS vendeurs
FROM sellers
GROUP BY seller_state
HAVING COUNT(*) >= 100
ORDER BY vendeurs DESC;

6 lignes sur les 23 États. Impossible à écrire avec un WHERE : au moment où WHERE s'exécute, les groupes n'existent pas encore. COUNT(*) n'a donc pas de sens à cet endroit.

Voici l'ordre utile pour expliquer où placer chaque filtre :

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Deux conséquences directes :

• Un alias (le nom donné à une colonne avec AS) défini dans le SELECT n'est pas utilisable dans le WHERE, qui s'exécute avant. Il l'est dans le ORDER BY, qui s'exécute après. PostgreSQL l'accepte aussi dans le GROUP BY, par toléranceVocabulaire · toléranceLe plus grand écart accepté entre deux valeurs pour les considérer comme assez proches dans un usage précis., et tu en verras un plus bas. Il le refuse dans le HAVING, avec le message column ... does not exist.
• Filtrer dans le WHERE ce qui peut l'être fait travailler la base sur moins de lignes. HAVING sert seulement à ce qui porte sur un agrégatVocabulaire · agrégatUn calcul qui résume plusieurs lignes en une seule valeur : COUNT, SUM, AVG, MIN, MAX..

4. NULL : une absence de valeur

Cette source d'erreur ne produit jamais de message. Elle est donc difficile à repérer.

SELECT NULL = NULL;

Renvoie ni vrai ni faux, mais NULL. Deux absences ne sont pas égales : on ne sait pas ce qu'elles cachent. Les opérateurs = et <> ne renvoient pas vrai face à une absence. Utilise IS NULL ou IS NOT NULL pour tester l'absence. PostgreSQL propose aussi IS DISTINCT FROM pour comparer deux expressions en traitant NULL comme une valeur comparable.

Sur les données du TP : products contient 32 951 lignes, dont 610 sans catégorie.

SELECT COUNT(*) FROM products WHERE product_category_name <> 'cama_mesa_banho';

29 312.

SELECT COUNT(*) FROM products
WHERE product_category_name IS DISTINCT FROM 'cama_mesa_banho';

29 922.

610 produits d'écart : exactement les 610 sans catégorie. Le <> les a écartés. Pour chacun, la comparaison vaut NULL, ni vrai ni faux, et la ligne ne passe pas le filtre. IS DISTINCT FROM traite l'absence comme une valeur différente et garde ces lignes.

Trois comportements à connaître :

La moyenne d'une colonne à moitié vide est calculée sur l'autre moitié, sans avertissement.

5. Cinq différences entre MySQL et PostgreSQL

Chacune coûte quelques minutes la première fois.

Une sixième différence : PostgreSQL refuse un JOIN sans ON, avec une erreur de syntaxe. MySQL l'accepte et produit un produit cartésien, c'est-à-dire chaque ligne de la première table associée à chaque ligne de la seconde. Pour croiser deux tables sans condition en PostgreSQL, il faut écrire CROSS JOIN.

Sur les guillemets : en PostgreSQL, les guillemets doubles désignent un identifiant (un nom de table ou de colonne), les guillemets simples une valeur texte. = "SP" demande donc la colonne nommée SP. C'est pour ça que le message parle d'une colonne alors que tu voulais écrire du texte.

Trois écritures propres à PostgreSQL, fréquentes en analyse.

Conversion de type, avec :: :

SELECT order_purchase_timestamp::date FROM orders LIMIT 3;

Compter sous condition, sans sous-requêteVocabulaire · sous-requêteUne requête écrite à l'intérieur d'une autre, entre parenthèses. :

SELECT COUNT(*) FILTER (WHERE order_status = 'canceled') AS annulees FROM orders;

Ramener une date au début de son mois, pour regrouper :

SELECT date_trunc('month', order_purchase_timestamp)::date AS mois,
       COUNT(*) AS commandes
FROM orders
GROUP BY mois
ORDER BY mois
LIMIT 5;

6. Le chemin minimum

Si tu ne sais pas par où entrer, ces huit formes couvrent la partie 1 du TP. La colonne de droite dit à quelle question chacune sert.

Deux choses manquent à ce tableau. Elles ont la même forme.

La question 2 demande un pourcentage. Un pourcentage a besoin du total, et ton GROUP BY ne le donne pas. Les questions 5 et 6 demandent combien. HAVING COUNT(*) > 1 donne la liste de ce qui apparaît plusieurs fois, pas son décompte : il faut compter les lignes de ce résultat, donc l'envelopper dans une autre requête.

Dans les deux cas, il s'agit de nommer un résultat intermédiaire pour s'en resservir. C'est ce que fait une CTEVocabulaire · CTECommon Table Expression : un résultat intermédiaire auquel on donne un nom avec WITH, pour s'en servir ensuite comme d'une table. (WITH nom AS (...)). Écrire une requête avec WITH montre comment compter des groupes en section 2 et calculer un pourcentage en section 5.

Les window functionsVocabulaire · window functionUne fonction qui calcule un agrégat en gardant toutes les lignes du résultat, au lieu de les écraser comme le fait un GROUP BY., qui calculent sur un groupe sans fusionner les lignes, ne servent qu'à partir de la partie 2. COUNT(*) FILTER (WHERE ...) est un agrégat conditionnel : un compte qui ne porte que sur les lignes remplissant une condition. C'est la seule notion de ce tableau qui dépasse le SQL de base.

7. Trois questions avant de passer à la suite

Réponds sans exécuter, puis exécute.

1. Après une jointure entre commandes et articles, que représente une ligne du résultat, et comment peux-tu le vérifier ?
2. Dans une table de 1 500 lignes, une colonne est renseignée sur 1 000 lignes et absente sur les 500 autres. AVG de cette colonne divise par combien ?
3. Pourquoi WHERE COUNT(*) > 100 ne peut jamais fonctionner ?