SQL Practice : 11 exercices pratiques sur les fonctions de fenêtrage avec des solutions détaillées

Dans cet article, nous présentons 11 exercices d'entraînement impliquant les fonctions de fenêtre SQL, ainsi que les solutions et des explications détaillées.

Les fonctions de fenêtrage sont une fonctionnalité puissante qui nous permet d'extraire facilement des informations significatives de nos données, mais peu de cours de SQL proposent des exercices sur les fonctions de fenêtrage. Pourtant, peu de cours de SQL proposent des exercices sur les fonctions de fenêtre SQL, ce qui rend la pratique des fonctions de fenêtre assez difficile. Dans cet article, vous trouverez 11 exercices d'entraînement qui utilisent les fonctions de fenêtre.

Tous les exercices présentés dans cet article proviennent de nos cours interactifs Fonctions de fenêtrage et Fonctions de fenêtrage ensemble d'exercices. Le cours Fonctions de fenêtrage est un didacticiel approfondi comprenant plus de 200 exercices. Nous recommandons ce cours pour apprendre ou réviser les connaissances sur les fonctions de fenêtre SQL. Fonctions de fenêtrage ensemble d'exercices est un cours d'entraînement pour ceux qui connaissent les fonctions de fenêtrage et qui souhaitent s'entraîner davantage.

Fonctions de fenêtrage en bref

Les fonctions de fenêtrage sont des outils qui permettent d'analyser les données de différentes manières. Qu'il s'agisse de calculer des totaux courants et des moyennes mobiles, de comparer des données au sein de sous-ensembles spécifiques, d'identifier les plus performants ou de réaliser des classements et des partitionnements complexes, ces fonctions nous permettent d'extraire des informations plus approfondies de nos données - et d'accomplir des tâches complexes en toute simplicité.

Les fonctions de fenêtrage offrent un ensemble d'outils polyvalents permettant d'améliorer l'analyse des données. Cet ensemble d'outils comprend

• ROW_NUMBER(), RANK(), DENSE_RANK(), et NTILE() pour le classement des données.
• SUM() AVG() MAX() MIN()ROW_NUMBER(), RANK(), DENSE_RANK() et COUNT() pour le classement des données.
• LEAD() et LAG() pour comparer les données entre les lignes.
• FIRST_VALUE() et LAST_VALUE() pour l'extraction des valeurs limites.

En outre, la clauseOVER() permet de partitionner et d'ordonner les données avec précision au sein de ces fonctions, ce qui permet aux utilisateurs d'effectuer des calculs complexes sur des sous-ensembles de données définis.

La maîtrise des fonctions de fenêtrage devient de plus en plus cruciale pour les professionnels des données, les analystes et les ingénieurs. Elle leur permet non seulement de résoudre efficacement des problèmes analytiques complexes, mais aussi de mieux comprendre les données. La pratique des fonctions de fenêtre SQL améliorera votre capacité à créer des requêtes avancées et vous aidera à découvrir de nouvelles informations à partir des données. Il s'agit d'une compétence vitale dans le monde actuel axé sur les données.

Avant de vous attaquer aux exercices, vous pouvez jeter un coup d'œil à notre aide-mémoire sur les fonctions de fenêtre, qui vous rappellera la liste des fonctions de fenêtre et leur syntaxe.

Exercices pratiques SQL Fonctions de fenêtrage: Boutique de films en ligne

Avant de commencer les exercices, voyons le jeu de données que nous allons utiliser.

Jeu de données

Les exercices suivants utilisent la base de données du magasin de films en ligne, qui contient six tables.

• La table customer contient des informations sur tous les clients enregistrés. Les colonnes sont id, first_name, last_name, join_date, et country.
• La table movie contient les enregistrements de tous les films disponibles dans le magasin. Les colonnes sont id, title, release_year, genre, et editor_ranking.
• La table review stocke les évaluations des films par les clients. Les colonnes sont id, rating, customer_id (référence à la table) et (référence à la table). customer ) et movie _id (référence à la table movie table).
• La table single_rental stocke des informations sur les films qui ont été loués pendant une certaine période par des clients. Les colonnes sont id, rental_date, rental_period, platform, customer_id (fait référence à la table), (fait référence à la table) et (fait référence à la table). customer table), movie _id (référence à la table movie table), payment_date et payment_amount.
• La table subscription stocke les enregistrements de tous les clients qui se sont abonnés au magasin. Les colonnes sont id, length (en jours), start_date, platform, payment_date, payment_amount, et customer_id (fait référence à la table). customer table).
• Le tableau giftcard contient des informations sur les cartes-cadeaux achetées. Les colonnes sont id, amount_worth, customer_id (référence à la table), , et (référence à la table). customer table), payment_date et payment_amount.

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL.

Exercice 1 : Classer les locations par prix

Exercice :

Pour chaque location, affichez le site rental_date, le titre du film loué, son genre, le montant du paiement et le rang de la location en fonction du prix payé (la location la plus chère doit avoir le rang = 1). Le classement doit être créé séparément pour chaque genre de film. Autorisez le même rang pour plusieurs lignes et autorisez les écarts de numérotation.

Solution :

SELECT
  rental_date,
  title,
  genre,
  payment_amount,
  RANK() OVER(PARTITION BY genre ORDER BY payment_amount DESC)
FROM movie
JOIN single_rental
  ON single_rental.movie_id = movie.id;

Explication de la solution :

L'instruction nous demande d'afficher certaines informations sur les locations individuelles et les films. Nous joignons donc la table single_rental avec la table movie sur leur colonne commune (c'est-à-dire la colonne movie_id ).

Ensuite, nous devons classer toutes les locations en fonction du prix payé par location. Pour ce faire, nous utilisons RANK(). Ensuite, dans la clause OVER(), nous ordonnons les données par la colonne payment_amount dans l'ordre décroissant, de sorte que la location la plus chère ait le rang 1.

Comme le classement doit être créé séparément pour chaque genre de film, dans la clause OVER(), nous partitionnons les données en fonction de la colonne du genre.

Pourquoi avons-nous choisi RANK() au lieu de DENSE_RANK() ou ROW_NUMBER()? L'instruction indique que le même classement pour plusieurs lignes est autorisé ; nous réduisons donc les options à RANK() et DENSE_RANK(). La fonction ROW_NUMBER() attribue des nombres consécutifs comme rangs aux lignes successives ; elle ne permet pas que plusieurs lignes aient le même rang.

Les sauts dans la numérotation des lignes sont autorisés, c'est pourquoi nous avons besoin de la fonction RANK(). DENSE_RANK() ne saute aucun numéro dans une séquence, même si plusieurs lignes ont le même rang. Le tableau suivant présente ces fonctions de classement et leur fonctionnement à partir d'une liste de valeurs de données :

Consultez cet article pour en savoir plus sur les différentes fonctions de classement.

Exercice 2 : trouver le^deuxième client qui achète une carte-cadeau

Exercice :

Indiquez le nom et le prénom du client qui a acheté la deuxième carte-cadeau la plus récente, ainsi que la date à laquelle le paiement a été effectué. Supposez qu'un rang unique est attribué à chaque achat de carte-cadeau.

Solution :

WITH ranking AS (
  SELECT
    first_name,
    last_name,
    payment_date,
    ROW_NUMBER() OVER(ORDER BY payment_date DESC) AS rank
  FROM customer
  JOIN giftcard
    ON customer.id = giftcard.customer_id
)

SELECT
  first_name,
  last_name,
  payment_date
FROM ranking
WHERE rank = 2;

Explication de la solution :

Nous allons afficher des informations sur les clients et leurs achats par carte-cadeau. customer et la table giftcard sur leur colonne commune, (customer_id).

L'instruction indique de trouver le client qui a acheté la deuxième carte-cadeau la plus récente. Pour ce faire, classons d'abord les achats de cartes cadeaux à l'aide de la fonction ROW_NUMBER(); nous supposons qu'un rang unique est attribué à chaque achat de carte cadeau.

L'instruction interne SELECT sélectionne les informations sur les clients et les dates de leurs achats par carte-cadeau. Ensuite, nous classons les lignes à l'aide de la fonction ROW_NUMBER() pour marquer le deuxième achat par carte-cadeau le plus récent (c'est-à-dire la valeur de rang 2).

Cette instruction SELECT interne est une expression de table commune (CTE). Elle est enveloppée dans la clause WITH et s'appelle ranking. Nous sélectionnons les données pertinentes à partir de cette CTE et fournissons une condition dans la clause WHERE pour ne sortir que la ligne dont le rang est égal à 2.

Pourquoi devons-nous définir un CTE et l'interroger ensuite ? Parce que nous ne pouvons pas utiliser la colonne "rank" dans la clause WHERE de la clause interne SELECT. La raison en est l'ordre d'exécution, qui est le suivant : FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, et LIMIT. La colonne rank n'est donc pas encore définie au moment où la clause WHERE de la clause interne SELECT est exécutée.

Exercice 3 : Calculer le total courant des paiements

Exercice :

Pour chaque location, indiquez id, rental_date, payment_amount et le total courant de payment_amounts de toutes les locations depuis la plus ancienne (en termes de rental_date) jusqu'à la ligne actuelle.

Solution :

SELECT
  id,
  rental_date,
  payment_amount,
  SUM(payment_amount) OVER(
    ORDER BY rental_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM single_rental;

Explication de la solution :

Tout d'abord, nous sélectionnons les informations relatives à chaque location dans le tableau. single_rental tableau.

Ensuite, nous allons trouver le total courant des montants de paiement de toutes les locations en utilisant la fonction SUM() (qui prend la colonne payment_amount comme argument) avec la clause OVER(). Voici un article expliquant les détails du total courant et comment le calculer en SQL.

L'instruction indique qu'il faut trouver le total courant depuis la date de location la plus ancienne jusqu'à la date de la ligne actuelle. Ainsi, dans la clause OVER(), nous devons ordonner les données en fonction de la colonne rental_date, puis définir ROWS pour qu'il soit pris en compte dans le total courant, de la date la plus ancienne (BETWEEN UNBOUNDED PRECEDING) jusqu'à la date actuelle (AND CURRENT ROW).

SQL Fonctions de fenêtrage Exercices pratiques : Clinique de santé

Ensemble de données

Les exercices suivants utilisent la base de données d'une clinique médicale qui contient deux tables.

• La table doctor contient des informations sur les médecins. Les colonnes sont id, first_name, last_name, et age.
• La table procedure contient des informations sur les procédures effectuées par les médecins sur les patients. Les colonnes sont id, procedure_date, doctor_id (référence à la table), , , , , et . doctor table), patient_id, category, name, price, et score.

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL.

Exercice 4 : Calculer la moyenne mobile des scores

Exercice :

Pour chaque procédure, montrez les informations suivantes : procedure_date, doctor_id, category, name, score et la moyenne des scores des procédures de la même catégorie qui sont incluses dans le cadre de la fenêtre suivante : les deux lignes précédentes, la ligne actuelle et les trois lignes suivantes en termes de date de procédure.

Solution :

SELECT
  procedure_date,
  doctor_id,
  category,
  name,
  score,
  AVG(score) OVER(
    PARTITION BY category
    ORDER BY procedure_date
    ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING)
FROM procedure;

Explication de la solution :

Nous allons afficher les informations pour chaque procédure en les sélectionnant dans le tableau. procedure tableau.

Ensuite, nous trouverons le score moyen des procédures de la même catégorie. Pour ce faire, nous utilisons la fonction AVG() avec la colonne score comme argument. Cette fonction est suivie de la clause OVER(), dans laquelle nous partitionnons l'ensemble de données en catégories.

En outre, nous ne devons prendre en considération que les lignes incluses dans le cadre de la fenêtre suivante : les deux lignes précédentes, la ligne actuelle et les trois lignes suivantes en termes de date de procédure. Nous définissons ce cadre de données dans la clause OVER(). Tout d'abord, nous classons l'ensemble de données en fonction de la colonne procedure_date afin que les procédures soient répertoriées chronologiquement. Ensuite, nous définissons les lignes à prendre en considération pour calculer la valeur moyenne du score : deux lignes précédentes (BETWEEN 2 PRECEDING) et trois lignes suivantes (AND 3 FOLLOWING), y compris la ligne actuelle.

C'est ce qu'on appelle une moyenne mobile. Pour en savoir plus, consultez la section Qu'est-ce qu'une moyenne mobile et comment la calculer en SQL.

Exercice 5 : Trouver la différence entre les prix des procédures

Exercice :

Pour chaque procédure, affichez les informations suivantes : id Le prix de la procédure, procedure_date, name, price, price de la procédure précédente (en termes de id) et la différence entre ces deux valeurs. Nommez les deux dernières colonnes previous_price et difference.

Solution :

SELECT
  id,
  procedure_date,
  name,
  price,
  LAG(price) OVER(ORDER BY id) AS previous_price,
  price - LAG(price) OVER(ORDER BY id) AS difference
FROM procedure;

Explication de la solution :

Une fois de plus, nous commençons par sélectionner les informations relatives à chaque procédure dans le tableau. procedure tableau.

L'instruction indique d'afficher le prix de la procédure précédente. Pour ce faire, nous utilisons la fonction LAG(), qui renvoie la valeur de la ligne précédente pour son argument (ici, pour la colonne price ). Pour nous assurer que nous choisissons le prix de la procédure précédente en fonction de la clause id, nous ordonnons l'ensemble de données en fonction de la colonne id dans la clause OVER(). Nous l'appelons previous_price.

Maintenant que nous disposons de la valeur du prix et de la valeur du prix précédent, nous pouvons sélectionner la différence entre ces deux valeurs. Il suffit de soustraire la fonction LAG() de la colonne price et de l'appeler difference.

Pour en savoir plus, consultez la section Comment calculer la différence entre deux lignes en SQL.

Exercice 6 : Trouver la différence entre le prix actuel et le meilleur prix

Exercice :

Pour chaque procédure, indiquez le :

• procedure_date
• name
• price
• category
• score
• Le prix de la meilleure procédure (en termes de score) de la même catégorie (colonne best_procedure).
• La différence entre ce price et le best_procedure (colonne difference).

Solution :

SELECT 
  procedure_date, 
  name, 
  price,
  category,
  score, 
  FIRST_VALUE(price) OVER(PARTITION BY category ORDER BY score DESC)
     AS best_procedure,
  price - FIRST_VALUE(price) OVER(PARTITION BY category 
     ORDER BY score DESC) AS difference
FROM procedure;

Explication de la solution :

Nous commençons par sélectionner les informations relatives à chaque procédure dans le tableau des procédures.

L'étape suivante consiste à trouver le prix de la meilleure procédure. Nous utilisons la fonction FIRST_VALUE(), qui renvoie la première valeur d'une partition ordonnée d'un ensemble de résultats. Pour obtenir le prix de la meilleure procédure de la même catégorie, nous devons partitionner l'ensemble de données en fonction de la colonne category. Et pour obtenir le prix de la meilleure procédure en termes de score, nous devons ordonner l'ensemble de données par la colonne score dans l'ordre décroissant. Nous appelons cette expression best_procedure.

Enfin, nous trouvons la différence entre price et best_procedure en soustrayant la fonction FIRST_VALUE() de la colonne prix.

Exercice 7 : Trouver le meilleur médecin par intervention

Exercice :

Trouvez le meilleur médecin pour chaque procédure. Pour chaque procédure, sélectionnez le nom de la procédure ainsi que le prénom et le nom de tous les médecins qui ont obtenu des scores élevés (supérieurs ou égaux au score moyen pour cette procédure). Classez les médecins par procédure en fonction du nombre de fois où ils ont pratiqué cette procédure. Ensuite, montrez les meilleurs médecins pour chaque procédure, c'est-à-dire ceux qui ont un rang de 1.

La solution :

WITH cte AS (
  SELECT
    name,
    first_name,
    last_name,
    COUNT(*) c,
    RANK() OVER(PARTITION BY name ORDER BY count(*) DESC) AS rank
  FROM procedure p 
  JOIN doctor d
    ON p.doctor_id = d.id
  WHERE score >= (SELECT avg(score) 
                  FROM procedure pl 
                  WHERE pl.name = p.name)
  GROUP BY name, first_name, last_name
)

SELECT 
  name,
  first_name,
  last_name
FROM cte
WHERE rank = 1;

Explication de la solution :

Tout d'abord, nous sélectionnons le nom de la procédure et les informations sur les médecins. procedure et la table doctor sur leur colonne commune (doctor_id).

Nous voulons sélectionner tous les médecins qui ont obtenu un score élevé (supérieur ou égal au score moyen pour cette procédure). Pour ce faire, nous définissons la condition de la clause WHERE pour la colonne score. La colonne score doit contenir une valeur égale ou supérieure au score moyen pour la procédure de la ligne actuelle.

Classons les médecins par procédure. Nous utiliserons la fonction RANK() avec la clause OVER(), où nous partitionnons l'ensemble de données par le nom de la procédure. En outre, nous devons établir un classement en fonction du nombre de fois où le médecin a pratiqué cette procédure. Pour obtenir le nombre de fois que le médecin a effectué cette procédure, nous devons COUNT(*) tout en groupant par le nom de la procédure et le nom et le prénom du médecin (c'est-à-dire que nous groupons par toutes les colonnes énumérées dans l'instruction SELECT ).

Tout ce que nous avons fait jusqu'à présent est de définir une expression de table commune (CTE), qui est l'instruction interne SELECT entourée par la clause WITH et nommée cte.

Nous sélectionnons maintenant les colonnes pertinentes à partir de cette CTE. Pour obtenir les meilleurs médecins pour chaque procédure (ceux qui ont un rang de 1), nous définissons la clause WHERE avec la condition pour la colonne rank.

Pourquoi devons-nous définir un CTE et l'interroger ensuite ? Parce que nous ne pouvons pas utiliser la colonne rank dans la clause WHERE de la clause interne SELECT. La raison en est l'ordre d'exécution, qui est le suivant : FROM, JOINs, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, et LIMIT. La colonne rank n'a pas été définie lorsque la clause WHERE est exécutée.

SQL Fonctions de fenêtrage Exercices pratiques : Championnats d'athlétisme

Jeu de données

Les exercices suivants utilisent la base de données des championnats d'athlétisme qui contient huit tables.

• Le tableau competition contient des informations sur les concours. Les colonnes sont id, name, start_date, end_date, year, et location.
• La table des disciplines contient des informations sur toutes les disciplines de course à pied (des courses de courte distance (par exemple le 100 mètres) aux courses de longue distance (par exemple le marathon)). Les colonnes sont id, name, is_men et distance.
• Le tableau event contient des informations sur la compétition et la discipline pour chaque épreuve. Les colonnes sont id, competition_id (référence au tableau) et (référence au tableau). competition ) et discipline_id (référence au tableau discipline (référence au tableau).
• Le tableau round stocke les tours de chaque épreuve. Les colonnes sont id, event_id (référence au tableau), , , et . event tableau), round_name, round_number, et is_final.
• Le tableau race tableau stocke les données pour chaque course de chaque tour. Les colonnes sont id, round_id (référence au tableau), (identique au tableau), (référence au tableau), (référence au tableau). round tableau), round_name (identique au tableau round tableau), race_number, race_date, is_final (identique au tableau), et round tableau) et wind.
• Le tableau athlete contient des informations sur les athlètes participant à la compétition. Les colonnes sont id, first_name, last_name, nationality_id (référence au tableau) et . nationality tableau) et birth_date.
• Le tableau nationality contient des informations sur les pays d'origine des athlètes. Les colonnes sont id, country_name, et country_abbr.
• La table result contient des informations sur tous les participants à un événement particulier. Les colonnes sont race_id (référence au tableau), (référence au tableau), (référence au tableau) et . race table), athlete_id (référence à la table athlete table), result, place, is_dsq, is_dns, et is_dnf.

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL.

