À quoi sert PARTITION BY dans une fonction de fenêtrage ?

Elle divise les lignes en groupes et calcule la fonction de fenêtrage séparément dans chaque groupe

Elle divise physiquement la table en plusieurs fichiers sur le disque

Elle supprime les doublons avant de calculer le résultat

Elle trie le résultat final à la place de ORDER BY

Que va faire AVG(cena) OVER (PARTITION BY kategoria_id) dans une requête ?

Elle ajoute à chaque ligne le prix moyen calculé au sein de sa catégorie

Elle renvoie une seule ligne avec le prix moyen de tous les livres

Elle supprime toutes les lignes sauf la moins chère de la catégorie

Elle change le prix de chaque livre pour la valeur moyenne

Cours SQL et MySQL · Module 10: Transactions et SQL avancé

PARTITION BY et agrégats fenêtrés - classements au sein de groupes

2 min de lecture

Dans cette leçon4

Tu connais déjà ROW_NUMBER() et RANK() calculés sur toute la table. Nous allons maintenant grimper tout en haut de la Tour de Guet : nous allons apprendre à diviser les lignes en groupes et à calculer des fonctions fenêtre séparément au sein de chaque groupe. C'est à cela que sert la clause PARTITION BY.

PARTITION BY - une fenêtre séparée pour chaque groupe

Imagine que tu veuilles un classement des livres par prix, mais séparément au sein de chaque catégorie. Sans PARTITION BY, nous numéroterions tous les livres ensemble. Avec elle - le compteur se réinitialise à chaque nouvelle catégorie :

1SELECT tytul, kategoria_id, cena,
2       ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC) AS miejsce
3FROM ksiazki;

Dans la catégorie 1, le livre le plus cher obtient miejsce = 1, le suivant 2, et ainsi de suite. Quand nous passons à la catégorie 2, le compteur repart de zéro. C'est comme un classement séparé sur chaque rayon.

L'ordre à l'intérieur de PARTITION BY

Dans la clause OVER (...), nous écrivons d'abord PARTITION BY (selon quoi nous répartissons) puis ORDER BY (comment nous ordonnons au sein du groupe) :

1OVER (PARTITION BY kategoria_id ORDER BY cena DESC)

Les agrégats comme fonctions fenêtre

Le plus intéressant, c'est que les agrégats ordinaires - AVG, SUM, COUNT, MAX, MIN - peuvent eux aussi fonctionner comme des fonctions fenêtre. Il suffit d'ajouter OVER (...). Ils ne réduisent alors pas les lignes, mais inscrivent le résultat à côté de chacune d'elles :

1SELECT tytul, kategoria_id, cena,
2       AVG(cena) OVER (PARTITION BY kategoria_id) AS srednia_kat
3FROM ksiazki;

Chaque livre garde sa ligne, mais à côté apparaît le prix moyen de sa catégorie. Ainsi, tu peux voir d'un coup si un livre donné est plus cher ou moins cher que la moyenne de son rayon - sans requête GROUP BY séparée.

Une fenêtre face à GROUP BY

Caractéristique	GROUP BY	Fonction fenêtre (OVER)
Nombre de lignes	réduit à une par groupe	garde tout
Détails visibles	non	oui
À quoi ça sert ?	des résumés	des comparaisons ligne par ligne

C'est l'outil analytique le plus puissant de la Bibliothèque. Avec PARTITION BY, le Gardien voit à la fois l'ensemble et le détail - et rien ne lui échappe.

Tu as repéré une erreur dans cette leçon ?

Teste tes connaissances

Réponds aux questions de cette leçon. Choisis une réponse pour voir tout de suite si elle est correcte.

1. À quoi sert PARTITION BY dans une fonction de fenêtrage ?
2. Que va faire AVG(cena) OVER (PARTITION BY kategoria_id) dans une requête ?

Voici 2 des 4 questions de cette leçon. Réponds aux autres dans le jeu.

Tâches pratiques dans le jeu

Éditeur de code
Écris une requête qui récupère tytul, kategoria_id et cena de la table ksiazki, et ajoute en plus une colonne nommée miejsce contenant le résultat de ROW_NUMBER() OVER (PARTITION BY kategoria_id ORDER BY cena DESC).
Tri horizontal
Ordonne sur une ligne la clause de fenêtrage avec une division par catégorie et un tri par prix :
Éditeur de code
Écris une requête qui récupère tytul, kategoria_id et cena de la table ksiazki, et ajoute en plus une colonne nommée srednia_kat contenant le résultat de AVG(cena) OVER (PARTITION BY kategoria_id).
Clique dans l'ordre
Clique sur les éléments dans l'ordre pour construire une fonction de fenêtrage avec une division et un tri :
Tri vertical
Ordonne les lignes d'une requête avec une fonction de fenêtrage dans le bon ordre (de haut en bas) :
Éditeur de code
Écris une requête qui récupère imie et nazwisko de la table czytelnicy, et ajoute en plus une colonne nommée numer contenant le résultat de ROW_NUMBER() OVER (ORDER BY data_zapisu).
Tri vertical
Ordonne la transaction complète d'emprunt dans le bon ordre (de haut en bas) :
Clique dans l'ordre
Clique sur les éléments dans l'ordre pour construire la définition de la vue dostepne_ksiazki :
Éditeur de code
Écris une CTE nommée ranking qui sélectionne tytul, cena et ROW_NUMBER() OVER (ORDER BY cena DESC) AS numer de la table ksiazki. Puis, dans la requête principale, récupère toutes les colonnes de ranking. Utilise la syntaxe WITH ranking AS (SELECT ...) SELECT * FROM ranking;
Tri horizontal
Ordonne sur une ligne le début d'une CTE nommée ranking :