Les fonctions de fenêtre de BigQuery expliquées

Suivre les tendances en matière d'analyse de données permet à votre organisation - et à votre CV - d'être à la pointe du progrès. Dans cet article, nous allons examiner les fonctions de fenêtre BigQuery et comment vous pouvez les utiliser pour mieux comprendre vos données.

Lancé en 2010, BigQuery de Google s'impose de plus en plus comme un choix populaire auprès des organisations qui ont besoin d'analyser rapidement de grandes quantités d'informations et de comparer leurs propres données à des données statistiques du domaine public.

Depuis que Google a aligné le langage de recherche de données de BigQuery sur le langage SQL standard - et inclus des fonctionnalités avancées telles que les fonctions SQL window - sa popularité s'est accrue. De nombreuses organisations considèrent désormais les compétences BigQuery comme indispensables, ce qui signifie que les compétences SQL sont plus demandées que jamais. Cet article explique pourquoi la connaissance du langage SQL est essentielle pour travailler avec BigQuery

Le langage SQL reste la compétence principale pour toute personne qui doit travailler avec des données. Si vous n'êtes pas encore un gourou du SQL, vous serez peut-être intéressé par notre piste d'apprentissageLe SQL de A à Z . Il comprend 7 cours qui vous emmènent du débutant absolu à l'expert en SQL. Le cours comporte des centaines de défis de codage du monde réel et dure environ 84 heures. Comme vous pouvez accéder aux bases de données d'exemple via votre navigateur, vous n'avez pas besoin d'installer de logiciel pour commencer.

Qu'est-ce que BigQuery ?

Google BigQuery est un entrepôt de données à grande vitesse situé dans le cloud. Conçu spécialement pour contenir des données utilisées à des fins d'analyse, il peut traiter des pétaoctets de données en quelques minutes. Si vous ne connaissez pas encore les pétaoctets, sachez qu'un pétaoctet correspond à un quadrillion d'octets (ou un million de gigaoctets).

Dans BigQuery, vous payez pour ce que vous utilisez, de sorte que le coût du stockage et de l'analyse d'énormes quantités de données est généralement bien inférieur à celui d'un investissement dans un grand nombre de disques durs. Et comme toutes sortes de données statistiques utiles provenant de gouvernements et d'organisations mondiales sont stockées publiquement dans BigQuery, vous pouvez y accéder pour obtenir des informations sur la manière dont les performances de votre organisation pourraient être améliorées.

Qu'est-ce que SQL Fonctions de fenêtrage?

Les fonctions de fenêtre sont également connues sous le nom de fonctions analytiques ou de fonctions OVER. Elles ont été ajoutées à la norme SQL en 2003, et la plupart des grands fournisseurs de bases de données ont commencé à les mettre en œuvre à partir de 2010. Il s'agit donc d'un ajout relativement récent à SQL.

Avant que les fonctions de fenêtre ne soient incluses dans SQL, vous pouviez soit lister des lignes individuelles, soit calculer des agrégats tels que des totaux et des moyennes. Vous ne pouviez pas facilement faire les deux dans la même requête, à moins d'écrire des sous-requêtes complexes - et probablement lentes et inefficaces.

Cela signifie que vous pouvez soit avoir une liste comme celle-ci ...

Student ID	Subject	Score
1	Math	63
1	Science	50
2	Math	59

... soit afficher des agrégats comme ceci :

Subject	Class Average
Math	52
Science	61
English	55
Overall Average	56

Dans cet exemple, si vous vouliez savoir comment la note d'un élève particulier se compare à la moyenne de la classe, vous devriez consulter les deux rapports côte à côte. Il serait bien plus agréable de voir la moyenne de la classe sur la même ligne que la note de l'élève, comme ceci :

Student ID	Subject	Score	Class Average
1	Math	63	62
1	Science	50	61
2	Math	59	52

