Extensões de agregação
O Amazon Redshift oferece suporte a extensões de agregação para realizar o trabalho de várias operações GROUP BY em uma única instrução.
Os exemplos de extensões de agregação usam a tabela orders, que contém dados de vendas de uma empresa de dispositivos eletrônicos. Você pode criar orders com o seguinte.
CREATE TABLE ORDERS ( ID INT, PRODUCT CHAR(20), CATEGORY CHAR(20), PRE_OWNED CHAR(1), COST DECIMAL ); INSERT INTO ORDERS VALUES (0, 'laptop', 'computers', 'T', 1000), (1, 'smartphone', 'cellphones', 'T', 800), (2, 'smartphone', 'cellphones', 'T', 810), (3, 'laptop', 'computers', 'F', 1050), (4, 'mouse', 'computers', 'F', 50);
GROUPING SETS
Calcula um ou mais conjuntos de agrupamento em uma única instrução. Um conjunto de agrupamento é o conjunto de uma única cláusula GROUP BY, um conjunto de 0 ou mais colunas pelo qual você pode agrupar o conjunto de resultados de uma consulta. GROUP BY GROUPING SETS é equivalente a executar uma consulta UNION ALL em um conjunto de resultados agrupado por colunas diferentes. Por exemplo, GROUP BY GROUPING SETS((a), (b)) é equivalente a GROUP BY a UNION ALL GROUP BY b.
O exemplo a seguir retorna o custo dos produtos da tabela de pedidos agrupados de acordo com as categorias de produtos e o tipo de produto vendido.
SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows)
ROLLUP
Assume uma hierarquia em que as colunas anteriores são consideradas pais das colunas subsequentes. ROLLUP agrupa os dados pelas colunas fornecidas, retornando linhas de subtotal extras representando os totais em todos os níveis de colunas de agrupamento, além das linhas agrupadas. Por exemplo, você pode usar GROUP BY ROLLUP((a), (b)) para retornar um conjunto de resultados agrupado primeiro por a, depois por b, assumindo que b é uma subseção de a. ROLLUP também retorna uma linha com todo o conjunto de resultados sem colunas de agrupamento.
GROUP BY ROLLUP((a), (b)) é equivalente a GROUP BY GROUPING SETS((a,b), (a), ()).
O exemplo a seguir retorna o custo dos produtos da tabela de pedidos agrupados primeiro por categoria, depois por produto, com o produto como uma subdivisão da categoria.
SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows)
CUBE
Agrupa os dados pelas colunas fornecidas, retornando linhas de subtotal extras representando os totais em todos os níveis de colunas de agrupamento, além das linhas agrupadas. CUBE retorna as mesmas linhas que ROLLUP, enquanto inclui linhas de subtotal adicionais para cada combinação de coluna de agrupamento não contemplada por ROLLUP. Por exemplo, você pode usar GROUP BY CUBE((a), (b)) para retornar um conjunto de resultados agrupado primeiro por a, depois por b, assumindo que b é uma subseção de a, depois apenas por b. CUBE também retorna uma linha com todo o conjunto de resultados sem colunas de agrupamento.
GROUP BY CUBE((a), (b)) é equivalente a GROUP BY GROUPING SETS((a,b), (a), (b), ()).
O exemplo a seguir retorna o custo dos produtos da tabela de pedidos agrupados primeiro por categoria, depois por produto, com o produto como uma subdivisão da categoria. Ao contrário do exemplo anterior para ROLLUP, a instrução retorna resultados para cada combinação de coluna de agrupamento.
SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows)
Funções GROUPING/GROUPING_ID
ROLLUP e CUBE adicionam valores NULL ao conjunto de resultados para indicar linhas de subtotal. Por exemplo, GROUP BY ROLLUP((a), (b)) retorna uma ou mais linhas que têm um valor NULL na coluna de agrupamento b para indicar que são subtotais de campos na coluna de agrupamento a. Esses valores NULL servem apenas para satisfazer o formato das tuplas de retorno.
Quando você executa operações GROUP BY com ROLLUP e CUBE em relações que armazenam valores NULL em si, isso pode produzir conjuntos de resultados com linhas que parecem ter colunas de agrupamento idênticas. Voltando ao exemplo anterior, se a coluna de agrupamento b contiver um valor NULL armazenado, GROUP BY ROLLUP((a), (b)) retornará uma linha com um valor NULL na coluna de agrupamento b que não é um subtotal.
Para distinguir entre valores NULL criados por ROLLUP e CUBE e os valores NULL armazenados nas próprias tabelas, você pode usar a função GROUPING ou seu alias GROUPING_ID. GROUPING usa um único conjunto de agrupamento como argumento e, para cada linha no conjunto de resultados, retorna um valor de bit 0 ou 1 correspondente à coluna de agrupamento nessa posição, depois converte esse valor em um inteiro. Se o valor nessa posição for um valor NULL criado por uma extensão de agregação, GROUPING retornará 1. Retornará 0 para todos os outros valores, incluindo valores NULL armazenados.
Por exemplo, GROUPING(category, product) pode retornar os seguintes valores para determinada linha, dependendo dos valores da coluna de agrupamento dessa linha. Neste exemplo, todos os valores NULL na tabela são valores NULL criados por uma extensão de agregação.
| Coluna category | Coluna product | Valor de bit da função GROUPING | Valor decimal |
|---|---|---|---|
| não NULL | não NULL | 00 | 0 |
| não NULL | NULL | 01 | 1 |
| NULL | não NULL | 10 | 2 |
| NULL | NULL | 11 | 3 |
As funções GROUPING aparecem na parte da lista SELECT da consulta no formato a seguir.
SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
O exemplo a seguir é igual ao exemplo anterior para CUBE, mas com a adição de funções GROUPING para seus conjuntos de agrupamento.
SELECT category, product, GROUPING(category) as grouping0, GROUPING(product) as grouping1, GROUPING(category, product) as grouping2, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 3,1,2; category | product | grouping0 | grouping1 | grouping2 | total ----------------------+----------------------+-----------+-----------+-----------+------- cellphones | smartphone | 0 | 0 | 0 | 1610 cellphones | | 0 | 1 | 1 | 1610 computers | laptop | 0 | 0 | 0 | 2050 computers | mouse | 0 | 0 | 0 | 50 computers | | 0 | 1 | 1 | 2100 | laptop | 1 | 0 | 2 | 2050 | mouse | 1 | 0 | 2 | 50 | smartphone | 1 | 0 | 2 | 1610 | | 1 | 1 | 3 | 3710 (9 rows)
ROLLUP e CUBE parciais
Você pode executar operações ROLLUP e CUBE com apenas uma parte dos subtotais.
A sintaxe para operações parciais de ROLLUP e CUBE é a seguinte.
GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])
Aqui, a cláusula GROUP BY cria apenas linhas de subtotal no nível de expr2 e em diante.
Os exemplos a seguir mostram operações parciais de ROLLUP e CUBE na tabela de pedidos, primeiro agrupando os produtos seminovos, depois executando ROLLUP e CUBE nas colunas category e product.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | | 6 | 1100 T | | | 6 | 2610 (9 rows) SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 (13 rows)
Como a coluna pre-owned não está incluída nas operações ROLLUP e CUBE, não há uma linha de total geral que inclua todas as outras linhas.
Agrupamento concatenado
Você pode concatenar várias cláusulas GROUPING SETS/ROLLUP/CUBE para calcular diferentes níveis de subtotal. Os agrupamentos concatenados retornam o produto cartesiano dos conjuntos de agrupamento fornecidos.
A sintaxe para concatenar as cláusulas GROUPING SETS/ROLLUP/CUBE é a seguinte.
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
Considere o exemplo a seguir para ver como um pequeno agrupamento concatenado pode produzir um grande conjunto de resultados finais.
SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ()) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 | cellphones | smartphone | 1 | 1610 | computers | laptop | 1 | 2050 | computers | mouse | 1 | 50 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 | cellphones | | 3 | 1610 | computers | | 3 | 2100 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 | | laptop | 5 | 2050 | | mouse | 5 | 50 | | smartphone | 5 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 | | | 7 | 3710 (22 rows)
Agrupamento aninhado
Você pode usar as operações GROUPING SETS/ROLLUP/CUBE como expr de seu GROUPING SETS para formar um agrupamento aninhado. O subagrupamento dentro da GROUPING SETS aninhada é nivelado.
A sintaxe para agrupamento aninhado é a seguinte.
GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])
Considere o seguinte exemplo.
SELECT category, product, pre_owned, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned)) ORDER BY 4,1,2,3; category | product | pre_owned | group_id | total ----------------------+----------------------+-----------+----------+------- cellphones | | | 3 | 1610 computers | | | 3 | 2100 | laptop | F | 4 | 1050 | laptop | T | 4 | 1000 | mouse | F | 4 | 50 | smartphone | T | 4 | 1610 | laptop | | 5 | 2050 | mouse | | 5 | 50 | smartphone | | 5 | 1610 | | F | 6 | 1100 | | T | 6 | 2610 | | | 7 | 3710 | | | 7 | 3710 (13 rows)
Observe que, como ROLLUP(category) e CUBE(product, pre_owned) contêm o conjunto de agrupamento (), a linha que representa o total geral é duplicada.
Observações de uso
-
A cláusula GROUP BY é compatível com até 64 conjuntos de agrupamento. No caso de ROLLUP e CUBE, ou alguma combinação de GROUPING SETS, ROLLUP e CUBE, essa limitação se aplica ao número implícito de conjuntos de agrupamento. Por exemplo, GROUP BY CUBE((a), (b)) conta como 4 conjuntos de agrupamento, não 2.
-
Não é possível usar constantes como colunas de agrupamento ao usar extensões de agregação.
-
Não é possível fazer um conjunto de agrupamento que contém colunas duplicadas.
