Discussion :

[Magnus]

Salut,

Je débute dans l'utilisation des fonctions analytiques.
J'ai lu le tuto de Lalystar mais à ma grande surprise, je n'arrive pas à les appliquer sur l'exemple qui suit.

Soit la table DVP suivante :

CN CC
---------- ------------------------
12 A
20 A
20 B
2220 D
2 D
0 D
0 X
0 X
0 Z
1 Z
2 Z
3 Z
4 Z
4 Z

Je désire faire un regroupement par nombre de CN par CC et prendre seul les 3 plus grands regroupement.

Le regroupement :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
SELECT   COUNT(cn) AS NB,
		     CC
FROM     dvp
GROUP BY CC
ORDER BY NB DESC;

produit :

NB CC
---------- -------------------------
6 Z
3 D
2 A
2 X
1 B

Les 3 meilleurs regroupements :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  NB, CC
FROM   (SELECT   COUNT(CN) AS NB,
                 CC
        FROM     DVP
        GROUP BY CC
        ORDER BY NB DESC) T
WHERE  (SELECT   COUNT(*)
        FROM ( SELECT   COUNT(CN) AS NB,
                        CC
    	         FROM     DVP
	             GROUP BY CC
	             ORDER BY NB DESC ) T2
        WHERE T.NB <= T2.NB
        AND   T.CC <> T2.CC ) <= 3

Je voudrais réécrire (et simplifier) cette dernière requête avec les fonctions analytiques.
Je pense avoir trouvé mon bonheur lorsque j'ai trouvé :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT   T.NB, T.CC,
		     rank() over(order by T.NB desc) rk
FROM     ( SELECT   COUNT(cn) AS NB,
           		      CC
           FROM     dvp
           GROUP BY CC
           ORDER BY NB DESC ) T
ORDER BY NB DESC

qui donne :

NB CC RK
---------- ------------------------- ----------
6 Z 1
3 D 2
2 A 3
2 X 3
1 B 5

MAIS impossible de filtrer sur ce critère :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT   T.NB, T.CC,
		     rank() over(order by T.NB desc) rk
FROM     ( SELECT   COUNT(cn) AS NB,
           		      CC
           FROM     dvp
           GROUP BY CC
           ORDER BY NB DESC ) T
WHERE RANK() OVER(ORDER BY T.NB DESC) <= 3
ORDER BY NB DESC

engendre :

WHERE RANK() OVER(ORDER BY T.NB DESC) <= 3
*
ERREUR à la ligne 8 :
ORA-30483: fonctions de fenêtrage interdites ici

Comment faire avec les fonctions analytiques ?

[Magnus]

Une lumière m'éclaira soudainement :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT X.NB, X.CC, X.RK
FROM ( SELECT   T.NB NB,
                T.CC CC,
  		          RANK() OVER(ORDER BY T.NB DESC) RK
       FROM     ( SELECT   COUNT(CN) AS NB,
                  		      CC
                  FROM     DVP
                  GROUP BY CC
                  ORDER BY NB DESC ) T
       ORDER BY NB DESC ) X
WHERE X.RK < 4
ORDER BY X.NB DESC;

Bien sûr => il faut faire intervenir les fonctions analytiques à un niveau "inférieur" tout comme on le fait pour trier sur le rownum.

Désolé pour le dérangement !