Comme je l'ai mentionné, vous pourriez y parvenir en utilisant des sous-requêtes. Mais les sous-requêtes sont notoirement lentes à exécuter et peuvent rendre votre requête très compliquée.

C'est le genre de choses que les fonctions de fenêtre vous permettent de faire facilement et efficacement : inclure des agrégats avec des détails sur la même ligne.

Pourquoi les appelle-t-on Fonctions de fenêtrage?

Elles sont appelées fonctions de fenêtre parce que, tout en examinant une ligne individuelle, vous pouvez également "regarder par la fenêtre" et extraire des informations de l'ensemble du jeu de données ou des lignes liées à la ligne actuelle.

Prenons l'exemple d'un tableau de notes d'étudiants :

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Comme dans l'exemple précédent, nous voulons afficher la moyenne de la classe à côté de chaque étudiant, de sorte qu'une seule ligne ressemble à ceci :

Student ID	Subject	Grade	Class Average
4	Math	45	62

Pour ce faire, nous devons prendre en compte la moyenne de tous les autres étudiants lors de l'extraction de cette ligne et afficher le résultat à côté des autres données.

En SQL, les fonctions de fenêtre utilisent une fenêtre glissante de lignes pour extraire des informations supplémentaires de l'ensemble des données ou d'un sous-ensemble lié à la ligne actuelle.

Dans le diagramme ci-dessus, la ligne actuelle est un résultat mathématique et la fenêtre comprend toutes les notes de mathématiques.

Lorsque nous extrayons cette ligne...

Student ID	Subject	Grade	Class Average
1	Science	70	62

... nous aurons besoin que la fenêtre "glisse" pour afficher tous les résultats en sciences afin de calculer la moyenne pour les sciences.

Les fonctions de fenêtre ont de nombreuses capacités similaires à celles de la clause GROUP BY, mais la différence est qu'elles nous permettent d'afficher des agrégats et des détails côte à côte.

Syntaxe de BigQuery Fonctions de fenêtrage

La clause OVER()

La clause OVER() indique que vous utilisez une fonction de fenêtre. Vous écrivez votre requête comme d'habitude, et vous incluez les agrégats que vous voulez avec les autres noms de colonnes. Chaque agrégat est identifié par la clause OVER(). Lorsque vous utilisez cette clause seule, la "fenêtre" est l'ensemble des données. Je reviendrai sur les fenêtres coulissantes un peu plus loin dans cet article.

Par exemple, si vous souhaitez extraire tous les résultats de mathématiques et afficher la moyenne de la classe ainsi que la note la plus élevée et la plus basse par rapport à la note de chaque élève, votre requête ressemblerait à ceci :

SELECT 
  student_id,
  grade,
  AVG(grade) OVER() AS average,
  MIN(grade) OVER() AS lowest,
  MAX(grade) OVER() AS highest
FROM exam_results
WHERE subject = 'Math';

Vos résultats ressembleraient alors à ceci :

student_id	grade	average	lowest	highest
1	63	62	45	80
2	80	62	45	80
3	60	62	45	80
4	45	62	45	80
5	52	62	45	80
6	70	62	45	80
7	65	62	45	80

La clause PARTITION BY

Cette clause utilise une fenêtre coulissante. Au lieu d'une fenêtre contenant l'ensemble des données, elle n'inclut qu'une partition (ou partie) de l'ensemble.

Dans l'exemple précédent, je n'ai inclus que les résultats mathématiques en excluant tous les autres à l'aide de la clause WHERE. Si vous vouliez un rapport affichant les résultats de toutes les matières mais calculant la moyenne en utilisant uniquement les lignes où la matière correspond à la ligne actuelle, vous utiliseriez la clause PARTITION BY:

SELECT 
  student_id,
  subject,
  grade,
  AVG(grade) OVER(PARTITION BY subject) AS average
FROM exam_results;

Jetez un coup d'œil à cette copie codée par couleur du tableau des notes des élèves pour voir comment les partitions fonctionneront :

