Fonctions Analytiques SQL sous Oracle

Guide Complet d’Apprentissage

16 janvier 2026

Résumé
Ce document présente une synthèse complète des fonctions analytiques SQL sous
Oracle. Il couvre les concepts fondamentaux, la syntaxe détaillée de chaque fonction, des
exemples pratiques et des cas d’utilisation courants. Conçu pour les étudiants et dévelop-
peurs, il sert de référence complète pour maîtriser l’analyse de données avec SQL.

Table des matières

1 Introduction aux Fonctions Analytiques 5
1.1 Définition et Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2 Différence avec GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.3 Avantages Principaux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

2 Syntaxe Générale et Structure 5

2.1 Structure de Base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2 Composants Détaillés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2.1 PARTITION BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.2.2 ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.2.3 Window Clause (ROWS/RANGE) . . . . . . . . . . . . . . . . . . . . . 6

3 Fonctions d’Agrégation Analytique 6

3.1 SUM() - Somme Cumulative . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.1.1 Rôle et Utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.1.2 Syntaxe et Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.2 AVG() - Moyenne Mobile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.2.1 Rôle et Utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.2.2 Syntaxe et Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3 COUNT() - Comptage Analytique . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3.1 Rôle et Utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3.2 Syntaxe et Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

1
 3.4 MIN()/MAX() - Valeurs Extrêmes . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.4.1 Rôle et Utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
3.4.2 Syntaxe et Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8

4 Fonctions de Classement 8
4.1 ROW_NUMBER() - Numérotation Séquentielle . . . . . . . . . . . . . . . . . . 8
4.1.1 Caractéristiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
4.1.2 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
4.1.3 Cas d’Usage Typiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.2 RANK() - Classement avec Sauts . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.2.1 Caractéristiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.2.2 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
4.2.3 Cas d’Usage Typiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.3 DENSE_RANK() - Classement sans Sauts . . . . . . . . . . . . . . . . . . . . . 10
4.3.1 Caractéristiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.3.2 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.3.3 Cas d’Usage Typiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
4.4 Tableau Comparatif des Fonctions de Classement . . . . . . . . . . . . . . . . . 11
4.4.1 Règles de Choix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

5 Fonctions de Navigation 11
5.1 LAG() - Accès à la Ligne Précédente . . . . . . . . . . . . . . . . . . . . . . . . 11
5.1.1 Syntaxe Complète . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.1.2 Paramètres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.1.3 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
5.1.4 Cas d’Usage Typiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2 LEAD() - Accès à la Ligne Suivante . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.1 Syntaxe Complète . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.2 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
5.2.3 Cas d’Usage Typiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
5.3 Comparaison LAG() vs LEAD() . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

6 Fonctions de Première et Dernière Valeur 13

6.1 FIRST_VALUE() - Première Valeur . . . . . . . . . . . . . . . . . . . . . . . . 13
6.1.1 Rôle et Utilisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
6.1.2 Syntaxe et Exemples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
6.2 LAST_VALUE() - Dernière Valeur . . . . . . . . . . . . . . . . . . . . . . . . . 14

2
 6.2.1 Attention Importante . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
6.2.2 Syntaxe Correcte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
6.2.3 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
6.3 Patterns d’Utilisation Courants . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
6.3.1 Suivi d’Évolution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
6.3.2 État Courant dans Historique . . . . . . . . . . . . . . . . . . . . . . . . 15

7 Fonction NTILE() - Distribution 15

7.1 Présentation de NTILE() . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
7.1.1 Rôle et Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
7.1.2 Comportement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.2 Syntaxe et Paramètres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.3 Applications Pratiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.4 Comparaison avec DENSE_RANK() . . . . . . . . . . . . . . . . . . . . . . . . 16
7.5 Cas d’Usage Spécifiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.5.1 Analyse des Performances . . . . . . . . . . . . . . . . . . . . . . . . . . 16
7.5.2 Analyse des Prix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

8 Fenêtres Avancées (ROWS/RANGE BETWEEN) 17

8.1 Concepts des Fenêtres . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
8.1.1 Définition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
8.1.2 Syntaxe Complète . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.2 Options de Début et Fin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.3 ROWS vs RANGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.3.1 ROWS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.3.2 RANGE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.3.3 Exemple de Différence . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.4 Patterns de Fenêtres Courants . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.4.1 Pattern 1 : Cumul Standard . . . . . . . . . . . . . . . . . . . . . . . . . 18
8.4.2 Pattern 2 : Fenêtre Centrée . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.4.3 Pattern 3 : Fenêtre Arrière . . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.4.4 Pattern 4 : Fenêtre Avant . . . . . . . . . . . . . . . . . . . . . . . . . . 19
8.4.5 Pattern 5 : Toute la Partition . . . . . . . . . . . . . . . . . . . . . . . . 19
8.5 Exemples Complets Avancés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
8.5.1 Exemple 1 : Cumul avec Restart Annuel . . . . . . . . . . . . . . . . . . 20
8.5.2 Exemple 2 : Moyenne Mobile Excluant Courant . . . . . . . . . . . . . . 20

3
 8.5.3 Exemple 3 : Différence avec Moyenne Récente . . . . . . . . . . . . . . . 20
8.6 Performances et Optimisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

9 Applications Pratiques et Cas d’Usage 21

9.1 Rapport Commercial Complet . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
9.2 Détection d’Anomalies avec Statistiques . . . . . . . . . . . . . . . . . . . . . . 22
9.3 Analyse de Saisonnalité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
9.4 Gestion de Stock avec Historique . . . . . . . . . . . . . . . . . . . . . . . . . . 24

10 Bonnes Pratiques et Optimisation 26

10.1 Performance et Indexation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
10.1.1 Recommandations de Performance . . . . . . . . . . . . . . . . . . . . . 26
10.1.2 Exemple d’Optimisation . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
10.2 Lisibilité et Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
10.2.1 Pattern 1 : CTE pour Complexité . . . . . . . . . . . . . . . . . . . . . . 26
10.2.2 Pattern 2 : Commentaires pour Fenêtres Complexes . . . . . . . . . . . . 27
10.3 Gestion des Valeurs NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
10.3.1 Techniques de Gestion des NULL . . . . . . . . . . . . . . . . . . . . . . 27
10.3.2 Exemple Complet de Gestion NULL . . . . . . . . . . . . . . . . . . . . 28
10.4 Erreurs Courantes et Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
10.5 Checklist de Vérification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28

11 Feuille de Révision Rapide 29

11.1 Guide de Choix des Fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
11.2 Syntaxe des Fenêtres Courantes . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
11.3 Formules Utiles Prédéfinies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
11.4 Acronymes et Mnémotechniques . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
11.5 Checklist Examen/Devoir . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
11.6 Questions Types d’Examen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30

4
 1 Introduction aux Fonctions Analytiques

1.1 Définition et Objectifs

Les fonctions analytiques, également appelées window functions, permettent d’effectuer des
calculs sur un ensemble de lignes relatives à la ligne courante, tout en conservant toutes les
lignes individuelles de la requête.

1.2 Différence avec GROUP BY