Exercice 8 : Calculer la différence entre les moyennes quotidiennes de la vitesse du vent

Exercice :

Pour chaque date à laquelle il y a eu une course, affichez race_date, la vitesse moyenne du vent à cette date arrondie à la troisième décimale, et la différence entre la vitesse moyenne du vent à cette date et la vitesse moyenne du vent à la date précédente, également arrondie à la troisième décimale. Les colonnes doivent être nommées race_date, avg_wind, et avg_wind_delta.

Solution :

SELECT
  race_date,
  ROUND(AVG(wind), 3) AS avg_wind,
  ROUND(AVG(wind) - LAG(AVG(wind)) OVER(ORDER BY race_date), 3) 
     AS avg_wind_delta
FROM race
GROUP BY race_date;

Explication de la solution :

Nous allons afficher les informations sur les courses pour chaque date de course, nous sélectionnons donc les données dans le tableau race tableau.

Pour trouver la vitesse moyenne du vent à cette date, arrondie à trois décimales, nous utilisons la fonction AVG() avec la colonne wind comme argument. Ensuite, nous l'enfermons dans la fonction ROUND() et l'arrondissons à trois décimales. Notez que nous devons grouper par la colonne race_date, puisque nous utilisons la fonction d'agrégation AVG().

Nous pouvons obtenir le vent moyen à la date précédente en utilisant la fonction LAG() avec la valeur AVG(wind) comme argument. La clause OVER() définit l'ordre de l'ensemble des données en fonction de la colonne race_date afin que les lignes de données soient listées chronologiquement.

