Aller au contenu principal

td2

Oui. Sur cette base market, on peut construire un exercice progressif autour des fonctions de fenêtrage, en particulier OVER (PARTITION BY ...), sans nécessiter de modifier le schéma.

L'idée est de faire travailler ROW_NUMBER, RANK, DENSE_RANK, SUM, AVG, COUNT, LAG et LEAD.

Exercice — Fonctions de fenêtrage​

Niveau 1 — Agrégats par partition​

1. Pour chaque article vendu, afficher le prix unitaire de chaque ligne ainsi que le prix unitaire moyen de cet article sur l'ensemble des tickets.

Colonnes attendues : article_code, prix_unitaire, prix_moyen_article.

2. Pour chaque ticket, afficher chaque ligne avec le montant total du ticket.

Le montant d'une ligne est calculé ainsi :

prix_unitaire × quantite

Colonnes attendues : ticket_id, article_code, prix_unitaire, quantite, total_ticket.

3. Pour chaque adhérent, afficher chaque ticket et le nombre total de tickets réalisés par cet adhérent.

Colonnes attendues : adherent_id, ticket_id, date_ticket, nombre_tickets.

4. Pour chaque famille d'articles, afficher chaque article avec le prix moyen des articles de sa famille.

Colonnes attendues : famille_code, code, article, prix, prix_moyen_famille.

Niveau 2 — Classement​

5. Pour chaque famille, classer les articles par prix décroissant.

Utiliser RANK().

Colonnes attendues : famille_code, code, article, prix, rang.

6. Pour chaque adhérent, classer ses tickets du plus récent au plus ancien.

Utiliser ROW_NUMBER().

Colonnes attendues : adherent_id, ticket_id, date_ticket, numero_ticket.

7. Pour chaque famille, classer les articles du moins cher au plus cher en utilisant DENSE_RANK().

Comparer le résultat avec celui obtenu avec RANK() lorsqu'il existe plusieurs articles au même prix.

8. Pour chaque adhérent, identifier son premier ticket.

La requête doit retourner uniquement les premiers tickets.

Indice : utiliser ROW_NUMBER() dans une sous-requête ou une CTE.

Niveau 3 — Cumuls​

9. Pour chaque ticket, afficher les lignes dans l'ordre de leur id et calculer le montant cumulé du ticket au fur et à mesure des lignes.

Colonnes attendues :

ticket_id
ligne_id
article_code
montant_ligne
montant_cumule

10. Pour chaque adhérent, afficher ses tickets chronologiquement et calculer le montant cumulé de ses achats.

Cela nécessite de calculer le montant de chaque ticket avant d'appliquer la fonction de fenêtrage.

11. Pour chaque famille, calculer le prix cumulé des articles, les articles étant ordonnés par prix croissant.

Niveau 4 — Comparaison avec les lignes précédentes​

Ces questions permettent d'introduire LAG().

12. Pour chaque adhérent, afficher chaque ticket avec la date de son ticket précédent.

Colonnes attendues :

adherent_id
ticket_id
date_ticket
date_ticket_precedent

13. Pour chaque adhérent, calculer le nombre de jours écoulés depuis son ticket précédent.

Exemple :

adhérent   ticket   date          ticket précédent   jours
42         1001     2026-01-10    NULL               NULL
42         1045     2026-02-03    2026-01-10         24
42         1087     2026-03-15    2026-02-03         40

14. Pour chaque article, afficher le prix de la ligne précédente où cet article a été vendu.

On pourra utiliser :

LAG(prix_unitaire)
OVER (
    PARTITION BY article_code
    ORDER BY ticket_id
)

Puis afficher également la différence de prix entre les deux ventes.

Niveau 5 — Comparaison avec le suivant​

15. Pour chaque adhérent, afficher la date du ticket suivant.

Utiliser LEAD().

16. Pour chaque adhérent, déterminer le délai entre chaque ticket et le ticket suivant.

Identifier ensuite les adhérents ayant eu le plus grand intervalle entre deux achats consécutifs.

Niveau 6 — Questions plus métier​

17. Pour chaque ticket, déterminer quelle ligne représente la plus grosse dépense du ticket.

Utiliser ROW_NUMBER() ou RANK() avec une partition par ticket_id.

18. Pour chaque adhérent, déterminer son article le plus acheté en quantité.

Il faudra d'abord agréger les quantités par adhérent et par article, puis utiliser une fonction de fenêtrage.

19. Pour chaque famille, déterminer l'article le plus cher.

Le résultat doit contenir un seul article par famille.

20. Pour chaque adhérent, afficher ses tickets et indiquer pour chacun s'il représente :

  • son premier achat ;
  • son dernier achat ;
  • un achat intermédiaire.

Niveau 7 — Challenge​

21. Pour chaque adhérent, calculer le montant moyen de ses tickets, puis afficher chaque ticket avec :

  • son montant ;
  • la moyenne de l'adhérent ;
  • l'écart par rapport à cette moyenne ;
  • le rang du ticket parmi les achats de l'adhérent.

