data / sqlalchemy

Requêtes avancées : subqueries et agrégations

Leçon 101 exercice

Explication

Ce que vous allez apprendre

Écrire une sous-requête scalaire pour comparer une valeur à une moyenne calculée
Transformer un résultat intermédiaire en "table dérivée" réutilisable avec .subquery()
Nommer une sous-requête avec une CTE (.cte()) pour améliorer la lisibilité et la réutiliser
Parcourir une hiérarchie (chaîne de managers) avec une CTE récursive
Calculer un rang par groupe sans fusionner les lignes grâce à une fonction de fenêtrage

Dans quel contexte ?

Un tableau de bord e-commerce doit afficher, pour chaque catégorie de produits, le produit le plus cher, ainsi que la liste des clients dont le total des commandes dépasse la moyenne générale. Ces deux besoins dépassent ce qu'un simple WHERE peut exprimer : ils demandent de calculer une valeur intermédiaire (une moyenne, un rang) avant de filtrer ou de comparer, exactement ce que couvrent les sous-requêtes, CTE et fonctions de fenêtrage de cette leçon.

D'abord, les limites d'un simple filtre

Les leçons précédentes ont montré comment récupérer des lignes et les joindre. Mais certaines questions restent hors de portée d'un simple WHERE : "quels produits coûtent plus cher que la moyenne ?", "quel est le rang de chaque produit dans sa catégorie ?". Ces questions demandent de calculer une valeur intermédiaire avant de filtrer ou comparer.

Étape 1 : une requête à l'intérieur d'une requête

Une sous-requête scalaire (.scalar_subquery()) calcule une seule valeur utilisable dans un WHERE, comme "la moyenne des prix". C'est un raisonnement en deux temps : d'abord calculer un résultat intermédiaire, puis l'utiliser dans la requête principale.

Outil	Retourne	Cas d'usage
`.scalar_subquery()`	une seule valeur	comparer à une moyenne, un maximum
`.subquery()`	une table dérivée	joindre un résultat agrégé comme une table
`.cte()`	une table nommée, réutilisable	lisibilité, récursivité (hiérarchies)
`func.row_number().over(...)`	une valeur par ligne, sans fusionner	classement, rang par groupe

Étape 2 : une sous-requête qui ressemble à une table

Quand le résultat intermédiaire contient plusieurs lignes et colonnes, .subquery() en fait une "table dérivée" qu'on peut ensuite joindre exactement comme une vraie table.

Il reste un problème : la lisibilité

Enchaîner plusieurs sous-requêtes imbriquées devient vite illisible, et impossible à réutiliser plusieurs fois dans la même requête.

Étape 3 : nommer sa sous-requête avec une CTE

Une CTE (WITH ... AS, obtenue via .cte()) résout ce problème en donnant un nom à une sous-requête, pour la référencer plusieurs fois. Sa variante récursive va plus loin encore : elle permet de parcourir une hiérarchie, comme une chaîne de managers, que rien ne peut représenter avec une requête classique à profondeur fixe.

Étape 4 : calculer sans fusionner les lignes

Un dernier besoin reste : calculer un rang ou une moyenne glissante SANS résumer les lignes en une seule, contrairement à GROUP BY. Une fonction de fenêtrage (row_number().over(...)) répond exactement à ce besoin, en annotant chaque ligne individuelle au lieu de les fusionner.

Le saviez-vous ?

GROUP BY fusionne plusieurs lignes en une seule ligne résumée (une moyenne par catégorie, par exemple). Une fonction de fenêtrage (OVER) fait l'inverse : elle calcule un agrégat tout en GARDANT une ligne par résultat d'origine, ce qui permet d'afficher "ce produit est classé 3e de sa catégorie" sans perdre le détail de chaque produit.

Vers la suite

Ces outils sont puissants mais à réserver aux cas où une requête simple ne suffit vraiment plus. La prochaine leçon change complètement de sujet : comment exécuter ce genre de requêtes sans bloquer tout le serveur pendant l'attente de la base, avec l'asynchrone.

