Intermédiaire

CTEs — écrire des requêtes lisibles

Les Common Table Expressions (CTEs) permettent de nommer des sous-requêtes et de les réutiliser dans la requête principale.

Le problème : les sous-requêtes imbriquées

Imaginons que l'on veuille afficher les régions dont le chiffre d'affaires est supérieur à la moyenne. Sans CTE, on obtient une requête imbriquée :

sql

SELECT region, chiffre_affaires
FROM (
    SELECT region, SUM(montant) AS chiffre_affaires
    FROM ventes
    GROUP BY region
) AS ca_par_region
WHERE chiffre_affaires > (
    SELECT AVG(chiffre_affaires)
    FROM (
        SELECT region, SUM(montant) AS chiffre_affaires
        FROM ventes
        GROUP BY region
    ) AS ca_par_region_2
)
ORDER BY chiffre_affaires DESC

Deux problèmes :

Duplication : la sous-requête SUM(montant) ... GROUP BY region est écrite deux fois.
Lisibilité : avec deux niveaux d'imbrication, il est difficile de comprendre le flux logique.

Ajoutez un troisième niveau et la requête devient impossible à maintenir.

Syntaxe CTE : WITH ... AS

Une CTE se déclare avec le mot-clé WITH avant la requête principale :

sql

WITH ca_par_region AS (
    SELECT region, SUM(montant) AS chiffre_affaires
    FROM ventes
    GROUP BY region
)
SELECT region, chiffre_affaires
FROM ca_par_region
WHERE chiffre_affaires > (SELECT AVG(chiffre_affaires) FROM ca_par_region)
ORDER BY chiffre_affaires DESC

Lecture naturelle

Lisez une CTE comme une variable : « Soit ca_par_region le résultat de cette requête. Maintenant, utilisons ca_par_region dans la suite. » La sous-requête n'est écrite qu'une seule fois et peut être référencée plusieurs fois.

La structure est toujours la même :

sql

WITH nom_cte AS (
    -- Sous-requête
    SELECT ...
)
SELECT ...
FROM nom_cte
WHERE ...

Quel est l'avantage principal d'une CTE par rapport à une sous-requête imbriquée ?

CTEs chaînées

On peut enchaîner plusieurs CTEs en les séparant par des virgules. Chaque CTE peut référencer les CTEs précédentes :

sql

WITH ca_par_region AS (
    SELECT region, SUM(montant) AS chiffre_affaires
    FROM ventes
    GROUP BY region
),
moyenne_globale AS (
    SELECT AVG(chiffre_affaires) AS moyenne
    FROM ca_par_region
),
regions_au_dessus AS (
    SELECT
        r.region,
        r.chiffre_affaires,
        m.moyenne,
        CASE
            WHEN r.chiffre_affaires >= m.moyenne THEN 'Au-dessus'
            ELSE 'En-dessous'
        END AS statut
    FROM ca_par_region r, moyenne_globale m
)
SELECT region, chiffre_affaires, moyenne, statut
FROM regions_au_dessus
ORDER BY chiffre_affaires DESC

Flux de données

Les CTEs chaînées forment un pipeline logique :

ca_par_region — agrégation de base
moyenne_globale — calcul sur le résultat de l'étape 1
regions_au_dessus — enrichissement avec classification

Chaque étape est lisible indépendamment et testable séparément.

La syntaxe des CTEs chaînées :

sql

WITH
cte_1 AS (SELECT ...),
cte_2 AS (SELECT ... FROM cte_1),
cte_3 AS (SELECT ... FROM cte_1 JOIN cte_2 ON ...)
SELECT ... FROM cte_3

Virgule entre les CTEs, pas de WITH répété

Chaque CTE supplémentaire est séparée par une virgule. Le mot-clé WITH n'apparaît qu'une seule fois, au début. Erreur fréquente :

sql

-- FAUX : deux WITH
WITH cte_1 AS (...)
WITH cte_2 AS (...)  -- Erreur de syntaxe !

