Ceci est particulièrement utile lorsque vous devez calculer les agrégations ou les classements pour des groupes de lignes sans réellement réduire l'ensemble du jeu de résultats.
Rejoignez-nous dans ce didacticiel pour apprendre tout ce qu'il faut savoir pour commencer à travailler avec la clause OVER.
Exigences:
Avant de plonger dans les fonctionnalités et le fonctionnement de la clause OVER, assurez-vous de maîtriser les bases de SQL. Nous supposons également que vous avez accès à une base de données que vous pouvez utiliser pour tester vos connaissances.
Dans notre cas, nous utiliserons la base de données MySQL avec la base de données exemple Sakila. Assurez-vous simplement que vous disposez des autorisations suffisantes et que votre moteur de base de données prend en charge les fonctions de fenêtre.
Syntaxe:
Comme nous l'avons mentionné précédemment, dans la plupart des cas, nous utilisons principalement la clause OVER en conjonction avec les fonctions window.
Ainsi, nous pouvons exprimer la syntaxe de la clause comme suit :
[PARTITION PAR expression_partition, ...]
[ORDER BY expression_tri [ASC | DESC], ...]
[frame_spécification]
)
Dans la syntaxe donnée, nous pouvons décomposer chaque composant comme suit :
-
– Il fait référence à la fonction de fenêtre que nous souhaitons appliquer sur une fenêtre spécifique de lignes telles que SUM(), AVG(), ROW_NUMBER(), RANK, etc. - Expression – Ceci spécifie une colonne ou une expression pour laquelle la fonction de fenêtre est appliquée.
- PARTITION BY – Il s'agit d'une clause facultative qui divise l'ensemble de résultats en partitions où chaque partition est comme une unité distincte où la fonction est appliquée. Les lignes d'une même partition partagent les mêmes valeurs dans les colonnes spécifiées.
- ORDER BY – Ceci spécifie l'ordre dans lequel les lignes de chaque partition sont traitées.
- frame_specification – Il s'agit d'une clause facultative qui définit le cadre des lignes au sein de la partition. Les spécifications de trame courantes incluent ROWS BETWEEN
AND ou RANGE BETWEEN AND
Cela étant dit, explorons quelques exemples pratiques sur la façon de l’utiliser.
Exemple:
Montrons comment utiliser la clause à l'aide de l'exemple de base de données Sakila. Prenons un exemple où nous devons déterminer les revenus totaux pour chaque catégorie de film.
Nous pouvons utiliser la fonction sum window avec la clause OVER et un tas d'instructions de jointure, comme le montre l'exemple suivant :
SÉLECTIONNERcatégorie.nom AS catégorie_nom,
film.titre AS film_titre,
film.rental_rate,
SUM(payment.amount) OVER (PARTITION BYcategory.name) AS total_revenue
DEPUIS
film
REJOINDRE
film_category ON
film.film_id = film_category.film_id
REJOINDRE
catégorie ON
film_category.category_id = catégorie.category_id
REJOINDRE
inventaire activé
film.film_id = inventaire.film_id
REJOINDRE
location ON
inventaire.inventory_id = location.inventory_id
REJOINDRE
Paiement le
location.rental_id = paiement.rental_id
COMMANDÉ PAR
Nom de catégorie,
titre de film;
Dans la requête donnée, nous commençons par sélectionner le titre du film, le tarif de location et utilisons l'expression somme (paiement. montant sur partition par catégorie. nom) pour déterminer la somme de chaque partition de catégorie par le nom de la catégorie.
Il faut utiliser la clause PARTITION BY pour s'assurer que le calcul de la somme redémarre à chaque catégorie unique.
Le résultat résultant est le suivant :
Voilà!
Conclusion
Dans cet exemple, nous avons exploré les principes fondamentaux de l'utilisation de la clause OVER dans SQL. Il ne s'agit pas d'une clause de base et nécessite une connaissance préalable des autres fonctionnalités SQL.