🧪 Envie de pratiquer ? Exécutez les exemples de cet article dans notre SQL Playground gratuit — aucun logiciel à installer.
Fonctions analytiques SQL Oracle : guide complet et exemples pratiques
Les fonctions analytiques Oracle sont parmi les outils les plus puissants du langage SQL pour analyser des données sans réduire le nombre de lignes retournées. Contrairement aux fonctions d’agrégation classiques, les fonctions analytiques Oracle permettent de calculer des totaux cumulés, des rangs, des moyennes mobiles ou des comparaisons entre lignes, tout en conservant le détail de chaque enregistrement. Elles sont incontournables dans les contextes de reporting, de Business Intelligence et d’analyse de données métier.
Définition et utilisation des fonctions analytiques Oracle
Une fonction analytique (ou window function) calcule une valeur pour chaque ligne d’un résultat en se basant sur un ensemble de lignes voisines appelé fenêtre (window). Ce comportement la distingue fondamentalement des fonctions d’agrégation comme SUM() ou AVG() utilisées avec GROUP BY, qui réduisent plusieurs lignes en une seule.
En entreprise, les fonctions analytiques Oracle sont utilisées pour :
- Classer des éléments : classement des meilleurs vendeurs par région, des produits les plus vendus par catégorie.
- Calculer des totaux cumulés : suivi du chiffre d’affaires cumulé mois par mois.
- Comparer une ligne à ses voisines : afficher la valeur du mois précédent ou suivant (
LAG,LEAD). - Calculer des parts de marché : ratio d’une valeur individuelle sur un total de groupe.
- Identifier des doublons ou des écarts : détecter les lignes dupliquées avec
ROW_NUMBER().
Parmi les fonctions analytiques les plus utilisées sous Oracle, on trouve : ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), COUNT(), MIN(), MAX(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() et NTILE().
Syntaxe des fonctions analytiques Oracle
La syntaxe générale d’une fonction analytique Oracle est la suivante :
fonction_analytique() OVER (
[PARTITION BY colonne1, colonne2, ...]
[ORDER BY colonne3 [ASC|DESC]]
[ROWS | RANGE BETWEEN ... AND ...]
)
Voici le détail de chaque clause :
fonction_analytique(): la fonction à appliquer (SUM,RANK,LAG, etc.).OVER(): clause obligatoire qui indique à Oracle qu’il s’agit d’une fonction analytique. Sans elle, la fonction est traitée comme une agrégation classique.PARTITION BY: divise le résultat en groupes indépendants (similaire àGROUP BYmais sans réduire les lignes). Optionnel : si absent, toute la table forme une seule partition.ORDER BY: définit l’ordre des lignes au sein de chaque partition pour le calcul. Obligatoire pour les fonctions de classement (RANK,ROW_NUMBER) et les fonctions de décalage (LAG,LEAD).ROWS | RANGE BETWEEN ... AND ...: définit précisément la fenêtre de calcul autour de la ligne courante. Par exemple,ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWpour un cumul depuis le début.
Exemples pratiques de fonctions analytiques Oracle
Exemple 1 – Classement des vendeurs par chiffre d’affaires dans chaque région
Contexte métier : La direction commerciale souhaite identifier le top 3 des vendeurs par région, en se basant sur leur chiffre d’affaires annuel.
-- Classement des vendeurs par CA décroissant, par région
-- RANK() attribue le même rang en cas d'égalité (avec saut de rang suivant)
-- DENSE_RANK() attribue le même rang sans saut
SELECT
vendeur_id,
nom_vendeur,
region,
chiffre_affaires,
RANK() OVER (
PARTITION BY region -- Une partition par région
ORDER BY chiffre_affaires DESC -- Du plus grand CA au plus petit
) AS rang_region,
DENSE_RANK() OVER (
PARTITION BY region
ORDER BY chiffre_affaires DESC
) AS rang_dense_region
FROM vendeurs
ORDER BY region, rang_region;
Résultat attendu : Chaque vendeur conserve sa ligne avec son CA. La colonne rang_region indique sa position dans sa région. Si deux vendeurs ont le même CA, RANK() attribuera le rang 1 aux deux, puis passera au rang 3, tandis que DENSE_RANK() passera au rang 2.
Exemple 2 – Calcul du total cumulé du CA mensuel et comparaison avec le mois précédent
Contexte métier : Le service financier souhaite suivre l’évolution mensuelle des ventes avec un cumul progressif sur l’année et l’affichage du CA du mois précédent pour mesurer la progression.
-- Cumul progressif du CA et comparaison avec le mois précédent
-- LAG() permet d'accéder à la valeur d'une ligne précédente
-- SUM() OVER avec fenêtre cumulative calcule le total progressif
SELECT
annee,
mois,
ca_mensuel,
-- Total cumulé depuis le début de l'année
SUM(ca_mensuel) OVER (
PARTITION BY annee -- Repart à zéro chaque année
ORDER BY mois
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS ca_cumule,
-- CA du mois précédent (NULL pour le 1er mois)
LAG(ca_mensuel, 1, 0) OVER (
PARTITION BY annee
ORDER BY mois
) AS ca_mois_precedent,
-- Écart en valeur absolue vs mois précédent
ca_mensuel - LAG(ca_mensuel, 1, 0) OVER (
PARTITION BY annee
ORDER BY mois
) AS ecart_vs_precedent
FROM ventes_mensuelles
ORDER BY annee, mois;
Résultat attendu : Chaque ligne de résultat affiche le CA du mois, son cumul depuis janvier, le CA du mois précédent et l’écart. Le paramètre 0 dans LAG(ca_mensuel, 1, 0) est la valeur par défaut retournée pour le premier mois (pas de mois précédent).
Erreurs courantes avec les fonctions analytiques Oracle
Erreur : utiliser une fonction analytique dans la clause WHERE
Problème : Il est tentant de filtrer directement sur le résultat d’une fonction analytique dans la clause WHERE. Oracle renvoie alors une erreur ORA-00904 ou un comportement inattendu.
-- ❌ INCORRECT : Oracle n'autorise pas les fonctions analytiques dans WHERE
SELECT vendeur_id, nom_vendeur, chiffre_affaires,
RANK() OVER (ORDER BY chiffre_affaires DESC) AS rang
FROM vendeurs
WHERE RANK() OVER (ORDER BY chiffre_affaires DESC) <= 3; -- ERREUR !
Solution : Il faut encapsuler la requête dans une sous-requête ou utiliser une CTE (Common Table Expression) avec WITH, puis filtrer dans la requête externe.
-- ✅ CORRECT : filtrage via une sous-requête
SELECT *
FROM (
SELECT vendeur_id, nom_vendeur, chiffre_affaires,
RANK() OVER (ORDER BY chiffre_affaires DESC) AS rang
FROM vendeurs
)
WHERE rang <= 3;
Résumé des fonctions analytiques Oracle
| Point clé | Détail |
|---|---|
| Clause obligatoire | OVER() — indique qu’il s’agit d’une fonction analytique |
| Partitionnement | PARTITION BY — divise les données en groupes indépendants |
| Ordre de calcul | ORDER BY dans OVER() — définit le tri au sein de la fenêtre |
| Fenêtre de calcul | ROWS / RANGE BETWEEN — précise les bornes de la fenêtre |
| Fonctions de classement | RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE() |
| Fonctions de décalage | LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() |
| Fonctions d’agrégation analytiques | SUM(), AVG(), COUNT(), MIN(), MAX() |
| Filtrage sur résultat | Toujours via une sous-requête ou une CTE, jamais dans WHERE |
Bonnes pratiques Oracle
- Préférez les CTEs (
WITH) pour améliorer la lisibilité : lorsque vous combinez plusieurs fonctions analytiques ou que vous devez filtrer sur leurs résultats, l’utilisation d’une CTE rend le code plus lisible et plus facile à maintenir qu’une imbrication de sous-requêtes. - Soyez précis sur la clause de fenêtrage (
ROWSvsRANGE) : par défaut, Oracle utiliseRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWdès qu’unORDER BYest présent dansOVER(). Ce comportement peut inclure des lignes ayant la même valeur que la ligne courante. Utilisez explicitementROWS BETWEENpour un contrôle précis des lignes incluses dans la fenêtre.
Aller plus loin
Pour approfondir votre maîtrise des fonctions analytiques et du SQL Oracle, nous vous recommandons ces lectures complémentaires :
- Fonctions de rang Oracle : RANK, DENSE_RANK et ROW_NUMBER en détail — comprenez les nuances entre ces trois fonctions de classement et choisissez la bonne selon votre cas d’usage.
- LAG et LEAD Oracle : comparer des lignes entre elles — maîtrisez les fonctions de décalage pour analyser les évolutions temporelles et les tendances dans vos données.
- Les CTE Oracle avec la clause WITH — apprenez à structurer vos requêtes complexes avec les Common Table Expressions pour améliorer lisibilité et performances.