Choisir entre WITH, FILTER et OVER

Ta requête doit compter une sous-population, réutiliser un résultat ou ajouter un calcul sans perdre les lignes. Ces trois besoins se ressemblent dans un long SELECT, mais ils n'appellent pas le même outil.

Cette aide-mémoire travaille BC01 et BC02. Ouvre seulement la section qui correspond à ton blocage.

Après vingt minutes de lecture, ferme la page et modifie une requête.

Nommer une étape avec WITH

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., pour Common Table Expression, est un résultat intermédiaire nommé avec WITH. Prédis d'abord ce qu'une ligne de par_etat représente.

WITH par_etat AS (
    SELECT seller_state, COUNT(*) AS vendeurs
    FROM sellers
    GROUP BY seller_state
)
SELECT COUNT(*) AS etats_representes
FROM par_etat;

La CTE contient une ligne par État présent dans sellers. Le COUNT(*) final compte des États, pas les vendeurs d'origine.

Pour inspecter une CTE, remplace temporairement la requête finale par :

SELECT *
FROM nom_de_la_cte
LIMIT 20;

Modifie la requête pour compter les États ayant au moins dix vendeurs. Si tu bloques, conserve la CTE et ajoute un filtre final. Vérifie en affichant les États filtrés.

Compter des cas avec FILTER

FILTER (WHERE ...) applique une condition à un seul agrégatVocabulaire · agrégatUn calcul qui résume plusieurs lignes en une seule valeur : COUNT, SUM, AVG, MIN, MAX.. Le WHERE placé avant GROUP BY retire au contraire les lignes pour tous les calculs.

Avant d'exécuter, prédis si categories_renseignees peut dépasser produits :

SELECT COUNT(*) AS produits,
       COUNT(*) FILTER (
           WHERE product_category_name IS NOT NULL
       ) AS categories_renseignees
FROM products;

Le résultat représente le catalogue. categories_renseignees compte une partie des mêmes lignes et ne peut donc pas dépasser produits.

Ajoute une colonne qui compte les catégories absentes. Vérifie que les deux sous-populations donnent ensemble le total.

Garder les lignes avec OVER

GROUP BY produit une ligne par groupe. Une fonction de fenêtrage avec OVER garde les lignes et ajoute un calcul fondé sur plusieurs d'entre elles.

SELECT seller_id,
       seller_state,
       COUNT(*) OVER (PARTITION BY seller_state) AS vendeurs_dans_l_etat
FROM sellers
WHERE seller_state IN ('BA', 'CE')
ORDER BY seller_state, seller_id
LIMIT 20;

Avant d'exécuter, prédis ce qui se répète. Une ligne représente toujours un vendeur ; le compteur porte la même valeur sur tous les vendeurs de son État.

Retire temporairement PARTITION BY seller_state. Avec OVER (), quel nombre sera répété ? Vérifie avec une requête séparée :

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

Pour comparer une période à la ligne précédente avec LAG, ouvre Comparer des périodes quand une période manque.

Construire des tranches avec CASE

CASE produit une valeur selon la première condition vraie. Il permet de construire des catégories exclusives avant de les compter.

SELECT product_id,
       product_weight_g,
       CASE
           WHEN product_weight_g IS NULL THEN 'poids absent'
           WHEN product_weight_g < 500 THEN 'moins de 500 g'
           WHEN product_weight_g < 2000 THEN 'de 500 g à moins de 2 kg'
           ELSE '2 kg ou plus'
       END AS tranche_poids
FROM products
ORDER BY product_id
LIMIT 20;

Prédis la tranche d'un produit de 500 g et celle d'un produit de 2 000 g. Les bornes basses appartiennent à la branche suivante, car < exclut la valeur de la borne. Le cas NULL est placé en premier : une comparaison avec une valeur absente n'est jamais vraie.

Modifie uniquement la borne de 2 kg. Vérifie ensuite qu'aucun produit n'est perdu en comptant les tranches et en comparant leur somme au nombre de produits.

Décider à partir de la ligne attendue

Ces outils peuvent se combiner, mais n'empile pas les trois avant d'avoir exécuté chaque étape seule.

Pour le transfert autonome, choisis un jeu qui n'est pas Olist. Écris la phrase « une ligne du résultat représente… », puis construis un FILTER, un OVER ou un CASE. Donne une seconde requête de contrôle.

En cas d'erreur

Une CTE n'existe que pendant l'instruction terminée par le point-virgule. Plusieurs CTE s'écrivent après un seul WITH, séparées par des virgules.