Aspect GROUP BY Fonctions Analytiques

Résultat Agrégation (perd le détail) Conserve toutes les lignes
Syntaxe Clause principale Clause SELECT avec OVER()
Performance Souvent plus lent Optimisé pour grands jeux
Utilisation Statistiques globales Analyses détaillées

Table 1 – Comparaison GROUP BY vs Fonctions Analytiques

1.3 Avantages Principaux

— Conservation du niveau de détail original
— Calculs complexes sans sous-requêtes
— Performances améliorées
— Analyses temporelles simplifiées
— Rapports riches en contexte

2 Syntaxe Générale et Structure

2.1 Structure de Base

1 fo n c ti o n_ a na l y ti q ue ([ arguments ]) OVER (
2 [ PARTITION BY colonne1 , colonne2 , ...]
3 [ ORDER BY colonne [ ASC | DESC ] , ...]
4 [ window_clause ]
5 )

Listing 1 – Structure générale des fonctions analytiques

2.2 Composants Détaillés

2.2.1 PARTITION BY

— Rôle : Divise les données en groupes indépendants

— Effet : Les calculs redémarrent à zéro pour chaque partition
— Exemple :

5
 1 -- Calcul s p a r par r g i o n
2 SUM ( montant ) OVER ( PARTITION BY region )

2.2.2 ORDER BY

— Rôle : Définit l’ordre de traitement des lignes

— Critique pour les cumuls et comparaisons temporelles
— Exemple :
1 -- Cumul chronologique
2 SUM ( montant ) OVER ( ORDER BY date )

2.2.3 Window Clause (ROWS/RANGE)

— Rôle : Définit précisément la fenêtre de calcul

— Syntaxe : ROWS BETWEEN début AND fin
— Détails : Voir section 8

3 Fonctions d’Agrégation Analytique

3.1 SUM() - Somme Cumulative

3.1.1 Rôle et Utilisation

Calculer des totaux et cumuls tout en conservant les lignes détaillées.

3.1.2 Syntaxe et Exemples

1 -- 1. Total global ( constant sur toutes les lignes )

2 SELECT montant ,
3 SUM ( montant ) OVER () AS total_global
4 FROM ventes ;
5
6 -- 2. Total par groupe ( r e d m a r r e par partition )
7 SELECT region , montant ,
8 SUM ( montant ) OVER ( PARTITION BY region ) AS total_region
9 FROM ventes ;
10
11 -- 3. Cumul progressif
12 SELECT date , montant ,
13 SUM ( montant ) OVER ( ORDER BY date ) AS cumul
14 FROM ventes ;
15
16 -- 4. Cumul explicite avec f e n t r e
17 SELECT date , montant ,
18 SUM ( montant ) OVER (
19 ORDER BY date
20 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
21 ) AS cumul_explicite
22 FROM ventes ;

6
 Listing 2 – Utilisations de SUM() OVER()

Note Importante
Sans ORDER BY, SUM() OVER() donne un total constant pour toute la partition. Avec
ORDER BY, il donne un cumul progressif.

3.2 AVG() - Moyenne Mobile

3.2.1 Rôle et Utilisation

Calculer des moyennes sur des fenêtres glissantes ou fixes.

3.2.2 Syntaxe et Exemples

1 -- 1. Moyenne globale
2 AVG ( montant ) OVER () AS moyenne_globale
3
4 -- 2. Moyenne par groupe
5 AVG ( montant ) OVER ( PARTITION BY region ) AS moy_region
6
7 -- 3. Moyenne glissante sur 3 p r i o d e s
8 AVG ( montant ) OVER (
9 ORDER BY date
10 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
11 ) AS moyenne _glissan te
12
13 -- 4. Moyenne des 30 derniers jours
14 AVG ( valeur ) OVER (
15 ORDER BY date
16 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
17 ) AS moyenne_30j

Listing 3 – Utilisations de AVG() OVER()

3.3 COUNT() - Comptage Analytique

3.3.1 Rôle et Utilisation

Compter les lignes ou valeurs non-nulles dans des fenêtres.

3.3.2 Syntaxe et Exemples

1 -- 1. Nombre total de lignes

2 COUNT (*) OVER () AS nb_total
3
4 -- 2. Nombre par groupe
5 COUNT (*) OVER ( PARTITION BY region ) AS nb_region
6
7 -- 3. N u m r o de ligne dans le groupe
8 ROW_NUMBER () OVER ( PARTITION BY region ORDER BY date ) AS numero

7
9
10 -- 4. Compter valeurs non - nulles
11 COUNT ( commande_id ) OVER ( PARTITION BY client ) AS nb_commandes

Listing 4 – Utilisations de COUNT() OVER()

3.4 MIN()/MAX() - Valeurs Extrêmes

3.4.1 Rôle et Utilisation

Trouver les valeurs minimales et maximales dans une fenêtre.

3.4.2 Syntaxe et Exemples

1 -- 1. Min / Max global

2 MIN ( montant ) OVER () AS minimum_global
3 MAX ( montant ) OVER () AS maximum_global
4
5 -- 2. Min / Max par groupe
6 MIN ( montant ) OVER ( PARTITION BY region ) AS min_region
7 MAX ( montant ) OVER ( PARTITION BY region ) AS max_region
8
9 -- 3. cart au maximum
10 SELECT montant ,
11 MAX ( montant ) OVER ( PARTITION BY region ) AS max_region ,
12 MAX ( montant ) OVER ( PARTITION BY region ) - montant AS ecart_max
13 FROM ventes ;

Listing 5 – Utilisations de MIN()/MAX() OVER()

4 Fonctions de Classement

4.1 ROW_NUMBER() - Numérotation Séquentielle

4.1.1 Caractéristiques

— Numéro unique pour chaque ligne (1, 2, 3, ...)

— Pas d’égalités possibles
— Dépend de l’ORDER BY pour la séquence
— Redémarre à 1 pour chaque partition

4.1.2 Applications Pratiques

1 -- 1. N u m r o t e r toutes les lignes

2 ROW_NUMBER () OVER ( ORDER BY date ) AS numero
3
4 -- 2. N u m r o t e r par groupe avec tri
5 ROW_NUMBER () OVER (
6 PARTITION BY region
7 ORDER BY montant DESC

8
8 ) AS rang_region
9

10 -- 3. S l e c t i o n n e r les 3 premiers par r g i o n

11 SELECT * FROM (
12 SELECT * ,
13 ROW_NUMBER () OVER (
14 PARTITION BY region
15 ORDER BY montant DESC
16 ) AS rang
17 FROM ventes
18 ) WHERE rang <= 3;
19
20 -- 4. limination de doublons ( garder 1 occurrence )
21 DELETE FROM doublons
22 WHERE id IN (
23 SELECT id FROM (
24 SELECT id ,
25 ROW_NUMBER () OVER (
26 PARTITION BY colonne_ duplique e
27 ORDER BY id
28 ) AS dup_num
29 FROM doublons
30 ) WHERE dup_num > 1
31 );

Listing 6 – Utilisations de ROW_NUMBER()

