Les 7 meilleures requêtes SQL avancées pour l'analyse des données

Découvrez les requêtes SQL avancées essentielles pour l'analyse de données.

Le langage de requête structuré, ou SQL, est un outil indispensable pour les analystes de données. La plupart des gens apprennent le langage relativement rapidement et peuvent commencer à analyser des données en profondeur après seulement quelques leçons. Pour cette raison, de nombreux analystes de données ont tendance à rester au niveau débutant/intermédiaire de l'utilisation de SQL. Ce niveau de compréhension vous permet de "faire le travail", mais ce n'est peut-être pas la manière la plus efficace d'écrire une requête.

La maîtrise de techniques SQL avancées peut considérablement améliorer vos capacités d'analyse de données, vous permettant d'approfondir vos ensembles de données et d'en tirer des informations précieuses. Dans cet article, nous allons explorer sept requêtes SQL avancées qui peuvent être utiles pour l'analyse des données. Pour chaque exemple, nous présenterons la requête et ses résultats, nous discuterons de la syntaxe utilisée et nous expliquerons brièvement comment la base de données calcule le résultat.

Si vous souhaitez améliorer vos compétences en SQL, n'hésitez pas à suivre notre cours sur les rapports SQL. C'est un excellent moyen d'apprendre et de pratiquer des fonctions SQL plus avancées.

7 SQL avancé Requêtes que les analystes de données doivent connaître

1. Grouper des données par période de temps

Dans ce premier exemple, examinons les données du tableau sales. Cet ensemble de données comprend un identifiant pour chaque produit, la date de la transaction et le montant total de la transaction.

Un scénario d'analyse de données très courant consiste à prendre des données brutes comme celles-ci et à les agréger par périodes de temps spécifiques, telles que le mois ou l'année. C'est ce que nous allons faire en exécutant la requête suivante :

SELECT
  EXTRACT(YEAR FROM sale_date) AS year, 
  EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY year, month
ORDER BY year, month;

Cette requête renvoie les résultats suivants :

Dans cette requête, nous utilisons la fonction EXTRACT() pour extraire l'année et le mois du champ sale_date. Nous regroupons ensuite les données en fonction des champs de la nouvelle année et du nouveau mois et nous calculons les ventes totales pour chaque groupe à l'aide de la fonction SUM().

Notez que nous devons regrouper les données par année et par mois pour obtenir des résultats précis. Si nous ne prenions que le mois, les résultats combineraient les valeurs pour des mois spécifiques sur l'ensemble des années (ainsi, tous les mois de janvier de toutes les années seraient combinés en une seule ligne, tous les mois de février seraient combinés ensemble, etc.) Lorsque nous regroupons les résultats à la fois par année et par mois, les mois des différentes années sont placés dans des lignes distinctes.

Nous ordonnons ensuite les lignes par année et par mois pour obtenir des résultats ordonnés.

Les différents moteurs de base de données disposent souvent de fonctions différentes et parfois meilleures pour obtenir le même résultat, comme DATE_TRUNC() dans PostgreSQL ou TRUNC() dans Oracle.

2. Créer plusieurs niveaux de regroupement à l'aide de ROLLUP

Il peut arriver que vous souhaitiez regrouper des données à plusieurs niveaux dans une seule requête. Supposons que vous souhaitiez ajouter le total des ventes (pour toutes les années) ainsi que le total des ventes pour chaque année à l'exemple précédent. Vous pouvez ajouter des lignes supplémentaires pour le total général et les totaux de chaque année en utilisant le mot-clé ROLLUP.

En utilisant le même ensemble de données, nous allons exécuter cette requête :

SELECT
 EXTRACT(YEAR FROM sale_date) AS year,
 EXTRACT(MONTH FROM sale_date) AS month,
 SUM(amount) AS total_sales
FROM sales
GROUP BY ROLLUP(year,month)
ORDER BY year, month;

Résultat :

