Dans ce didacticiel, nous découvrirons le fonctionnement de la clause PARTITION BY dans SQL et découvrirons comment l'utiliser pour partitionner les données pour un sous-ensemble plus granulaire.
Syntaxe:
Commençons par la syntaxe de la clause PARTITION BY. La syntaxe peut dépendre du contexte dans lequel vous l'utilisez mais voici la syntaxe générale :
SELECT colonne1, colonne2, ...
OVER (PARTITION PAR partition_column1, partition_column2, ...)
FROM nom_table
La syntaxe donnée représente les éléments suivants :
- column1, column2 – Cela fait référence aux colonnes que nous souhaitons inclure dans l’ensemble de résultats.
- Colonnes PARTITION BY – Cette clause définit la manière dont nous souhaitons partitionner ou regrouper les données.
Exemples de données
Créons une table de base avec un exemple de données pour montrer comment utiliser la clause PARTITION BY. Pour cet exemple, créons une table de base qui stocke les informations sur le produit.
Produits CRÉER UNE TABLE (
product_id INT CLÉ PRIMAIRE AUTO_INCREMENT,
nom_produit VARCHAR( 255 ),
catégorie VARCHAR( 255 ),
prix DÉCIMAL( dix , 2 ),
quantité INT,
date_d'expiration DATE,
code-barres BIGINT
);
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Toque de chef 25 cm' ,
'boulangerie' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Œufs de caille - en conserve' ,
'garde-manger' ,
17,99 ,
67 ,
'2023-09-29' ,
1708039594250 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Café - Lait de Poule Capuccino' ,
'boulangerie' ,
92.53 ,
dix ,
'2023-09-22' ,
8704051853058 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Poire - Piquante' ,
'boulangerie' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Pâtes - Cheveux d'Ange' ,
'garde-manger' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Vin - Prosecco Valdobiaddene' ,
'produire' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Pâtisserie - Mini Assortiment Français' ,
'garde-manger' ,
36,73 ,
52 ,
'2023-05-29' ,
5963886298051 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Orange - en conserve, mandarine' ,
'produire' ,
65,0 ,
1 ,
'2023-04-20' ,
6131761721332 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( 'Épaule de porc' ,
'produire' ,
55.55 ,
73 ,
'2023-05-01' ,
9343592107125 );
insérer
dans
produits (nom_produit,
catégorie,
prix,
quantité,
date d'expiration,
code à barre)
valeurs ( « DC Hikiage Hira Huba » ,
'produire' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Une fois que nous avons l'exemple de configuration de données, nous pouvons continuer et utiliser la clause PARTITION BY.
Utilisation de base
Supposons que nous souhaitions calculer le total des articles pour chaque catégorie de produits dans le tableau précédent. Nous pouvons utiliser la PARTITION BY pour diviser les articles en catégories uniques, puis déterminer le total de la quantité dans chaque catégorie.
Un exemple est le suivant :
SÉLECTIONNER
nom_produit,
catégorie,
quantité,
SUM(quantité) OVER (PARTITION PAR catégorie) AS total_items
DEPUIS
des produits;
Notez que dans l'exemple donné, nous partitionnons les données en utilisant la colonne « catégorie ». Nous utilisons ensuite la fonction d'agrégation SUM() pour déterminer séparément le total des éléments dans chaque catégorie. Le résultat montre le total des éléments dans chaque catégorie.
Utilisation de la clause PARTITION BY
Pour résumer, le cas d'utilisation le plus courant de la clause PARTITION BY est en conjonction avec les fonctions de fenêtre. La fonction fenêtre est appliquée à chaque partition séparément.
Certaines des fonctions de fenêtre courantes à utiliser avec PARTITION BY sont les suivantes :
- SUM() – Calcule la somme d'une colonne dans chaque partition.
- AVG() – Calcule la moyenne d'une colonne dans chaque partition.
- COUNT() – Comptez le nombre de lignes dans chaque partition.
- ROW_NUMBER() – Attribuez un numéro de ligne unique à chaque ligne de chaque partition.
- RANK() – Attribuez un rang à chaque ligne de chaque partition.
- DENSE_RANK() – Attribuez un rang dense à chaque ligne de chaque partition.
- NTILE() – Divisez les données en quantiles au sein de chaque partition.
C'est ça!
Conclusion
Dans ce didacticiel, nous avons appris à utiliser la clause PARTITION BY dans SQL pour partitionner les données en différents segments, puis à appliquer une opération spécifique à chacune des partitions résultantes séparément.