Vue d'ensemble
- Connaître les fonctions de la fenêtre SQL
- Comprendre ce qui manque aux fonctions d'agrégat et pourquoi nous avons besoin de fonctions de fenêtre en SQL
introduction
Les données prolifèrent à un rythme étonnant, Grandir 44 zettaoctets fr 2020! Et il va sans dire que la technologie permettant de gérer des quantités de données aussi gigantesques évolue également à un rythme proportionnel..
Aujourd'hui, nous avons une grande variété d'outils tels que Hive et Spark pour gérer le Big Data. Mais, même s'ils diffèrent à certains égards, utilisent toujours les bases de SQL, ce qui permet aux personnes de tous horizons de manipuler facilement le Big Data en un clin d'œil. Bien que nous échouions encore dans certains aspects de SQL. Ensuite, dans cet article, Je vais parler d'un de ces aspects en particulier: fonctions de fenêtre.
C'est correct! Il y a des fonctions de fenêtre en SQL, ce n'est pas une blague! Et vu l'expression étonnée de son visage, cet article semble être le besoin du moment. Même moi, je n'étais pas très au courant de ces fonctions jusqu'à récemment, ce qui montre à quel point ces fonctions sont sous-estimées.
Mais attendez la fin de cet article car Window Functions va vraiment vous épater par la simplicité avec laquelle ils résolvent des problèmes aussi complexes.. Et si, ingénieurs de données, scientifiques des données, les analystes de données et tous ceux qui flirtent avec les données devraient donner à ces rôles le crédit qui leur est dû.
Avant de continuer, Je vous propose de vous familiariser avec les fonctions SQL de base en consultant cet article: 24 fonctions SQL couramment utilisées. Et si vous souhaitez apprendre SQL dans un format de cours, consultez notre cours: Langage de requêtes structurées (SQL) pour la science des données.
Table des matières
- Nous présentons l'ensemble de données
- Où sont les fonctions d'agrégat en retard?
- Quelles sont les fonctions de fenêtre en SQL?
- Comprendre les fonctions de la fenêtre SQL – Plus de clause
- Fenêtres avec PARTITION BY
- Organiser les lignes dans les partitions
- Fonctions de fenêtre
- Numéro de ligne
- Rango vs Dense_Rank
- Nième_Valeur
- Ntile
- Avance et retard
- fichier de code SQL
Nous présentons l'ensemble de données
avant de continuer, permettez-moi de vous présenter le jeu de données fictif sur lequel nous allons travailler dans cet article. Supposons qu'il existe une entreprise qui conserve des enregistrements de noms, l'emploi et le salaire de l'employé comme suit:
Nous utiliserons cet exemple de jeu de données pour comprendre les concepts de cet article.. Très bien, Commençons!
Où sont les fonctions d'agrégat en retard?
Supposons que vous vouliez déterminer le salaire total de tous les employés de l'entreprise. Comment le ferais-tu? Vous pouvez simplement utiliser la fonction d'agrégation SUM () dans la colonne SALAIRE.
Facile.
Que diriez-vous de déterminer le salaire total des employés par catégorie d'emploi? Utilisez la dernière requête et ajoutez une clause GROUP BY sur la colonne JOB.
Excellent!
Maintenant laisse moi te poser deux autres questions:
- Afficher le salaire total et le salaire total par catégorie d'emploi ainsi que la valeur de chaque ligne.
- Organiser les salaires par ordre décroissant au sein de chaque catégorie d'emploi.
As-tu compris? Non? Probablement?
Ceux-ci n'étaient certainement pas aussi faciles que les premiers que vous pouviez obtenir instantanément. Mais pourquoi?
Bon, si tu penses à ça, les requêtes précédentes nécessitaient des fonctions d'agrégation simples pour résoudre le problème. Les fonctions d'agrégation SQL ne nous donnent qu'une seule valeur pour le groupe de lignes agrégé (pensez à la première requête que nous avons écrite).
Mais les dernières requêtes ne peuvent pas être résolues simplement en utilisant de telles fonctions. Ces requêtes veulent que nous conservions l'identité d'origine des lignes individuelles, quelque chose que les fonctions agrégées ne parviennent pas à résoudre. Donc, pour résoudre ce type de requêtes, nous avons besoin de différents types de fonctions: fonctions de fenêtre.
Quelles sont les fonctions de fenêtre en SQL?
Fonctions de fenêtre effectuer des calculs sur un ensemble de lignes liées les unes aux autres. Mais, contrairement aux fonctions d'agrégat, les fonctions de fenêtre ne réduisent pas le résultat des lignes en une seule valeur. En échange, toutes les lignes conservent leur identité d'origine et le résultat calculé est renvoyé pour chaque ligne.
Comprendre les fonctions de la fenêtre SQL – Plus de clause
Par exemple, si je devais montrer le salaire total des employés avec chaque valeur de ligne, ça ressemblerait à ça:
Les SUR clause signifie une fenêtre de lignes sur laquelle une fonction de fenêtre est appliquée. Peut être utilisé avec des fonctions d'agrégat, comme nous l'avons utilisé ici avec la fonction SOMME, le convertissant ainsi en une fonction de fenêtre. Ou il peut également être utilisé avec des fonctions non agrégées qui ne sont utilisées que comme fonctions de fenêtre (nous en apprendrons plus à leur sujet dans les sections suivantes).
Ensuite, la syntaxe pour définir une fonction de fenêtre simple qui génère la même valeur pour toutes les lignes est la suivante:
nom_fonction_fenêtre () PLUS DE ()
Mais, Que diriez-vous d'appliquer la fonction de fenêtre à des lignes spécifiques au lieu de la table entière?
Fenêtres avec PARTITION BY
Les PARTITION PAR La clause est utilisée en conjonction avec la clause OVER. Divisez les lignes en différentes partitions. Ensuite, la fonction window agit sur ces partitions.
Par exemple, pour afficher le salaire total par catégorie d'emploi pour toutes les lignes, nous aurions à modifier notre requête SQL d'origine comme suit:
Comme tu peux le voir, les total_travail_salaire La colonne représente la somme des ventes pour cette catégorie d'emploi spécifique et non pour l'ensemble du tableau.
Ensuite, la syntaxe pour définir la fonction de fenêtre pour la partition de ligne est la suivante:
nom_fonction_fenêtre () PLUS DE ()
À présent, Que diriez-vous d'organiser les lignes dans chaque partition?
Organiser les lignes dans les partitions
Nous savons que pour organiser les lignes d'un tableau, on peut utiliser la clause ORDER BY. Ensuite, pour organiser les lignes au sein de chaque partition, il faut modifier la clause OVER par la clause ORDER BY.
Ici, les rangs ont été répartis selon leur catégorie d'emploi, comme indiqué dans la colonne JOB. Comme il défile vers le bas, Vous remarquerez que la colonne SALAIRE a été triée par ordre décroissant et que le salaire_travail_ordonné La colonne représente le total cumulé pour la catégorie d'emploi (recommencer après chaque partition).
Ensuite, La syntaxe pour définir la fonction de fenêtre pour partitionner les lignes et les organiser dans l'ordre est la suivante:
nom_fonction_fenêtre () PLUS DE ( )
Fonctions de fenêtre
Maintenant que nous savons définir des fonctions de fenêtre en utilisant la clause OVER et certaines de ses versions modifiées, Nous pouvons enfin passer au travail avec les fonctions de fenêtre!
1. Row_Number
Parfois, votre ensemble de données peut ne pas avoir de colonne décrivant l'ordre séquentiel des lignes, comme c'est le cas avec notre jeu de données. Dans ce cas, nous pouvons utiliser le NUMÉRO DE LIGNE() fonction fenêtre. Attribuez un numéro séquentiel unique à chaque ligne du tableau.
Notez que la numérotation commence à partir de 1. En outre, pour éviter tout conflit avec le mot-clé MySQL de la fonction, j'ai mis le nom de la colonne entre guillemets.
Mais, puisqu'il s'agit d'une fonction fenêtre, nous pouvons également le limiter aux partitions, puis ordonner ces partitions.
Ici, nous avons divisé les lignes dans la colonne TRAVAIL et nous les avons arrangées selon le SALAIRE de l'employé. Remarquez comment la numérotation redémarre à chaque démarrage d'une nouvelle partition.
Mais supposons que nous voulions classer les employés en fonction de leurs salaires.
2. Classement vs Dense_Rank
Les RANG() La fonction fenêtre, comme le nom le suggère, trie les lignes de votre partition en fonction de la condition donnée.
Remarquez la partie en surbrillance. Dans le cas de ROW_NUMBER (), nous avons un numéro séquentiel. D'un autre côté, dans le cas de RANG (), nous avons la même plage pour les lignes avec la même valeur.
Mais voici un problème. Bien que les lignes avec la même valeur se voient attribuer le même rang, le rang suivant saute le rang manquant. Cela ne nous donnerait pas les résultats souhaités si nous devions renvoyer "top N different" des valeurs d'un tableau. Donc, nous avons une fonction différente pour résoudre ce problème.
Les DENSE_RANK () La fonction est similaire à RANK () à une différence près, ne saute aucune plage lors du tri des lignes.
Ici, toutes les plages sont distinctes et augmentent séquentiellement dans chaque partition. Par rapport à la fonction RANK (), aucune plage n'a été ignorée dans une partition.
3. Nième_Valeur
Si vous souhaitez récupérer la nième valeur d'un cadre de fenêtre pour une expression, vous pouvez utiliser la fonction de fenêtre NTH_VALUE (expression, N).
Par exemple, récupérer le troisième salaire le plus élevé dans chaque catégorie d'EMPLOI, on peut diviser les lignes selon la colonne TRAVAIL, puis triez les lignes dans les partitions en fonction du salaire décroissant et, finalement, utiliser la fonction NTH_VALUE pour récupérer la valeur. La commande sera la suivante:
Vous devez avoir remarqué quelque chose de différent après la clause Order By. C'est le Clause cadre. Déterminer le sous-ensemble de la partition (le jalon) qui sera utilisé par la fonction window pour calculer la valeur de la ligne courante.
Ici, J'ai mentionné que toutes les lignes précédentes et suivantes d'une ligne actuelle seront prises en compte dans le cadre lors de l'application de la fonction de fenêtre. Mais, Pourquoi ai-je utilisé la clause frame ici et pas avec d'autres fonctions? C'est parce que les autres fonctions de fenêtre fonctionnent sur toute la partition, même si une clause cadre est fournie. Pero solo NTH_VALUE () peut travailler sur des cadres au sein d'une partition.
Supposons maintenant que vous vouliez générer la première valeur de chaque partition. Bien qu'il existe un FIRST_VALUE () fonction aussi, Je vais utiliser le NTH_VALUE pour le même.
De la même manière, nous avons aussi un LAST_VALUE () une fonction. Mais je vais déterminer la dernière valeur dans chaque partition comme ci-dessus, en utilisant l'ordre décroissant des lignes.
4. Ntile
Parfois, vous voudrez peut-être trier les lignes de la partition en un certain nombre de groupes. Ceci est utile lorsque vous souhaitez déterminer le centile, quartile, etc. dans laquelle se trouve une ligne particulière. Les NTILE () La fonction est utilisée à ces fins. Renvoie le numéro de groupe pour chaque ligne de la partition.
Par exemple, Trouvons le quartile de chaque ligne selon le SALAIRE de l'employé:
de la même manière, vous pouvez diviser les lignes en différents nombres de groupes et calculer le NTILE pour différentes partitions.
5. Avance et retard
Souvent, vous pouvez comparer la valeur de la ligne actuelle avec celle de la ligne précédente ou suivante. Aide à l'analyse facile des données. Les MENER() Oui RETARD() Les fonctions de fenêtre sont là uniquement à cet effet.
Ici, nous créons une nouvelle colonne contenant SALAIRE à partir de la ligne suivante dans chaque partition classée par salaire en utilisant la fonction LEAD. Notez que la dernière ligne de chaque partition contient une valeur nulle car il n'y a pas de ligne suivante pour extraire les données.
À présent, faisons de même avec la fonction LAG.
Ici, nous créons deux nouvelles colonnes. La première colonne contient SALAIRE de la ligne précédente dans chaque partition classée par salaire. Alors que la deuxième colonne contient la différence entre le SALAIRE de la ligne précédente et la ligne actuelle. Comme tu peux le voir, ceci est très utile pour une analyse rapide de la différence entre les salaires au sein d'une même partition.
fichier de code SQL
Tout le code SQL lié aux fonctions de fenêtre pour cet article peut être trouvé dans ce lien.
Remarques finales
Nous avons déjà vu pas mal de fonctions de fenêtre et j'espère que vous pouvez maintenant apprécier la beauté des fonctions de fenêtre en SQL. Mais l'apprentissage ne s'arrête pas là. Après avoir maîtrisé les fondamentaux, il est temps de maîtriser les outils pour gérer le Big Data.
Si vous souhaitez passer au domaine Data Engineering, Je suggère les articles suivants pour une transition facile:
