Clause GROUP BY

Article
04/18/2024

S’applique à : case marquée oui Databricks SQL Databricks Runtime

La clause GROUP BY est utilisée pour regrouper les lignes en fonction d’un ensemble d’expressions de regroupement et d’agrégations de calcul spécifiées sur le groupe de lignes sur la base d’une ou plusieurs fonctions d’agrégation données. Databricks SQL prend également en charge des agrégations avancées permettant d’effectuer plusieurs agrégations pour le même jeu d’enregistrements d’entrée via des clauses GROUPING SETS, CUBE et ROLLUP. Les expressions de regroupement et les agrégations avancées peuvent être mélangées dans la clause GROUP BY, et imbriquées dans une clause GROUPING SETS.

Pour plus d’informations, consultez la section Analyse de regroupement mixte/imbriqué.

Lorsqu’une clause FILTER est attachée à une fonction d’agrégation, seules les lignes correspondantes sont transmises à cette fonction.

Syntaxe

GROUP BY ALL

GROUP BY group_expression [, ...] [ WITH ROLLUP | WITH CUBE ]

GROUP BY { group_expression | { ROLLUP | CUBE | GROUPING SETS } ( grouping_set [, ...] ) } [, ...]

grouping_set
   { expression |
     ( [ expression [, ...] ] ) }

Quand les fonctions d’agrégation sont définies en tant que

aggregate_name ( [ DISTINCT ] expression [, ...] ) [ FILTER ( WHERE boolean_expression ) ]

Paramètres

ALL

S’applique à : Databricks SQL Databricks Runtime 12.2 LTS et versions ultérieures

Notation abrégée pour ajouter toutes les expressions SELECT-list qui ne contiennent pas de fonctions d’agrégation de type group_expression. S’il n’y a pas d’expression de ce type, GROUP BY ALL équivaut à omettre la clause GROUP BY, le résultat étant une agrégation globale.

GROUP BY ALL ne garantit pas la génération d’un ensemble d’expressions de groupe qui peuvent être résolues. Azure Databricks génère une erreur UNRESOLVED_ALL_IN_GROUP_BY ou MISSING_AGGREGATION si la clause produite n’est pas correctement formée.
group_expression

Spécifie les critères de regroupement de lignes. Le regroupement de lignes est effectué en fonction des valeurs de résultat des expressions de regroupement. Une expression de regroupement peut être un nom de colonne comme GROUP BY a, une position de colonne comme GROUP BY 0, ou une expression comme GROUP BY a + b. Si group_expression contient une fonction d’agrégation , Azure Databricks génère une erreur GROUP_BY_AGGREGATE.
grouping_set

Un jeu de regroupement est spécifié par zéro ou plusieurs expressions séparées par des virgules entre parenthèses. Lorsque le jeu de regroupement ne comprend qu’un seul élément, les parenthèses peuvent être omises. Par exemple, GROUPING SETS ((a), (b)) est identique à GROUPING SETS (a, b).
GROUPING SETS

Regroupe les lignes pour chaque jeu de regroupement spécifié après GROUPING SETS. Par exemple :

GROUP BY GROUPING SETS ((warehouse), (product)) est sémantiquement équivalent à une union des résultats de GROUP BY warehouse et GROUP BY product.

Cette clause est un raccourci pour une UNION ALL où chaque tronçon de l’opérateur UNION ALL effectue l’agrégation de chaque jeu de regroupement spécifié dans la clause GROUPING SETS.

De même, GROUP BY GROUPING SETS ((warehouse, product), (product), ()) est sémantiquement équivalent à l’union des résultats de et GROUP BY warehouse, product, GROUP BY product et d’un agrégat global.

Notes

Pour la compatibilité avec Hive, Databricks SQL autorise GROUP BY ... GROUPING SETS (...). Les expressions GROUP BY sont généralement ignorées mais, si elles contiennent des expressions en plus des expressions GROUPING SETS, ces expressions supplémentaires sont incluses dans les expressions de regroupement et la valeur est toujours null. Par exemple, SELECT a, b, c FROM ... GROUP BY a, b, c GROUPING SETS (a, b), la sortie de la colonne c est toujours null.

ROLLUP

