Les cinq principales fonctions de fenêtre SQL que vous devez connaître pour les entretiens en science des données

SQL est le langage universel dans le monde des données et constitue la compétence la plus importante à maîtriser en tant que professionnel des données.

La raison pour laquelle SQL est si important est qu'il s'agit de la principale compétence requise pendant la phase de gestion des données. Une grande partie de l'exploration des données, de la manipulation des données, du développement de pipelines et de la création de tableaux de bord est effectuée via SQL.

Ce qui sépare les grands scientifiques des données des bons scientifiques des données, c'est que les grands scientifiques des données peuvent brouiller les données autant que les capacités de SQL le permettent. Une grande partie de la capacité d'utiliser pleinement tout ce que SQL a à offrir est de savoir comment utiliser les fonctions de fenêtre.

Cela dit, plongeons-y !

1. Deltas avec LEAD() et LAG()

 
LEAD() et LAG() sont principalement utilisés pour comparer une période de temps avec la période de temps précédente pour une métrique donnée. Pour donner quelques exemples…

• Vous pouvez obtenir le delta entre les ventes de chaque année et les ventes de l'année précédente
• Vous pouvez obtenir le delta du nombre d'inscriptions/conversions/visites de site Web sur une base mensuelle
• Vous pouvez comparer le taux de désabonnement des utilisateurs sur une base mensuelle

Mise en situation :

La requête suivante montre comment vous pouvez interroger la variation mensuelle en pourcentage des coûts

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Sommes cumulées avec SUM() ou COUNT()

 
Le calcul des totaux cumulés peut être simplement effectué via une fonction Windows qui commence par SUM() ou COUNT(). Il s'agit d'un outil puissant lorsque vous souhaitez afficher la croissance d'une métrique particulière au fil du temps. Plus précisément, il est utile dans les circonstances suivantes :

• Obtenez un total cumulé des revenus et des coûts au fil du temps
• Obtenez un total cumulé de temps passé sur l'application par utilisateur
• Obtenez un total cumulé de conversions au fil du temps

Mise en situation :

L'exemple suivant montre comment inclure une colonne de somme cumulée des coûts mensuels :

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Moyennes mobiles avec AVG()

 
AVG () est vraiment puissant dans les fonctions Windows car il vous permet de calculer moyennes mobiles heures supplémentaires.

Les moyennes mobiles sont un moyen simple, mais efficace, de prévoir les valeurs à court terme. Ils sont également extrêmement utiles pour lisser les courbes volatiles sur un graphique. Généralement, les moyennes mobiles sont utilisées pour évaluer la direction générale de l'évolution des choses.

Plus précisement…

• Ils peuvent être utilisés pour obtenir la tendance générale des ventes hebdomadaires (la moyenne augmente-t-elle au fil du temps ?). Cela indiquerait une croissance en tant qu'entreprise.
• Ils peuvent également être utilisés pour obtenir la tendance générale des conversions hebdomadaires ou des visites de sites Web.

Mise en situation :

La requête suivante est un exemple d'obtention de la moyenne mobile sur 10 jours pour les conversions.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() est particulièrement utile lorsque vous souhaitez obtenir le premier ou le dernier enregistrement. Par exemple, si vous avez un tableau indiquant quand les membres du gymnase sont venus au gymnase et que vous souhaitez obtenir la date du premier jour où ils sont venus au gymnase, vous pouvez PARTITION PAR client (nom/identifiant) et COMMANDER PAR date d'achat. Ensuite, afin d'obtenir la première ligne, vous pouvez simplement filtrer les lignes avec rowNumber égal à un.

Mise en situation :

Cet exemple montre comment vous pouvez utiliser ROW_NUMBER() pour obtenir la première date de visite de chaque membre (utilisateur).

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Pour récapituler, si jamais vous avez besoin d'obtenir le premier ou le dernier enregistrement, ROW_NUMBER() est un excellent moyen d'y parvenir.

5. Classement des records avec DENSE_RANK()

 
DENSE_RANK() est similaire à ROW_NUMBER() sauf qu'il renvoie le même rang pour des valeurs égales. Le classement dense est très utile lorsqu'il s'agit de récupérer les meilleurs records, par exemple :

• Si vous voulez tirer le top 10 des émissions Netflix les plus regardées cette semaine
• Si vous voulez obtenir les 100 meilleurs utilisateurs en fonction des dollars dépensés
• Si vous voulez voir le comportement des 1000 utilisateurs les moins actifs

Mise en situation :

Si vous vouliez classer vos meilleurs clients en fonction des ventes totales, DENSE_RANK() serait une fonction appropriée à utiliser.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Merci d'avoir lu!

 
Et c'est tout! J'espère que cela vous aidera dans votre préparation à l'entretien - je suis sûr que si vous connaissez ces 5 concepts à fond, vous vous en sortirez très bien lorsqu'il s'agira de la plupart des problèmes de fonction de fenêtre SQL.

Comme toujours, je vous souhaite le meilleur dans vos efforts d'apprentissage!

 
 
Térence Shin est un passionné de données avec plus de 3 ans d'expérience en SQL et 2+ ans d'expérience en Python, et un blogueur sur Towards Data Science et KDnuggets.

ORIGINALE. Republié avec permission.

Source : https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html