GROUPING SETS - MaxCompute - Centre de documentation Alibaba Cloud

Pour agréger la colonne a, la colonne b, ainsi que les colonnes a et b conjointement dans le cadre d'une analyse de données multidimensionnelles, utilisez GROUPING SETS. Cette rubrique explique comment utiliser GROUPING SETS pour réaliser des agrégations multidimensionnelles.

Description

GROUPING SETS étend la clause GROUP BY d'une instruction SELECT. Elle permet de grouper les résultats selon plusieurs combinaisons sans exécuter successivement plusieurs instructions SELECT combinées par UNION ALL. MaxCompute génère ainsi des plans d'exécution plus efficaces, offrant de meilleures performances.

Le tableau suivant décrit la syntaxe associée à GROUPING SETS.

Type	Description
`CUBE`	Forme spéciale de `GROUPING SETS`, CUBE énumère toutes les combinaisons possibles des colonnes spécifiées en tant que `grouping sets`. Vous pouvez également utiliser CUBE avec `GROUPING SETS`. `group by cube (a, b, c) -- Equivalent to the following clauses: grouping sets ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),()) group by cube ((a,b),(c,d)) -- Equivalent to the following clauses: grouping sets ( (a,b,c,d), (a,b), (c,d), () ) group by a, cube (b,c), grouping sets ((d),(e)) -- Equivalent to the following clause: group by grouping sets ( (a, b, c, d), (a, b, c, e), (a, b, d), (a, b, e), (a, c, d), (a, c, e), (a, d), (a, e) )`
`ROLLUP`	Forme spéciale de `GROUPING SETS`. ROLLUP agrège les colonnes spécifiées et génère des `grouping sets` de manière hiérarchique. Vous pouvez également utiliser ROLLUP avec `GROUPING SETS`. `group by rollup (a,b,c) -- Equivalent to the following clauses: grouping sets ((a,b,c),(a,b),(a),()) group by rollup (a,(b,c),d) -- Equivalent to the following clauses: grouping sets ( (a,b,c,d), (a,b,c), (a), () ) group by grouping sets((b),(c),rollup(a,b,c)) -- Equivalent to the following clause: group by grouping sets ( (b),(c),(a,b,c),(a,b),(a),() )`
`GROUPING`	`GROUPING` permet de distinguer les espaces réservés NULL des valeurs NULL réelles. Cette fonction accepte le nom d'une colonne en paramètre. Elle renvoie 0 si les lignes sont agrégées selon cette colonne, et 1 dans les autres cas.
`GROUPING_ID`	`GROUPING_ID` accepte les noms d'une ou plusieurs colonnes en paramètres. Les résultats de `grouping` des colonnes sont utilisés pour former des valeurs entières selon un format `Bitmap`.
`GROUPING__ID`	`GROUPING__ID` ne nécessite aucun paramètre. Elle est utilisée pour les requêtes compatibles Hive. Elle équivaut à `GROUPING_ID(GROUP BY Parameter list)`. Les paramètres de GROUPING__ID suivent le même ordre que ceux de `GROUP BY`. Remarque Si vous utilisez Hive 2.3.0 ou une version ultérieure, nous vous recommandons d'utiliser cette fonction dans MaxCompute. Si vous utilisez une version de Hive antérieure à 2.3.0, nous vous déconseillons d'utiliser cette fonction dans MaxCompute.

Exemples

Exemple d'utilisation de GROUPING SETS :

Préparez les données.

create table requests lifecycle 20 as 
select * from values 
    (1, 'windows', 'PC', 'Beijing'),
    (2, 'windows', 'PC', 'Shijiazhuang'),
    (3, 'linux', 'Phone', 'Beijing'),
    (4, 'windows', 'PC', 'Beijing'),
    (5, 'ios', 'Phone', 'Shijiazhuang'),
    (6, 'linux', 'PC', 'Beijing'),
    (7, 'windows', 'Phone', 'Shijiazhuang') 
as t(id, os, device, city);

Utilisez l'une des méthodes suivantes pour grouper les données :