Student ID	Teacher ID	Subject	Grade
1	1	Math	63
2	1	Math	80
3	2	Math	60
4	2	Math	45
5	1	Math	52
6	1	Math	70
7	2	Math	65
1	2	Science	70
2	2	Science	62
3	2	Science	90
4	2	Science	30
5	2	Science	53
1	5	English	59
3	5	English	70
5	5	English	45
6	5	English	62
1	12	History	55
3	12	History	67
4	12	History	58

Lors du traitement de chaque ligne, les lignes incluses dans la fenêtre changent en fonction de la valeur de la colonne subject. Cela signifie que la moyenne n'est calculée que pour la partition de l'ensemble de données où le sujet correspond à la ligne actuelle. Vous pouvez visualiser cela comme suit :

Les résultats ressembleraient à ceci :

Student ID	Subject	Grade	Class Average
1	Math	63	62
2	Math	80	62
3	Math	60	62
4	Math	45	62
5	Math	52	62
6	Math	70	62
7	Math	65	62
1	Science	70	61
2	Science	62	61
3	Science	90	61
4	Science	30	61
5	Science	53	61
1	English	59	59
3	English	70	59
5	English	45	59
6	English	62	59
1	History	55	60
3	History	67	60
4	History	58	60

La clause ORDER BY

La clause ORDER BY de la fonction OVER() utilise un autre type de fenêtre coulissante. Lorsque vous utilisez OVER(ORDER BY column_name), la fenêtre n'inclut que les lignes où la valeur de la colonne spécifiée est inférieure ou égale à la valeur de cette colonne dans la ligne actuelle.

La clause ORDER BY est utile pour calculer les totaux courants et les moyennes mobiles. À titre d'exemple, j'utiliserai un tableau appelé monthly_transactions qui contient les transactions des comptes bancaires :

account_id	tran_date	transaction	value
1	2023-09-01	Opening Balance	500.00
1	2023-09-03	Deposit	137.45
1	2023-09-12	Withdrawal	-200.00
1	2023-09-18	Withdrawal	-250.00
2	2023-09-01	Opening Balance	1200.00
2	2023-09-14	Deposit	900.00
2	2023-09-20	Purchase	-318.90

La requête ci-dessous répertorie les transactions du compte ID 1, en affichant un solde courant.

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(ORDER BY tran_date) AS balance
FROM monthly_transactions
WHERE account_id = 1;

L'inclusion de la clause ORDER BY dans la clause OVER permet de contrôler une fenêtre glissante.

Si vous le souhaitez, vous pouvez également utiliser la clause habituelle ORDER BY à la fin de la requête pour contrôler l'ordre final des lignes dans le rapport. Il n'est pas nécessaire qu'elles soient affichées dans l'ordre initial.

Par défaut, l'utilisation de ORDER BY à l'intérieur de la clause OVER fait glisser la fenêtre de manière à n'afficher que les lignes dont la date est inférieure ou égale à la date de la ligne actuelle. Il existe d'autres mots-clés qui peuvent modifier cette valeur par défaut, mais ils sortent un peu du cadre de cet article.

Vous pouvez visualiser cela comme suit :

Les résultats ressembleraient à ceci :

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Utilisation de ORDER BY avec la clause de PARTITION BY

Si vous souhaitez afficher tous les comptes avec leurs soldes courants, vous pouvez utiliser PARTITION BY et ORDER BY ensemble :

SELECT 
  account_id,
  tran_date,
  transaction,
  value,
  SUM(value) OVER(PARTITION BY account_id ORDER BY tran_date) AS balance
FROM monthly_transactions
ORDER BY account_id, tran_date

La clause PARTITION BY permet à la fenêtre coulissante de n'inclure que les lignes dont l'adresse account_id correspond à la ligne actuelle. La clause ORDER BY permet de n'inclure que les lignes de cette partition dont la date est inférieure ou égale à la date de la ligne actuelle.

Les résultats seraient les suivants :