4.1.3 Cas d’Usage Typiques

— Pagination (alternative à OFFSET/LIMIT)

— Sélection d’échantillons uniques
— Élimination des doublons
— Numérotation de séquences

4.2 RANK() - Classement avec Sauts

4.2.1 Caractéristiques

— Mêmes valeurs = même rang

— Saute les numéros après égalités
— Exemple : 1, 2, 2, 4, 5, ...

4.2.2 Applications Pratiques

1 -- 1. Classement g n r a l avec galits

2 RANK () OVER ( ORDER BY montant DESC ) AS classement
3
4 -- 2. Classement par groupe
5 RANK () OVER (
6 PARTITION BY region
7 ORDER BY montant DESC
8 ) AS rang_region
9

9
10 -- 3. Pour les podiums ( peut avoir ex - aequo )
11 SELECT * FROM (
12 SELECT athlete , score ,
13 RANK () OVER ( ORDER BY score DESC ) AS rang
14 FROM competitions
15 ) WHERE rang <= 3; -- M d a i l s (3+ si ex - aequo )

Listing 7 – Utilisations de RANK()

4.2.3 Cas d’Usage Typiques

— Classements sportifs et concours

— Évaluations avec notes identiques
— Sélections avec égalités possibles
— Classements officiels

4.3 DENSE_RANK() - Classement sans Sauts

4.3.1 Caractéristiques

— Mêmes valeurs = même rang

— Pas de saut de numéros
— Exemple : 1, 2, 2, 3, 4, ...

4.3.2 Applications Pratiques

1 -- 1. Classement dense continu

2 DENSE_RANK () OVER ( ORDER BY montant DESC ) AS classement_dense
3
4 -- 2. Segmentation en c a t g o r i e s
5 SELECT client , revenu ,
6 CASE
7 WHEN DENSE_RANK () OVER ( ORDER BY revenu DESC ) <= 10
8 THEN ’ Top 10 ’
9 WHEN DENSE_RANK () OVER ( ORDER BY revenu DESC ) <= 50
10 THEN ’ Top 50 ’
11 ELSE ’ Autre ’
12 END AS categorie
13 FROM clients ;
14

15 -- 3. Groupes de performance
16 DENSE_RANK () OVER (
17 PARTITION BY departement
18 ORDER BY performance DESC
19 ) AS c l a s s e m e n t _ d e p a r t e m e n t

Listing 8 – Utilisations de DENSE_RANK()

4.3.3 Cas d’Usage Typiques

— Segmentation marketing continue

10
 — Catégorisations sans sauts
— Analyses de distribution
— Déciles et percentiles (avec NTILE souvent mieux)

4.4 Tableau Comparatif des Fonctions de Classement

Scénario ROW_NUMBER RANK DENSE_RANK

Valeurs uniques 1,2,3,4 1,2,3,4 1,2,3,4
Avec égalité (90,90,80) 1,2,3 1,1,3 1,1,2
Après égalité Continue Saute numéro Continue
Usage typique ID unique Classement sportif Segmentation

Table 2 – Comparaison détaillée des fonctions de classement

4.4.1 Règles de Choix

— Pour un identifiant unique : ROW_NUMBER()

— Pour un classement officiel (sport, concours) : RANK()
— Pour des catégories continues : DENSE_RANK()
— Pour éliminer les doublons : ROW_NUMBER()
— Pour afficher tous les ex-aequo : RANK()

5 Fonctions de Navigation

5.1 LAG() - Accès à la Ligne Précédente

5.1.1 Syntaxe Complète

LAG(colonne, offset, default) OVER(partitions)

5.1.2 Paramètres

— colonne : Colonne dont on veut la valeur précédente

— offset : Nombre de lignes en arrière (défaut : 1)
— default : Valeur si pas de ligne précédente (défaut : NULL)

5.1.3 Applications Pratiques

1 -- 1. Valeur du mois p r c d e n t
2 LAG ( montant ) OVER (
3 PARTITION BY vendeur
4 ORDER BY mois
5 ) AS mois_precedent
6
7 -- 2. Avec valeur par d f a u t ( viter NULL )
8 LAG ( montant , 1 , 0) OVER (...) AS pre cedent_o u_zero

11
9
10 -- 3. R c u p r e r il y a 3 p r i o d e s
11 LAG ( montant , 3) OVER (...) AS il_y_a_3_mois
12
13 -- 4. Calcul de variation absolue
14 montant - LAG ( montant ) OVER (...) AS varia tion_abs olue
15
16 -- 5. Calcul de variation relative (%)
17 ( montant - LAG ( montant ) OVER (...) ) * 100.0 /
18 LAG ( montant ) OVER (...) AS va ri at ion _p ou rce nt
19
20 -- 6. D t e c t i o n de changement
21 CASE
22 WHEN montant > LAG ( montant ) OVER (...)
23 THEN ’ HAUSSE ’
24 WHEN montant < LAG ( montant ) OVER (...)
25 THEN ’ BAISSE ’
26 ELSE ’ STABLE ’
27 END AS tendance

Listing 9 – Utilisations de LAG()

5.1.4 Cas d’Usage Typiques

— Analyses temporelles (mois/année précédent(e))

— Calcul de variations et tendances
— Détection de changements significatifs
— Alertes sur baisses de performance
— Comparaisons périodiques

5.2 LEAD() - Accès à la Ligne Suivante

5.2.1 Syntaxe Complète

LEAD(colonne, offset, default) OVER(partitions)

5.2.2 Applications Pratiques

1 -- 1. Valeur du mois suivant

2 LEAD ( montant ) OVER (
3 PARTITION BY vendeur
4 ORDER BY mois
5 ) AS mois_suivant
6
7 -- 2. P r v i s i o n court terme
8 LEAD ( montant , 2 , AVG ( montant ) OVER () ) AS prevision_2_mois
9

10 -- 3. Calcul de progression a n t i c i p e
11 LEAD ( montant ) OVER (...) - montant AS pr o gr e ss i o n_ a tt e n du
12
13 -- 4. D t e c t i o n de pics futurs
14 CASE
15 WHEN LEAD ( montant ) OVER (...) > montant * 1.5
16 THEN ’ Pic attendu ’

12
17 ELSE ’ Stable ’
18 END AS alerte_pic
19
20 -- 5. Calcul de d u r e entre vnements
21 LEAD ( date_evenement ) OVER ( ORDER BY date_evenement ) - date_evenement
22 AS d u r ee _ ve r s _p r oc h ai n

Listing 10 – Utilisations de LEAD()

5.2.3 Cas d’Usage Typiques

— Prévisions et projections simples

— Détection d’opportunités futures
— Planification basée sur tendances
— Analyses de séquences d’événements
— Calculs de délais et attentes

5.3 Comparaison LAG() vs LEAD()

Caractéristique LAG() LEAD()

Direction Vers le passé Vers le futur
Usage typique Analyse historique Prévision
Défaut pour NULL Première ligne Dernière ligne
Paramètre offset Vers l’arrière Vers l’avant

Table 3 – Comparaison LAG() et LEAD()

