Nommez vos étapes avec les CTE

Pourquoi cette leçon est ici et pas plus tard

La plupart des cours reportent les expressions de table courantes à un module avancé. Celui-ci leur apprend maintenant, car les agents écrivent du SQL lourd en CTE. Un étudiant qui ne peut pas lire une chaîne de cinq CTE ne peut pas auditer tout ce qu'un agent produit. La lecture précède l'écriture, et les CTE sont ce que vous allez lire.

Une expression de table commune, écrite avec WITH, est une requête nommée à laquelle vous pouvez vous référer plus bas. Les enchaîner vous permet de créer un résultat étape par étape, de sorte que chaque étape ait un nom qui indique ce qu'elle produit.

Voici une question dans le style de la dernière leçon - revenus par pays, hors commandes annulées - rédigée de la même manière que la plupart des gens l'écrivent au départ, en un seul bloc :

SELECT s.country_name, SUM(o.order_total) AS revenue
FROM orders o
JOIN stores s ON o.store_id = s.store_id
WHERE o.order_status IS DISTINCT FROM 'cancelled'
GROUP BY s.country_name
ORDER BY revenue DESC;

Cela fonctionne, mais tout se passe en même temps : le filtre, la jointure et l'agrégat sont réunis dans une seule instruction, et pour l'examiner, vous devez tenir les trois ensemble dans votre tête. Désormais, la même requête est divisée en étapes : filtrer les commandes, rejoindre le magasin, puis agréger. Chaque CTE porte le nom de sa sortie.

WITH order_level AS (
  SELECT order_id, store_id, order_total
  FROM orders
  WHERE order_status IS DISTINCT FROM 'cancelled'
),
with_country AS (
  SELECT o.order_total, s.country_name
  FROM order_level o
  JOIN stores s ON o.store_id = s.store_id
)
SELECT country_name, SUM(order_total) AS revenue
FROM with_country
GROUP BY country_name
ORDER BY revenue DESC;

Cela conserve le grain de la commande tout au long, donc order_total est additionné une fois par commande et il n'y a pas de répartition. stores a une ligne propre par magasin, donc la jointure ajoute un pays sans rien dupliquer. Exécutez la version CTE et le Royaume-Uni est en tête avec 95 706,22. Lisez ensuite à haute voix chaque nom de CTE : "niveau de commande, puis par pays, puis chiffre d'affaires par pays". La lecture d'une requête à haute voix est une technique de révision peu coûteuse et véritablement efficace, car un nom qui ne correspond pas à ce que fait l'étape est facile à entendre.

L'ordre dans lequel SQL s'exécute réellement

Vous écrivez SELECT en premier, mais la base de données l'exécute tardivement. L'ordre logique est le suivant :

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

Cela explique deux choses qui déroutent les débutants. Un alias de colonne défini dans SELECT ne peut pas être utilisé dans WHERE, car WHERE s'exécute en premier. Et HAVING peut filtrer sur un agrégat, car il s'exécute après GROUP BY, alors que WHERE ne le peut pas.

Votre tâche

Refactorisez la requête monobloc en CTE dans queries/04-cte.sql, un CTE par étape logique, chacun nommé d'après ce qu'il produit - order_level, puis with_country, puis l'agrégat. Gardez l'ordre du grain partout afin qu'il n'y ait pas de répartition. Exécutez les versions monobloc et CTE et confirmez qu'elles renvoient des chiffres identiques, le Royaume-Uni étant en tête avec 95 706,22. Une requête multiligne convient parfaitement entre guillemets - collez-la telle quelle :

bruin query --connection duckdb-default \
  --description "revenue by country, CTE version" \
  --query "WITH order_level AS (
  SELECT order_id, store_id, order_total
  FROM orders
  WHERE order_status IS DISTINCT FROM 'cancelled'
),
with_country AS (
  SELECT o.order_total, s.country_name
  FROM order_level o
  JOIN stores s ON o.store_id = s.store_id
)
SELECT country_name, SUM(order_total) AS revenue
FROM with_country
GROUP BY country_name
ORDER BY revenue DESC;"

Vérifiez votre compréhension

• Que représente une ligne de chaque CTE ?
• Pourquoi HAVING peut-il filtrer sur SUM(order_total) alors que WHERE ne le peut pas ?
• Lequel s'exécute en premier, SELECT ou FROM ?

Faites-le avec votre agent

Dites next lesson et votre agent vous enseigne 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.