27th Nov 2023 15 minutes de lecture Les fonctions de fenêtre de BigQuery expliquées Jill Thornhill sql fonctions de fenêtrage 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 IDSubjectScore 1Math63 1Science50 2Math59 ... soit afficher des agrégats comme ceci : SubjectClass Average Math52 Science61 English55 Overall Average56 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 IDSubjectScoreClass Average 1Math6362 1Science5061 2Math5952 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 IDTeacher IDSubjectGrade 11Math63 21Math80 32Math60 42Math45 51Math52 61Math70 72Math65 12Science70 22Science62 32Science90 42Science30 52Science53 15English59 35English70 55English45 65English62 112History55 312History67 412History58 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 IDSubjectGradeClass Average 4Math4562 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 IDSubjectGradeClass Average 1Science7062 ... 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_idgradeaveragelowesthighest 163624580 280624580 360624580 445624580 552624580 670624580 765624580 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 IDTeacher IDSubjectGrade 11Math63 21Math80 32Math60 42Math45 51Math52 61Math70 72Math65 12Science70 22Science62 32Science90 42Science30 52Science53 15English59 35English70 55English45 65English62 112History55 312History67 412History58 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 IDSubjectGradeClass Average 1Math6362 2Math8062 3Math6062 4Math4562 5Math5262 6Math7062 7Math6562 1Science7061 2Science6261 3Science9061 4Science3061 5Science5361 1English5959 3English7059 5English4559 6English6259 1History5560 3History6760 4History5860 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_idtran_datetransactionvalue 12023-09-01Opening Balance500.00 12023-09-03Deposit137.45 12023-09-12Withdrawal-200.00 12023-09-18Withdrawal-250.00 22023-09-01Opening Balance1200.00 22023-09-14Deposit900.00 22023-09-20Purchase-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_idtran_datetransactionvaluebalance 12023-09-01Opening Balance500.00500.00 12023-09-03Deposit137.45637.45 12023-09-12Withdrawal-200.00437.45 12023-09-18Withdrawal-250.00187.45 22023-09-01Opening Balance1200.001200.00 22023-09-14Deposit900.002100.00 22023-09-20Purchase-318.901781.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_idtran_datetransactionvaluebalance 12023-09-01Opening Balance500.00500.00 12023-09-03Deposit137.45637.45 12023-09-12Withdrawal-200.00437.45 12023-09-18Withdrawal-250.00187.45 22023-09-01Opening Balance1200.001200.00 22023-09-14Deposit900.002100.00 22023-09-20Purchase-318.901781.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_idsubjectgradeplace 3English701 6English622 1English593 5English454 3History671 4History582 1History553 2Math801 6Math702 7Math653 1Math634 3Math605 5Math526 4Math457 3Science901 1Science702 2Science623 5Science534 4Science305 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 NoScoreRank 4851 5832 10832 9814 2765 6765 7727 3708 8689 16210 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 NoScoreRank 4851 5832 10832 9813 2764 6764 7725 3706 8687 1628 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 NoScoreRank 4851 5832 10833 9814 2765 6766 7727 3708 8689 16210 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 : yearsales_valuesales_quantityprofit 2019540009008000 202075000120011000 2021300004501000 202260000100007000 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: : yearsales_valuesales_quantityprofitly_valuely_qtyly_profitinc_dec 2019540009008000NULLNULLNULLNULL 2020750001200110005400090080003000 202130000450100075000120011000-10000 2022600001000070003000045010006000 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... yearsales_valuesales_quantityprofitly_valuely_qtyly_profitinc_dec 2020750001200110005400090080003000 ... 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 : yearprofitly_profitly_inc_decny_profitny_inc_dec 20198000NULLNULL110003000 202011000800030001000-10000 2021100011000-1000070006000 2022700010006000NULLNULL 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 ! Tags: sql fonctions de fenêtrage