Bonnes Pratiques avec LAG/LEAD

1. Toujours spécifier ORDER BY pour un ordre logique
2. Utiliser COALESCE ou valeur par défaut pour gérer les NULL
3. Vérifier les cas limites (première/dernière ligne)
4. Combiner avec PARTITION BY pour des analyses par groupe

6 Fonctions de Première et Dernière Valeur

6.1 FIRST_VALUE() - Première Valeur

6.1.1 Rôle et Utilisation

Retourner la première valeur d’une fenêtre selon l’ordre spécifié.

6.1.2 Syntaxe et Exemples

13
1 -- 1. P r e m i r e vente du vendeur
2 FIRST_VALUE ( montant ) OVER (
3 PARTITION BY vendeur
4 ORDER BY date_vente
5 ) AS premiere_vente
6
7 -- 2. Premier mois de chaque a n n e
8 FIRST_VALUE ( mois ) OVER (
9 PARTITION BY annee
10 ORDER BY mois_num
11 ) AS pr em ie r_m oi s_ ann ee
12
13 -- 3. volution depuis le d b u t
14 montant - FIRST_VALUE ( montant ) OVER (...) AS pr og re ssi on _t ota le
15
16 -- 4. Ratio par rapport au premier
17 montant * 100.0 / FIRST_VALUE ( montant ) OVER (...) AS pour cent_du_ debut
18
19 -- 5. Date de p r e m i r e commande par client
20 FIRST_VALUE ( date_commande ) OVER (
21 PARTITION BY client_id
22 ORDER BY date_commande
23 ) AS d a t e _ p r e m i e r e _ c o m m a n d e

Listing 11 – Utilisations de FIRST_VALUE()

6.2 LAST_VALUE() - Dernière Valeur

6.2.1 Attention Importante

Sans spécification de fenêtre, LAST_VALUE() retourne la valeur courante, pas la

dernière de la partition !

6.2.2 Syntaxe Correcte

1 -- INCORRECT : Donne la valeur courante

2 LAST_VALUE ( montant ) OVER ( ORDER BY date )
3
4 -- CORRECT : S p c i f i e r la f e n t r e c o m p l t e
5 LAST_VALUE ( montant ) OVER (
6 ORDER BY date
7 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
8 )

Listing 12 – Syntaxe correcte pour LAST_VALUE()

6.2.3 Applications Pratiques

1 -- 1. D e r n i r e vente du vendeur
2 LAST_VALUE ( montant ) OVER (
3 PARTITION BY vendeur
4 ORDER BY date_vente
5 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
6 ) AS derniere_vente

14
7
8 -- 2. Dernier statut connu
9 LAST_VALUE ( statut ) OVER (
10 PARTITION BY dossier_id
11 ORDER BY date_ modifica tion
12 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
13 ) AS dernier_statut
14

15 -- 3. Comparaison p r e m i r e / d e r n i r e
16 LAST_VALUE ( montant ) OVER (...) - FIRST_VALUE ( montant ) OVER (...)
17 AS evolution_totale
18
19 -- 4. D e r n i r e valeur non - nulle
20 LAST_VALUE ( montant IGNORE NULLS ) OVER (...) AS d e r n i e r e _ v a l e u r _ n o n _ n u l l e

Listing 13 – Utilisations correctes de LAST_VALUE()

6.3 Patterns d’Utilisation Courants

6.3.1 Suivi d’Évolution

1 SELECT periode , valeur ,

2 FIRST_VALUE ( valeur ) OVER ( ORDER BY periode ) AS debut ,
3 LAST_VALUE ( valeur ) OVER (
4 ORDER BY periode
5 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
6 ) AS fin ,
7 ( LAST_VALUE ( valeur ) OVER (...) - FIRST_VALUE ( valeur ) OVER (...) )
8 AS evolution
9 FROM mesures ;

6.3.2 État Courant dans Historique

1 SELECT date_modification , statut ,

2 LAST_VALUE ( statut ) OVER (
3 ORDER BY date_ modifica tion
4 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
5 ) AS statut_courant
6 FROM historique ;

Règle d’Or pour LAST_VALUE

Toujours utiliser ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
avec LAST_VALUE() pour obtenir réellement la dernière valeur de la fenêtre.

7 Fonction NTILE() - Distribution

7.1 Présentation de NTILE()

7.1.1 Rôle et Principe

Diviser les données en N groupes de taille aussi égale que possible selon l’ordre spécifié.

15
 7.1.2 Comportement

— Crée N groupes (buckets) de taille similaire

— Les groupes sont numérotés de 1 à N
— Groupe 1 contient les plus petites valeurs (si ORDER BY ASC)
— Groupe N contient les plus grandes valeurs

7.2 Syntaxe et Paramètres

NTILE(n) OVER(ORDER BY colonne)

— n : Nombre de groupes à créer (2=median split, 4=quartiles, 10=déciles)

— ORDER BY : Obligatoire pour déterminer l’ordre des valeurs

7.3 Applications Pratiques

1 -- 1. Quartiles (4 groupes gaux )
2 NTILE (4) OVER ( ORDER BY montant DESC ) AS quartile
3 -- Groupe 1 = Top 25% , Groupe 4 = Bottom 25%
4
5 -- 2. D c i l e s (10 groupes )
6 NTILE (10) OVER ( ORDER BY score ) AS decile
7 -- Groupe 1 = Bottom 10% , Groupe 10 = Top 10%
8

9 -- 3. M d i a n split (2 groupes )
10 NTILE (2) OVER ( ORDER BY revenu ) AS groupe_revenu
11 -- Groupe 1 = Moins de 50% , Groupe 2 = Plus de 50%
12
13 -- 4. Par partition ( quartiles par r g i o n )
14 NTILE (4) OVER (
15 PARTITION BY region
16 ORDER BY performance
17 ) AS quartile_region
18
19 -- 5. Segmentation marketing a v a n c e
20 SELECT client_id , depenses ,
21 CASE NTILE (5) OVER ( ORDER BY depenses DESC )
22 WHEN 1 THEN ’ VIP ( Top 20%) ’
23 WHEN 2 THEN ’ F i d l e (20 -40%) ’
24 WHEN 3 THEN ’ Moyen (40 -60%) ’
25 WHEN 4 THEN ’ Occasionnel (60 -80%) ’
26 WHEN 5 THEN ’ Nouveau (80 -100%) ’
27 END AS segment_client
28 FROM clients ;

Listing 14 – Utilisations de NTILE()

7.4 Comparaison avec DENSE_RANK()

7.5 Cas d’Usage Spécifiques

7.5.1 Analyse des Performances

16
 Caractéristique NTILE(4) DENSE_RANK()
Nombre groupes Fixe (4) Variable selon valeurs
Taille groupes Égale Variable
Valeurs égales Même groupe possible Même rang
Usage typique Quartiles/déciles Classement continu

Table 4 – NTILE() vs DENSE_RANK()

1 -- Classer les vendeurs en quartiles

