Perfectionnez votre maîtrise de SQL

Votre DBA chez DVD Rental, Rodolphe, souhaite optimiser vos requêtes et éviter d’utiliser des CTEs pour des opérations simples. Il vous a donc demandé de réviser certaines requêtes pour les rendre plus efficaces en utilisant les Windows Functions, qui permettent de manipuler et d'agréger des données tout en évitant les sous-requêtes complexes ou les jointures inutiles.

Comprenez les Windows Functions

Pour répondre à cette demande, il est important de bien maîtriser les Windows Functions. Ces fonctions offrent une solution élégante pour manipuler des données tout en évitant de recourir à des requêtes imbriquées complexes.

Découvrez la sémantique des Windows Functions

Une Windows Function permet de calculer des résultats à partir d’un groupe de lignes, appelé "fenêtre", tout en conservant chaque ligne dans les résultats. Contrairement aux requêtes traditionnelles avecGROUP BY, où les résultats sont condensés en groupes, les Windows Functions vous permettent d'ajouter des calculs sans perdre de vue les détails des données originales.

Prenons l’exemple suivant : vous souhaitez connaître le total des ventes réalisées par chaque employé, tout en gardant une trace de chaque transaction individuelle. En temps normal, si vous utilisiez unGROUP BY, vous perdriez le détail de chaque paiement, car les données seraient agrégées par employé. Avec une Windows Function, vous pouvez calculer le total des ventes tout en affichant chaque transaction.

Résultat avec unGROUP BY:

SELECT
c.first_name,
c.last_name,
COUNT(r.rental_id)
FROM
customer c
JOIN rental r ON c.customer_id = r.customer_id
GROUP BY
c.first_name, c.last_name
ORDER BY
c.first_name, c.last_name;

Résultat avec des Windows Functions :

SELECT
c.first_name,
c.last_name,
r.rental_date,
COUNT(r.rental_id) OVER (PARTITION BY c.first_name, c.last_name) AS total_rentals_per_customer
FROM
customer c
JOIN rental r ON c.customer_id = r.customer_id
ORDER BY
c.first_name, c.last_name;

Pourquoi utiliser une Windows Function ?

• Conserver les détails des transactions : Contrairement à une requêteGROUP BY, vous ne perdez pas les informations détaillées.

• Faciliter les calculs sur un groupe de lignes : Vous pouvez calculer des totaux, des moyennes, ou des rangs pour chaque partition (groupe de lignes) sans réécrire des sous-requêtes complexes.

• Plus flexible : Les Windows Functions vous permettent de partitionner et trier les données pour appliquer des calculs spécifiques, comme classer des ventes ou calculer des moyennes par groupe.

Vous avez vu l'intérêt d'utiliser les Windows Functions pour optimiser et simplifier vos requêtes. Passons maintenant à la mise en place avec la syntaxe adaptée.

Syntaxe des Windows Functions

Vous trouvez un message de Rodolphe dans Slack :

Salut,

Sofia souhaite qu’on ait plus de détails sur les ventes réalisées par chaque employé, tout en voyant chaque transaction individuelle. Actuellement, avec les requêtesGROUP BY, on perd les informations sur les paiements individuels. Peux-tu essayer de simplifier ça en utilisant des Windows Functions pour obtenir à la fois le montant total des ventes de chaque employé et le détail de chaque transaction ?

Merci beaucoup !

La syntaxe générale d'une Windows Function est la suivante :

window_function(arg1, arg2, ...)
OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST}]]
)

• window_function(arg1, arg2, ...) : La fonction à appliquer, commeSUM(),ROW_NUMBER(),RANK(), etc.

• OVER(): Définit la "fenêtre" sur laquelle la fonction sera appliquée.

• PARTITION BY: Divise les données en sous-ensembles sur lesquels la fonction est appliquée séparément (par exemple, partitionner les données par client).

• ORDER BY: Spécifie l'ordre des lignes dans la fenêtre.

• ROWS | RANGE: Définit le cadre de la fenêtre, c’est-à-dire quelles lignes autour de la ligne courante seront incluses dans le calcul.

Pour répondre à la question de Rodolphe, nous allons calculer combien chaque employé a généré de ventes pour chaque transaction, tout en affichant le montant exact de chaque paiement effectué.

Si nous utilisions unGROUP BY, nous perdrions le détail de chaque paiement, car l’agrégation condenserait les lignes :

SELECT
s.staff_id,
s.first_name,
SUM(p.amount) AS total_sales
FROM
staff s
JOIN payment p ON s.staff_id = p.staff_id
GROUP BY
s.staff_id, s.first_name;

Dans ce cas, on ne voit plus le détail de chaque paiement.

En utilisant les Windows Functions, nous conservons ces informations détaillées tout en ajoutant des données calculées, comme le total des ventes par employé, sur la partition :

