Retrouver la 100ème valeur. Optimisation

J'ai une table avec une seule colonne COL01 utile pour ce besoin.
Je veux la 100ème valeur de cette colonne COL01.

J'ai cette requête, elle convient parfaitement :

Code:

---

1 2 3

select COL01 from ( select COL01 , rank() over ( order by COL01) as rk from MaTable ) where rk = 100

---

J'imagine qu'il y a une fonction analytique qui peut faire ça sans requête imbriquée mais je ne la trouve pas.
Ca c'est mon besoin d'aujourd'hui.

Mais j'imagine qu'il y a aussi une syntaxe pour réécrire la requête ci-dessous :

Code:

---

1 2 3

select COL01, COL02, COL03 from ( select COL01, COL02 , COL03 rank() over ( partition by COL01 order by COL02) as rk from MaTable ) where rk = 100

---

Autrement dit, dans chaque groupe identifié par COL01, récupérer la 100 ème plus petite valeur sur la colonne COL02, et récupérer la description COL03 correspondant à cette 100ème valeur.

Si au lieu de la 100ème ligne, je veux la 1ère ligne, je sais faire , avec MIN () KEEP (DENSE_RANK ...)
Mais pour une ligne arbitraire, autre que la 1ère ?

Bonjour,

Quelle est ta version de base? A partir de la version 11.2 tu as la fonction NTH_VALUE:

https://docs.oracle.com/cd/E11882_01...htm#SQLRF30031

En 12c tu as le OFFSET : https://oracle-base.com/articles/12c...-queries-12cr1

Exemple pour avoir la 10ème ligne

Code:

---

1 2 3 4 5

WITH t AS (SELECT LEVEL num FROM dual connect BY LEVEL < 100) SELECT num FROM t ORDER BY num OFFSET 9 ROWS FETCH FIRST 1 ROW ONLY

---

10

La 10ème ligne en tri desc

Code:

---

1 2 3 4 5

WITH t AS (SELECT LEVEL num FROM dual connect BY LEVEL < 100) SELECT num FROM t ORDER BY num desc OFFSET 9 ROWS FETCH FIRST 1 ROW ONLY

---

90


Pour ton second cas, je ne pense pas qu'une seule requête non imbriquée le fasse

Citation:

---

Envoyé par McM

En 12c tu as le OFFSET : https://oracle-base.com/articles/12c...-queries-12cr1

Exemple pour avoir la 10ème ligne

Code:

---

1 2 3 4 5

WITH t AS (SELECT LEVEL num FROM dual connect BY LEVEL < 100) SELECT num FROM t ORDER BY num OFFSET 9 ROWS FETCH FIRST 1 ROW ONLY

---

10

---

ATTENTION : ceci peut être faux.... 100e valeur ne veut pas dire 100e ligne... À cause des ex æquo ! Une requête avec un RANK est donc nécessaire pour ce faire...

A +

Citation:

---

Envoyé par SQLpro

ATTENTION : ceci peut être faux.... 100e valeur ne veut pas dire 100e ligne... À cause des ex æquo !

---

Dans ce cas en effet l'utilisation de RANK() pour faire rnk = 100 peut ne rien ramener même s'il y a plus de 100 lignes. Il faut alors utiliser DENSE_RANK(), mais on peut ramener plusieurs lignes. Le mieux c'est ROW_NUMER() dans ce cas, d'ailleurs sous le capot la ROW LIMITING CLAUSE d'Oracle utilise ROW_NUMBER().

Je suis en version 12C.

J'avais regardé nth_value() avant de poser la question, et a priori, ça ne convient pas. Mais je vais fouiller plus, parce que je ne comprends pas à 100% ce que fait cette fonction.

Offset convient parfaitement. La gestion des ex-aequo me convient. Si mes valeurs sont 1,2,2,3,3,3,4,4 4, 5,5,5 etc. , je considère que la 10ème valeur de cette liste est 5. Et de toutes façons, dans ma clause de tri, je fais en sorte de ne pas avoir d'ex-aequo.

Merci.

Bonjour,

nth_value ne conviendra pas en effet puisque tu veux une seule ligne. Puisque tu es en 12c tu peux utiliser une requête match_recognize pour ton second point:

Code:

---

1 2 3 4 5 6 7 8 9 10

select * from matable match_recognize(partition by col01 order by col02 measures match_number() as mn, col02 as col02, col03 as col03 pattern (ligne{1}) define ligne as 1=1 ) where mn = 100;

---

Testé différentes versions avec match_recognize (notamment pattern A{1} et A{100}) sur une table de > 6M objects :
nécessite plus ou moins 60% du temps de la version avec rank() et pas de différences significatives entre les différents pattern.