Les fonctions analytiques sont très puissantes et très utiles, elles permettent d’alléger les requêtes SQL d’une façon élégante et performante.
Une fonction que j’ai utilisée récemment est la fonction ROW_NUMBER, elle permette de donner un ordre numérique pour les lignes, la pseudo-colonne ROWNUM existe mais son utilisation nécessite l’écriture de plus d’instruction.
Voila un exemple qui illustre l’utilité de cette fonction ; supposons que vous voulez faire un ordre sur la colonne salaire de la table EMP du schéma SCOTT :
SQL> select EMPNO,ENAME,SAL
2 from scotty.emp order by SAL;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7900 JAMES 950
7876 ADAMS 1100
7521 WARD 1250
7654 MARTIN 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
EMPNO ENAME SAL
---------- ---------- ----------
7788 SCOTT 3000
7902 FORD 3000
7839 KING 5000
14 ligne(s) selectionnee(s).
Ajoutons une colonne pour le classement de ces lignes selon le salaire, si on utilise directement ROWNUM ça marcherai pas :
SQL> select EMPNO,ENAME,SAL, rownum classement from scotty.emp order by SAL;
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 12
7876 ADAMS 1100 11
7521 WARD 1250 3
7654 MARTIN 1250 5
7934 MILLER 1300 14
7844 TURNER 1500 10
7499 ALLEN 1600 2
7782 CLARK 2450 7
7698 BLAKE 2850 6
7566 JONES 2975 4
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7788 SCOTT 3000 8
7902 FORD 3000 13
7839 KING 5000 9
14 ligne(s) selectionnee(s).
Certes il a classé les donner mais c’est donne l’ordre de l’extraction des lignes mais pas dans l’ordre des valeurs des salaires, pour corriger ça il faut écrire :
SQL> select EMPNO,ENAME,SAL,rownum classement from
2 (select EMPNO,ENAME,SAL from scotty.emp order by SAL) ;
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 4
7654 MARTIN 1250 5
7934 MILLER 1300 6
7844 TURNER 1500 7
7499 ALLEN 1600 8
7782 CLARK 2450 9
7698 BLAKE 2850 10
7566 JONES 2975 11
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7788 SCOTT 3000 12
7902 FORD 3000 13
7839 KING 5000 14
14 ligne(s) selectionnee(s).
C’est la que devienne l’utilisation des fonctions analytiques intéressante, voila le même résultat en utilisant ROW_NUMBER :
SQL> select EMPNO,ENAME,SAL, row_number() over(order by SAL) classement
2 from scotty.emp
3 order by SAL;
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 4
7654 MARTIN 1250 5
7934 MILLER 1300 6
7844 TURNER 1500 7
7499 ALLEN 1600 8
7782 CLARK 2450 9
7698 BLAKE 2850 10
7566 JONES 2975 11
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7788 SCOTT 3000 12
7902 FORD 3000 13
7839 KING 5000 14
14 ligne(s) selectionnee(s).
Le résultat est bien trier selon les valeurs des salaires et il est classé mais il y a un problème sur les salaires égaux (par exemple SCOTT et FORD) ont le même salaire mais un classement différent, la aussi c’est les fonctions analytiques qui viennent au secours :
SQL> select EMPNO,ENAME,SAL, DENSE_RANK() over(order by SAL) classement
2 from scotty.emp
3 order by SAL;
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 2
7876 ADAMS 1100 3
7521 WARD 1250 4
7654 MARTIN 1250 4
7934 MILLER 1300 5
7844 TURNER 1500 6
7499 ALLEN 1600 7
7782 CLARK 2450 8
7698 BLAKE 2850 9
7566 JONES 2975 10
EMPNO ENAME SAL CLASSEMENT
---------- ---------- ---------- ----------
7788 SCOTT 3000 11
7902 FORD 3000 11
7839 KING 5000 12
14 ligne(s) selectionnee(s).
La fonction DENSE_RANK permet de donner le même classement pour les mêmes valeurs dans un résultat trié.
Chaque fois que vous écrivez des instructions SQL ou des blocs PL/SQL afin d’analyser les données, jeter un coup d’œil sur les fonctions analytiques vous pouvez y trouver un contournement.