SELECT
s.staff_id,
s.first_name,
p.payment_date,
p.amount,
SUM(p.amount) OVER (PARTITION BY s.staff_id) AS total_sales_per_employee
FROM
staff s
JOIN payment p ON s.staff_id = p.staff_id;

Maintenant que vous comprenez la base des Windows Functions, il est temps d'approfondir les possibilités en partitionnant et en triant vos données.

Optimisez l’analyse en partitionnant et en triant vos données

Nous allons maintenant voir comment affiner vos calculs grâce à la clausePARTITION BY. Cette option divise vos données en petits groupes, et applique des calculs spécifiques à chaque groupe séparément. En combinant cela avec un tri grâce àORDER BY, vous pouvez effectuer des analyses beaucoup plus ciblées.

Les partitions dans les Windows Functions

La clausePARTITION BYcrée des groupes dans vos données et applique des calculs à chaque groupe individuellement. Chez DVD Rental, cela peut servir à analyser les paiements des clients en prenant en compte l'employé qui a enregistré chaque transaction. De cette façon, vous obtenez un aperçu des performances des employés tout en comprenant mieux le comportement des clients.

Par exemple, Rodolphe vous demande d'inclure dans le rapport des paiements plusieurs informations détaillées. Il souhaite connaître le montant total payé par chaque client à chaque employé, mais aussi le montant total que chaque client a réglé, tout employé confondu. En plus de cela, il aimerait obtenir le montant global de tous les paiements effectués chez DVD Rental. Pour répondre à cette demande, vous pouvez utiliser la clausePARTITION BYpour regrouper les paiements par client et employé, mais aussi calculer des totaux globaux sans partition.

SELECT
customer_id,
staff_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id, staff_id) AS total_amount_per_customer_per_staff,
SUM(amount) OVER (PARTITION BY customer_id) AS total_amount_per_customer,
SUM(amount) OVER () AS total_amount_overall
FROM payment;

Dans cette requête :

• SUM(amount) OVER (PARTITION BY customer_id, staff_id): Calcule le montant total payé par chaque client à chaque employé, permettant de voir combien un client a réglé à un employé spécifique.

• SUM(amount) OVER (PARTITION BY customer_id): Calcule le montant total payé par chaque client, tous employés confondus. Cela permet d'obtenir une vue globale des paiements d'un client sans distinguer les employés.

• SUM(amount) OVER (): Calcule le montant total de tous les paiements effectués dans la base de données, indépendamment des clients ou des employés. Cela donne une vue globale du total des paiements pour l'ensemble de DVD Rental.

Chaque ligne affichera le même total pour une combinaison donnée de client et d'employé, client ou total.

Le tri dans les Windows Functions

En ajoutant la clauseORDER BYà une Windows Function, vous pouvez trier les résultats dans chaque partition avant d’appliquer le calcul. Cela est très utile pour obtenir des sommes cumulatives ou pour travailler avec des données chronologiques, par exemple.

Prenons un exemple plus spécifique. Vous souhaitez non seulement calculer le total des paiements pour chaque client par employé, mais aussi voir comment ce total évolue avec chaque nouvelle transaction. Vous pouvez le faire en ajoutant unORDER BYbasé sur les dates des transactions :

SELECT
customer_id,
staff_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id, staff_id ORDER BY payment_date) AS cumulative_amount,
payment_date
FROM payment;

Dans cette requête :

• PARTITION BY customer_id: Regroupe les paiements par client et employé.

• ORDER BY payment_date: Trie les paiements dans chaque groupe par date afin que la somme soit calculée de manière cumulative, transaction après transaction.

Après avoir appris à partitionner et trier vos données, il est temps de passer à l'étape suivante : définir précisément quelles lignes inclure dans vos calculs avec les cadres de fenêtre.

Appliquez des Windows Functions selon vos besoins

Rodolphe vous adresse un email avec une demande spécifique :

Objet : Analyse des paiements et comparaison des transactions

Salut,

Nous avons besoin de calculer des statistiques détaillées sur les paiements effectués par les clients. J'aimerais que tu mettes en place les calculs pour la somme totale des paiements et la moyenne par client. De plus, il serait utile d'attribuer un classement aux transactions en fonction du montant payé.

Enfin, pour mieux comprendre les comportements des clients, pourrais-tu ajouter une comparaison entre chaque transaction et la précédente afin de voir les écarts entre elles ? Cela nous permettra de mieux analyser les changements dans leurs habitudes de paiement.

Merci pour ton aide !

Vous avez désormais accès à une gamme de Windows Functions qui vous permettent de réaliser des analyses détaillées et complexes tout en conservant chaque ligne des résultats. Pour répondre à la demande de Rodolphe sur les paiements des clients, nous allons explorer trois catégories de fonctions : agrégation, classement, et offset. Voici un récapitulatif sous forme de tableau pour mieux comprendre chaque fonction.