2 SELECT vendeur , ventes ,
3 NTILE (4) OVER ( ORDER BY ventes DESC ) AS quartile_perf ,
4 CASE NTILE (4) OVER ( ORDER BY ventes DESC )
5 WHEN 1 THEN ’ Top Performer ’
6 WHEN 2 THEN ’ Bon Performer ’
7 WHEN 3 THEN ’ Performer Moyen ’
8 WHEN 4 THEN ’ Am liorer ’
9 END AS categorie_perf
10 FROM stats_vendeurs ;

7.5.2 Analyse des Prix

1 -- Segmenter les produits par prix

2 SELECT produit , prix ,
3 NTILE (4) OVER ( ORDER BY prix ) AS quartile_prix ,
4 CASE
5 WHEN NTILE (4) OVER ( ORDER BY prix ) = 1 THEN ’ E n t r e de gamme ’
6 WHEN NTILE (4) OVER ( ORDER BY prix ) = 2 THEN ’ Milieu de gamme ’
7 WHEN NTILE (4) OVER ( ORDER BY prix ) = 3 THEN ’ Haut de gamme ’
8 WHEN NTILE (4) OVER ( ORDER BY prix ) = 4 THEN ’ Premium ’
9 END AS gamme
10 FROM produits ;

Conseils pour NTILE()

— Utiliser des nombres pairs (2, 4, 10) pour des analyses standard
— Toujours spécifier ORDER BY pour contrôler la répartition
— NTILE(100) donne les percentiles exacts
— Pour des groupes de taille exacte, vérifier le nombre total de lignes

8 Fenêtres Avancées (ROWS/RANGE BETWEEN)

8.1 Concepts des Fenêtres

8.1.1 Définition

Une fenêtre (window) définit précisément quelles lignes sont incluses dans le calcul pour chaque
ligne courante.

17
 8.1.2 Syntaxe Complète

1 FONCTION () OVER (
2 PARTITION BY ...
3 ORDER BY ...
4 { ROWS | RANGE } BETWEEN debut AND fin
5 )

8.2 Options de Début et Fin

Option Signification Exemple

UNBOUNDED PRECEDING Depuis le début de la partition Ligne 1 de la partition
n PRECEDING n lignes avant la courante 2 lignes avant la courante
CURRENT ROW Ligne courante Ligne actuelle
n FOLLOWING n lignes après la courante 3 lignes après la courante
UNBOUNDED FOLLOWING Jusqu’à la fin de la partition Dernière ligne de la partition

Table 5 – Options de fenêtrage

8.3 ROWS vs RANGE

8.3.1 ROWS

— Compte les lignes physiques

— Ignore les égalités de valeurs
— Plus rapide que RANGE

8.3.2 RANGE

— Compte les valeurs

— Inclut toutes les lignes avec même valeur
— Plus lent mais sémantique différente

8.3.3 Exemple de Différence

1 -- Avec ROWS : exactement 2 lignes physiquement avant

2 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
3
4 -- Avec RANGE : toutes les lignes avec valeur valeur courante
5 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

8.4 Patterns de Fenêtres Courants

8.4.1 Pattern 1 : Cumul Standard

18
1 -- Du d b u t jusqu ’ la ligne courante
2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
3
4 -- Exemple : Cumul mensuel
5 SELECT mois , ventes ,
6 SUM ( ventes ) OVER (
7 ORDER BY mois
8 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
9 ) AS cumul_annuel
10 FROM ventes _mensuel les ;

8.4.2 Pattern 2 : Fenêtre Centrée

1 -- Ligne courante + une avant + une a p r s

2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
3
4 -- Exemple : Moyenne mobile sur 3 points
5 SELECT date , temperature ,
6 AVG ( temperature ) OVER (
7 ORDER BY date
8 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
9 ) AS moyenne _glissan te
10 FROM temperatures ;

8.4.3 Pattern 3 : Fenêtre Arrière

1 -- Les N d e r n i r e s lignes ( inclut courante )

2 ROWS BETWEEN N PRECEDING AND CURRENT ROW
3
4 -- Exemple : Somme des 7 derniers jours
5 SELECT date , ventes ,
6 SUM ( ventes ) OVER (
7 ORDER BY date
8 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
9 ) AS somme_7_jours
10 FROM v e nt e s _q u ot i d ie n ne s ;

8.4.4 Pattern 4 : Fenêtre Avant

1 -- Les N prochaines lignes ( inclut courante )

2 ROWS BETWEEN CURRENT ROW AND N FOLLOWING
3
4 -- Exemple : Moyenne des 3 prochains mois
5 SELECT mois , budget ,
6 AVG ( budget ) OVER (
7 ORDER BY mois
8 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
9 ) AS m o y e n n e _ 3 _ m o i s _ f u t u r s
10 FROM budget_prev ;

8.4.5 Pattern 5 : Toute la Partition

19
1 -- Toutes les lignes du groupe
2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
3
4 -- Exemple : Total du groupe sur chaque ligne
5 SELECT region , ville , population ,
6 SUM ( population ) OVER (
7 PARTITION BY region
8 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
9 ) AS total_region
10 FROM villes ;

8.5 Exemples Complets Avancés

8.5.1 Exemple 1 : Cumul avec Restart Annuel

1 SELECT annee , mois , ventes ,

2 SUM ( ventes ) OVER (
3 PARTITION BY annee
4 ORDER BY mois
5 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
6 ) AS cumul_annuel ,
7 SUM ( ventes ) OVER (
8 ORDER BY annee , mois
9 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
10 ) AS cumul_total
11 FROM ventes_annuelles ;

8.5.2 Exemple 2 : Moyenne Mobile Excluant Courant

1 -- Moyenne des 4 p r c d e n t s ( exclut courant )

2 SELECT date , valeur ,
3 AVG ( valeur ) OVER (
4 ORDER BY date
5 ROWS BETWEEN 4 PRECEDING AND 1 PRECEDING
6 ) AS mo y e n n e _ 4 _ p r e c e d e n t s
7 FROM s eri es _t emp or el les ;

8.5.3 Exemple 3 : Différence avec Moyenne Récente

1 SELECT date , ventes ,

2 AVG ( ventes ) OVER (
3 ORDER BY date
4 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
5 ) AS moyenne_30j ,
6 ventes - AVG ( ventes ) OVER (
7 ORDER BY date
8 ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
9 ) AS di ff er enc e_ mo yen ne
10 FROM v e nt e s _j o ur n a li e re s ;

20
 Fenêtre Performance Recommandation
ROWS Excellente Toujours préférer
RANGE Moyenne Éviter si possible
UNBOUNDED Variable Limiter si grands jeux
n PRECEDING/FOLLOWING Bonne Préférer aux UNBOUNDED

Table 6 – Performance des fenêtres

8.6 Performances et Optimisation

Règles d’Or pour les Fenêtres

1. Préférer ROWS à RANGE pour la performance
2. Limiter la taille des fenêtres quand c’est possible
3. Toujours inclure ORDER BY pour les fenêtres glissantes
4. Tester avec EXPLAIN PLAN pour les requêtes complexes

9 Applications Pratiques et Cas d’Usage

9.1 Rapport Commercial Complet

