[SQLSERVER 2008R2]Over & Group By

Bonjour,

J'ai trouvé, un peu malgré moi, que SQL-Server introduisait une "nouvelle notion", la clause OVER. Je me suis penché un peu sur cette clause et je n'arrive pas à me faire une idée quant à la différence entre le OVER et le GROUP BY. Mis à part que la clause OVER propose plus que le GROUP BY. J'ai eu beau rechercher sur dev.net et sur internet d'une manière générale, je n'ai pas trouvé d'explication claire pour bien comprendre la différence.

De ce que j'ai pu lire et comprendre, la clause OVER fonctionnerai de la même manière qu'une sous requête (SELECT dans un SELECT) d'un point de vue "exécution de la requête" et permettrai d'obtenir les mêmes résultats que celle-ci ou qu'un GROUP BY.

Je cherche donc à bien comprendre l’intérêt de cette clause sur celles "standard". Si vous pouviez éclairer ma lanterne de quelques explications.

P.S : Quand je parle de GROUP BY, je fais implicitement référence aux différentes fonctions d'agrégation.

Bonjour,

La clause OVER permet d'introduire le fenêtrage dans les groupes calculés par la clause GROUP BY.
Prenons un exemple : on souhaite connaître le nombre d'articles le plus vendus par mois, et classer les articles à l'intérieur des groupes de mois par le nombre de ventes.
La clause GROUP BY va calculer les groupes mensuels, et retourner pour chaque article le nombre qu'il en a été vendu dans le mois.
En ajoutant la fonction RANK() qui nécessite la clause OVER, on peut classer par ordre décroissant (par exemple) les articles les plus vendus suivant le SUM() des ventes.

Pour en savoir plus, vous pouvez lire l'article de SQLPro sur le sujet

@++ ;)

Merci beaucoup pour cette source d'info, c'est très détaillé et bien expliqué !

Il me reste cependant une dernière question pour laquelle je n'ai pas pu trouver de réponse. Dans le tutoriel, on compare souvent le temps d'exécution d'une requête avec clause OVER et sans cette clause (en la remplaçant par des fonctions d'agrégation). On remarque d'ailleurs que c'est vraiment rentable d'utiliser cette "fonction". Et ma question se trouve là, où est l'intérêt de continuer à utiliser le GROUP BY si la clause OVER permet de faire la même chose voir plus et en mieux ?

Je vais prendre un exemple pour mieux décrire ma question. Dans le chapitre II-B exemple 5, on souhaite avoir les 3 metteurs en scène les plus prolifique (dans le sens durée du film). Ce qui pourrait s'écrire de cette façon :

Code:

---

1 2 3 4

SELECT TOP 3 FLM_REALISATEUR, SUM(FLM_DUREE_MN) FROM T_FILM_FLM GROUP BY FLM_REALISATEUR ORDER BY SUM(FLM_DUREE_MN)

---

Avec la clause OVER :

Code:

---

1 2 3 4 5 6

SELECT FLM_REALISATEUR, DUREE_TOTALE FROM (SELECT FLM_REALISATEUR, SUM(FLM_DUREE_MN) AS DUREE_TOTALE, RANK() OVER(ORDER BY SUM(FLM_DUREE_MN) DESC) AS RANG_DT FROM T_FILM_FLM GROUP BY FLM_REALISATEUR) AS T WHERE RANG_DT <= 3

---

J'ai donc l'impression que peu importe l'utilisation du GROUP BY que l'on veut faire, on pourra toujours le remplacer par la clause OVER et cela sera plus performant. Est-ce que je me trompe ?

En fait il peut y avoir des cas ou vous avez besoin des deux. Par exemple, pour faire un classement sur une somme.
Un cas est donné dans l'exemple 3

Un autre cas : faire un max d'une somme.
Exemple : chercher dans la base HOTEL, quel est la capacité de couchage maximal sur une même étage :

Code:

---

1 2 3

SELECT DISTINCT MAX(SUM(CHB_COUCHAGE)) OVER() AS MAX_COUCHE FROM S_CHB.T_CHAMBRE_CHB GROUP BY CHB_ETAGE

---

A +

Je viens de me rendre compte que je me suis très mal exprimé et qu'il y avait quelques incohérences dans ma réponse en demandant l'utilité du GROUP BY tout en l'utilisant dans mes deux exemples !

Si je devais simplifier ce que j'ai compris, la clause OVER est en quelque sorte une "extension" des fonctions d'agrégations. Elle ne remplace en aucun cas la clause GROUP BY, ce sont deux notions bien distinctes.
La clause OVER permet de couvrir certains besoins que le clause GROUP BY peut couvrir mais elle (la clause OVER) ne peut pas tous les couvrir et inversement. Il y aura toujours des cas où nous aurons besoin des deux (c.f: les messages précédents).

J'ai eu du mal à appréhender ce OVER mais finalement je pense en avoir assimilé une bonne partie. Merci à vous deux pour vos réponses !

En plus de ce qui a été dit.
Avec la clause group by vous réduisez chaque groupe à une ligne.
Avec la clause over le nombre de lignes reste inchangé.