Les fonctions d'agrégation (Aggregate Functions)

Les fonctions d'agrégation sont utilisées pour effectuer des calculs sur un groupe de lignes, mais elles conservent la granularité de chaque ligne dans les résultats. Les Windows Functions d'agrégation vous permettent de calculer une somme ou une moyenne tout en affichant chaque ligne d'origine.

Cela permet de réaliser des analyses plus granulaires, par exemple, en calculant la somme des paiements de chaque client tout en conservant les détails de chaque transaction.

Les fonctions de classement (Ranking Functions)

Les fonctions de classement attribuent un rang ou un numéro à chaque ligne d’une partition. Elles sont particulièrement utiles lorsque vous avez besoin de créer un classement ou d'attribuer des rangs basés sur des valeurs de colonnes spécifiques (comme le montant d'une transaction).

Ces fonctions se distinguent des fonctions d'agrégation par le fait qu'elles ne réalisent pas de calculs sur des valeurs, mais plutôt qu'elles attribuent un ordre relatif à chaque ligne selon des critères définis. Elles permettent également de gérer les égalités de valeurs de manière flexible (par exemple,RANK()laisse des "trous" dans le classement, alors queDENSE_RANK() ne le fait pas).

Les fonctions d'offset (Offset Functions)

Les fonctions d'offset permettent de comparer les lignes actuelles avec les lignes précédentes ou suivantes dans une fenêtre. Elles sont idéales pour analyser les changements d'une ligne à l'autre, comme comparer la transaction actuelle avec la transaction précédente d'un client.

Ces fonctions vous permettent de naviguer à travers les lignes relatives dans la même partition, ce qui est utile pour repérer des tendances, des comportements ou des anomalies.

Chacune de ces catégories permet d’effectuer des analyses spécifiques sur des partitions de données. Avant de plonger dans les détails des requêtes SQL, voici un récapitulatif sous forme de tableau pour mieux comprendre les fonctions disponibles et leur utilité.

Nous avons désormais tout ce qu'il nous faut pour répondre à la demande de Rodolphe. Passons aux exemples pratiques qui illustrent comment appliquer ces Windows Functions aux données de DVD Rental et obtenir les informations demandées.

SELECT
customer_id,
amount,
payment_date,
-- Calcul de la somme et de la moyenne des paiements par client
SUM(amount) OVER (PARTITION BY customer_id) AS total_amount,
AVG(amount) OVER (PARTITION BY customer_id) AS average_amount,
-- Classement des transactions par montant
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS payment_rank,
-- Comparaison avec la transaction précédente
LAG(amount, 1) OVER (PARTITION BY customer_id ORDER BY payment_date) AS previous_amount
FROM payment
ORDER BY customer_id, payment_date;

À vous de jouer

Contexte

Sofia souhaite analyser les habitudes de location des clients entre le 1er mars 2005 et le 1er septembre 2005. Elle vous demande d'identifier les clients les plus actifs durant cette période et de comprendre comment leurs comportements de location évoluent au fil des mois. Votre mission est de créer une vue qui liste les locations par client, d’attribuer un classement en fonction du nombre total de films loués, et de comparer les locations d'un mois à l'autre pour chaque client.

Consignes

1. Créez une vue qui liste les locations effectuées par client entre le 1er mars 2005 et le 1er septembre 2005, en incluant la date et le nombre de films loués par mois.

2. Utilisez une Windows Function pour attribuer un classement aux clients en fonction du nombre total de films loués.

3. Utilisez une fonctionLAGpour comparer le nombre de films loués d'un mois sur l'autre pour chaque client.

Vérifiez votre travail à l’aide de cet exemple de corrigé

Si vous êtes coincé, la solution est disponible ci-dessous :

Dans cette vidéo, vous avez vu :

• Comment créer une vue pour lister les locations mensuelles des clients sur une période donnée.

• Utiliser la fonctionRANK()pour attribuer un classement basé sur le nombre de films loués.

• Comparer les locations d’un mois à l’autre pour chaque client grâce à la fonctionLAG().

En résumé

• Vous avez découvert comment les Windows Functions permettent d'effectuer des calculs complexes tout en conservant chaque ligne de la table.

• Vous avez utilisé des partitions et des fenêtres de données pour segmenter les résultats et réaliser des calculs ciblés.

• Vous avez appris à ordonner les résultats dans une fenêtre et à utiliser les Windows Frames pour définir la portée des calculs.

• Vous avez exploré les trois grandes catégories de Windows Functions : agrégation, classement, et offset, pour des analyses détaillées.

Après avoir exploré l'utilisation des Windows Functions pour analyser vos données de manière plus précise et efficace, il est temps de passer à un autre concept fondamental : les ensembles. Dans ce prochain chapitre, vous apprendrez à combiner, comparer et manipuler les résultats de plusieurs tables pour enrichir vos analyses.