1 SELECT
2 -- D o n n e s de base
3 v . region ,
4 v . vendeur ,
5 TO_CHAR ( v . date_vente , ’ YYYY - MM ’) AS mois ,
6 v . montant ,
7
8 -- 1. Analyses temporelles
9 LAG ( v . montant ) OVER (
10 PARTITION BY v . vendeur
11 ORDER BY v . date_vente
12 ) AS mois_precedent ,
13
14 v . montant - LAG ( v . montant ) OVER (
15 PARTITION BY v . vendeur
16 ORDER BY v . date_vente
17 ) AS variation_montant ,
18
19 -- 2. Pourcentage de variation
20 ROUND (
21 ( v . montant - LAG ( v . montant ) OVER (...) ) * 100.0 /
22 NULLIF ( LAG ( v . montant ) OVER (...) , 0) ,
23 2
24 ) AS variation_pourcent ,
25
26 -- 3. Cumuls
27 SUM ( v . montant ) OVER (
28 PARTITION BY v . vendeur
29 ORDER BY v . date_vente
30 ) AS cumul_vendeur ,
31
32 -- 4. Analyses comparatives
33 v . montant * 100.0 / SUM ( v . montant ) OVER (

21
34 PARTITION BY v . region , TO_CHAR ( v . date_vente , ’ YYYY - MM ’)
35 ) AS part_region_mois ,
36
37 -- 5. Classements
38 RANK () OVER (
39 PARTITION BY v . region , TO_CHAR ( v . date_vente , ’ YYYY - MM ’)
40 ORDER BY v . montant DESC
41 ) AS classement_regional_mois ,
42
43 -- 6. Performance relative
44 v . montant * 100.0 / FIRST_VALUE ( v . montant ) OVER (
45 PARTITION BY v . region , TO_CHAR ( v . date_vente , ’ YYYY - MM ’)
46 ORDER BY v . montant DESC
47 ) AS pourcent_du_meilleur ,
48
49 -- 7. Segmentation
50 NTILE (4) OVER (
51 PARTITION BY TO_CHAR ( v . date_vente , ’ YYYY - MM ’)
52 ORDER BY v . montant DESC
53 ) AS quartile_mensuel ,
54
55 -- 8. Tendance
56 CASE
57 WHEN v . montant > LAG ( v . montant ) OVER (...) THEN ’ HAUSSE ’
58 WHEN v . montant < LAG ( v . montant ) OVER (...) THEN ’ BAISSE ’
59 WHEN LAG ( v . montant ) OVER (...) IS NULL THEN ’ NOUVEAU ’
60 ELSE ’= STABLE ’
61 END AS tendance
62
63 FROM ventes v
64 WHERE v . date_vente >= ADD_MONTHS ( SYSDATE , -12)
65 ORDER BY v . region , v . vendeur , v . date_vente ;

Listing 15 – Rapport analytique commercial complet

9.2 Détection d’Anomalies avec Statistiques

1 WITH stats_ventes AS (
2 SELECT
3 vendeur_id ,
4 date_vente ,
5 montant ,
6 -- Moyenne glissante sur 30 jours
7 AVG ( montant ) OVER (
8 PARTITION BY vendeur_id
9 ORDER BY date_vente
10 ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING
11 ) AS moyenne_30j_glissante ,
12

13 -- cart - type sur 30 jours

14 STDDEV ( montant ) OVER (
15 PARTITION BY vendeur_id
16 ORDER BY date_vente
17 ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING
18 ) AS ecart_type_30j ,
19
20 -- Nombre de jours avec d o n n e s
21 COUNT ( montant ) OVER (
22 PARTITION BY vendeur_id

22
23 ORDER BY date_vente
24 ROWS BETWEEN 29 PRECEDING AND 1 PRECEDING
25 ) AS nb_jours_donnees
26
27 FROM ve n t es _ qu o t id i en n es
28 WHERE date_vente >= SYSDATE - 365
29 )
30 SELECT
31 vendeur_id ,
32 date_vente ,
33 montant ,
34 moyenne_30j_glissante ,
35 ecart_type_30j ,
36

37 -- Calcul des bornes

38 m o y e n n e _ 3 0 j _ g l i s s a n t e - (2 * ecart_type_30j ) AS borne_inf ,
39 m o y e n n e _ 3 0 j _ g l i s s a n t e + (2 * ecart_type_30j ) AS borne_sup ,
40
41 -- D t e c t i o n d ’ anomalie
42 CASE
43 WHEN nb_jours_donnees >= 15 -- Au moins 15 jours de d o n n e s
44 AND montant > m o y e n n e _ 3 0 j _ g l i s s a n t e + (3 * ecart_type_30j )
45 THEN ’ PIC EXCEPTIONNEL ’
46
47 WHEN nb_jours_donnees >= 15
48 AND montant < m o y e n n e _ 3 0 j _ g l i s s a n t e - (3 * ecart_type_30j )
49 THEN ’ BAISSE EXCEPTIONNELLE ’
50
51 WHEN nb_jours_donnees >= 15
52 AND montant > m o y e n n e _ 3 0 j _ g l i s s a n t e + (2 * ecart_type_30j )
53 THEN ’ PIC SIGNIFICATIF ’
54
55 WHEN nb_jours_donnees >= 15
56 AND montant < m o y e n n e _ 3 0 j _ g l i s s a n t e - (2 * ecart_type_30j )
57 THEN ’ BAISSE SIGNIFICATIVE ’
58
59 ELSE ’ NORMAL ’
60 END AS statut_anomalie ,
61
62 -- cart en sigmas
63 ROUND (
64 ( montant - m o y e n n e _ 3 0 j _ g l i s s a n t e ) / NULLIF ( ecart_type_30j , 0) ,
65 2
66 ) AS ecart_sigmas
67
68 FROM stats_ventes
69 WHERE date_vente >= SYSDATE - 30
70 ORDER BY vendeur_id , date_vente ;

Listing 16 – Détection d’anomalies statistiques

9.3 Analyse de Saisonnalité

1 WITH ventes _mensuel les AS (
2 SELECT
3 EXTRACT ( YEAR FROM date_vente ) AS annee ,
4 EXTRACT ( MONTH FROM date_vente ) AS mois ,
5 SUM ( montant ) AS ventes_mois
6 FROM ventes

23
7 GROUP BY EXTRACT ( YEAR FROM date_vente ) ,
8 EXTRACT ( MONTH FROM date_vente )
9 )
10 SELECT
11 annee ,
12 mois ,
13 ventes_mois ,
14

15 -- 1. Moyenne historique du m m e mois