22. Pour chaque famille, calculer la part de chaque article dans la valeur totale des articles de la famille.

Par exemple :

famille   article       prix    total_famille    part
CHAUSSURE A             50      350              14,29 %
CHAUSSURE B             80      350              22,86 %
...

Ici, SUM(...) OVER (PARTITION BY ...) est particulièrement pertinent.

23. Pour chaque ticket, calculer la part de chaque ligne dans le montant total du ticket.

Exemple :

ticket   article   montant   total_ticket   part
1001     A         20        100            20 %
1001     B         50        100            50 %
1001     C         30        100            30 %

24. Pour chaque adhérent, identifier les tickets dont le montant est supérieur à la moyenne de ses tickets.

La difficulté est de ne pas utiliser une sous-requête corrélée : la moyenne doit être obtenue avec une fonction de fenêtrage.

25. — Challenge final

Pour chaque adhérent, produire un tableau contenant :

adhérent
ticket
date
montant_ticket
moyenne_adhérent
rang_ticket
montant_cumule
ticket_precedent
écart_avec_ticket_précédent

Toutes les informations doivent être calculées à partir des fonctions de fenêtrage autant que possible.

Progression pédagogique​

Je structurerais l'exercice ainsi :

QuestionsNotions
1–4AVG() OVER, COUNT() OVER, SUM() OVER
5–8ROW_NUMBER, RANK, DENSE_RANK
9–11SUM() OVER (... ORDER BY ...)
12–14LAG()
15–16LEAD()
17–20Combinaison PARTITION BY + classement
21–24Agrégats fenêtrés + calculs métier
25Synthèse

Cela permet notamment de faire apparaître progressivement la différence fondamentale entre :

GROUP BY

qui réduit le nombre de lignes, et

SUM(...) OVER (PARTITION BY ...)

qui conserve les lignes tout en ajoutant une information calculée sur leur partition.

Oui, c'est effectivement le cœur de l'intérêt des fonctions de fenêtrage en BI. Les premières questions que j'avais proposées étaient davantage des exercices techniques sur OVER, mais elles ne mettent pas suffisamment en évidence le problème métier : analyser une série temporelle sans perdre le niveau de détail nécessaire à la comparaison.

Je proposerais donc de refaire l'exercice autour d'un scénario de pilotage commercial de market.

Exercice — Analyse des ventes avec les fonctions de fenêtrage​

On considère que le chiffre d'affaires est calculé à partir des lignes de tickets :

CA = prix_unitaire × quantite

1. Chiffre d'affaires mensuel​

Calculer le chiffre d'affaires réalisé pour chaque mois, avec :

  • l'année ;
  • le mois ;
  • le chiffre d'affaires.

2. Évolution mensuelle du chiffre d'affaires​

Pour chaque mois, afficher :

  • le chiffre d'affaires du mois ;
  • le chiffre d'affaires du mois précédent ;
  • l'évolution en valeur entre les deux mois.

On attend notamment l'utilisation de :

LAG(...)
OVER (ORDER BY ...)

3. Évolution mensuelle en pourcentage​

Compléter la question précédente en calculant :

((CA_mois - CA_mois_précédent) / CA_mois_précédent) × 100

Le résultat permettra par exemple d'obtenir :

année  mois  CA       CA précédent  évolution
2024   01    125000   NULL           NULL
2024   02    132000   125000         +5.60 %
2024   03    119000   132000         -9.85 %
...

4. Comparaison avec le même mois de l'année précédente​

Pour chaque mois, afficher :

  • le CA du mois ;
  • le CA du même mois de l'année précédente ;
  • l'évolution en valeur ;
  • l'évolution en pourcentage.

Par exemple :

année  mois  CA       CA N-1    évolution
2025   01    142000   125000    +13.60 %
2025   02    138000   132000    +4.55 %

Question importante : utiliser une fonction de fenêtrage pour réaliser cette comparaison, et non une auto-jointure.

C'est ici que LAG() devient particulièrement intéressant :

LAG(ca, 12) OVER (ORDER BY année, mois)

à condition que la série mensuelle soit complète.

5. Comparaison année par année​

Calculer, pour chaque année :

  • le CA annuel ;
  • le CA de l'année précédente ;
  • l'évolution en pourcentage.

On veut obtenir quelque chose comme :

année  CA          CA N-1       évolution
2023   1 245 000   NULL         NULL
2024   1 387 000   1 245 000    +11.41 %
2025   1 512 000   1 387 000    +9.01 %

Analyse des produits​

C'est là que le modèle devient particulièrement intéressant.

6. Évolution mensuelle des ventes d'un article​

Pour chaque article et chaque mois, calculer la quantité vendue.

Afficher :

  • l'article ;
  • le mois ;
  • la quantité vendue ;
  • la quantité vendue le mois précédent ;
  • l'évolution en pourcentage.

La fenêtre devra être partitionnée par article :

LAG(quantite)
OVER (
    PARTITION BY article_code
    ORDER BY année, mois
)

