6 exemples de la fonction NTILE() en SQL

La fonction SQL NTILE() peut grandement simplifier l'analyse des données et la création de rapports. Suivez ces six exemples pour apprendre ce qu'elle est et quand l'utiliser.

Les fonctions Window sont très utiles et offrent une grande puissance d'expression au langage SQL. Dans cet article, nous abordons la fonction NTILE(), qui permet de diviser un ensemble d'enregistrements en sous-ensembles de taille approximativement égale. Cette fonction est largement utilisée dans les calculs financiers ou économiques.

Avant d'aborder la fonction NTILE(), passons rapidement en revue les fonctions de fenêtre en SQL.

Qu'est-ce que SQL Fonctions de fenêtrage?

Les fonctions de fenêtre sont des fonctions qui travaillent sur un groupe de lignes appelé fenêtre ; elles renvoient une valeur basée sur ce groupe. La fonction NTILE(N) reçoit un paramètre entier(N) et divise l'ensemble des lignes en sous-ensembles. N sous-ensembles. Chaque sous-ensemble a approximativement le même nombre de lignes et est identifié par un numéro compris entre 1 et N. C'est ce numéro d'identification que renvoie NTILE().

Si vous souhaitez apprendre les fonctions de la fenêtre SQL, je vous recommande notre cours interactif. Il contient 218 exercices pratiques pour vous aider à mettre en pratique les différentes fonctions de fenêtre. À la fin, vous vous sentirez à l'aise dans l'utilisation de cette technique SQL avancée.

Si vous voulez voir les fonctions de fenêtre en action, consultez notre article Exemple de fonction de fenêtre SQL avec explications. Et si vous voulez explorer les différences entre GROUP BY et les fonctions de fenêtre, lisez SQL Fonctions de fenêtrage vs. GROUP BY.

Exemple de données : Un magasin de football

Dans la suite de l'article, nous baserons nos exemples sur la base de données d'un magasin de football fictif. Nous nous concentrerons sur la table sales dont la structure et les données sont les suivantes :

Je pense que tout ce qui se trouve dans cette table est explicite, alors passons aux exemples.

Exemple n° 1 : division des lignes en deux groupes

Nous allons commencer par un exemple très simple. Nous voulons diviser les lignes du tableau en deux groupes : le groupe 1 et le groupe 2. sales en deux groupes : le groupe 1 et le groupe 2. C'est ce que fait la requête suivante :

SELECT NTILE(2) OVER() AS group, 
       sale_date, 
       product_id,
 soccer_team 
FROM sales;

Dans la requête, l'expression NTILE(2) OVER() renvoie 1 pour les premiers 50 % des lignes de l'ensemble de résultats et 2 pour les seconds 50 % des lignes. Les lignes sont affectées à chaque groupe de manière non déterministe, c'est-à-dire qu'il n'y a pas de critères pour affecter les lignes à un groupe particulier. Voici un résultat partiel de la requête, montrant chaque groupe dans une couleur différente :

Exemple n° 2 : répartition des ventes en 2023

La table sales stocke des enregistrements pour chaque vente réalisée. Le service marketing souhaite analyser la répartition des ventes en fonction du montant dépensé. Il a demandé un rapport regroupant toutes les ventes de l'année 2023 en quatre groupes de même taille (le nombre de ventes dans chaque groupe doit être identique). Chaque vente doit être affectée en fonction de son montant.