account_id	tran_date	transaction	value	balance
1	2023-09-01	Opening Balance	500.00	500.00
1	2023-09-03	Deposit	137.45	637.45
1	2023-09-12	Withdrawal	-200.00	437.45
1	2023-09-18	Withdrawal	-250.00	187.45
2	2023-09-01	Opening Balance	1200.00	1200.00
2	2023-09-14	Deposit	900.00	2100.00
2	2023-09-20	Purchase	-318.90	1781.10

Chaque compte a son propre solde courant.

SQL Fonctions de fenêtrage disponible dans BigQuery

Vous avez vu comment les fonctions d'agrégation SQL courantes telles que SUM(), AVG(), MIN() et MAX() peuvent être utilisées conjointement avec la clause OVER pour extraire des agrégats d'une fenêtre de données.

Google BigQuery, comme beaucoup d'autres dialectes SQL, possède des fonctions supplémentaires qui permettent d'obtenir des informations plus approfondies sur les données. En voici quelques exemples.

RANK()

Cette fonction classe l'ensemble de données du plus élevé au plus bas sur une colonne spécifiée. Elle peut répondre à des questions telles que :

• Quelle est la place de chaque étudiant dans la classe, sur la base des résultats aux examens ?
• Quels produits ont été les plus rentables ?
• Quels clients ont dépensé le plus d'argent ?
• Quel entrepôt a reçu le plus de plaintes ?

En utilisant l'exemple de tableau que nous avons examiné précédemment, classons les étudiants en fonction de leurs résultats d'examen à l'aide de cette requête :

SELECT 
  student_id,
  subject,
  grade,
  RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS place
FROM exam_results
ORDER BY subject, grade DESC;

Le résultat serait :

student_id	subject	grade	place
3	English	70	1
6	English	62	2
1	English	59	3
5	English	45	4
3	History	67	1
4	History	58	2
1	History	55	3
2	Math	80	1
6	Math	70	2
7	Math	65	3
1	Math	63	4
3	Math	60	5
5	Math	52	6
4	Math	45	7
3	Science	90	1
1	Science	70	2
2	Science	62	3
5	Science	53	4
4	Science	30	5

Lors du calcul de ces résultats, la clause PARTITION BY subject fait en sorte que SQL n'examine que les résultats correspondant à la même matière que la ligne actuelle. La clause ORDER BY grade DESC les classe par ordre décroissant. La fonction RANK() classe ensuite les étudiants dans cet ordre.

L'élève 3 ayant la meilleure note en anglais, son rang est 1 ; l'élève 6, le suivant, est classé 2 dans cette matière. L'élève 2 a la meilleure note en mathématiques et est classé 1.

DENSE_RANK()

DENSE_RANK() est utilisée dans le même but que RANK. La meilleure façon d'expliquer la différence entre les deux est d'examiner les résultats de ces requêtes, qui représentent les scores d'une compétition de tir.

En utilisant RANK, la requête serait: :

SELECT 
  competitor_no,
  score,
  RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

Les résultats sont les suivants :

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	4
2	76	5
6	76	5
7	72	7
3	70	8
8	68	9
1	62	10

Les concurrents 5 et 10 sont arrivés ex aequo à la deuxième place et ont tous deux obtenu un classement de 2. Le concurrent 9 est le suivant et obtient un classement de 4. La troisième place n'a pas été prise en compte.

À l'aide de DENSE_RANK, la requête est la suivante :

SELECT 
  competitor_no,
  score,
  DENSE RANK() OVER(ORDER BY score desc) AS rank
FROM match_results
ORDER BY score DESC;

Les résultats sont les suivants :

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	2
9	81	3
2	76	4
6	76	4
7	72	5
3	70	6
8	68	7
1	62	8

Les concurrents 5 et 10 sont toujours classés 2, mais la troisième place n'est pas exclue : Le concurrent 9 est maintenant classé 3.

