Les fonctions analytiques disponible dans ORACLE depuis 8.1 pour répondre à certains problèmes qu'il est difficile d'aborder avec SQL, comme les requêtes pour trouver les n premiers enregistrements dans un group; ou faire un calcul par rapport à la nième ligne, leurs utilisation est plus facile et plus performante que de faire des petites fonctions en PL/SQL, la syntaxe générale :
>fonction_analytique(argument) OVER ()
La clause partition :
Comme son nom l'indique permet de partitionner (pas au sens des tables partitionnées) un résultat en un ensemble de groups selon une valeur.
Exemple:
SQL> select ename,sal,deptno,avg(sal)
over(partition by deptno) from emp ;
14 ligne(s) sélectionné(s).
SQL>
La clause d'ordre :
Cette clause sert pour spécifier l'ordre dans lequel le groupe sera traité, attention ici l'ordre by à un comportement spécial il ajoute une fenêtre par défaut qui définit par la ligne en cours est tous les lignes précédentes dans le groupe.
Exemple:
SQL> select ename,sal,deptno,avg(sal)
over(partition by deptno order by ename) as avg_an from emp ;
14 ligne(s) sélectionné(s).
Ici comme vous le constater le résultat est partitionné par deptno est le tri par ENAME dans chaque département, mais la moyenne du salaire est calculé par rapport aux lignes précédentes en même département, mais si vous ne spécifiez pas PARTITION BY alors le groupe pris est toute la table.
SQL> select ename,sal,deptno,avg(sal) over(order by ename) as avg_an from emp ;
14 ligne(s) sélectionnée(s).
La clause de fenêtrage :
La clause de fenêtrage sert à définir une fenêtre dans le même groupe à lequel sera appliquée la fonction, exemple:
SQL> select ename,sal,deptno,avg(sal)
2 over(partition by deptno order by ename rows 2 preceding) as avg_an
3 from emp ;
14 ligne(s) sélectionnée(s).
Ici la fenêtre est de 3 lignes et la moyenne est calculée à partir de la ligne courante et les deux lignes précédente (ex: scott (3000+2975+3000)/3 = 2991,6667), il y a autres spécificateurs de fenetrage pour la list complète consulter la reference SQL.
Si vous omettez toutes les clauses alors la pas de notion d'ordre mais la fenêtre est la partition ne font qu'un toute la table :
SQL> select ename,sal,deptno,avg(sal) over() as avg_an from emp;
14 ligne(s) sélectionnée(s).
>fonction_analytique(argument) OVER (
La clause partition :
Comme son nom l'indique permet de partitionner (pas au sens des tables partitionnées) un résultat en un ensemble de groups selon une valeur.
Exemple:
SQL> select ename,sal,deptno,avg(sal)
over(partition by deptno) from emp ;
| ENAME | SAL | DEPTNO | AVG(SAL)OVER(PARTITIONBYDEPTNO) |
| CLARK | 2450 | 10 | 2916,66667 |
| KING | 5000 | 10 | 2916,66667 |
| MILLER | 1300 | 10 | 2916,66667 |
| SMITH | 800 | 20 | 2175 |
| ADAMS | 1100 | 20 | 2175 |
| FORD | 3000 | 20 | 2175 |
| SCOTT | 3000 | 20 | 2175 |
| JONES | 2975 | 20 | 2175 |
| ALLEN | 1600 | 30 | 1566,66667 |
| BLAKE | 2850 | 30 | 1566,66667 |
| MARTIN | 1250 | 30 | 1566,66667 |
| JAMES | 950 | 30 | 1566,66667 |
| TURNER | 1500 | 30 | 1566,66667 |
| WARD | 1250 | 30 | 1566,66667 |
14 ligne(s) sélectionné(s).
SQL>
La clause d'ordre :
Cette clause sert pour spécifier l'ordre dans lequel le groupe sera traité, attention ici l'ordre by à un comportement spécial il ajoute une fenêtre par défaut qui définit par la ligne en cours est tous les lignes précédentes dans le groupe.
Exemple:
SQL> select ename,sal,deptno,avg(sal)
over(partition by deptno order by ename) as avg_an from emp ;
| ENAME | SAL | DEPTNO | AVG_AN |
| CLARK | 2450 | 10 | 2450 |
| KING | 5000 | 10 | 3725 |
| MILLER | 1300 | 10 | 2916,66667 |
| ADAMS | 1100 | 20 | 1100 |
| FORD | 3000 | 20 | 2050 |
| JONES | 2975 | 20 | 2358,33333 |
| SCOTT | 3000 | 20 | 2518,75 |
| SMITH | 800 | 20 | 2175 |
| ALLEN | 1600 | 30 | 1600 |
| BLAKE | 2850 | 30 | 2225 |
| JAMES | 950 | 30 | 1800 |
| MARTIN | 1250 | 30 | 1662,5 |
| TURNER | 1500 | 30 | 1630 |
| WARD | 1250 | 30 | 1566,66667 |
14 ligne(s) sélectionné(s).
Ici comme vous le constater le résultat est partitionné par deptno est le tri par ENAME dans chaque département, mais la moyenne du salaire est calculé par rapport aux lignes précédentes en même département, mais si vous ne spécifiez pas PARTITION BY alors le groupe pris est toute la table.
SQL> select ename,sal,deptno,avg(sal) over(order by ename) as avg_an from emp ;
| ENAME | SAL | DEPTNO | AVG_AN |
| ADAMS | 1100 | 20 | 1100 |
| ALLEN | 1600 | 30 | 1350 |
| BLAKE | 2850 | 30 | 1850 |
| CLARK | 2450 | 10 | 2000 |
| FORD | 3000 | 20 | 2200 |
| JAMES | 950 | 30 | 1991,66667 |
| JONES | 2975 | 20 | 2132,14286 |
| KING | 5000 | 10 | 2490,625 |
| MARTIN | 1250 | 30 | 2352,77778 |
| MILLER | 1300 | 10 | 2247,5 |
| SCOTT | 3000 | 20 | 2315,90909 |
| SMITH | 800 | 20 | 2189,58333 |
| TURNER | 1500 | 30 | 2136,53846 |
| WARD | 1250 | 30 | 2073,21429 |
14 ligne(s) sélectionnée(s).
La clause de fenêtrage :
La clause de fenêtrage sert à définir une fenêtre dans le même groupe à lequel sera appliquée la fonction, exemple:
SQL> select ename,sal,deptno,avg(sal)
2 over(partition by deptno order by ename rows 2 preceding) as avg_an
3 from emp ;
| ENAME | SAL | DEPTNO | AVG_AN |
| CLARK | 2450 | 10 | 2450 |
| KING | 5000 | 10 | 3725 |
| MILLER | 1300 | 10 | 2916,66667 |
| ADAMS | 1100 | 20 | 1100 |
| FORD | 3000 | 20 | 2050 |
| JONES | 2975 | 20 | 2358,33333 |
| SCOTT | 3000 | 20 | 2991,66667 |
| SMITH | 800 | 20 | 2258,33333 |
| ALLEN | 1600 | 30 | 1600 |
| BLAKE | 2850 | 30 | 2225 |
| JAMES | 950 | 30 | 1800 |
| MARTIN | 1250 | 30 | 1683,33333 |
| TURNER | 1500 | 30 | 1233,33333 |
| WARD | 1250 | 30 | 1333,33333 |
14 ligne(s) sélectionnée(s).
Ici la fenêtre est de 3 lignes et la moyenne est calculée à partir de la ligne courante et les deux lignes précédente (ex: scott (3000+2975+3000)/3 = 2991,6667), il y a autres spécificateurs de fenetrage pour la list complète consulter la reference SQL.
Si vous omettez toutes les clauses alors la pas de notion d'ordre mais la fenêtre est la partition ne font qu'un toute la table :
SQL> select ename,sal,deptno,avg(sal) over() as avg_an from emp;
| ENAME | SAL | DEPTNO | AVG_AN |
| SMITH | 800 | 20 | 2073,21429 |
| ALLEN | 1600 | 30 | 2073,21429 |
| WARD | 1250 | 30 | 2073,21429 |
| JONES | 2975 | 20 | 2073,21429 |
| MARTIN | 1250 | 30 | 2073,21429 |
| BLAKE | 2850 | 30 | 2073,21429 |
| CLARK | 2450 | 10 | 2073,21429 |
| SCOTT | 3000 | 20 | 2073,21429 |
| KING | 5000 | 10 | 2073,21429 |
| TURNER | 1500 | 30 | 2073,21429 |
| ADAMS | 1100 | 20 | 2073,21429 |
| JAMES | 950 | 30 | 2073,21429 |
| FORD | 3000 | 20 | 2073,21429 |
| MILLER | 1300 | 10 | 2073,21429 |
14 ligne(s) sélectionnée(s).
0 comments:
Enregistrer un commentaire