Rejoignez deux tables sans casser le nombre

La leçon qui casse le plus de chiffres

Une jointure combine les lignes de deux tables partageant une clé. Un INNER JOIN conserve uniquement les lignes qui correspondent des deux côtés. Un LEFT JOIN conserve chaque ligne de la table de gauche, en remplissant les valeurs nulles là où le côté droit n'a pas de correspondance. Choisir le mauvais, ou additionner après la mauvaise jointure, produit un nombre qui est certainement faux. C’est la leçon qui bat plus de totaux que toute autre.

Fan-out gonfle un total

orders correspond à une ligne par commande. order_items c'est une ligne par ligne, soit 2,40 par commande en moyenne. Rejoignez-les et additionnez une colonne au niveau de la commande, et le total de chaque commande est compté une fois par ligne.

D'abord le total correct, à la commande de grain :

bruin query --connection duckdb-default \
  --description "correct total order value at order grain" \
  --query "SELECT SUM(order_total) AS revenue FROM orders;"

Cela renvoie 604 065,00 (le terminal imprime 604065). Maintenant la version cassée, résumant la même colonne après avoir joint les lignes :

bruin query --connection duckdb-default \
  --description "inflated total order value after the lines join" \
  --query "SELECT SUM(o.order_total) AS revenue FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

Cela s'exécute sans erreur et renvoie 1.44977182e+06 - notation scientifique pour 1 449 771,82, soit 2,4 fois le chiffre correct. Il n'y a aucun avertissement. order_total appartient à la commande, il doit donc être additionné au grain de la commande, et non après une jointure un-à-plusieurs. Le correctif consiste à agréger avant de rejoindre ou à additionner uniquement les colonnes au niveau de la ligne telles que quantity * net_price.

Vous pouvez laisser la base de données effectuer la division à votre place :

bruin query --connection duckdb-default \
  --description "how inflated is the joined total" \
  --query "SELECT ROUND(SUM(o.order_total) / 604065.0, 2) AS inflation FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

inflation revient exactement à 2,4 - le nombre moyen de lignes par commande, ce qui n'est pas un hasard. Chaque commande était comptée une fois par ligne.

Un LEFT JOIN qui devient tranquillement un INNER JOIN

order_items comporte 15 lignes dont product_id n'est pas dans products. Un INNER JOIN vers products supprime ces 15 lignes, et avec elles 3 637,89 de revenus de ligne, sans un mot. Un LEFT JOIN les conserve. Voyez par vous-même les lignes supprimées - ce modèle, un LEFT JOIN filtré sur les lignes sans correspondance, est la façon dont vous trouvez ce qu'un INNER JOIN éliminerait silencieusement :

bruin query --connection duckdb-default \
  --description "order lines whose product does not exist" \
  --query "SELECT COUNT(*) AS orphan_lines, ROUND(SUM(oi.quantity * oi.net_price), 2) AS orphan_revenue FROM order_items oi LEFT JOIN products p ON oi.product_id = p.product_id WHERE p.product_id IS NULL;"

Il existe un deuxième piège. Un LEFT JOIN protège les lignes de gauche uniquement si le filtre de la table de droite réside dans la clause ON. Déplacez ce filtre vers WHERE, et les lignes sans correspondance échouent à la condition et disparaissent, transformant à nouveau LEFT JOIN en INNER JOIN. Mettez les conditions sur la table de droite dans ON et conservez WHERE pour la table de gauche.

L'ajout de DISTINCT ne corrige pas la diffusion. Cela rend le décompte plausible alors que le grain est toujours erroné. Traitez tout DISTINCT dans une requête comme quelque chose à justifier, pas comme une réparation.

Votre tâche

Reproduisez les trois nombres dans queries/03-joins.sql. Écrivez le total correct de grains de commande et confirmez 604 065,00. Ensuite, additionnez order_total après avoir rejoint order_items et regardez-le gonfler jusqu'à 1 449 771,82, puis exécutez la requête d'inflation pour confirmer que le facteur est exactement de 2,4 :

bruin query --connection duckdb-default \
  --description "how inflated is the joined total" \
  --query "SELECT ROUND(SUM(o.order_total) / 604065.0, 2) AS inflation FROM orders o JOIN order_items oi ON o.order_id = oi.order_id;"

Enfin, recherchez les lignes orphelines qu'un INNER JOIN supprimerait - 15 lignes et 3 637,89 de revenus - avec le modèle LEFT JOIN ci-dessus. N'utilisez pas DISTINCT pour réparer quoi que ce soit.

Vérifiez votre compréhension

• Quelle est la valeur totale correcte de la commande et à quoi la jointure la gonfle-t-elle ?
• Pourquoi exactement 2,4x ?
• Combien de lignes orphelines et combien de revenus un INNER JOIN entraînerait-il ?

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.