Conjuntos de agrupamentos

A sintaxe GROUPING SETS () oferece uma generalização da capacidade de sintetização e cubos. Qualquer operação rollup() ou cube() pode ser convertida em uma especificação de conjuntos de agrupamentos. A sintaxe é a seguinte:

GROUP BY [<set quantifier>] GROUPING SETS(<grouping-set-list>);

Em que <set quantifier> é DISTINCT ou ALL e padronizado para ALL; <grouping-set-list> é uma lista separada por vírgula de conjuntos de agrupamento, cada um é um <grouping-col-list> colocado entre parênteses, como no exemplo a seguir:

SELECT col1, col2, col3, COUNT(*) FROM <table> GROUP BY GROUPING SETS 
((col1,col2), (col2,col3), (col2), ());

Este exemplo é equivalente ao agrupamento pelos seguintes agrupamentos simultaneamente:

GROUP BY (col1, col2)
GROUP BY (col2, col3)
GROUP BY col2
GROUP BY ()

O resultado da SELECT anterior é equivalente ao resultado de um UNION ALL para as quatro agregações de seleção nos quatro níveis de grupo. Portanto, uma operação rollup() ou cube() pode ser convertida em uma especificação de conjuntos de agrupamentos.

Uma entrada em uma <grouping-set-list> pode ser um <grouping-set-list> e, portanto, também pode ser um cube() ou rollup(). Qualquer grouping-set-list complexo especificado pode ser sempre expandido em um grouping-set-list como no exemplo a seguir:

GROUPING SETS (ROLLUP(col1,col2), CUBE(col1,col2))

Qual é equivalente ao seguinte:

GROUPING SETS ((col1,col2), (col1), (), (col1,col2), (col1), (col2), 
())

Com o quantificador de conjunto distinto a seguir:

DISTINCT GROUPING SETS (ROLLUP(col1,col2), CUBE(col1,col2))

O que é equivalente ao seguinte, mostrando que CUBE(<list>) é um superconjunto de ROLLUP(<list>):

GROUPING SETS ((col1,col2), (col1), (col2), ())

Além disso, o conjunto de agrupamentos (col1, col2) é equivalente a (col2, col1), portanto, a eliminação de duplicatas em um grouping-set-list é feita colocando entradas da lista em um formato canônico que lista as entradas exclusivas na ordem em que elas ocorrem na cláusula de conjuntos de agrupamentos.

Vários conjuntos de agrupamentos no mesmo nível (não aninhados) são tratados como no exemplo a seguir:

GROUP BY GROUPING SETS ((A), (B)), GROUPING SETS ((X, Y), (Z))

Qual é equivalente ao seguinte:

GROUP BY GROUPING SETS ((A, X, Y), (A, Z), (B, X, Y), (B, Z))

Um outro exemplo é o seguinte:

GROUP BY A, GROUPING SETS ((X,Y), (Z))

Qual é equivalente ao seguinte:

GROUP BY GROUPING SETS ((A, X, Y), (A, Z))

O uso da função GROUPING ajuda a distinguir as diferenças. A seguir há um exemplo de uma consulta de sintetização:

STATE  |     CITY      |  SUM
_____________________________
 CA    | Los Angeles   |  600
 CA    | San Diego     |  225
 CA    | San Francisco |  450
 CA    |               | 1275
 MA    | Boston        |  460
 MA    | Springfield   |  345
 MA    |               |  805
       |               | 2080

Como alguns valores da coluna do grupo são NULL em linhas superagregadas, se houvesse uma cidade NULL, poderia ser difícil informar uma linha agregada de uma superagregada. Usando GROUPING (city), o resultado retornaria 0 se a cidade fosse incluída em “group by” e retornaria um 1 se a cidade não fosse incluída em “group by”. O exemplo a seguir mostra o resultado:

STATE  |     CITY      |  SUM  | GROUPING(city) 
_____________________________________________
 CA    | Los Angeles   |  600  |       0       
 CA    | San Diego     |  225  |       0       
 CA    | San Francisco |  450  |       0       
 CA    |               | 1275  |       1       
 MA    | Boston        |  460  |       0       
 MA    | Springfield   |  345  |       0       
 MA    |               |  805  |       1       
       |               | 2080  |       1

Usando GROUPING (state), o resultado retornaria 0 se o estado fosse incluído em “group by” e retornaria 1 se o estado não fosse incluído em “group by”. O exemplo a seguir mostra o resultado:

STATE  |     CITY      |  SUM  | GROUPING(city)  | GROUPING(state) 
________________________________________________________________
 CA    | Los Angeles   |  600  |       0         |       0
 CA    | San Diego     |  225  |       0         |       0 
 CA    | San Francisco |  450  |       0         |       0 
 CA    |               | 1275  |       1         |       0 
 MA    | Boston        |  460  |       0         |       0 
 MA    | Springfield   |  345  |       0         |       0 
 MA    |               |  805  |       1         |       0 
       |               | 2080  |       1         |       1