Spécifie plusieurs niveaux d’agrégations dans une instruction unique. Cette clause est utilisée pour calculer des agrégations basées sur plusieurs jeux de regroupement. ROLLUP est un raccourci pour GROUPING SETS. Par exemple :

GROUP BY warehouse, product WITH ROLLUP ou GROUP BY ROLLUP(warehouse, product) équivalent à

GROUP BY GROUPING SETS((warehouse, product), (warehouse), ()).

Tandis que GROUP BY ROLLUP(warehouse, product, (warehouse, location))

équivaut à GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse), ()).

Les N éléments d’une spécification ROLLUP génèrent N+1 GROUPING SETS.
CUBE

La clause CUBE est utilisée pour effectuer des agrégations en fonction d’une combinaison de colonnes de regroupement spécifiées dans la clause GROUP BY. CUBE est un raccourci pour GROUPING SETS. Par exemple :

GROUP BY warehouse, product WITH CUBE ou GROUP BY CUBE(warehouse, product) équivalent à

GROUP BY GROUPING SETS((warehouse, product), (warehouse), (product), ()).

GROUP BY CUBE(warehouse, product, (warehouse, location)) équivaut à ce qui suit :
```
GROUP BY GROUPING SETS((warehouse, product, location), (warehouse, product), (warehouse, location), (product, warehouse, location), (warehouse), (product), (warehouse, product), ())
```
Les N éléments d’une spécification CUBE génèrent 2^N GROUPING SETS.
aggregate_name

Nom de fonction d’agrégation (MIN, MAX, COUNT, SUM, AVG, etc.).
DISTINCT

Supprime les doublons dans les lignes d’entrée avant que celles-ci soient passées aux fonctions d’agrégation.
FILTER

Filtre les lignes d’entrée pour lesquelles boolean_expression dans la clause WHERE prend la valeur true sont passées à la fonction d’agrégation ; les autres lignes sont ignorées.

###m Analyse de regroupement mixte/imbriqué

Une clause GROUP BY peut inclure plusieurs group_expressions et plusieurs clauses CUBE, ROLLUP et GROUPING SETS.

La clause GROUPING SETS peut également avoir des clauses imbriquées CUBE, ROLLUP ou GROUPING SETS. Par exemple :

GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location)), GROUPING SETS(warehouse, GROUPING SETS(location, GROUPING SETS(ROLLUP(warehouse, location), CUBE(warehouse, location))))

Les clauses CUBE et ROLLUP sont du sucre syntaxique pour la clause GROUPING SETS. Reportez-vous aux sections ci-dessus pour savoir comment traduire CUBE et ROLLUP en GROUPING SETS. L’expression group_expression peut être traitée comme une clause GROUPING SETS de groupe unique dans ce contexte.

Pour plusieurs clauses GROUPING SETS dans la clause GROUP BY, Databricks SQL génère une seule clause GROUPING SETS en effectuant un produit croisé de la clause GROUPING SETS d’origine.

Pour la clause GROUPING SETS imbriquée dans la clause GROUPING SETS, Databricks SQL prend ses ensembles de regroupements et les supprime. Par exemple, les requêtes suivantes :

GROUP BY warehouse, GROUPING SETS((product), ()), GROUPING SETS((location, size), (location), (size), ());

GROUP BY warehouse, ROLLUP(product), CUBE(location, size);

sont équivalents aux éléments suivants :

GROUP BY GROUPING SETS( (warehouse, product, location, size), (warehouse, product, location), (warehouse, product, size), (warehouse, product), (warehouse, location, size), (warehouse, location), (warehouse, size), (warehouse))

Tandis que GROUP BY GROUPING SETS(GROUPING SETS(warehouse), GROUPING SETS((warehouse, product)))

équivaut à GROUP BY GROUPING SETS((warehouse), (warehouse, product)).

Exemples

CREATE TEMP VIEW dealer (id, city, car_model, quantity) AS
VALUES (100, 'Fremont', 'Honda Civic', 10),
       (100, 'Fremont', 'Honda Accord', 15),
       (100, 'Fremont', 'Honda CRV', 7),
       (200, 'Dublin', 'Honda Civic', 20),
       (200, 'Dublin', 'Honda Accord', 10),
       (200, 'Dublin', 'Honda CRV', 3),
       (300, 'San Jose', 'Honda Civic', 5),
       (300, 'San Jose', 'Honda Accord', 8);

