SQLShack (Français)

Cet article traite de la clause SQL PARTITION BY et, en particulier, la différence avec GROUP BY dans une instruction select. Nous explorerons également divers cas dutilisation de SQL PARTITION BY.

Nous utilisons SQL PARTITION BY pour diviser lensemble de résultats en partitions et effectuer des calculs sur chaque sous-ensemble de données partitionnées.

Préparation de léchantillon Données

Créons une table Commandes dans mon exemple de base de données SQLShackDemo et insérons des enregistrements pour écrire dautres requêtes.

Jutilise ApexSQL Generate pour insérer des exemples de données dans cet article. Cliquez avec le bouton droit sur la table des commandes et générez les données de test.

Il lance ApexSQL Generate. Jai généré un script pour insérer des données dans la table Orders. Exécutez ce script pour insérer 100 enregistrements dans la table Orders.

Une fois que nous exécutons les instructions dinsertion, nous pouvons voir les données dans la table Orders dans limage suivante.

Nous utilisons la clause SQL GROUP BY pour regrouper les résultats par colonne spécifiée et utilisons des fonctions dagrégation telles que Avg (), Min (), Max () pour calculer les valeurs requises.

Syntaxe de la fonction Group By

Supposons que nous voulions trouver les valeurs suivantes dans le tableau des commandes

• Valeur minimale de commande dans une ville
• Valeur maximale de commande dans une ville
• Valeur moyenne de la commande dans une ville

Exécutez la requête suivante avec la clause GROUP BY pour calculer ces valeurs.

Dans la capture décran suivante, nous pouvons voir Moyenne, Minimum et les valeurs maximales regroupées par CustomerCity.

Maintenant, nous voulons ajouter les colonnes CustomerName et OrderAmount également dans la sortie. Ajoutons ces colonnes dans linstruction select et exécutons le code suivant.

Une fois que nous exécutons cette requête, nous recevons un message derreur . Dans la clause SQL GROUP BY, nous pouvons utiliser une colonne dans linstruction select si elle est également utilisée dans la clause Group by. Il nautorise aucune colonne de la clause select qui ne fait pas partie de la clause GROUP BY.

Nous pouvons utiliser le SQL Clause PARTITION BY pour résoudre ce problème. Explorons-le plus en détail dans la section suivante.

SQL PARTITION BY

Nous pouvons utiliser la clause SQL PARTITION BY avec la clause OVER pour spécifier la colonne sur laquelle nous devons effectuer lagrégation . Dans lexemple précédent, nous avons utilisé la colonne Group By avec CustomerCity et calculé les valeurs moyenne, minimale et maximale.

Réexécutons ce scénario avec la clause SQL PARTITION BY en utilisant la requête suivante.

Dans la sortie, nous obtenons des valeurs agrégées similaires à un GROUPE Par clause. Vous remarquerez peut-être une différence dans la sortie de la sortie des clauses SQL PARTITION BY et GROUP BY.

Dans lexemple précédent, nous obtenons un message derreur si nous essayons dajouter une colonne qui ne fait pas partie de la clause GROUP BY.

Nous pouvons ajouter les colonnes requises dans une instruction select avec la clause SQL PARTITION BY . Ajoutons les colonnes CustomerName et OrderAmount et exécutons la requête suivante.

Nous obtenons la colonne CustomerName et OrderAmount avec la sortie de la fonction agrégée. Nous obtenons également toutes les lignes disponibles dans le tableau des commandes.

Dans la capture décran suivante, vous pouvez pour CustomerCity Chicago, il fonctionne agrégations (Moy, Min et Max) et donne des valeurs dans les colonnes respectives.

De même, nous pouvons utiliser dautres fonctions dagrégation telles comme count pour connaître le nombre total de commandes dans une ville particulière avec la clause SQL PARTITION BY.

Nous pouvons voir le nombre de commandes pour une ville particulière. Par exemple, nous avons donc deux commandes de la ville dAustin; il affiche la valeur 2 dans la colonne CountofOrders.

Clause PARTITION BY avec ROW_NUMBER ()

Nous pouvons utilisez la clause SQL PARTITION BY avec la fonction ROW_NUMBER () pour avoir un numéro de ligne pour chaque ligne. Nous définissons les paramètres suivants pour utiliser ROW_NUMBER avec la clause SQL PARTITION BY.

