Compter, additionner et grouper

Un numéro par groupe

Un agrégat regroupe plusieurs lignes en un seul nombre. GROUP BY exécute cet agrégat une fois par groupe. C'est ainsi que vous transformez 1 200 commandes en revenus par catégorie, ou en nombre de commandes par magasin.

bruin query --connection duckdb-default \
  --description "order count and revenue by status" \
  --query "SELECT order_status, COUNT(*) AS orders, SUM(order_total) AS revenue FROM orders GROUP BY order_status ORDER BY orders DESC;"

Regardez le résultat avant de continuer. Le statut d'une ligne s'imprime sous la forme <nil> (certains outils impriment NULL) - c'est-à-dire le groupe de commandes sans aucun statut, 24 d'entre elles. NULL forme son propre groupe dans un GROUP BY. Gardez un œil sur cette rangée ; cela comptera deux fois dans cette leçon.

WHERE filtre les lignes avant de les regrouper. HAVING filtre les groupes après l'agrégation. Utilisez WHERE order_total > 0 pour supprimer les lignes en premier ; utilisez HAVING SUM(order_total) > 10000 pour conserver uniquement les grands groupes. Les mélanger est l’erreur la plus courante à ce niveau.

Trois chefs d'accusation en désaccord

COUNT(*) compte les lignes. COUNT(column) compte les lignes où cette colonne n'est pas NULL. COUNT(DISTINCT column) compte des valeurs distinctes non NULL. Exécutez les trois sur unit_cost, qui est NULL sur 57 lignes de commande :

bruin query --connection duckdb-default \
  --description "three counts of unit_cost that disagree" \
  --query "SELECT COUNT(*) AS rows, COUNT(unit_cost) AS non_null, COUNT(DISTINCT unit_cost) AS distinct_values FROM order_items;"

rows vaut 2 880 et non_null vaut 2 823, soit une différence d'exactement 57. AVG(unit_cost) divise par 2 823, et non par 2 880, car AVG ignore les valeurs NULL. Le dénominateur n’est pas celui que vous avez supposé, la moyenne est donc supérieure à ce que suggère une lecture par ligne.

Une liste d'inclusion supprime également les NULL

Vous avez appris que != 'cancelled' supprime les statuts NULL. Une liste d’inclusion fait la même chose, et elle semble plus sûre, c’est pourquoi elle attire les gens. order_status IN ('completed', 'shipped') conserve uniquement ces deux valeurs - et supprime silencieusement tous les statuts NULL ainsi que les statuts que vous vouliez exclure.

Les statuts que vous pouvez voir exclus - annulé, retourné, traitement - sont un choix que vous avez fait exprès. Les lignes NULL ne sont pas un choix. Ils n'apparaissent dans aucune liste, donc rien dans la requête n'indique qu'ils ont disparu. Mesurez exactement ce qui a disparu sans votre consentement :

bruin query --connection duckdb-default \
  --description "orders silently dropped by an inclusion list" \
  --query "SELECT COUNT(*) AS dropped_orders, SUM(order_total) AS dropped_revenue FROM orders WHERE order_status IS NULL;"

La liste d'inclusion supprime silencieusement 24 commandes portant 10 900,00 dans order_total - les mêmes 24 lignes de statut NULL que le filtre != a supprimées, perdues de la même manière. Une liste d’inclusion est aussi dangereuse qu’une liste d’exclusion. Les deux mangent des NULL.

Votre tâche

Agréger dans queries/02-aggregates.sql. Écrivez la requête de comptage et de revenus par statut et lisez le groupe NULL. Confirmez ensuite que les trois chefs d'accusation sont en désaccord d'exactement 57 :

bruin query --connection duckdb-default \
  --description "three counts of unit_cost that disagree" \
  --query "SELECT COUNT(*) AS rows, COUNT(unit_cost) AS non_null, COUNT(DISTINCT unit_cost) AS distinct_values FROM order_items;"

rows devrait être 2 880 et non_null 2 823. Confirmez ensuite que order_status IN ('completed', 'shipped') abandonne silencieusement 24 commandes d'une valeur de 10 900,00 dans order_total. Enfin, rédigez une requête sur les revenus totaux par catégorie de produits en 2023 et lisez quelle catégorie est en tête. Décidez vous-même quelle colonne correspond aux revenus et sur quelle colonne de date filtrer, et soyez prêt à défendre les deux choix.

Vérifiez votre compréhension

• Pourquoi COUNT(unit_cost) est-il plus petit que COUNT(*), et de combien ?
• Qu'est-ce que order_status IN ('completed', 'shipped') laisse tomber silencieusement, et quels revenus ?
• Quelle catégorie de produits a généré les revenus les plus élevés en 2023 ?

Faites-le avec votre agent

Dites next lesson et votre agent vous apprend cela, vous pose ces questions, puis définit la tâche ci-dessus. Faites-le à la main, puis dites review my work - il exécute votre requête, la vérifie par rapport à une rubrique et vous indique ce qu'il faut corriger ou marque la leçon comme terminée.