Comme nous voulons voir la différence entre la vitesse moyenne du vent à cette date et la vitesse moyenne du vent à la date précédente, nous soustrayons LAG(AVG(wind)) de AVG(wind). Et pour arrondir à trois décimales, nous utilisons à nouveau la fonction ROUND().

Exercice 9 : comparer les meilleurs résultats et les résultats précédents

Exercice :

Pour chaque femme ayant participé à la finale du marathon féminin de Rio, affichez les informations suivantes :

• La place qu'elle a obtenue dans la course.
• Leur prénom.
• Leur nom de famille.
• comparison_to_best - La différence entre leur temps et le meilleur temps de cette finale.
• comparison_to_previous - La différence entre leur temps et le résultat de l'athlète qui a obtenu le meilleur temps suivant.

Triez les lignes en fonction de la colonne place.

Solution :

SELECT
  place,
  first_name,
  last_name,
  result - FIRST_VALUE(result) OVER (ORDER BY result) 
     AS comparison_to_best,
  result - LAG(result) OVER(ORDER BY result) 
     AS comparison_to_previous
FROM competition
JOIN event
  ON competition.id = event.competition_id
JOIN discipline
  ON discipline.id = event.discipline_id
JOIN round
  ON event.id = round.event_id
JOIN race
  ON round.id = race.round_id