Commandes & code

Requêtes avancées : subqueries et agrégations

from sqlalchemy import select, func, and_

with SessionLocal() as session:
    # Sous-requête scalaire dans un WHERE
    prix_moyen = select(func.avg(Produit.prix)).scalar_subquery()
    stmt = select(Produit).where(Produit.prix > prix_moyen)

    # Sous-requête comme table dérivée (subquery())
    sous_stats = (
        select(Commande.client_id, func.sum(Commande.montant).label("total"))
        .group_by(Commande.client_id)
        .subquery()
    )
    stmt = (
        select(Client.nom, sous_stats.c.total)
        .join(sous_stats, sous_stats.c.client_id == Client.id)
        .where(sous_stats.c.total > 1000)
    )
    for nom, total in session.execute(stmt):
        print(nom, total)

    # CTE avec .cte() (équivalent WITH ... AS)
    ventes_cte = (
        select(Commande.client_id, func.sum(Commande.montant).label("total"))
        .group_by(Commande.client_id)
        .cte("ventes_par_client")
    )
    stmt = select(Client.nom, ventes_cte.c.total).join(
        ventes_cte, ventes_cte.c.client_id == Client.id
    )

    # CTE récursive
    hierarchie = (
        select(Employe.id, Employe.nom, Employe.manager_id, func.cast(1, sa.Integer).label("niveau"))
        .where(Employe.manager_id.is_(None))
        .cte("hierarchie", recursive=True)
    )
    hierarchie_alias = aliased(Employe, name="e")
    hierarchie = hierarchie.union_all(
        select(
            hierarchie_alias.id, hierarchie_alias.nom, hierarchie_alias.manager_id,
            (hierarchie.c.niveau + 1).label("niveau"),
        ).join(hierarchie, hierarchie_alias.manager_id == hierarchie.c.id)
    )
    stmt = select(hierarchie.c.nom, hierarchie.c.niveau).order_by(hierarchie.c.niveau)

    # EXISTS
    from sqlalchemy import exists
    stmt = select(Client).where(
        exists().where(Commande.client_id == Client.id).where(Commande.montant > 500)
    )

    # Window function : rang par catégorie
    stmt = select(
        Produit.nom,
        Produit.categorie,
        func.row_number().over(
            partition_by=Produit.categorie, order_by=Produit.prix.desc()
        ).label("rang"),
    )
    for nom, categorie, rang in session.execute(stmt):
        print(nom, categorie, rang)

    # Somme conditionnelle (CASE)
    from sqlalchemy import case
    stmt = select(
        func.sum(case((Commande.statut == "livree", Commande.montant), else_=0)).label("ca_livre")
    )

    # GROUP BY + HAVING
    stmt = (
        select(Produit.categorie, func.count().label("nb"), func.avg(Produit.prix).label("prix_moyen"))
        .group_by(Produit.categorie)
        .having(func.count() >= 5)
        .order_by(func.avg(Produit.prix).desc())
    )

Résumé

.scalar_subquery() pour une valeur unique dans un WHERE ; .subquery() pour une table dérivée jointe.
.cte() génère un WITH ..., avec recursive=True pour les CTE récursives (hiérarchies).
func.row_number().over(partition_by=..., order_by=...) expose les window functions SQL directement dans l'ORM.
case((condition, valeur), else_=...) reproduit un CASE WHEN SQL pour des agrégations conditionnelles.

Exercices pratiques

1 disponible

Mission : un tableau de bord qui doit comparer chaque produit à la moyenne

Objectif : Construire une requête combinant sous-requête scalaire et fonction de fenêtrage pour un rapport e-commerce.

Contexte

Le tableau de bord e-commerce doit afficher les produits dont le prix dépasse la moyenne générale de tous les produits, ainsi que le rang de chaque produit au sein de sa propre catégorie. Un simple WHERE ne peut exprimer ni l'un ni l'autre sans calculer d'abord une valeur intermédiaire.

Résoudre l’exercice →