-- Sum of quantity per dealership. Group by `id`.
> SELECT id, sum(quantity) FROM dealer GROUP BY id ORDER BY id;
  id sum(quantity)
 --- -------------
 100            32
 200            33
 300            13

-- Use column position in GROUP by clause.
> SELECT id, sum(quantity) FROM dealer GROUP BY 1 ORDER BY 1;
  id sum(quantity)
 --- -------------
 100            32
 200            33
 300            13

-- Multiple aggregations.
-- 1. Sum of quantity per dealership.
-- 2. Max quantity per dealership.
> SELECT id, sum(quantity) AS sum, max(quantity) AS max
    FROM dealer GROUP BY id ORDER BY id;
  id sum max
 --- --- ---
 100  32  15
 200  33  20
 300  13   8

-- Count the number of distinct dealers in cities per car_model.
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY car_model;
    car_model count
 ------------ -----
  Honda Civic     3
    Honda CRV     2
 Honda Accord     3

-- Count the number of distinct dealers in cities per car_model, using GROUP BY ALL
> SELECT car_model, count(DISTINCT city) AS count FROM dealer GROUP BY ALL;
    car_model count
 ------------ -----
  Honda Civic     3
    Honda CRV     2
 Honda Accord     3

-- Sum of only 'Honda Civic' and 'Honda CRV' quantities per dealership.
> SELECT id,
         sum(quantity) FILTER (WHERE car_model IN ('Honda Civic', 'Honda CRV')) AS `sum(quantity)`
    FROM dealer
    GROUP BY id ORDER BY id;
  id sum(quantity)
 --- -------------
 100            17
 200            23
 300             5

-- Aggregations using multiple sets of grouping columns in a single statement.
-- Following performs aggregations based on four sets of grouping columns.
-- 1. city, car_model
-- 2. city
-- 3. car_model
-- 4. Empty grouping set. Returns quantities for all city and car models.
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
    ORDER BY city;
      city    car_model sum
 --------- ------------ ---
      null         null  78
      null  HondaAccord  33
      null     HondaCRV  10
      null   HondaCivic  35
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

-- Group by processing with `ROLLUP` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), ())
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH ROLLUP
    ORDER BY city, car_model;
      city    car_model sum
 --------- ------------ ---
      null         null  78
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

-- Group by processing with `CUBE` clause.
-- Equivalent GROUP BY GROUPING SETS ((city, car_model), (city), (car_model), ())
> SELECT city, car_model, sum(quantity) AS sum
    FROM dealer
    GROUP BY city, car_model WITH CUBE
    ORDER BY city, car_model;
      city    car_model sum
 --------- ------------ ---
      null         null  78
      null  HondaAccord  33
      null     HondaCRV  10
      null   HondaCivic  35
    Dublin         null  33
    Dublin  HondaAccord  10
    Dublin     HondaCRV   3
    Dublin   HondaCivic  20
   Fremont         null  32
   Fremont  HondaAccord  15
   Fremont     HondaCRV   7
   Fremont   HondaCivic  10
  San Jose         null  13
  San Jose  HondaAccord   8
  San Jose   HondaCivic   5

--Prepare data for ignore nulls example
> CREATE TEMP VIEW person (id, name, age) AS
   VALUES (100, 'Mary', NULL),
          (200, 'John', 30),
          (300, 'Mike', 80),
          (400, 'Dan' , 50);

--Select the first row in column age
> SELECT FIRST(age) FROM person;
  first(age, false)
 --------------------
  NULL

--Get the first row in column `age` ignore nulls,last row in column `id` and sum of column `id`.
> SELECT FIRST(age IGNORE NULLS), LAST(id), SUM(id) FROM person;
  first(age, true)    last(id, false)    sum(id)
 ------------------- ------------------ ----------
  30                  400                1000

Clause GROUP BY

Syntaxe

Paramètres

###m Analyse de regroupement mixte/imbriqué

Exemples

Articles connexes

Ressources supplémentaires