Dans notre exemple, ROLLUP ajoute trois lignes supplémentaires : une pour le total général de toutes les ventes et deux pour les ventes totales de chaque année. Dans notre ensemble de résultats, la troisième ligne est le total annuel pour 2021 : la valeur de la colonne year est 2021 et la valeur de la colonne month est NULL. La sixième ligne est le total pour 2022 : la valeur dans la colonne année est 2022 et la valeur dans la colonne month est NULL. La dernière ligne est le total pour les deux années : elle contient NULL dans les colonnes "year" et " month ". Ces lignes ont été ajoutées par ROLLUP. Les valeurs NULL dans les colonnes année et month indiquent les lignes agrégées.

3. Classement des données Utilisation Fonctions de fenêtrage

Le classement des données est une exigence courante dans l'analyse avancée des données. Il peut s'agir, par exemple, de classer vos produits en fonction des ventes les plus élevées afin de savoir quels produits génèrent le plus de revenus ou de classer les magasins en fonction des ventes les plus faibles afin de savoir quels magasins sont les moins performants.

Vous pouvez utiliser des fonctions de fenêtre telles que RANK() ou DENSE_RANK() pour attribuer des rangs aux lignes en fonction de la valeur d'une colonne spécifique. Examinons tout d'abord la fonction RANK():

SELECT
  product_id,
  SUM(amount) AS total_sales,
  RANK() OVER(ORDER BY SUM(amount) DESC) AS rank
FROM sales
GROUP BY id;

Résultat :

Cette requête calcule d'abord le total des ventes pour chaque site product_id, puis les classe par ordre décroissant. La fonction RANK() est une fonction fenêtre utilisée pour calculer le classement. La clause OVER() est la syntaxe utilisée avec les fonctions à fenêtre. La fonction RANK() attribue un rang unique à chaque ligne d'un ensemble de résultats, sur la base d'un ordre spécifique. La clause ORDER BY de la clause OVER() spécifie l'ordre dans lequel le rang sera attribué, sur la base d'une ou de plusieurs colonnes. Dans notre exemple, nous classons les lignes en fonction du montant total des ventes.

La même syntaxe peut être utilisée pour la fonction DENSE_RANK(). La différence entre les deux réside dans le scénario où deux valeurs sont égales. Si deux valeurs sont à égalité pour la première place, RANK() passera à 3 pour la troisième ligne, tandis que DENSE_RANK() attribuera la valeur 2 à la troisième ligne.

Pour plus d'informations sur les fonctions de classement, lisez notre article détaillé intitulé Aperçu des fonctions de classement en SQL.

Vous pouvez également filtrer les X premières lignes en utilisant une expression de table commune (CTE) et une clause WHERE. Par exemple, nous pouvons exécuter la requête suivante pour extraire uniquement la ligne la moins bien classée :

WITH sales_cte AS (
  SELECT
    product_id,
    SUM(amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(amount) DESC) AS rank
  FROM sales
  GROUP BY id 
) 
SELECT * 
FROM sales_cte
WHERE rank = 1;

Dans cette requête, nous définissons une CTE appelée sales_ctequi indique le rang de chaque site product_id en fonction de ses ventes totales. Nous interrogeons ensuite cet ETC, en n'extrayant que les lignes dont le rang = 1. Cela nous permet de ne renvoyer que la première ligne.

4. Calcul de la différence (Delta) entre les lignes

Il est parfois nécessaire de comparer les valeurs de lignes consécutives. Par exemple, on peut extraire les ventes d'un mois précédent pour les comparer à celles du mois en cours. Le fait de disposer à la fois des ventes du mois en cours et des ventes du mois précédent vous permet de calculer la différence entre les deux valeurs.

Pour ce faire, vous pouvez utiliser des fonctions de fenêtre telles que LEAD() et LAG(). Cette fois-ci, nous utiliserons un tableau différent : sales_agg. Ce tableau contient les ventes globales pour chaque année et chaque mois.

Calculons la différence de ventes d'un mois sur l'autre à l'aide de LAG():

SELECT
  year, 
  month,
  total_sales,
  LAG(total_sales) OVER(ORDER BY year, month) AS previous_month,
  total_sales – LAG(total_sales) OVER(ORDER BY year, month) AS            sales_difference
FROM sales_agg
ORDER BY year, month;

Cette requête utilise la fonction LAG() pour calculer la différence entre les ventes du mois en cours et celles du mois précédent :

