GROUPING SETS - MaxCompute: Central de Documentação do Alibaba Cloud

Em análises e agregações de dados multidimensionais, para agregar a Coluna a, a Coluna b e ambas simultaneamente, use GROUPING SETS. Este tópico descreve como usar GROUPING SETS para agregação multidimensional.

Descrição

GROUPING SETS é uma extensão da cláusula GROUP BY em uma instrução SELECT. Essa cláusula permite agrupar resultados de várias formas sem executar múltiplas instruções SELECT e UNION ALL em sequência. Assim, o MaxCompute gera planos de execução mais eficientes e com melhor desempenho.

A tabela a seguir descreve a sintaxe associada ao GROUPING SETS.

Tipo	Descrição
`CUBE`	Forma especial de `GROUPING SETS`, o CUBE lista todas as combinações possíveis das colunas especificadas como `grouping sets`. Também é possível usar CUBE com `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`	Outra forma especial de `GROUPING SETS`. O ROLLUP agrega as colunas especificadas e gera `grouping sets` hierarquicamente. Assim como o CUBE, o ROLLUP pode ser combinado com `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`	A função `GROUPING` distingue espaços reservados NULL de valores NULL reais. Ela aceita o nome de uma coluna como parâmetro. Retorna 0 se as linhas forem agregadas com base nessa coluna; caso contrário, retorna 1.
`GROUPING_ID`	`GROUPING_ID` aceita os nomes de uma ou mais colunas como parâmetros. Os resultados de `grouping` das colunas formam valores inteiros no formato `Bitmap`.
`GROUPING__ID`	`GROUPING__ID` não requer parâmetros e destina-se a consultas compatíveis com Hive. Equivale a `GROUPING_ID(GROUP BY Parameter list)`. Os parâmetros de GROUPING__ID seguem a mesma ordem do `GROUP BY`. Nota Recomendamos o uso desta função no MaxCompute apenas com o Hive 2.3.0 ou posterior. Para versões do Hive anteriores à 2.3.0, evite utilizá-la.

Exemplos

Exemplo de uso do GROUPING SETS:

Prepare os dados.

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);

Agrupe os dados usando um dos métodos abaixo:

Execute múltiplas instruções SELECT para agrupar os dados.

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;

Use GROUPING SETS para agrupar os dados.

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

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Nota

Quando algumas expressões não são usadas no GROUPING SETS, NULL serve como espaço reservado. Por exemplo, observe o valor NULL na coluna city da quarta à oitava linha. Esse mecanismo permite executar operações nos conjuntos de resultados.

Exemplos de uso de CUBE ou ROLLUP

Veja a seguir exemplos de uso de CUBE ou ROLLUP baseados na sintaxe do GROUPING SETS:

Exemplo 1: Uso de CUBE para listar todas as combinações possíveis das colunas os, device e city como 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),());

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Exemplo 2: Aplicação de CUBE para listar todas as combinações possíveis dos grupos de colunas (os, device) e (device, city) como 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),());

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Exemplo 3: Uso de ROLLUP para agregar as colunas os, device e city hierarquicamente, gerando múltiplos 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),());

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Exemplo 4: Agregação hierárquica de os, (os,device) e city com ROLLUP para gerar vários 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),());

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Exemplo 5: Combinação de GROUP BY, CUBE e GROUPING SETS para produzir múltiplos 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));

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Exemplo de uso de GROUPING e GROUPING_ID

Instrução de exemplo:

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);

Resultado retornado:

+------------+------------+------------+------------+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+------------+------------+------------+------------+

Por padrão, as colunas não especificadas no GROUP BY recebem NULL. Use GROUPING para definir os valores desejados. Veja abaixo uma instrução de exemplo baseada na sintaxe do 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), ());

Resultado retornado:

+------------+------------+------------+------------+
| 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          |
+------------+------------+------------+------------+

Exemplo de uso de GROUPING__ID

Exemplo de uso do GROUPING__ID sem parâmetros especificados:

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));

Resultado retornado:

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