Exécutez plusieurs instructions SELECT pour grouper les données.

select NULL, NULL, NULL, count(*)
from requests
union all
select os, device, NULL, count(*)
from requests group by os, device
union all
select null, null, city, count(*)
from requests group by city;

Utilisez GROUPING SETS pour grouper les données.

select os,device, city ,count(*)
from requests
group by grouping sets((os, device), (city), ());

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | NULL       | Beijing    | 4          |
| NULL       | NULL       | Shijiazhuang | 3          |
| ios        | Phone      | NULL       | 1          |
| linux      | PC         | NULL       | 1          |
| linux      | Phone      | NULL       | 1          |
| windows    | PC         | NULL       | 3          |
| windows    | Phone      | NULL       | 1          |
+------------+------------+------------+------------+

Remarque

Si certaines expressions ne figurent pas dans GROUPING SETS, NULL sert d'espace réservé pour ces expressions, comme illustré par les valeurs NULL de la colonne city dans les quatrième à huitième lignes. Cela permet d'effectuer des opérations sur les ensembles de résultats.

Exemples d'utilisation de CUBE ou ROLLUP

Exemples d'utilisation de CUBE ou de ROLLUP basés sur la syntaxe de GROUPING SETS :

Exemple 1 : Utilisez CUBE pour énumérer toutes les combinaisons possibles des colonnes os, device et city en tant que grouping sets.

select os,device, city, count(*)
from requests 
group by cube (os, device, city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os, city),(device,city),(os),(device),(city),());

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | NULL       | Beijing    | 4          |
| NULL       | NULL       | Shijiazhuang | 3          |
| NULL       | PC         | NULL       | 4          |
| NULL       | PC         | Beijing    | 3          |
| NULL       | PC         | Shijiazhuang | 1          |
| NULL       | Phone      | NULL       | 3          |
| NULL       | Phone      | Beijing    | 1          |
| NULL       | Phone      | Shijiazhuang | 2          |
| ios        | NULL       | NULL       | 1          |
| ios        | NULL       | Shijiazhuang | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | NULL       | Beijing    | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | NULL       | Beijing    | 2          |
| windows    | NULL       | Shijiazhuang | 2          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Exemple 2 : Utilisez CUBE pour énumérer toutes les combinaisons possibles des groupes de colonnes (os, device) et (device, city) en tant que grouping sets.

select os,device, city, count(*) 
from requests 
group by cube ((os, device), (device, city));
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*) 
from requests 
group by grouping sets ((os, device, city),(os, device),(device,city),());

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| NULL       | PC         | Beijing    | 3          |
| NULL       | PC         | Shijiazhuang | 1          |
| NULL       | Phone      | Beijing    | 1          |
| NULL       | Phone      | Shijiazhuang | 2          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Exemple 3 : Utilisez ROLLUP pour agréger hiérarchiquement les colonnes os, device et city afin de générer plusieurs grouping sets.