La fonction LAG() est une fonction à fenêtre. Elle extrait la valeur total_sales de la ligne précédente, classée par année et par mois. Nous remplaçons cette colonne par previous_month.

La colonne sales_difference est ensuite calculée en prenant la valeur total_sales pour chaque ligne et en soustrayant la valeur previous_month. La première ligne contient les valeurs NULL, puisqu'il n'y a pas de ligne précédente.

Si vous deviez extraire les ventes du mois suivant, vous utiliseriez la fonction de fenêtre LEAD() à la place de LAG(). LAG() compare la ligne actuelle aux valeurs précédentes ; LEAD() compare la ligne actuelle aux valeurs suivantes.

How to Calculate the Difference Between Two Rows in SQL (Comment calculer la différence entre deux lignes en SQL ) donne plusieurs exemples concrets d'utilisation de cette syntaxe pour calculer la différence entre deux lignes.

5. Calcul d'un total courant

Un total courant calcule la somme d'une séquence de nombres. Il est également connu sous le nom de total cumulé ou de somme cumulée ; il ajoute chaque nouvelle valeur au total précédent.

Les totaux courants sont utiles pour calculer l'effet cumulatif des points de données au fil du temps. Par exemple, vous pourriez vouloir calculer le nombre cumulé d'utilisateurs qui ont visité votre site web jusqu'à une certaine date afin de comprendre la croissance du nombre d'utilisateurs de votre site web.

Revenons sur la fonction SUM() et voyons comment nous pouvons l'utiliser pour calculer la somme des ventes. Comme dans l'exemple précédent, nous devons utiliser SUM() comme fonction fenêtre pour obtenir les résultats souhaités.

SELECT
  year,
  month,
  total_sales,
  SUM(total_sales) OVER(ORDER BY year, month) AS running_total
FROM sales_agg
ORDER BY year, month;

Ce total courant est calculé à l'aide de la fonction SUM() avec la clause OVER(). Cette fonction ajoute le total_sales de la ligne actuelle à toutes les lignes précédentes dans l'ordre spécifié.

Dans la deuxième ligne, le total courant est calculé en agrégeant la valeur total_sales de la première et de la deuxième ligne. Dans notre exemple, la deuxième ligne correspond à février 2021. La valeur running_total est la somme de la première ligne (pour janvier 2021) et de la deuxième ligne (pour février 2021).

Dans la troisième ligne, le total courant est calculé en agrégeant les valeurs de la première à la troisième ligne. Le même schéma se répète pour chaque ligne.

Pour plus d'informations sur le calcul des totaux courants en SQL, consultez notre article Qu'est-ce qu'un total courant SQL et comment le calculer ?

6. Calculer une moyenne mobile

Lorsque l'on étudie les tendances des ventes dans l'analyse des données, il est souvent utile d'utiliser une moyenne mobile plutôt que chaque point de données individuel. Une moyenne mobile (également connue sous le nom de moyenne glissante) calcule la moyenne de la valeur actuelle et d'un nombre spécifié de valeurs immédiatement antérieures.

Cette technique permet de lisser les données et d'identifier les tendances, en particulier lorsque les données sont très volatiles. L'idée principale est d'examiner comment ces moyennes se comportent dans le temps au lieu d'examiner le comportement des points de données originaux.

Par exemple, vous pourriez avoir besoin d'analyser les ventes quotidiennes d'un restaurant dont les ventes sont élevées le week-end et faibles du lundi au mercredi. Si vous deviez représenter chaque point de données individuellement, vous verriez des valeurs très élevées et très basses les unes à côté des autres, ce qui rendrait les tendances à long terme plus difficiles à percevoir. En utilisant une moyenne mobile sur trois jours, vous prenez la moyenne des trois derniers jours, ce qui égalise les hauts et les bas.

Pour cet exemple, nous allons modifier notre tableau sales_agg.

Calculons maintenant une moyenne mobile des ventes sur 3 mois :

SELECT
  year, 
  month, 
  total_sales, 
  AVG(total_sales) OVER(ORDER BY year, month ROWS BETWEEN 2 PRECEDING and CURRENT ROW) AS moving_average
FROM aales_agg
ORDER BY year, month;