JOIN result
  ON result.race_id = race.id 
JOIN athlete
  ON athlete.id = result.athlete_id
WHERE competition.name = 'Rio de Janeiro Olympic Games'
  AND discipline.name = 'Women''s Marathon'
  AND round.is_final IS TRUE
ORDER BY place;

Explication de la solution :

Nous allons utiliser des informations sur les compétitions, les disciplines, les tours, les athlètes et les résultats. Par conséquent, nous devons joindre toutes ces tables sur leurs colonnes communes, comme indiqué dans l'introduction de l'ensemble de données.

L'instruction indique d'afficher des informations pour chaque femme qui a couru dans la phase finale du marathon féminin de Rio. Nous la couvrons dans la clause WHERE qui contient les conditions suivantes :

• Le nom de la compétition doit être Rio de Janeiro Olympic Games.
• Le nom de la discipline doit être Women's Marathon.
• Le tour doit être le tour final.

Ensuite, nous sélectionnons la colonne place de la table result et les colonnes first_name et last_name de la table athlete du tableau.

Pour trouver la différence entre leur temps et le meilleur temps de cette finale, nous utilisons la fonction FIRST_VALUE() avec la colonne result comme argument. Cette fonction est suivie de la clause OVER(), qui ordonne l'ensemble des données en fonction de la colonne result. Ensuite, nous soustrayons cette fonction FIRST_VALUE() de la ligne actuelle result. Nous l'appelons comparison_to_best.

