• 20 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_video

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 20/10/2020

Appréhendez le fenêtrage avec OVER et PARTITION BY

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Les fonctions de fenêtrage sont souvent utilisées pour calculer (entre autres) :

  • des sommes cumulées,

  • des numéros de rang, lorsque l'on classe des lignes.

Vous allez voir, il y a beaucoup de similitudes entre les agrégations et le fenêtrage. N'hésitez-donc pas à vous rafraîchir la mémoire.

Réfléchissons...

Mettons-y un petit coup de baguette magique... :magicien:

SELECT incorporation_date, min(id) OVER() FROM entity ;

Tadaaam ! Il n'y a plus d'erreur ! Le fait d'ajouter OVER() a pour effet que la fonction d'agrégation min() renvoie autant de lignes que n'en a entity, ce qui n'est pas le comportement classique des fonctions d'agrégation !

Le fenêtrage

Oui oui, il y a bien des similitudes entre les agrégations et le fenêtrage. Tous deux se réalisent en 2 étapes, et seule la seconde étape diffère :

 

Agrégation

Fenêtrage

Etape 1

Partitionnement selon les attributs de partitionnement

Partitionnement selon les attributs de partitionnement

Etape 2

Application d'une fonction d'agrégation

Application...

  • d'une fonction d'agrégation avec un comportement modifié

  • OU d'une fonction de rang.

Résultat

 Autant de lignes que d'agrégats

Autant de lignes que la table d'origine

Pour appliquer un fenêtrage, il y a 2 cas différents, qui ont chacun des comportements... différents ! Voici les 2 syntaxes correspondantes :

  • OVER (PARTITION BY ...)

  • OVER (PARTITION BY ... ORDER BY ...)

OVER (PARTITION BY...)

Analysons ces deux requêtes, qui utilisent la table nb_entities du chapitre précédent :

-- REQUETE n°1
SELECT sum(cnt_entities) FROM nb_entities GROUP BY id_intermediary ;
-- REQUETE n°2
SELECT sum(cnt_entities) OVER (PARTITION BY id_intermediary) FROM nb_entities ;

La requête 2 fonctionne presque comme l'agrégation de la requête 1 ; mais la seule chose qui diffère, c'est ce que retourne sum.

Dans la requête 2, si sum reçoit en entrée la liste de valeurs [10, 1, 5], elle ne renverra pas 16 mais plutôt [16, 16, 16].

Dans les deux cas, on calcule la somme des sociétés créées par chaque intermédiaire. Mais voici comment le résultat est présenté avec la fonction de fenêtrage :

SELECT id_intermediary,
jurisdiction,
cnt_entities,
sum(cnt_entities) OVER (PARTITION BY id_intermediary) AS entities_by_intermediary
FROM nb_entities ;

id_intermediary

jurisdiction

cnt_entities

entities_by_intermediary 

4000

SAM

10

16

4000

PMA

1

16

4000

CYP

5

16

4001

SAM

3

5

4001

NEV

2

5

[...]

[...]

[...]

[...]

OVER (PARTITION BY... ORDER BY...)

Avec une fonction d'agrégation

Lorsque l'on ajoute ORDER BY dans le OVER, alors la fonction d'agrégation se comporte d'une manière encore différente !

En effet, elle considère que les valeurs sont ordonnées. Comme précédemment, la fonction d'agrégation va renvoyer autant de lignes qu'elle n'en reçoit en entrée. Mais voici ce qu'elle va faire :

  1. Elle effectue son calcul sur la 1ère valeur, et renvoie son résultat ;

  2. Elle effectue son calcul sur les 1ère et 2ème valeurs, et renvoie son résultat ;

  3. Elle effectue son calcul sur les 1ère, 2ème et 3ème valeurs, et renvoie son résultat ;

  4. Ainsi de suite.

Ainsi, si la fonction sum reçoit [10, 5, 1], elle renverra [10, 15, 16], car

  • 10 = 10

  • 10 + 5 = 15

  • 10 + 5 + 1 = 16

Ici, sum se comporte comme une somme cumulée !

Avec une fonction de rang

Si on utilise une fonction de rang (par exemple rank), alors celle-ci va simplement donner le rang des lignes. Ces lignes ont préalablement été triées grâce à ORDER BY.

Petit exemple : nous sommes dans une course où nous avons enregistré le temps mis par chaque coureur pour finir le parcours. Pour savoir qui sera sur le podium, il faut classer ces sportifs selon leur temps :

SELECT
prenom,
temps,
rank() OVER (ORDER BY temps)
FROM course ;

prenom

temps

rank()

Naïma

3 min 15 s

1

Sarah

3 min 19 s

3

Sonia

3 min 16 s

2

Luc

10 min 39 s

4

La somme cumulée et les numéros de rang

Attribuons donc un rang à chaque juridiction pour chacun des intermédiaires. Appelons notre classement rank. Pour leur attribuer un rang, je les classe par cnt_entities décroissant. Comme je veux un classement pour chacun des intermédiaires, j'écris PARTITION BY id_intermediary.

Nous sommes également capables d'effectuer une somme cumulée ! Faisons une somme cumulée de cnt_entities pour chaque intermédiaire, en triant les lignes par cnt_entities décroissant. Appelons notre somme cumulée cum_sum.

SELECT id_intermediary,
jurisdiction,
cnt_entities,
rank()
OVER(PARTITION BY id_intermediary ORDER BY cnt_entities DESC) AS rank,
sum(cnt_entities)
OVER(PARTITION BY id_intermediary ORDER BY cnt_entities DESC) AS cum_sum
FROM nb_entities ;

Voici le résultat :

id_intermediary

jurisdiction

cnt_entitites

rank

cum_sum

4000

SAM

10

1

10

4000

CYP

5

2

15

4000

PMA

1

3

16

4001

SAM

3

1

3

4001

NEV

2

2

5

[...]

[...]

[...]

[...]

[...]

En résumé

  • Le fenêtrage est très similaire à une agrégation, sauf qu'il ne modifie pas le nombre de lignes.

  • Le mot clé OVER peut être utilisé avec des fonctions d'agrégation ou des fonctions de rang.

  • C'est grâce au mot clé OVER que l'on peut réaliser des sommes cumulées ou des calculs de rang.

Exemple de certificat de réussite
Exemple de certificat de réussite