Cette requête calcule la moyenne mobile en utilisant la fonction AVG() avec la clause OVER(). La clause ROWS BETWEEN spécifie la plage de lignes à inclure dans le calcul de la moyenne mobile - dans ce cas, la ligne actuelle et les deux lignes précédentes.

Résultat :

Comme il n'y a pas de lignes précédentes, la moyenne mobile de la première ligne est simplement la valeur totale des ventes. La moyenne mobile de la deuxième ligne est la moyenne de 300 et 700. Pour la troisième ligne, nous disposons maintenant des deux lignes précédentes, telles que définies dans notre requête ; la moyenne mobile est calculée en faisant la moyenne de 300, 700 et 500. Ce schéma se poursuit pour les autres lignes.

Le siteWhat a Moving Average Is and How to Compute It in SQL (Ce qu'est une moyenne mobile et comment la calculer en SQL ) est une excellente ressource pour plus d'informations sur ce sujet.

7. Comptage des éléments dans les catégories personnalisées à l'aide de SUM() et CASE WHEN

Vous pouvez compter les éléments dans des catégories personnalisées en combinant SUM() avec CASE WHEN. Vous utiliserez cette méthode lorsque vous devrez créer une logique commerciale qui n'existe pas dans vos données. Par exemple, vous pouvez regrouper des lieux spécifiques par régions personnalisées, puis calculer des indicateurs basés sur ces régions.

CASE WHEN Les instructions vous permettent d'exécuter une logique conditionnelle dans les requêtes. La syntaxe est structurée comme suit :

CASE 
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
END

Les conditions sont évaluées de haut en bas. La première condition évaluée comme vraie détermine le résultat renvoyé.

Dans notre exemple, créons une ventilation des catégories de vente basée sur les montants des ventes :

SELECT
  SUM(CASE WHEN total_sales < 400 THEN 1 ELSE 0 END) AS low_sales,  
  SUM(CASE WHEN total_sales >= 400 AND total_sales < 700 THEN 1 ELSE 0 END) AS medium_sales,
  SUM(CASE WHEN total_sales >= 700 THEN 1 ELSE 0 END) AS high_sales
FROM sales_agg

Dans la requête ci-dessus, nous utilisons l'expression CASE WHEN pour classer chaque mois dans les catégories low_sales, medium_sales ou high_sales. Lorsque la condition de l'expression CASE WHEN est remplie, elle renvoie la valeur 1 ; dans le cas contraire, elle renvoie la valeur 0.

La fonction SUM() est ensuite utilisée pour additionner les 1 et les 0 de chaque catégorie, ce qui permet de compter le nombre de mois entrant dans chaque catégorie. Le résultat est une ligne unique contenant les décomptes des mois low_sales, medium_sales et high_sales.

Cette approche vous permet de créer des catégories personnalisées et de compter les éléments de chaque catégorie à l'aide d'une seule requête. La combinaison de SUM() et CASE WHEN est polyvalente et peut être adaptée à différents cas d'utilisation.

Si vous souhaitez utiliser cette construction dans votre requête, How to Use CASE WHEN with SUM() in SQL vous donnera plus de détails.

Améliorez votre analyse de données avec les requêtes SQL avancé

La maîtrise des requêtes SQL avancées est essentielle pour une analyse efficace et précise des données. Dans cet article, nous avons abordé sept techniques SQL puissantes qui peuvent vous aider à mieux comprendre vos données.

En apprenant à regrouper les données par période, à utiliser ROLLUP pour plusieurs niveaux de regroupement, à classer les données avec des fonctions de fenêtre, à calculer les différences entre les lignes, à calculer les totaux courants et les moyennes mobiles, et à compter les éléments dans des catégories personnalisées, vous serez bien équipé pour vous attaquer à des tâches d'analyse de données complexes.

En continuant à perfectionner vos compétences en SQL, vous découvrirez encore plus de façons d'exploiter ce puissant outil pour libérer tout le potentiel de vos ensembles de données et améliorer la prise de décision au sein de votre organisation.

Pour plus de pratique et pour acquérir un niveau de compréhension encore plus approfondi sur ces sujets, inscrivez-vous à notre piste SQL Reporting. C'est un excellent moyen de maîtriser ces fonctions SQL avancées.