Pour trouver la différence entre leur temps et le résultat de l'athlète qui a obtenu la meilleure place suivante, nous utilisons la fonction LAG() avec la colonne result comme argument pour obtenir le résultat précédent. Une fois de plus, cette fonction est suivie de la clause OVER() pour ordonner l'ensemble de données en fonction de la colonne result (afin de s'assurer que nous obtenons le meilleur résultat suivant). Ensuite, nous soustrayons cette fonction LAG() de la ligne actuelle result. Nous l'appelons comparison_to_previous.

Enfin, nous trions les lignes par la colonne place à l'aide de la clause ORDER BY.

Exercices pratiques SQL Fonctions de fenêtrage: Statistiques sur les sites web

Ensemble de données

Les exercices suivants utilisent la base de données des statistiques du site web qui contient deux tables.

• La table website contient des informations sur les sites web. Les colonnes sont id, name, budget et opened.
• La table statistics stocke des statistiques pour chaque site web. Les colonnes sont website_id (référence à la table), , , , , , , . website table), day, users, impressions, clicks, et revenue.

Maintenant que nous sommes familiarisés avec l'ensemble de données, passons aux exercices d'entraînement SQL.

Exercice 10 : Prévoir l'avenir avec la fonction LEAD()

Exercice :

Prenez les statistiques du site web avec id = 2 entre le 1er et le 14 mai 2016 et montrez le jour, le nombre d'utilisateurs et le nombre d'utilisateurs 7 jours plus tard.

