NTH_VALUE

Syntaxe

Paramètres

Description

Cette fonction permet de retourner la n-ième valeur d'une expression dans une fenêtre de résultats définie, selon l'ordre spécifié. Elle est utile pour récupérer des valeurs à des positions spécifiques dans une partition ou un groupe.

Remarques

• La fonction NTH_VALUE est une fonction analytique introduite dans Oracle 11gR2. Elle permet d'extraire la n-ième valeur d'une colonne dans une fenêtre d'analyse, ce qui la rend très utile pour des rapports nécessitant des classements ou des comparaisons de rangs. Contrairement aux fonctions d'agrégation, elle ne réduit pas le nombre de lignes.
• La clause OVER est obligatoire dans l'utilisation de NTH_VALUE, car elle définit le contexte d'analyse : partitionnement, ordre, et fenêtres. Sans cette clause, la fonction ne peut pas déterminer dans quel cadre chercher la n-ième valeur. Cela signifie que NTH_VALUE ne peut pas être utilisée comme une simple fonction d'agrégation globale.
• L'argument measure_expr peut être de n'importe quel type de données (texte, nombre, date,...). La fonction retournera une valeur du même type que cette expression. C'est donc une fonction très flexible, adaptée à une grande variété de cas d'usage métier.
• Le paramètre n doit toujours être un entier positif, sinon une erreur sera levée. Par exemple, utiliser NTH_VALUE(salaire, 0) provoquera une erreur, car il n'existe pas de "zéroième" valeur. Il faut donc s'assurer de la validité du paramètre, notamment s'il est dynamique.
• L'option FROM LAST est moins utilisée mais très puissante, car elle permet de chercher la n-ième valeur à partir de la fin de la fenêtre. Cela est utile dans des analyses où l'on souhaite obtenir les dernières occurrences d'un ensemble ordonné.
• Le choix entre RESPECT NULLS et IGNORE NULLS a un impact majeur sur le résultat, surtout dans des colonnes où les données manquantes sont fréquentes. Avec IGNORE NULLS, une valeur significative sera retournée même si des nulls précèdent sa position logique.
• Lorsqu'il n'y a pas suffisamment de lignes dans la fenêtre, la fonction retourne NULL. Par exemple, si vous demandez la 5? valeur mais qu'il n'y a que trois lignes dans la partition, le résultat sera automatiquement nul, sans erreur levée.
• La fonction est particulièrement utile en combinaison avec des partitions, via PARTITION BY dans la clause OVER. Cela permet d'obtenir, par exemple, la 2^e vente la plus élevée par client ou la 3? date de commande par région, sans écrire de sous-requêtes complexes.
• L'ordre spécifié avec ORDER BY dans la clause OVER est indispensable, car c'est lui qui détermine l'arrangement des valeurs à analyser. Sans un ordre défini, Oracle ne saura pas comment interpréter "n-ième valeur".
• Il est possible d'utiliser NTH_VALUE avec une clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ce qui restreint la fenêtre à l'historique jusqu'à la ligne courante. Cela permet d'obtenir des résultats progressifs ou cumulatifs.
• NTH_VALUE diffère de ROWNUM ou RANK dans sa logique, car elle ne numérote pas les lignes mais sélectionne une valeur précise à une position donnée. Elle ne change pas la structure du jeu de données mais ajoute une information ciblée à chaque ligne.
• Cette fonction est utile dans les tableaux de bord ou les indicateurs de performance (KPIs), où l'on veut afficher des valeurs spécifiques comme "la 3^e meilleure vente", "le 2^e score le plus bas",... Elle remplace avantageusement des sous-requêtes imbriquées plus complexes.