Les deux fonctions ont la même syntaxe. Si nous voulions recoder la requête sur les notes des étudiants précédents à l'aide de la fonction DENSE_RANK(), cela ressemblerait à ceci :

SELECT 
  student_id,
  subject,
  grade,
  DENSE_RANK() OVER(PARTITION BY subject ORDER BY grade desc) AS class_place
FROM exam_results
ORDER BY subject, grade DESC

Mais comme il n'y a pas de valeurs ex æquo, le résultat serait le même.

ROW_NUMBER()

La fonction ROW_NUMBER() fonctionne de la même manière que les deux fonctions précédentes, mais les lignes sont simplement numérotées dans l'ordre. Si des lignes ont la même valeur, elles seront numérotées consécutivement, en fonction de celle qui a été rencontrée en premier. Voici les résultats de la requête sur les compétitions de tir utilisant ROW_NUMBER() au lieu de RANK() ou DENSE_RANK():

Competitor No	Score	Rank
4	85	1
5	83	2
10	83	3
9	81	4
2	76	5
6	76	6
7	72	7
3	70	8
8	68	9
1	62	10

LAG()

Cette fonction vous permet de comparer les données de la ligne précédente de l'ensemble de résultats aux données de la ligne actuelle. Elle est idéale pour les comparaisons d'une année sur l'autre, vous permettant de découvrir des tendances et d'identifier les problèmes de performance de l'entreprise.

LAG() et la fonction connexe LEAD() ne peut être utilisée qu'en conjonction avec la clause OVER(ORDER BY).

Prenons l'exemple de la table suivante, qui contient les données de vente d'une petite entreprise :

year	sales_value	sales_quantity	profit
2019	54000	900	8000
2020	75000	1200	11000
2021	30000	450	1000
2022	60000	10000	7000

L'exemple de requête permettant de comparer les chiffres d'une année sur l'autre est le suivant :

SELECT 
  year,
  sales_value,
  sales_quantity,
  profit,
  LAG(sales_value) OVER(ORDER BY year) as ly_value,
  LAG(sales_quantity) OVER(ORDER BY year) as ly_qty,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as inc_dec
FROM annual_sales
ORDER BY year;

Les résultats de cette requête sont: :

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2019	54000	900	8000	NULL	NULL	NULL	NULL
2020	75000	1200	11000	54000	900	8000	3000
2021	30000	450	1000	75000	1200	11000	-10000
2022	60000	10000	7000	30000	450	1000	6000

Examinons cette ligne de la requête et voyons ce qu'elle a réellement fait :

LAG(sales_value) OVER(ORDER BY year) as ly_value

Sur cette ligne du résultat...

year	sales_value	sales_quantity	profit	ly_value	ly_qty	ly_profit	inc_dec
2020	75000	1200	11000	54000	900	8000	3000

... notre ligne actuelle correspond à l'année 2020. La fonction LAG() associée à ORDER BY year permet à SQL de consulter la ligne de l'année précédente (2019) et d'en extraire la valeur des ventes sous l'intitulé ly_value.

Vous remarquerez que dans la première ligne, les colonnes calculées par la fonction LAG() contiennent une valeur nulle, puisqu'il n'y a pas d'enregistrement précédent.

LEAD()

La fonction LEAD() est l'inverse de LAG(): elle obtient les données de la ligne qui suit la ligne actuelle, au lieu de celle qui la précède.

Pour comparer le bénéfice entre l'année en cours, l'année précédente et l'année suivante en utilisant le même tableau d'échantillons, la requête serait la suivante :

SELECT 
  year,
  profit,
  LAG(profit) OVER(ORDER BY year) as ly_profit,
  profit - LAG(profit) OVER(ORDER BY year) as ly_inc_dec,
  LEAD(profit) OVER(ORDER BY year) as ny_profit,
  LEAD(profit) OVER(ORDER BY year) - profit as ny_inc_dec
FROM annual_sales
ORDER BY year;

Les résultats de cette requête sont les suivants :