Le premier groupe (sale_group #1) doit contenir les ventes dont le montant est le plus bas et le dernier groupe (sale_group #4) doit contenir les ventes dont le montant est le plus élevé. Pour chaque vente, le rapport doit inclure le numéro du groupe de vente, le customer_id, le product_id et le soccer_team. La requête permettant d'obtenir ce résultat est la suivante :

  SELECT
      NTILE(4) OVER ( ORDER BY amount ) AS sale_group,
      product_id,
      product_category,
      soccer_team,
      amount as sales_amount
  FROM sales
  WHERE sale_date >= '2023-12-01' AND sale_date <= '2023-12-31';

Cette requête utilise une clause WHERE pour filtrer les ventes qui ont eu lieu en 2023. Ensuite, la fonction NTILE(4) tente de créer quatre groupes de lignes avec le même nombre de lignes chacun. Nous utilisons le terme "essaie" car il n'est pas toujours possible de créer des groupes de même taille ; certains groupes peuvent avoir une ligne de moins que les autres.

Comment définir le groupe auquel appartient chaque ligne ? La clause OVER (ORDER BY amount) indique qu'avant d'attribuer des groupes aux lignes, toutes les lignes doivent être classées en fonction du montant de la vente. Une fois triée, la fonction NTILE(4) prend le premier quart des ventes et lui attribue la valeur 1, puis prend le quart suivant des ventes et lui attribue la valeur 2, et ainsi de suite. Vous trouverez ci-dessous un résultat partiel montrant les enregistrements dans chaque sale_group:

Nous pouvons voir que dans le groupe le moins cher (1), nous n'avons que des ventes de la catégorie Accessoires. Cela s'explique par le fait que les produits accessoires sont généralement les moins chers, tandis que les chemises sont généralement plus chères. Nous pouvons également voir que les produits pour Barcelone sont dans les quatre groupes de vente, ce qui suggère que cette équipe a une offre à tous les niveaux de prix.

Exemple 3 : Ventes mensuelles pour chaque catégorie et chaque équipe

Dans la requête suivante, nous allons créer un rapport sur les ventes mensuelles. Le service marketing souhaite répartir les ventes mensuelles de chaque catégorie de produits et de chaque équipe en quatre groupes. Le premier groupe comprendra les catégories de produits, les équipes de football et les mois où les ventes totales sont les plus faibles. Le groupe suivant comprendra le niveau suivant de total_sales, et ainsi de suite. De cette manière, les spécialistes du marketing pourront analyser la répartition des ventes en fonction des mois et des catégories. La requête est présentée ci-dessous :

WITH monthly_sales_stats AS (
   SELECT
        EXTRACT(MONTH FROM sale_date) as month,
        product_category,
        soccer_team,
        SUM(amount) AS total_sales
   FROM sales
   WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
   GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
SELECT
     NTILE(4) OVER ( ORDER BY total_sales ) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
FROM monthly_sales_stats
ORDER BY group DESC, total_sales DESC;

La requête comporte une expression de table commune (CTE ) appelée monthly_sales_stats, qui calcule les ventes totales pour chaque combinaison de mois, product_category, et soccer_team.

Notez que nous filtrons les lignes de l'année 2023 dans la clause WHERE. L'expression EXTRACT(MONTH FROM sale_date) est utilisée pour obtenir la valeur du mois dans le champ sale_date.

Dans la requête principale, nous appelons NTILE(4) OVER (ORDER BY total_sales) pour attribuer à chaque ligne du CTE un numéro de groupe. Vous trouverez ci-dessous un résultat partiel de la requête montrant les trois premières lignes de chaque groupe :

Dans le résultat, vous pouvez voir que les ventes les plus élevées pour chaque équipe de football se situent en décembre, probablement en raison des achats de Noël. La catégorie "Accessoires" se trouve à la fin du tableau de résultats, car les accessoires sont généralement moins chers.

Exemple n° 4 : Ventes les plus basses et les plus élevées des équipes

Comme d'autres fonctions de fenêtre, vous pouvez utiliser NTILE() avec la clause PARTITION BY. Voici un exemple.

L'équipe marketing souhaite étudier la manière dont les ventes sont réparties entre les articles de chaque équipe de football. L'idée est de diviser les ventes de chaque équipe en ensembles basés sur le montant. Une fois encore, nous placerons les ventes les plus faibles dans le premier ensemble, puis nous remonterons jusqu'aux ventes les plus élevées dans le quatrième ensemble.

Il s'agit de la requête :

SELECT	soccer_team,
      	NTILE(4) OVER (PARTITION BY soccer_team ORDER BY amount) AS group_number,
product_id, 
product_category,
amount 
FROM sales ; 

La requête est très simple ; elle comporte un SELECT avec une liste de colonnes et un FROM avec un nom de table. La fonction NTILE() utilise PARTITION BY et ORDER BY. La fonction PARTITION BY soccer_team place toutes les lignes ayant la même valeur dans soccer_team dans la même partition. Le montant ORDER BY ordonne les enregistrements dans l'ensemble de chaque équipe, en plaçant ceux qui ont les valeurs les plus faibles en premier. Ensuite, NTILE(4) renvoie 1 pour les premiers 25 % des lignes du groupe de lignes, 2 pour les seconds 25 % des lignes du groupe, et ainsi de suite.

Voici quelques résultats partiels :

Exemple n° 5 : comportement des ventes de Noël par équipe de football

Cet exemple est très similaire à l'exemple 3, à la différence que la fonction NTILE() utilise une sous-clause PARTITION BY soccer_team. Cela signifie que NTILE() créera des groupes de lignes pour chaque soccer_team au lieu de créer des groupes à partir de l'ensemble des résultats (comme dans l'exemple 3). Par conséquent, chaque équipe de football aura quatre ensembles.

La requête est la suivante :

WITH monthly_sales_stats AS (
       SELECT
            EXTRACT(MONTH FROM sale_date) as month,
            product_category,
            soccer_team,
            SUM(amount) AS total_sales
      FROM sales
      WHERE sale_date >= '2023-01-01' and sale_date <= '2023-12-31'
      GROUP BY EXTRACT(MONTH FROM sale_date), product_category, soccer_team
)
  SELECT
     NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group,
     month,
     product_category,
     soccer_team,
     total_sales
  FROM monthly_sales_stats
  ORDER BY total_sales DESC;

L'ETC monthly_sales_stats est exactement le même que dans l'exemple précédent. Il comporte une clause GROUP BY qui permet de calculer le montant total des ventes pour chaque combinaison de mois, product_category et soccer_team.

Après avoir créé le CTE, nous écrivons un SELECT avec l'expression NTILE() suivante :

NTILE(4) OVER(PARTITION BY soccer_team ORDER BY total_sales DESC) AS group

Cette expression divise les lignes de l'ETC en ensembles ayant la même valeur dans le champ soccer_team. Pour chaque ensemble d'équipes de football, NTILE(4) tente de créer quatre sous-ensembles égaux, en attribuant à chaque ligne une valeur comprise entre 1 et 4.

Les autres colonnes du résultat proviennent de l'ETC. Les résultats partiels ci-dessous montrent deux lignes par groupe pour les équipes de Barcelone et de Manchester.

Exemple n° 6 : obtenir des groupes de clients uniformément répartis

Supposons que le service marketing souhaite créer trois groupes de clients uniformément répartis afin de mener trois campagnes de marketing différentes ; chaque campagne sera ciblée sur un groupe. Les clients de chaque groupe sont choisis au hasard. Le service marketing comparera ensuite les résultats des campagnes et évaluera laquelle est la meilleure.

Pour simuler une sélection aléatoire de clients, une idée consiste à utiliser les secondes de la dernière fois que chaque client a acheté quelque chose. Nous créerons ainsi trois groupes de clients, classés en fonction des secondes. Voyons la requête :

WITH customer_last_transaction_timestamp AS (
  SELECT customer_id, 
       max(sales_date) AS last_ts
  FROM   sales
  GROUP BY customer_id
)
SELECT NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts)) AS group_number, 
       customer_id 
FROM   customer_last_transaction_timestamp 
ORDER BY group_number;

La requête précédente renvoie tous les clients avec un group_number de 1 à 3 ; cela représente le groupe marketing auquel le client a été assigné. L'ETC customer_last_transaction_timestamp stocke chaque client avec l'horodatage de sa dernière transaction (obtenu avec MAX(sales_date)).

La requête principale utilise la fonction NTILE(3) pour créer trois groupes de clients de taille approximativement identique :

NTILE(3) OVER (ORDER BY EXTRACT(SECOND FROM last_ts))

L'instruction ci-dessus renvoie 1 pour les 33 % de clients dont la valeur de l'horodatage se situe dans le tiers inférieur. Elle renvoie 2 pour les 33 % suivants, et ainsi de suite. La sous-expression EXTRACT(SECOND FROM last_ts) prend la partie secondes (c'est-à-dire 22) d'un horodatage (c'est-à-dire '2023-03-30 10:30:22'). Voici un résultat partiel :

Poursuivre l'apprentissage de NTILE() et d'autres fonctions SQL Fonctions de fenêtrage

Dans cet article, nous avons montré plusieurs exemples d'utilisation de la fonction de fenêtre NTILE(). Nous avons également présenté différentes clauses OVER. La clause OVER est commune à toutes les fonctions de fenêtre de SQL. Si vous souhaitez acquérir une expérience pratique de ces commandes, je vous suggère notre cours interactif Fonctions de fenêtrage cours interactif.

Si vous utilisez fréquemment les fonctions de fenêtre, notre feuille de contrôle gratuite SQL Fonctions de fenêtrage est une ressource très utile. En fait, je l'ai accrochée au mur de mon bureau, prête à être utilisée lorsque j'ai des doutes sur la syntaxe. Je la recommande vivement.

Enfin, l'article Les 10 meilleures questions d'entretien sur les fonctions de fenêtre SQL est très utile si vous avez un entretien d'embauche et que vous voulez être préparé aux sujets SQL.

Bon apprentissage, et continuez à progresser avec les fonctions de fenêtre SQL !