Notez que les 7 dernières lignes n'ont pas de valeur dans la dernière colonne. Cela s'explique par le fait qu'aucune ligne "dans 7 jours" n'a pu être trouvée pour ces lignes. Dans ce cas, affichez -1 au lieu de NULL si aucune valeur LEAD() n'est trouvée.

Solution :

SELECT
  day,
  users,
  LEAD(users, 7, -1) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 2
  AND day BETWEEN '2016-05-01' AND '2016-05-14';

Explication de la solution :

Nous allons afficher le jour, le nombre d'utilisateurs et le nombre d'utilisateurs dans 7 jours. Les deux premières valeurs proviennent du tableau statistics il s'agit des colonnes "jour" et "utilisateurs". La dernière valeur doit être calculée à l'aide de la fonction LEAD().

Nous voulons voir la valeur de la colonne users après sept jours ; par conséquent, nous passons la colonne users comme premier argument et la valeur 7 comme deuxième argument à la fonction LEAD(). Et pour nous assurer que nous affichons -1 au lieu de NULL si aucune valeur LEAD() n'est trouvée, nous passons le troisième argument sous la forme -1.

La fonction LEAD() est suivie de la clause OVER(). Cette clause contient la condition permettant d'ordonner l'ensemble de données en fonction de la colonne "jour", car les statistiques doivent être classées par ordre chronologique.

Pour afficher les statistiques du site web avec id = 2 entre le 1er et le 14 mai 2016, nous devons définir les conditions pertinentes dans la clause WHERE.

Exercice 11 : Retour en arrière avec la fonction LAG()

Exercice :

Affichez les statistiques pour le site web avec id = 3 qui incluent le jour, le revenu, et le revenu 3 jours avant. Affichez -1.00 pour les lignes sans valeur de revenu 3 jours avant.

Solution :

SELECT
  day,
  revenue,
  LAG(revenue, 3, -1.00) OVER(ORDER BY day)
FROM statistics
WHERE website_id = 3;

Explication de la solution :

Nous allons afficher le jour, le revenu et le revenu 3 jours avant. Les deux premières valeurs proviennent du tableau statistics - il s'agit des colonnes jour et revenu. La dernière valeur doit être calculée à l'aide de la fonction LAG().

Nous voulons voir la valeur de la colonne des recettes de trois jours avant la ligne actuelle ; nous transmettons donc la colonne des recettes comme premier argument et la valeur 3 comme deuxième argument à la fonction LAG(). Et pour nous assurer que nous affichons -1,00 pour les lignes sans valeur de revenu trois jours auparavant, nous passons le troisième argument à -1,00.

La fonction LAG() est suivie de la clause OVER(). Elle contient la condition permettant d'ordonner l'ensemble de données en fonction de la colonne day, car les statistiques doivent être classées par ordre chronologique.

Pour afficher les statistiques du site web avec id = 3, nous devons définir une condition dans la clause WHERE.

Plus d'exercices SQL Fonctions de fenêtrage

Les exercices d'entraînement aux fonctions de la fenêtre SQL présentés dans cet article fournissent une plate-forme complète pour affiner vos compétences en SQL et en analyse de données, une requête à la fois. Ces exercices proviennent de nos cours ; pour trouver d'autres exercices d'entraînement, visitez les cours dont les liens figurent ci-dessous.

1. Fonctions de fenêtrage
2. Fonctions de fenêtrage Ensemble d'exercices

Si vous souhaitez vous familiariser avec les fonctions de fenêtre ou rafraîchir vos connaissances, nous vous suggérons de commencer par le cours Fonctions de fenêtrage qui offre une exploration approfondie de ce sujet. Pour ceux qui cherchent à perfectionner leurs compétences en matière de fonctions de fenêtre, explorez notre ensemble d'exercicesFonctions de fenêtrage . Il comprend 100 exercices structurés en trois parties distinctes, chacune utilisant un ensemble de données différent.

Inscrivez-vous maintenant et commencez gratuitement. Bonne chance !