• Colonne PARTITION BY – Dans cet exemple, nous voulons partitionner les données sur la colonne CustomerCity
• Trier par : Dans la colonne ORDER BY, nous définissons une colonne ou une condition qui définit le numéro de ligne. Dans cet exemple, nous voulons trier les données sur la colonne OrderAmount

Dans la capture décran suivante, nous voyons pour CustomerCity Chicago , nous avons la ligne numéro 1 pour lordre avec le montant le plus élevé 7577.90. il fournit le numéro de ligne avec OrderAmount décroissant.

Clause PARTITION BY avec valeur totale cumulée

Supposons que nous souhaitez obtenir un total cumulatif des commandes dans une partition.Le total cumulé doit être de la ligne actuelle et de la ligne suivante de la partition.

Par exemple, dans la ville de Chicago nous avons quatre commandes.

Dans la requête suivante, nous avons la clause ROWS spécifiée pour se lisez la ligne actuelle (en utilisant CURRENT ROW) et la ligne suivante (en utilisant 1 FOLLOWING). Il calcule en outre la somme sur ces lignes en utilisant sum (Orderamount) avec une partition sur CustomerCity (en utilisant OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).

De même, nous pouvons calculer la moyenne cumulée en utilisant la requête suivante avec la clause SQL PARTITION BY.

ROWS UNBOUNDED PRECEDING avec la clause PARTITION BY

Nous pouvons utiliser ROWS UNBOUNDED PRECEDING avec la clause SQL PARTITION BY pour sélectionner une ligne dans une partition avant la ligne courante et la valeur la plus élevée r ow après la ligne courante.

Dans le tableau suivant, nous pouvons voir pour la ligne 1; il na aucune ligne avec une valeur élevée dans cette partition. Par conséquent, la valeur moyenne cumulative est la même que celle de la ligne 1 OrderAmount.

Pour la ligne 2, elle recherche la valeur de ligne actuelle (7199,61) et la valeur la plus élevée de la ligne 1 (7577,9). Il calcule la moyenne de ces deux montants.

Pour la ligne 3, il recherche la valeur actuelle (6847,66) et une valeur de montant supérieure à cette valeur qui est 7199,61 et 7577,90. Il calcule la moyenne de ceux-ci et renvoie.

Exécutez la requête suivante pour obtenir ce résultat avec nos exemples de données.

Conclusion

Dans cet article, nous avons exploré la clause SQL PARTIION BY et sa comparaison avec la clause GROUP BY. Nous avons également appris son utilisation avec quelques exemples. Jespère que vous trouverez cet article utile et nhésitez pas à poser vos questions dans les commentaires ci-dessous

• Auteur
• Messages récents

En tant que formateur certifié MCSA et Microsoft Certified à Gurgaon, en Inde, avec 13 ans dexpérience, Rajendra travaille pour une variété de grandes entreprises se concentrant sur loptimisation des performances, le suivi et la haute disponibilité et stratégies de reprise après sinistre et mise en œuvre. Il est lauteur de centaines darticles faisant autorité sur SQL Server, Azure, MySQL, Linux, Power BI, le réglage des performances, AWS / Amazon RDS, Git et les technologies associées qui ont été consultés par plus de 10 millions de lecteurs à ce jour.
Il est le créateur de lune des plus grandes collections darticles en ligne gratuits sur un seul sujet, avec sa série de 50 articles sur les groupes de disponibilité Always On SQL Server. Sur la base de sa contribution à la communauté SQL Server, il a été récompensé par divers prix dont le prestigieux « Meilleur auteur de lannée » en continu en 2020 et 2021 chez SQLShack.
Raj est toujours intéressé par de nouveaux défis, donc si vous avez besoin de conseil aide sur tous les sujets traités dans ses écrits, il peut être contacté à [email protected]
Voir tous les articles de Rajendra Gupta

Derniers articles de Rajendra Gupta (voir tout)

• Délais dexpiration des sessions dans SQL Server Always On Availability Groupes – 8 février 2021
• Mise à niveau des versions mineures et majeures pour AWS RDS SQL Server – 29 janvier 2021
• Déploiement dinstances AWS RDS PostgreSQL – 27 janvier 2021