7. Évolution du chiffre d'affaires par article​

Même principe, mais cette fois avec le CA :

article   mois       CA       CA mois précédent   évolution
A001      2025-01    12000    NULL                NULL
A001      2025-02    14500    12000              +20.83 %
A001      2025-03    13200    14500               -8.97 %

8. Comparaison des ventes d'un produit avec l'année précédente​

Pour chaque article et chaque mois, comparer les ventes avec celles du même mois de l'année précédente.

La partition devient alors :

PARTITION BY article_code

et le décalage :

LAG(ca, 12)
OVER (
    PARTITION BY article_code
    ORDER BY année, mois
)

Analyse des familles​

9. Évolution du chiffre d'affaires par famille​

Calculer, pour chaque famille et chaque mois :

  • le CA ;
  • le CA du mois précédent ;
  • l'évolution en pourcentage.

On introduit ici une partition différente :

PARTITION BY famille_code

10. Part de chaque famille dans le chiffre d'affaires mensuel​

Pour chaque mois, calculer :

CA famille / CA total du mois × 100

Le résultat pourrait être :

mois      famille       CA famille   CA total    part
2025-01   CHAUSSURES    85000        150000      56.67 %
2025-01   VETEMENTS     42000        150000      28.00 %
2025-01   ACCESSOIRES   23000        150000      15.33 %

Ici, la fonction fondamentale est :

SUM(ca) OVER (PARTITION BY année, mois)

C'est un très bon exemple de BI, car GROUP BY seul ne permettrait pas de conserver simultanément le CA de la famille et le CA global du mois.

Analyse du classement​

11. Top des produits chaque mois​

Pour chaque mois, classer les articles par chiffre d'affaires décroissant.

Utiliser :

RANK()
OVER (
    PARTITION BY année, mois
    ORDER BY ca DESC
)

Puis ne conserver que les 5 premiers articles de chaque mois.

12. Évolution du classement des produits​

Pour chaque article, afficher son rang chaque mois et son rang le mois précédent.

Exemple :

article   mois      rang   rang précédent
A001      2025-01   4      NULL
A001      2025-02   2      4
A001      2025-03   1      2

On peut ensuite calculer :

gain de rang = ancien rang - nouveau rang

Analyse cumulée​

13. CA cumulé au cours de l'année​

Pour chaque mois, calculer le CA mensuel et le CA cumulé depuis le début de l'année.

Exemple :

année  mois  CA mensuel  CA cumulé
2025   01    120000      120000
2025   02    135000      255000
2025   03    110000      365000
...

Ici :

SUM(ca) OVER (
    PARTITION BY année
    ORDER BY mois
)

est particulièrement pédagogique.

14. CA cumulé par produit​

Même question, mais pour chaque article :

SUM(ca) OVER (
    PARTITION BY article_code, année
    ORDER BY mois
)

15. Comparaison du cumul avec l'année précédente​

Pour chaque mois, afficher :

  • CA cumulé de l'année ;
  • CA cumulé à la même date l'année précédente ;
  • écart en valeur ;
  • écart en pourcentage.

C'est une métrique très classique de reporting commercial YTD — Year To Date.

Challenge final : tableau de bord commercial

16. Construire un indicateur mensuel de performance​

Produire une ligne par mois contenant :

année
mois
ca
ca_mois_precedent
evolution_mensuelle
ca_meme_mois_n_1
evolution_annuelle
ca_cumule
ca_cumule_n_1
evolution_ytd

Avec notamment :

  • LAG() pour les comparaisons temporelles ;
  • SUM() OVER() pour les cumuls ;
  • PARTITION BY pour les séries indépendantes ;
  • plusieurs niveaux d'agrégation successifs avec des CTE.

17. Challenge produit​

Pour chaque mois et chaque article, produire :

année
mois
article
quantite
ca
ca_mois_precedent
evolution_mensuelle
ca_meme_mois_n_1
evolution_annuelle
rang_du_mois
ca_cumule_annuel

Cette dernière question fait réellement travailler les fonctions de fenêtrage comme elles sont utilisées dans un contexte BI / data analytics, plutôt que comme une simple collection de fonctions SQL.

Progression que je privilégierais​

QuestionsCompétence BIFonction
1Agrégation temporelleGROUP BY
2–3Évolution mensuelleLAG()
4YoY mensuelLAG(..., 12)
5Croissance annuelleLAG()
6–8Évolution produitPARTITION BY + LAG()
9Évolution famillePARTITION BY + LAG()
10Part de marchéSUM() OVER()
11–12ClassementRANK()
13–15YTD / cumulSUM() OVER(ORDER BY)
16–17Synthèse BIcombinaison des fenêtres

Attention pour comparer janvier 2025 à janvier 2024 avec LAG(..., 12), il faut disposer d'une série mensuelle complète. Si un mois sans vente disparaît du jeu de données, le décalage de 12 lignes ne signifie plus nécessairement « 12 mois avant ». C'est justement l'occasion d'introduire une table/calendrier ou une génération des mois manquants.