-- CORRECT : virgule
WITH cte_1 AS (...),
cte_2 AS (...)
SELECT ...

Quand utiliser une CTE ?

Les CTEs sont utiles dans trois situations :

Sous-requête utilisée plusieurs fois — évite la duplication de code
Requête suffisamment complexe pour mériter un nom — améliore la lisibilité
Calcul intermédiaire à réutiliser — par exemple, une moyenne utilisée dans un filtre et dans un affichage

Ne créez pas une CTE pour une simple sous-requête utilisée une seule fois et qui tient sur une ligne. L'objectif est la clarté, pas la complexité.

Comment enchaîne-t-on plusieurs CTEs dans une même requête ?

Intégration dans le repository

La CTE des régions au-dessus de la moyenne s'intègre dans le VentesRepository :

python

def get_regions_au_dessus_moyenne(self, annee=None):
    """Retourne les régions avec leur statut par rapport à la moyenne."""
    filtre = f"WHERE annee = {annee}" if annee else ""

    query = f"""
    WITH ca_par_region AS (
        SELECT region, SUM(montant) AS chiffre_affaires
        FROM ventes
        {filtre}
        GROUP BY region
    ),
    moyenne_globale AS (
        SELECT AVG(chiffre_affaires) AS moyenne
        FROM ca_par_region
    )
    SELECT
        r.region,
        r.chiffre_affaires,
        m.moyenne,
        CASE
            WHEN r.chiffre_affaires >= m.moyenne THEN 'Au-dessus'
            ELSE 'En-dessous'
        END AS statut
    FROM ca_par_region r, moyenne_globale m
    ORDER BY r.chiffre_affaires DESC
    """

    rows = self._execute(query)
    return [
        {
            "region": row[0],
            "chiffre_affaires": row[1],
            "moyenne": row[2],
            "statut": row[3]
        }
        for row in rows
    ]

Convention de nommage

Nommez vos CTEs comme des variables descriptives : ca_par_region, moyenne_globale, top_produits. Un bon nom de CTE rend le SELECT final auto-documenté.

Visualisation Streamlit

Le résultat se prête à un bar chart coloré par statut, avec une ligne horizontale pour la moyenne :

python

import plotly.express as px
import streamlit as st

data = repo.get_regions_au_dessus_moyenne(annee=2024)
df = pd.DataFrame(data)

fig = px.bar(
    df,
    x="region",
    y="chiffre_affaires",
    color="statut",
    color_discrete_map={
        "Au-dessus": "#2ca02c",
        "En-dessous": "#d62728"
    },
    labels={"chiffre_affaires": "CA (€)", "region": "Région"},
    title="Chiffre d'affaires par région vs moyenne"
)

# Ligne horizontale pour la moyenne
moyenne = df["moyenne"].iloc[0]
fig.add_hline(
    y=moyenne,
    line_dash="dash",
    line_color="gray",
    annotation_text=f"Moyenne : {moyenne:,.0f} €"
)

st.plotly_chart(fig, use_container_width=True)

fig.add_hline()

fig.add_hline() ajoute une ligne horizontale de référence. Paramètres utiles :

y — la valeur sur l'axe Y
line_dash — style de ligne : "solid", "dash", "dot", "dashdot"
line_color — couleur de la ligne
annotation_text — texte affiché à côté de la ligne

À retenir

Points clés

Une CTE se déclare avec WITH nom AS (SELECT ...) avant la requête principale
Les CTEs chaînées sont séparées par des virgules — un seul WITH au début
Utilisez les CTEs quand une sous-requête est réutilisée, complexe ou mérite un nom
L'intégration repository + Streamlit suit le même pattern : méthode → DataFrame → graphique Plotly

SQLite WITH clause

Documentation officielle des CTEs dans SQLite

Documentation

Common Table Expressions — Mode d'emploi

Guide pratique sur les CTEs avec exemples progressifs