GROUPING SETS PostgreSQL

Résumé : dans ce tutoriel, vous apprendrez le concept de grouping set et comment utiliser la clause GROUPING SETS de PostgreSQL pour générer plusieurs grouping sets dans une requête.

Configurer une table d’exemple

Démarrons en créant une nouvelle table appeléesales pour la démonstration.

Code language: SQL (Structured Query Language) (sql)

La table sales stocke le nombre de produits vendus par marque et par segment.

Introduction à PostgreSQL GROUPING SETS

Un ensemble de regroupement est un ensemble de colonnes par lequel vous regroupez en utilisant la clause GROUP BY.

Un ensemble de regroupement est dénoté par une liste de colonnes séparées par des virgules et placées entre parenthèses :

Par exemple, la requête suivante utilise la clause GROUP BY pour renvoyer le nombre de produits vendus par marque et par segment. En d’autres termes, elle définit un ensemble de regroupement de la marque et du segment qui est dénoté par (brand, segement)

Code language: SQL (Structured Query Language) (sql)

La requête suivante trouve le nombre de produits vendus par une marque. Elle définit un ensemble de regroupement (brand):

Code language: SQL (Structured Query Language) (sql)

La requête suivante trouve le nombre de produits vendus par segment. Elle définit un ensemble de regroupement (segment):

Code language: SQL (Structured Query Language) (sql)

La requête suivante trouve le nombre de produits vendus pour toutes les marques et tous les segments. Elle définit un ensemble de regroupement vide qui est désigné par ().

Code language: SQL (Structured Query Language) (sql)

Supposons que vous voulez tous les ensembles de regroupement en utilisant une seule requête. Pour y parvenir, vous pouvez utiliser la UNION ALL pour combiner toutes les requêtes ci-dessus.

Parce que la UNION ALL exige que tous les ensembles de résultats aient le même nombre de colonnes avec des types de données compatibles, vous devez ajuster les requêtes en ajoutant NULL à la liste de sélection de chacune d’entre elles comme indiqué ci-dessous:

Code language: SQL (Structured Query Language) (sql)

Cette requête a généré un seul ensemble de résultats avec les agrégats pour tous les ensembles de regroupement.

Même si la requête ci-dessus fonctionne comme vous l’attendiez, elle présente deux problèmes principaux.

• Premièrement, elle est assez longue.
• Deuxièmement, elle présente un problème de performance car PostgreSQL doit scanner la table sales séparément pour chaque requête.

Pour la rendre plus efficace, PostgreSQL fournit la clause GROUPING SETS qui est la sous-clause de la clause GROUP BY.

La GROUPING SETS vous permet de définir plusieurs ensembles de regroupement dans la même requête.

La syntaxe générale de la GROUPING SETS est la suivante :

Code language: SQL (Structured Query Language) (sql)

Dans cette syntaxe, nous avons quatre ensembles de regroupement (c1,c2), (c1), (c2) et ().

Pour appliquer cette syntaxe à l’exemple ci-dessus, vous pouvez utiliser la clause GROUPING SETS au lieu de la clause UNION ALL comme ceci:

Code language: SQL (Structured Query Language) (sql)

Cette requête est beaucoup plus courte et plus lisible. De plus, PostgreSQL optimisera le nombre de fois qu’il analyse la table sales et n’analysera pas plusieurs fois.

Fonction de regroupement

La fonction GROUPING() accepte un argument qui peut être un nom de colonne ou une expression:

La column_name ou expression doit correspondre à celle spécifiée dans la clause GROUP BY.

La fonction GROUPING() renvoie le bit 0 si l’argument est un membre de l’ensemble de regroupement actuel et 1 sinon.

Voir l’exemple suivant :

Code language: SQL (Structured Query Language) (sql)

Comme le montre la capture d’écran, lorsque la valeur dans le grouping_brand est 0, la colonne sum montre le sous-total du brand.

Lorsque la valeur dans le grouping_segment est zéro, la colonne somme montre le sous-total du segment.

Vous pouvez utiliser la fonction GROUPING() dans la clause HAVING pour trouver le sous-total de chaque marque comme ceci:

Dans ce tutoriel, vous avez appris à utiliser la GROUPING SETS de PostgreSQL pour générer des ensembles de regroupement multiples.

.