Les fonctions de fenêtre sont un groupe de fonctions qui effectueront des calculs sur un ensemble de lignes liées à votre ligne actuelle. Ils sont considérés comme SQL avancé et sont souvent demandés lors des entretiens de science des données. Il est également beaucoup utilisé au travail pour résoudre de nombreux types de problèmes. Résumons les 4 différents types de fonctions de fenêtre et expliquons pourquoi et quand les utiliser.
4 types de fonctions de fenêtre
1. Fonctions d’agrégation régulières
o Ce sont des agrégats comme AVG, MIN/MAX, COUNT, SUM
o Vous voudrez les utiliser pour agréger vos données et les regrouper dans une autre colonne comme le mois ou l’année
2. Fonctions de classement
o ROW_NUMBER, RANK, RANK_DENSE
o Ce sont des fonctions qui vous aident à classer vos données. Vous pouvez soit classer l’ensemble de vos données, soit les classer par groupes, par exemple par mois ou par pays
o Extrêmement utile pour générer des index de classement au sein des groupes
3. Génération de statistiques
o Ils sont parfaits si vous avez besoin de générer des statistiques simples comme NTILE (percentiles, quartiles, médianes)
o Vous pouvez l’utiliser pour l’ensemble de votre jeu de données ou par groupe
4. Traitement des données de séries chronologiques
o Une fonction de fenêtre très courante, surtout si vous avez besoin de calculer des tendances comme une moyenne mobile d’un mois sur l’autre ou une mesure de croissance
o LAG et LEAD sont les deux fonctions qui vous permettent de faire cela.
1. Fonction d’agrégation régulière
Les fonctions d’agrégation régulières sont des fonctions telles que moyenne, comptage, somme, min/max qui sont appliquées aux colonnes. L’objectif est d’appliquer la fonction d’agrégation si vous souhaitez appliquer des agrégations à différents groupes de l’ensemble de données, comme le mois.
Ceci est similaire au type de calcul qui peut être effectué avec une fonction d’agrégation que vous trouverez dans la clause SELECT, mais contrairement aux fonctions d’agrégation régulières, les fonctions de fenêtre ne regroupent pas plusieurs lignes en une seule ligne de sortie, elles sont regroupées ou conserver leur propre identité, selon la façon dont vous les trouvez.
Moy() Exemple :
Examinons un exemple de fonction de fenêtre avg() implémentée pour répondre à une question d’analyse de données. Vous pouvez voir la question et écrire le code dans le lien ci-dessous :
platform.stratascratch.com/coding-question?id=10302&python=
Ceci est un exemple parfait d’utilisation d’une fonction de fenêtre, puis d’application d’un avg () à un groupe de mois. Ici, nous essayons de calculer la distance moyenne par dollar par mois. C’est difficile à faire en SQL sans cette fonction de fenêtre. Ici, nous avons appliqué la fonction de fenêtre avg() à la 3ème colonne où nous avons trouvé la valeur moyenne pour le mois-année pour chaque mois-année dans l’ensemble de données. Nous pouvons utiliser cette métrique pour calculer la différence entre la moyenne du mois et la moyenne de la date pour chaque date de demande dans le tableau.
Le code pour implémenter la fonction de fenêtre ressemblerait à ceci :
SELECT a.request_date,
a.dist_to_cost,
AVG(a.dist_to_cost) OVER(PARTITION BY a.request_mnth) AS avg_dist_to_cost
DE
(SÉLECTIONNER *,
to_char(request_date::date, ‘YYYY-MM’) AS request_mnth,
(distance_to_travel/monetary_cost) AS dist_to_cost
DE uber_request_logs) un
COMMANDER PAR date_demande
2. Fonctions de classement
Les fonctions de classement sont une utilité importante pour un data scientist. Vous êtes toujours en train de classer et d’indexer vos données pour mieux comprendre quelles lignes sont les meilleures de votre ensemble de données. Les fonctions de fenêtre SQL vous offrent 3 utilitaires de classement — RANK(), DENSE_RANK(), ROW_NUMBER() — selon votre cas d’utilisation exact. Ces fonctions vous aideront à répertorier vos données dans l’ordre et en groupes en fonction de ce que vous désirez.
Rang() Exemple :
Examinons un exemple de fonction de fenêtre de classement pour voir comment nous pouvons classer les données au sein de groupes à l’aide des fonctions de fenêtre SQL. Suivez de manière interactive avec ce lien : platform.stratascratch.com/coding-question?id=9898&python=
Ici, nous voulons trouver les meilleurs salaires par département. Nous ne pouvons pas simplement trouver les 3 meilleurs salaires sans fonction de fenêtre, car cela nous donnera simplement les 3 meilleurs salaires dans tous les départements, nous devons donc classer les salaires par département individuellement. Ceci est fait par rank() et partitionné par département. À partir de là, il est très facile de filtrer le top 3 dans tous les départements
Voici le code pour générer cette table. Vous pouvez copier et coller dans l’éditeur SQL dans le lien ci-dessus et voir la même sortie.
Département SELECT,
un salaire,
RANK() OVER (PARTITION BY a.department
ORDER BY a.salary DESC) AS rank_id
DE
(département SELECT, salaire
DE twitter_employee
GROUPER PAR département, salaire
ORDRE PAR département, salaire) a
COMMANDER PAR département,
salaire DESC
3. NTILE
NTILE est une fonction très utile pour ceux qui travaillent dans l’analyse de données, l’analyse commerciale et la science des données. Souvent, lorsque vous avez des données statistiques à échéance, vous devez probablement créer des statistiques robustes telles que le quartile, le quintile, la médiane, le décile dans votre travail quotidien, et NTILE facilite la génération de ces sorties.
NTILE prend un argument du nombre de bacs (ou essentiellement du nombre de compartiments dans lesquels vous souhaitez diviser vos données), puis crée ce nombre de bacs en divisant vos données en ce nombre de bacs. Vous définissez la manière dont les données sont triées et partitionnées, si vous souhaitez des regroupements supplémentaires.
NTILE(100) Exemple
Dans cet exemple, nous allons apprendre à utiliser NTILE pour classer nos données en centiles. Vous pouvez suivre le lien de manière interactive ici : platform.stratascratch.com/coding-question?id=10303&python=
Ce que vous essayez de faire ici, c’est d’identifier les 5 % de réclamations les plus élevées en fonction d’un score généré par un algorithme. Mais vous ne pouvez pas simplement trouver les 5 % les plus performants et effectuer une commande par parce que vous souhaitez rechercher les 5 % les plus performants par état. Donc, une façon de faire est d’utiliser une fonction de classement NTILE() puis PARTITION par l’état. Vous pouvez ensuite appliquer un filtre dans la clause WHERE pour obtenir les 5 % supérieurs.
Voici le code pour afficher l’intégralité du tableau ci-dessus. Vous pouvez le copier et le coller dans le lien ci-dessus.
SELECT policy_num,
Etat,
coût_réclamation,
score_fraude,
centile
DE
(SÉLECTIONNER *,
NTILE(100) SUR(PARTITION PAR état
ORDER BY fraud_score DESC) AS centile
FROM fraud_score) a
OÙ centile <=5
4. Traitement des données de séries chronologiques
LAG et LEAD sont deux fonctions de fenêtre utiles pour traiter les données de séries chronologiques. La seule différence entre LAG et LEAD est de savoir si vous souhaitez extraire des lignes précédentes ou des lignes suivantes, presque comme un échantillonnage à partir de données précédentes ou de données futures.
Vous pouvez utiliser LAG et LEAD pour calculer la croissance mensuelle ou les moyennes mobiles. En tant que data scientist et analyste commercial, vous traitez toujours des données de séries chronologiques et créez ces métriques temporelles.
LAG() Exemple :
Dans cet exemple, nous voulons trouver le pourcentage de croissance d’une année sur l’autre, une question très courante à laquelle les data scientists et les analystes commerciaux répondent quotidiennement. L’énoncé du problème, les données et l’éditeur SQL se trouvent dans le lien suivant si vous souhaitez essayer de coder la solution par vous-même : platform.stratascratch.com/coding-question?id=9637&python=
Ce qui est difficile avec ce problème, c’est que les données sont configurées – vous devez utiliser la valeur de la ligne précédente dans votre métrique. Mais SQL n’est pas conçu pour cela. SQL est conçu pour calculer tout ce que vous voulez tant que les valeurs sont sur la même ligne. Nous pouvons donc utiliser la fonction de fenêtre lag() ou lead() qui prendra les lignes précédentes ou suivantes et les placera dans votre ligne actuelle, ce que fait cette question.
Voici le code pour afficher l’intégralité du tableau ci-dessus. Vous pouvez copier et coller le code dans l’éditeur SQL dans le lien ci-dessus :
SÉLECTIONNER l’année,
hôte_de_l’année_en_cours,
prev_year_host,
round(((current_year_host – prev_year_host)/(cast(prev_year_host AS numérique)))*100) estimate_growth
DE
(SÉLECTIONNER l’année,
hôte_de_l’année_en_cours,
LAG(current_year_host, 1) OVER (ORDER BY year) AS prev_year_host
DE
(SELECT extrait(année
FROM host_since::date) AS année,
count(id) current_year_host
DE airbnb_search_details
OÙ host_since N’EST PAS NULL
GROUP BY extrait(année
FROM host_since :: date)
ORDRE PAR année) t1) t2