16 AVG ( ventes_mois ) OVER (
17 PARTITION BY mois
18 ORDER BY annee
19 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
20 ) AS moyenne_historique_mois ,
21
22 -- 2. cart la moyenne historique
23 ventes_mois - AVG ( ventes_mois ) OVER (
24 PARTITION BY mois
25 ORDER BY annee
26 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
27 ) AS ecart_saisonnalite ,
28
29 -- 3. Pourcentage vs historique
30 ROUND (
31 ventes_mois * 100.0 /
32 AVG ( ventes_mois ) OVER (
33 PARTITION BY mois
34 ORDER BY annee
35 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
36 ),
37 2
38 ) AS pourcent_vs_historique ,
39
40 -- 4. Tendance sur 12 mois glissants
41 AVG ( ventes_mois ) OVER (
42 ORDER BY annee , mois
43 ROWS BETWEEN 11 PRECEDING AND CURRENT ROW
44 ) AS moyenne_12_mois_glissante ,
45
46 -- 5. Croissance annuelle
47 ventes_mois - LAG ( ventes_mois , 12) OVER (
48 ORDER BY annee , mois
49 ) AS croissance_annuelle ,
50
51 -- 6. Classement des mois dans l ’ a n n e
52 RANK () OVER (
53 PARTITION BY annee
54 ORDER BY ventes_mois DESC
55 ) AS rang_mois_annee
56
57 FROM ventes _mensuel les
58 ORDER BY annee , mois ;

Listing 17 – Analyse de saisonnalité avancée

9.4 Gestion de Stock avec Historique

1 WITH mouvements_stock AS (
2 SELECT

24
3 produit_id ,
4 date_mouvement ,
5 type_mouvement ,
6 quantite ,
7 CASE
8 WHEN type_mouvement = ’ ENTREE ’ THEN quantite
9 WHEN type_mouvement = ’ SORTIE ’ THEN - quantite
10 ELSE 0
11 END AS effet_quantite
12 FROM mouvements
13 WHERE produit_id = : produit_id
14 )
15 SELECT
16 produit_id ,
17 date_mouvement ,
18 type_mouvement ,
19 quantite ,
20
21 -- Stock courant ( cumul des effets )
22 SUM ( effet_quantite ) OVER (
23 ORDER BY date_mouvement
24 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
25 ) AS stock_courant ,
26
27 -- Premier mouvement
28 FIRST_VALUE ( date_mouvement ) OVER (
29 ORDER BY date_mouvement
30 ) AS date_premier_mouvement ,
31
32 -- Dernier mouvement
33 LAST_VALUE ( date_mouvement ) OVER (
34 ORDER BY date_mouvement
35 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
36 ) AS date_dernier_mouvement ,
37
38 -- Q u a n t i t totale e n t r e
39 SUM ( CASE WHEN type_mouvement = ’ ENTREE ’ THEN quantite ELSE 0 END )
40 OVER ( ORDER BY date_mouvement ) AS total_entrees ,
41
42 -- Q u a n t i t totale sortie
43 SUM ( CASE WHEN type_mouvement = ’ SORTIE ’ THEN quantite ELSE 0 END )
44 OVER ( ORDER BY date_mouvement ) AS total_sorties ,
45

46 -- Rotation des stocks ( cumul sorties / stock moyen )

47 ROUND (
48 SUM ( CASE WHEN type_mouvement = ’ SORTIE ’ THEN quantite ELSE 0 END )
49 OVER ( ORDER BY date_mouvement ) * 365.0 /
50 NULLIF (
51 AVG ( SUM ( effet_quantite ) OVER (...) )
52 OVER ( ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) ,
53 0
54 ),
55 2
56 ) AS t a u x _ r o t a t io n _ a n n u e l
57

58 FROM mouvements_stock
59 ORDER BY date_mouvement ;

Listing 18 – Système de gestion de stock avec historique

25
 10 Bonnes Pratiques et Optimisation

10.1 Performance et Indexation

10.1.1 Recommandations de Performance

1. Préférer ROWS à RANGE : RANGE est plus lent car doit gérer les égalités
2. Limiter la taille des fenêtres : Éviter UNBOUNDED FOLLOWING si possible
3. Indexer les colonnes de PARTITION BY et ORDER BY
4. Éviter les fonctions complexes dans ORDER BY
5. Utiliser WHERE pour réduire le jeu de données en amont

10.1.2 Exemple d’Optimisation

1 -- Non o p t i m i s
2 SELECT * ,
3 SUM ( montant ) OVER (
4 ORDER BY TO_CHAR ( date , ’ YYYY - MM - DD ’)
5 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
6 ) AS total
7 FROM grandes_table
8 WHERE region = ’ Nord ’;
9
10 -- Optimis
11 WITH donnees_filtrees AS (
12 SELECT date , montant
13 FROM grandes_table
14 WHERE region = ’ Nord ’
15 AND date >= SYSDATE - 365 -- R d u i r e le scope
16 )
17 SELECT * ,
18 SUM ( montant ) OVER (
19 ORDER BY date -- Colonne i n d e x e
20 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
21 ) AS cumul
22 FROM donnees_filtrees ;

10.2 Lisibilité et Maintenance

10.2.1 Pattern 1 : CTE pour Complexité

1 WITH v ent es _p rep ar at ion AS (

2 SELECT
3 region ,
4 vendeur ,
5 date_vente ,
6 montant ,
7 -- Calculs i n t e r m d i a i r e s
8 LAG ( montant ) OVER (
9 PARTITION BY vendeur
10 ORDER BY date_vente
11 ) AS precedent ,
12 AVG ( montant ) OVER (

26
13 PARTITION BY region
14 ) AS moyenne_region
15 FROM ventes
16 WHERE date_vente >= SYSDATE - 365
17 )
18 SELECT
19 region ,
20 vendeur ,
21 date_vente ,
22 montant ,
23 precedent ,
24 moyenne_region ,
25 -- Calculs finaux
26 montant - precedent AS variation ,
27 montant - moyenne_region AS ecart_moyenne
28 FROM v ent es _p rep ar at ion ;

10.2.2 Pattern 2 : Commentaires pour Fenêtres Complexes

1 SELECT
2 date ,
3 valeur ,
4 -- Moyenne glissante sur 7 jours : 3 avant + courant + 3 a p r s
5 AVG ( valeur ) OVER (
6 ORDER BY date
7 ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
8 ) AS moyenne_7_jours ,
9

10 -- Cumul mensuel avec restart annuel

11 SUM ( valeur ) OVER (
12 PARTITION BY EXTRACT ( YEAR FROM date )
13 ORDER BY date
14 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
15 ) AS cumul_annuel
16 FROM mesures ;

10.3 Gestion des Valeurs NULL

10.3.1 Techniques de Gestion des NULL

1 -- 1. COALESCE pour valeurs par d f a u t

2 COALESCE ( LAG ( montant ) OVER (...) , 0) AS pr e ce d e nt _ sa n s_ n u ll
3
4 -- 2. IGNORE NULLS ( Oracle 21 c +)
5 LAG ( montant ) IGNORE NULLS OVER (...) AS p rec ed en t_n on _n ull
6
7 -- 3. FILTER pour exclure certaines valeurs
8 AVG ( montant ) FILTER ( WHERE montant > 0) OVER (...) AS moyenne_positive
9
10 -- 4. NULLIF pour viter les divisions par z r o
11 ( valeur - precedent ) * 100.0 / NULLIF ( precedent , 0) AS evolution_pct
12

