5 constructions SQL avancées que tout analyste de données devrait apprendre

Votre travail implique-t-il l'analyse de données ? Voici cinq exemples montrant comment un SQL avancé peut vous aider dans votre travail quotidien.

Je suis analyste de données et je dois dire que les analystes de données peuvent être assez étranges. Les autres personnes ont généralement peur d'immenses quantités de données ; nous, nous aimons ça. Plus nous nous enfonçons dans les données, plus nous sommes heureux. Un outil important pour pénétrer dans les données (et donc devenir un analyste de données plus heureux) est SQL.

Vous vous souvenez de la première fois où vous avez découvert les fonctions SQL simples ? La joie que vous avez ressentie lorsque vous avez pu rapidement sélectionner les données, les regrouper et les ordonner ? Si vous avez maintenant une bonne maîtrise de ces fonctions de base, vous vous demandez probablement ce qu'est le SQL avancé et comment il peut vous aider à atteindre de nouveaux niveaux de traitement des données et de création de rapports intéressants. Après tout, les analystes de données sont des garçons et des filles ennuyeux à force de travailler sans s'amuser.

Eh bien, je ne vais pas m'attarder sur la définition. La meilleure façon d'expliquer le SQL avancé est de vous montrer quelques requêtes avancées. Vous verrez alors à quel point elles peuvent être utiles (et amusantes).

Comme je vais utiliser de nombreuses fonctions de la fenêtre SQL, il est préférable que vous sachiez déjà ce qu'elles sont et comment elles fonctionnent. Cet article vous permettra de vous mettre à niveau si vous n'êtes pas familier avec ces fonctions.

Requête 1 : classement des données

La direction aime voir des classements pour absolument tout : produits vendus, salaires, employés par département, argent gagné par tout segment imaginable - elle demandera toujours à voir le classement. Pour vous montrer un exemple de classement en SQL, je vais utiliser la table sales . Elle comporte les colonnes suivantes :

• product - Le nom du produit.
• product_price - Le prix du produit.
• items_sold - Le nombre d'articles vendus.

L'idée est de calculer le revenu de chaque produit et de le classer en utilisant la fonction RANK(). Le code ci-dessous résoudra cette tâche :

SELECT	product,
		product_price,
		items_sold,
		product_price * items_sold AS revenue,
		RANK() OVER (ORDER BY product_price * items_sold DESC) AS revenue_rank
FROM sales;

Le code ci-dessus sélectionne toutes les colonnes de la table. Pour obtenir le revenu par produit, vous devez multiplier le prix par les articles vendus. C'est exactement ce que la requête fera, et le résultat sera affiché dans la nouvelle colonne revenue. Enfin, il y a la fonction RANK(). Cette fonction classera toutes les lignes en fonction de la nouvelle colonne des recettes (définie par product_price * items_sold). Le rang sera affiché dans la nouvelle colonne revenue_rank.

Et voici le résultat :

RANK() La fonction revenue_rank n'est qu'une seule façon de classer des données en SQL. Vous pouvez voir les autres méthodes dans cet article. Et, si vous souhaitez approfondir le sujet, voici notre guide complet des fonctions de classement.

Requête 2 : Calcul des valeurs delta

Avec le classement, le calcul des valeurs delta est probablement l'une des tâches les plus courantes des analystes de données. Cette opération est généralement nécessaire pour calculer les changements d'un jour à l'autre, d'un mois à l'autre, d'un trimestre à l'autre ou d'une année à l'autre. Qu'il s'agisse de revenus, de coûts, de changements de prix, de changements de volume ou de tout autre élément imaginable, vous devrez calculer la différence entre les chiffres. Pour ce faire, une requête SQL avancée avec la fonction LAG() est ce dont vous avez besoin. Cette fonction est utilisée pour récupérer les données d'une ligne précédente. Laissez-moi vous montrer comment cela fonctionne en utilisant la table revenue table. Le tableau se compose de deux lignes :

• month - Le mois de l'année.
• revenue - Le revenu pour ce mois.