select os,device, city, count(*)
from requests 
group by rollup (os, device, city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| ios        | NULL       | NULL       | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Exemple 4 : Utilisez ROLLUP pour agréger hiérarchiquement os, (os,device) et city afin de générer plusieurs grouping sets.

select os,device, city, count(*)
from requests 
group by rollup (os, (os,device), city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets ((os, device, city),(os, device),(os),());

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 7          |
| ios        | NULL       | NULL       | 1          |
| ios        | Phone      | NULL       | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | NULL       | 2          |
| linux      | PC         | NULL       | 1          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | NULL       | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | NULL       | 4          |
| windows    | PC         | NULL       | 3          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | NULL       | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Exemple 5 : Utilisez GROUP BY, CUBE et GROUPING SETS pour générer plusieurs grouping sets.

select os,device, city, count(*)
from requests 
group by os, cube(os,device), grouping sets(city);
-- The preceding statement is equivalent to the following statement:
select os,device, city, count(*)
from requests 
group by grouping sets((os,device,city),(os,city),(os,device,city));

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | _c3        |
+------------+------------+------------+------------+
| ios        | NULL       | Shijiazhuang | 1          |
| ios        | Phone      | Shijiazhuang | 1          |
| linux      | NULL       | Beijing    | 2          |
| linux      | PC         | Beijing    | 1          |
| linux      | Phone      | Beijing    | 1          |
| windows    | NULL       | Beijing    | 2          |
| windows    | NULL       | Shijiazhuang | 2          |
| windows    | PC         | Beijing    | 2          |
| windows    | PC         | Shijiazhuang | 1          |
| windows    | Phone      | Shijiazhuang | 1          |
+------------+------------+------------+------------+

Exemple d'utilisation de GROUPING et GROUPING_ID

Exemple de requête :

select a,b,c,count(*),
grouping(a) ga, grouping(b) gb, grouping(c) gc, grouping_id(a,b,c) groupingid 
from values (1,2,3) as t(a,b,c)
group by cube(a,b,c);

Le résultat suivant s'affiche :

+------------+------------+------------+------------+------------+------------+------------+------------+
| a          | b          | c          | _c3        | ga         | gb         | gc         | groupingid |
+------------+------------+------------+------------+------------+------------+------------+------------+
| NULL       | NULL       | NULL       | 1          | 1          | 1          | 1          | 7          |
| NULL       | NULL       | 3          | 1          | 1          | 1          | 0          | 6          |
| NULL       | 2          | NULL       | 1          | 1          | 0          | 1          | 5          |
| NULL       | 2          | 3          | 1          | 1          | 0          | 0          | 4          |
| 1          | NULL       | NULL       | 1          | 0          | 1          | 1          | 3          |
| 1          | NULL       | 3          | 1          | 0          | 1          | 0          | 2          |
| 1          | 2          | NULL       | 1          | 0          | 0          | 1          | 1          |
| 1          | 2          | 3          | 1          | 0          | 0          | 0          | 0          |
+------------+------------+------------+------------+------------+------------+------------+------------+

Par défaut, les colonnes non spécifiées dans GROUP BY sont remplies avec NULL. Vous pouvez utiliser GROUPING pour spécifier les valeurs souhaitées. Exemple de requête basé sur la syntaxe de GROUPING SETS :

select
  if(grouping(os) == 0, os, 'ALL') as os,
  if(grouping(device) == 0, device, 'ALL') as device,
  if(grouping(city) == 0, city, 'ALL') as city, 
  count(*) as count 
from requests 
group by os, device, city grouping sets((os, device), (city), ());

Le résultat suivant s'affiche :

+------------+------------+------------+------------+
| os         | device     | city       | count      |
+------------+------------+------------+------------+
| ALL        | ALL        | ALL        | 7          |
| ALL        | ALL        | Beijing    | 4          |
| ALL        | ALL        | Shijiazhuang | 3          |
| ios        | Phone      | ALL        | 1          |
| linux      | PC         | ALL        | 1          |
| linux      | Phone      | ALL        | 1          |
| windows    | PC         | ALL        | 3          |
| windows    | Phone      | ALL        | 1          |
+------------+------------+------------+------------+

Exemple d'utilisation de GROUPING__ID

Exemple d'utilisation de GROUPING__ID sans paramètre spécifié :

set odps.sql.hive.compatible=true;
select      
a, b, c, count(*), grouping__id 
from values (1,2,3) as t(a,b,c) 
group by a, b, c grouping sets ((a,b,c), (a));
-- The preceding statement is equivalent to the following statement:
select      
a, b, c, count(*), grouping_id(a,b,c)  
from values (1,2,3) as t(a,b,c) 
group by a, b, c grouping sets ((a,b,c), (a));

Le résultat suivant s'affiche :

+------------+------------+------------+------------+------------+
| a          | b          | c          | _c3        | _c4        |
+------------+------------+------------+------------+------------+
| 1          | NULL       | NULL       | 1          | 3          |
| 1          | 2          | 3          | 1          | 0          |
+------------+------------+------------+------------+------------+