13 -- 5. NVL ( Oracle s p c i f i q u e )
14 NVL ( LAG ( montant ) OVER (...) , 0) AS precedent_nvl

27
 10.3.2 Exemple Complet de Gestion NULL

1 SELECT
2 date ,
3 ventes ,
4 COALESCE ( LAG ( ventes ) OVER ( ORDER BY date ) , 0) AS ventes_precedent ,
5 ventes - COALESCE ( LAG ( ventes ) OVER ( ORDER BY date ) , 0) AS variation_abs ,
6 CASE
7 WHEN COALESCE ( LAG ( ventes ) OVER ( ORDER BY date ) , 0) = 0
8 THEN NULL
9 ELSE ROUND (
10 ( ventes - LAG ( ventes ) OVER ( ORDER BY date ) ) * 100.0 /
11 LAG ( ventes ) OVER ( ORDER BY date ) ,
12 2
13 )
14 END AS v ar iat io n_ pct _s af e
15 FROM v e nt e s _j o ur n a li e re s ;

10.4 Erreurs Courantes et Solutions

Erreur Cause Solution

LAST_VALUE donne valeur courante Fenêtre non spécifiée Ajouter
UNBOUNDED PREC
UNBOUNDED FOLL
Pas de cumul avec ORDER BY Mauvaise compréhension SUM() sans OR
total constant, a
donne cumul
Résultats différents avec égalités Mauvais choix fonction ROW_NUMBER
DENSE_RANK
Performance lente Fenêtres trop larges Limiter
PRECEDING/FOLL
Division par zéro Gestion NULL insuffisante Utiliser NULLIF o

Table 7 – Erreurs courantes et solutions

10.5 Checklist de Vérification

1. Partitionnement : Le PARTITION BY est-il correct ?
2. Ordre : L’ORDER BY donne-t-il la séquence voulue ?
3. Fenêtre : La fenêtre est-elle correctement définie ?
4. Fonction : La fonction correspond-elle au besoin ?
5. NULL : Les valeurs NULL sont-elles gérées ?
6. Performance : La requête est-elle optimisée ?
7. Résultats : Les cas limites sont-ils testés ?

28
 Besoin Fonction Exemple
Total sans regrouper SUM() OVER() SUM(montant) OVER()
Cumul progressif SUM() OVER(ORDER BY) SUM(montant) OVER(ORDER BY date)
Classement unique ROW_NUMBER() ROW_NUMBER() OVER(ORDER BY score DESC)
Classement avec égalités RANK() RANK() OVER(ORDER BY temps)
Segmentation continue DENSE_RANK() DENSE_RANK() OVER(ORDER BY revenu)
Valeur précédente LAG() LAG(montant) OVER(ORDER BY mois)
Valeur suivante LEAD() LEAD(montant) OVER(ORDER BY mois)
Première valeur FIRST_VALUE() FIRST_VALUE(nom) OVER(ORDER BY date)
Dernière valeur LAST_VALUE() avec fenêtre LAST_VALUE(date) OVER(... ROWS BETWEEN U
Quartiles/Déciles NTILE() NTILE(4) OVER(ORDER BY montant)
Moyenne mobile AVG() avec ROWS BETWEEN AVG(temp) OVER(ORDER BY date ROWS BETWEE

Table 8 – Guide rapide de choix des fonctions

11 Feuille de Révision Rapide

11.1 Guide de Choix des Fonctions

11.2 Syntaxe des Fenêtres Courantes

1 -- Cumul depuis d b u t
2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
3

4 -- 7 derniers jours ( inclut courant )

5 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
6
7 -- Moyenne sur 5 points
8 ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
9

10 -- Comparaison avec voisins

11 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
12
13 -- Toute la partition
14 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

11.3 Formules Utiles Prédéfinies

1 -- Pourcentage du total
2 colonne * 100.0 / SUM ( colonne ) OVER () AS pourcent_total
3
4 -- Pourcentage du total par groupe
5 colonne * 100.0 / SUM ( colonne ) OVER ( PARTITION BY groupe ) AS pourcent_groupe
6

7 -- Variation en pourcentage
8 ( colonne - LAG ( colonne ) OVER (...) ) * 100.0 / LAG ( colonne ) OVER (...) AS
variation_pct
9
10 -- cart la moyenne
11 colonne - AVG ( colonne ) OVER ( PARTITION BY groupe ) AS ecart_moyenne
12
13 -- Ratio vs maximum
14 colonne * 100.0 / MAX ( colonne ) OVER ( PARTITION BY groupe ) AS pourcent_max

29
15
16 -- Classement centile
17 RANK () OVER ( ORDER BY colonne ) * 100.0 / COUNT (*) OVER () AS centile

11.4 Acronymes et Mnémotechniques

— P.O.W.E.R : Partition → Order → Window → Execute → Result
— LAG : Looking At Going-back (regarde en arrière)
— LEAD : Looking Forward (regarde devant)
— RANK : Rang avec sauts (comme courses)
— DENSE : Dense = compact, pas de sauts
— NTILE : N Tiles = N carreaux/dalles
— ROWS : Lignes physiques (Rows = Rangées)
— RANGE : Plage de valeurs (Range = Gamme)

11.5 Checklist Examen/Devoir

1. Lire attentivement la question et identifier le besoin
2. Déterminer : Partitionnement nécessaire ? Ordre important ?
3. Choisir la fonction adaptée au besoin
4. Construire la clause OVER() complète
5. Vérifier les cas limites (première/dernière ligne, NULL)
6. Tester mentalement sur 2-3 lignes d’exemple
7. Valider la logique du résultat attendu

11.6 Questions Types d’Examen

1. "Donnez le classement des employés par département selon leur salaire"
2. "Calculez l’évolution mois par mois des ventes pour chaque vendeur"
3. "Trouvez la part de marché de chaque produit dans sa catégorie"
4. "Détectez les baisses de performance supérieures à 20%"
5. "Créez un rapport montrant les quartiles de performance par région"
6. "Comparez chaque vente avec la moyenne des 3 mois précédents"
7. "Trouvez le premier et dernier achat de chaque client"

Conclusion
Les fonctions analytiques SQL représentent un outil puissant pour l’analyse de données direc-
tement dans la base de données. Ce document a couvert :

— Les concepts fondamentaux des fonctions analytiques

— La syntaxe détaillée de chaque fonction
— Des exemples pratiques et cas d’usage réels
— Les bonnes pratiques d’optimisation

30
 — Les pièges courants à éviter

Points Clés à Retenir :

1. Les fonctions analytiques conservent le détail contrairement à GROUP BY

2. La clause OVER() est le cœur du système
3. Chaque fonction a un rôle spécifique : bien la choisir est crucial
4. Les fenêtres (ROWS/RANGE) permettent un contrôle fin des calculs
5. LAST_VALUE nécessite toujours une fenêtre explicite
6. La performance est meilleure avec ROWS et fenêtres limitées

Pour réussir : Comprenez la logique, pratiquez avec des exemples, et testez vos requêtes sur
des jeux de données simples avant de passer à des cas complexes.

Bon courage pour vos examens et projets !

31