year	profit	ly_profit	ly_inc_dec	ny_profit	ny_inc_dec
2019	8000	NULL	NULL	11000	3000
2020	11000	8000	3000	1000	-10000
2021	1000	11000	-10000	7000	6000
2022	7000	1000	6000	NULL	NULL

Les colonnes ny_profit et ny_inc_dec utilisent la fonction LEAD(), qui permet à SQL de consulter la ligne suivante dans la séquence pour extraire ces champs. Si la ligne actuelle est celle de 2019, ces deux champs seront extraits de la ligne de 2020.

Cela devrait vous donner une idée de certaines des fonctions SQL utiles disponibles dans BigQuery. Vous trouverez une liste complète des fonctions dans la documentation SQL de BigQuery.

Pour plus d'exemples de fonctions de fenêtre, reportez-vous à l'article Exemples de fonctions de fenêtre SQL. Vous pouvez également trouver un guide de référence rapide pour la syntaxe des fonctions de fenêtre SQL dans notre Fonctions de fenêtrage Cheat Sheet.

Utilisations pratiques de BigQuery Fonctions de fenêtrage

Dans le monde réel, les fonctions de fenêtre de BigQuery peuvent vous fournir de nombreuses informations qui aideront votre organisation à être plus performante. Elles sont très puissantes et vous permettent de produire des rapports complexes très rapidement.

Voici quelques exemples d'utilisation des fonctions de fenêtre de BigQuery :

• Classer vos employés en fonction de leurs performances afin de les motiver. Consultez cet article pour plus d'informations sur le classement des lignes.
• Déterminer les performances de chaque ligne de produits par rapport à d'autres articles similaires.
• Apprendre comment les produits individuels affectent le bénéfice de la moyenne mobile. Pour en savoir plus sur le calcul des moyennes mobiles en SQL, cliquez ici.
• Comparer les données d'une année sur l'autre pour découvrir les tendances. Pour en savoir plus sur la préparation de comparaisons d'une année sur l'autre en SQL, lisez cet article.
• Utiliser les totaux courants pour voir exactement combien de ventes ont été réalisées à un moment donné. Pour en savoir plus sur le calcul des totaux courants en SQL, cliquez ici.

Prochaines étapes avec BigQuery Fonctions de fenêtrage

Maintenant que vous avez vu ce que les fonctions SQL window peuvent faire dans BigQuery et d'autres systèmes de gestion de base de données, il est temps de penser à améliorer vos compétences dans ce domaine important.

Le cours SQL Fonctions de fenêtrage de LearnSQL.fr est un bon point de départ. Vous apprendrez pas à pas à utiliser les techniques présentées dans cet article, avec une explication complète de chaque sujet. Vous aurez également beaucoup de pratique, avec plus de 200 exercices interactifs pour vous assurer que vous savez comment extraire des informations complexes dans des situations réelles. Une aide est disponible si vous êtes bloqué, et vous pourrez accéder à des bases de données d'échantillons via votre navigateur. Le cours dure environ 20 heures.

Si vous voulez vraiment devenir un expert, vous pouvez vous entraîner davantage à résoudre des problèmes complexes en travaillant sur notre kit de pratiqueFonctions de fenêtrage . Vous y trouverez 100 exemples de rapports complexes utilisant trois bases de données différentes.

Vous pouvez également lire cet article sur la façon de pratiquer les fonctions de fenêtre, et vous pouvez en savoir plus sur la syntaxe de BigQuery ici.

Si vous êtes à la recherche d'un emploi de haut niveau dans le domaine de l'analyse ou de la science des données, il est très probable que l'on vous demande de démontrer votre connaissance des fonctions SQL window lors de l'entretien. Pour avoir une idée du type de questions qui pourraient vous être posées (et de la manière d'y répondre), voici un article qui présente les principales questions d'entretien sur les fonctions SQL window.

Prenez dès aujourd'hui les premières mesures pour faire passer vos compétences en matière d'analyse de données au niveau supérieur !