Votre tâche consiste à calculer la différence entre les recettes de chaque mois et celles du mois précédent (c'est-à-dire le delta des recettes mensuelles). Comment procéder ? Si vous connaissez la fonction LAG(), c'est très facile. Voici le code :

SELECT	month,
		revenue,
		revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_delta
FROM revenue;

Le principe pour calculer le delta est de déduire le mois précédent du mois en cours. C'est exactement ce que fait le code ci-dessus. Tout d'abord, il sélectionne les colonnes mois et revenu. Ensuite, il déduit le montant du revenu du mois en cours du mois précédent. Cette opération est définie par la fonction LAG(). Les valeurs que nous mettons entre les parenthèses de la fonction (revenue, 1) indiquent que la valeur de la colonne des recettes sera déduite de la valeur précédente de cette colonne. C'est pourquoi il y a le chiffre 1 ; il définit le nombre de lignes sur lesquelles la fonction revient pour effectuer l'opération.

En théorie, LAG(revenue) fera la même chose, puisque le retour en arrière d'une ligne est la valeur par défaut de la fonction LAG(). Cependant, je voulais vous le montrer explicitement. C'est plus facile à comprendre et vous saurez quoi faire lorsque vous aurez besoin de remonter plus d'une ligne.

Les deltas doivent être calculés de manière séquentielle, et non par mois aléatoires ; c'est pourquoi il existe la fonction ORDER BY month. Le delta sera affiché dans la nouvelle colonne monthly_delta. Exécutez le code et vous obtiendrez le tableau résultant :

Requête 3 : Calcul des totaux courants

Les totaux courants (également connus sous le nom de sommes cumulatives) sont très utilisés dans l'analyse des données. Ils sont généralement utilisés avec des données de séries chronologiques pour voir comment certains indicateurs de performance évoluent (ou évolueront) dans le temps. Comme d'autres concepts SQL avancés, les totaux courants ont un usage pratique très large. Ils sont utilisés pour surveiller les ventes, les revenus, les coûts, les bénéfices et les budgets. Voici un article qui explique joliment les totaux courants et comment les calculer en SQL.

Pour l'instant, je vais vous montrer comment une somme cumulée fonctionne pour les budgets. Utilisons un tableau nommé de manière très imaginative budget. Il se compose de ces colonnes :

• month - Le mois du flux de trésorerie.
• client - Le nom du client.
• cash_flow - Le flux de trésorerie budgétisé.

Il y a trois clients. Le budget contient des projections mensuelles du flux de trésorerie annuel que votre entreprise percevra auprès d'eux. Vous devez calculer le flux de trésorerie cumulé pour chaque client. Il se peut que vous sachiez déjà intuitivement que vous devez utiliser la fonction SUM(), mais avec une sorte d'astuce. Voici comment calculer les totaux courants :

SELECT	month,
		client,
		cash_flow,
		SUM (cash_flow) OVER (PARTITION BY client ORDER BY month) AS running_total

FROM budget;

Il s'agit d'une simple petite requête qui fait des merveilles ! Elle sélectionne les colonnes mois, client et cash_flow. Pour calculer le total courant, vous devez résumer les flux de trésorerie. Ceci est défini par SUM (cash_flow).

Cependant, il n'est pas intéressant d'avoir des totaux courants au niveau de la table. Vous devez, d'une manière ou d'une autre, faire en sorte que les flux de trésorerie soient résumés mois par mois pour le premier client, puis réinitialisés et recommencés pour le deuxième client. Pour ce faire, vous avez besoin de OVER (PARTITION BY client ORDER BY month). Ici, la partition est définie par la colonne client, ce qui signifie que chaque ensemble de données est défini par les différents clients. De plus, l'opération ne sera effectuée que dans la partition, et non sur l'ensemble de la table. De cette façon, vous obtenez un total courant pour chaque client séparément.

Bien entendu, les flux financiers doivent être résumés de manière séquentielle ; c'est pourquoi ils sont classés par la colonne du mois. Le total courant apparaîtra dans la nouvelle colonne running_total.

Voici le tableau résultant :

J'ai utilisé les fonctions de fenêtre dans les trois derniers exemples. Si vous voulez en savoir plus sur ce sujet, un bon moyen est le cours Fonctions de fenêtrage , l'un de nos cours SQL avancés.

L'aide-mémoire SQL Fonctions de fenêtrage peut également s'avérer très utile, en particulier si vous êtes novice en matière de fonctions de fenêtre ou si vous ne les utilisez qu'occasionnellement. Je l'utiliserai la prochaine fois que j'écrirai sur les fonctions de fenêtre, c'est certain !

Requête 4 : Création d'un rapport basé sur des conditions multiples

L'une des principales tâches des analystes de données consiste à rendre les données plus conviviales pour les autres utilisateurs. En leur fournissant des données sous une forme qu'ils peuvent facilement utiliser, nous leur facilitons la tâche. Pour créer des rapports utiles, un analyste de données doit combiner les données de l'entreprise avec sa connaissance des données. L'un des outils qui peut vous aider à y parvenir est une déclaration CASE, qui est un autre concept SQL avancé.

Pour vous donner un exemple, imaginons le scénario suivant. Vous travaillez dans une banque et vos collègues vous demandent de créer un rapport. Il existe une table appelée debt qui montre les clients de la banque et les détails de leurs dettes. Le tableau se compose des colonnes suivantes :

• client - Le nom du client.
• date_due - Le jour où la dette est devenue exigible.
• amount_due - Le montant de la dette qui est due.

Ce que vous devez faire, c'est créer un rapport au 30.4.2020. Vous devez en quelque sorte calculer le nombre de jours d'échéance à la date du rapport. En outre, vous devez attribuer le client à une certaine tranche de temps, en fonction du nombre de jours d'échéance de leur compte.

La requête est juste en dessous. N'ayez pas peur - je vais l'analyser pour vous. Ce n'est pas aussi effrayant que ça en a l'air !

SELECT	client,
		date_due,
		amount_due,
		DATEDIFF ('2020-04-30', date_due) AS days_due,
		CASE
	WHEN  DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 90 AND DATEDIFF ('2020-04-30', date_due) <=180 THEN '91-180 days'
	WHEN  DATEDIFF ('2020-04-30', date_due) > 180 AND DATEDIFF ('2020-04-30', date_due) <=365 THEN '181-365 days'
	ELSE '> 365 days'
END AS time_bucket

FROM debt;

Tout d'abord, vous devez spécifier la partie SELECT de la requête. J'ai sélectionné les colonnes existantes client, date_due, et montant_due.

Ensuite, vous devez calculer les jours de retard. Pour cela, il faut soustraire la date d'échéance de la date de déclaration. C'est exactement ce que j'ai fait avec DATEDIFF ('2020-04-30', date_due) AS days_due. J'ai utilisé la fonction DATEDIFF() pour calculer la différence requise. Lorsque vous utilisez cette fonction, vous devez d'abord spécifier les dates que vous voulez soustraire. Dans notre cas, il s'agit de la date de déclaration et de la date d'échéance. Ensuite, vous devez indiquer comment vous voulez que le résultat soit affiché, c'est-à-dire en années, en mois ou en jours. Dans ce cas, vous avez besoin de jours, donc vous mettez day comme dernière valeur dans DATEDIFF().

Vient maintenant la partie passionnante - la création des conditions que j'ai utilisées dans l'instruction CASE. Cette instruction s'ouvre avec CASE et se termine avec END. Entre les deux, vous devez définir les conditions qui permettront de créer le rapport souhaité par vos collègues. Pour cela, vous utiliserez WHEN et THEN.

Disons que la première tranche des jours de retard est comprise entre 0 et 30 jours. La première condition de l'instruction CASE est WHEN DATEDIFF ('2020-04-30', date_due) <= 30 THEN '0-30 days'. Puisque vous devez attribuer les clients à une tranche de temps en fonction des jours dus, cette partie du code fait exactement cela. Elle se lit comme suit : si la différence entre la date de déclaration et la date d'échéance est inférieure ou égale à 30 jours, alors ce client sera affecté à la tranche de temps 0-30 jours.

La tranche de temps suivante est 31-90 jours, et c'est la partie du code qui la définit :

WHEN DATEDIFF ('2020-04-30', date_due) > 30 AND DATEDIFF ('2020-04-30', date_due) <=90 THEN '31-90 days'

Ce n'est pas si compliqué, n'est-ce pas ?

Le même principe fonctionne pour les deux autres tranches de temps : 91-180 jours et 181-365 jours. Toute dette échue depuis plus de 365 jours appartient à la tranche de 365 jours. Elle est définie par ELSE '> 365 days'. Il s'agit simplement de définir les critères de déclaration : si la valeur est telle, faites ceci ; si elle ne l'est pas, faites cela. Il s'agit essentiellement d'une version plus complexe de l'instruction IF.

Veuillez noter qu'il existe une manière plus élégante d'écrire ce code : J'aurais pu déclarer une variable contenant la valeur "2020-04-30" au lieu d'écrire manuellement "2020-04-30" partout dans le code. Cependant, je ne voulais pas vous embrouiller si vous n'êtes pas familier avec les variables.

Par ailleurs, en ce qui concerne la fonction DATEDIFF(), notez que j'ai utilisé la fonction et la syntaxe MySQL. En fonction du moteur de base de données que vous utilisez, il est possible que vous deviez adapter la syntaxe en conséquence.

Tous ces time buckets seront affichés dans la nouvelle colonne time_bucket. Puisque vous voulez que vos données aient une belle apparence, vous allez classer votre tableau par ordre croissant de days_due. Exécutez le code et vous obtiendrez un beau tableau. Et probablement un café gratuit de la part de vos collègues !

Excitant, n'est-ce pas ? Si vous voulez plus d'excitation comme celle-ci, Création de rapports basiques en SQL est fait pour vous ! Vous y apprendrez plus sur CASE WHEN et les nuances de GROUP BY.

Requête 5 : Ajout de sous-totaux à un rapport

Une demande très courante consiste à afficher les sous-totaux et les totaux dans le même rapport. La clause ROLLUP facilite grandement cette tâche. Il s'agit d'une extension de la clause GROUP BY. Elle vous permet d'ajouter des sous-totaux et des totaux généraux à vos données.

Voici comment utiliser ROLLUP. Vous avez le tableau warehouse avec les colonnes suivantes :

• warehouse - Le nom de l'entrepôt.
• brand - La marque du produit.
• product - Le nom du produit.
• quantity - La quantité de ce produit dans l'entrepôt.

Il y a deux marques différentes avec cinq produits entre elles. Et il y a deux entrepôts. Votre tâche consiste à calculer la quantité totale de produits pour les deux marques dans les deux entrepôts. Vous avez également besoin du total général de tous les produits dans les deux entrepôts. Et enfin, vous devez tout faire dans une seule table avec une seule requête. Comment le gérer ? Le code est le suivant :

SELECT	warehouse,
		brand,
		SUM (quantity) AS sum_product

FROM warehouse
GROUP BY ROLLUP (warehouse, brand);

Tout d'abord, vous sélectionnez les colonnes entrepôt et marque dans la table. Vous souhaitez également obtenir la somme de la colonne quantité, qui sera affichée dans le nouveau tableau. sum_product. Quelle est l'étape suivante ? C'est là que ROLLUP entre en jeu ! Il est utilisé pour obtenir des totaux pour plusieurs niveaux de regroupement de données. La partie GROUP BY ROLLUP (warehouse, brand) va faire exactement cela. Elle va regrouper les données par entrepôt et par colonne de marque. Ensuite, elle additionnera les données en fonction de chaque regroupement. Le résultat est le suivant :

Le tableau contient les totaux pour les marques Brando et Ostap dans les entrepôts d'Amsterdam et de Berlin et un total général. Le sous-total pour les deux produits dans l'entrepôt d'Amsterdam est indiqué dans la première ligne avec la valeur de la marque NULL. Il s'élève à 64 039, soit la somme des deux lignes précédentes.

Ensuite, vous pouvez voir les totaux pour les deux marques dans l'entrepôt de Berlin. Ensuite, il y a une autre ligne avec une valeur de marque NULL; il s'agit en fait du sous-total de Berlin, qui s'élève à 80 807. La dernière ligne indique le total général de tous les produits dans tous les entrepôts, soit 144 846.

Pourquoi y a-t-il des valeurs NULL dans certaines lignes ? Parce que SQL ne sait pas comment nommer les marques et les entrepôts lorsqu'ils sont regroupés et qu'un sous-total ou un total général est affiché. Pour découvrir plus de détails amusants sur les autres extensions de GROUP BY, consultez notre cours sur les extensions GROUP BY.

C'est la dernière requête SQL avancée pour le moment.

Avez-vous trouvé ces sujets SQL avancé utiles ?

SQL avancé L'analyse des données telle qu'elle est utilisée par les scientifiques et les analystes de données, entre autres, est un sujet très vaste. L'utilisation de SQL pour l'analyse des données offre de vastes possibilités. Cette piste SQL avancé vous donnera une idée de ce que vous pouvez faire avec SQL en tant qu'analyste de données.

Dans cet article, j'ai essayé de vous montrer quelques-unes des utilisations les plus courantes de SQL avancé. Les exemples sont pratiques et tirés de mon expérience, j'espère donc qu'ils vous seront utiles. J'ai essayé de rendre les requêtes aussi simples (et compréhensibles) que possible. Vous pouvez adapter ces requêtes à vos rapports et ensembles de données particuliers. N'hésitez pas à les utiliser !

Si vous avez des questions ou des commentaires, faites-le moi savoir dans la section des commentaires !