Categorias:: Sintaxe de consulta

GROUP BY ROLLUP¶

GROUP BY ROLLUP é uma extensão da cláusula GROUP BY que produz linhas agregadas em vários níveis de uma hierarquia (além das linhas agrupadas detalhadas). Por exemplo, se você agrupa por cidade e estado, o ROLLUP produz agregações para cada combinação de cidade/estado, cada total do estado e um total geral de todos os estados. Essas agregações são computadas usando as mesmas funções de agregação especificadas na cláusula SELECT.

É possível combinar ROLLUP com outras expressões GROUP BY. Por exemplo, você pode escrever GROUP BY x, ROLLUP(y, z) para agrupar pela coluna x em combinação com agregações de valor acumulado em y e z.

Pode-se pensar no acúmulo como gerando múltiplos conjuntos de resultados, cada um dos quais (após o primeiro) sendo o agregado do conjunto de resultados anterior. Assim, por exemplo, se você possuir uma cadeia de caracteres de lojas de varejo, você poderá querer ver o lucro por:

Cada loja.
Cada cidade (as grandes cidades podem ter várias lojas).
Cada estado.
Tudo (todas as lojas em todos os estados).

Você poderia criar relatórios separados para obter essas informações, mas é mais eficiente digitalizar os dados uma vez.

Se você estiver familiarizado com o conceito de conjuntos de agrupamento, poderá pensar em um agrupamento ROLLUP como equivalente a uma série de conjuntos de agrupamento, mas que é basicamente uma especificação mais curta. Os elementos N de uma especificação ROLLUP correspondem a N+1 GROUPING SETS.

Consulte também¶

GROUPING (função utilitária para identificar qual nível de agrupamento produziu cada linha)
GROUP BY GROUPING SETS
GROUP BY CUBE

Sintaxe¶

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] ROLLUP ( groupItem [ , groupItem [ , ... ] ] )
[ ... ]

Copy

Onde:

groupItem ::= { <column_alias> | <position> | <expr> }

Copy

Parâmetros¶

column_alias: Alias de coluna que aparece na lista SELECT do bloco de consulta.
position: Posição de uma expressão na lista SELECT.
expr: Qualquer expressão em tabelas no escopo atual.

Notas de uso¶

Como a consulta é agregada em níveis cada vez mais altos, ela mostra valores NULL em mais colunas de cada linha. Isso é apropriado. No exemplo a seguir, para a agregação no nível do estado, a coluna city é NULL, porque o valor na coluna profit não corresponde a uma cidade. Da mesma forma, no total final que agrega os dados de todos os estados e as cidades, a receita não é de um estado nem de uma cidade específica, portanto as colunas tanto state quanto city naquela linha são NULL.
A consulta deve listar o “nível mais significativo” em primeiro lugar entre os parênteses após o ROLLUP. Por exemplo, os estados contêm cidades, logo, se você estiver acumulando os dados de estados e cidades, a cláusula deverá ser GROUP BY ROLLUP (state, city).

Se você inverter a ordem dos nomes das colunas, obterá um resultado que provavelmente não é o que você quer. No exemplo abaixo, se você inverter a ordem de city e state na cláusula ROLLUP, o resultado será incorreto, pelo menos em parte, porque tanto Califórnia quanto Porto Rico têm uma cidade chamada San Jos (SJ), e você provavelmente não quer combinar a receita das duas cidades San Jose diferentes, exceto no total final de toda a receita. (Uma maneira alternativa de evitar combinar dados de diferentes cidades com o mesmo nome é criando uma ID única para cada cidade e usar a ID em vez do nome na consulta).
A função utilitária GROUPING pode ajudar a distinguir entre valores NULL que resultam da agregação de valor acumulado e valores NULL reais nos dados. GROUPING retorna 0 para uma linha agrupada em uma coluna especificada, e 1 para uma linha em que a coluna mostra NULL devido à agregação.

Exemplos¶

Comece criando e carregando uma tabela com informações sobre as vendas de uma cadeia de lojas que tem filiais em várias cidades e estados/territórios.

-- Create some tables and insert some rows.
CREATE TABLE products (product_ID INTEGER, wholesale_price REAL);
INSERT INTO products (product_ID, wholesale_price) VALUES 
    (1, 1.00),
    (2, 2.00);

CREATE TABLE sales (product_ID INTEGER, retail_price REAL, 
    quantity INTEGER, city VARCHAR, state VARCHAR);
INSERT INTO sales (product_id, retail_price, quantity, city, state) VALUES 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

Copy

Execute uma consulta de valor acumulado que mostra o lucro por cidade, estado e o total de todos os estados. A consulta produz três «níveis» de agregação:

Cada cidade.
Cada estado.
Todas as receitas combinadas de todos os estados.

A consulta usa ORDER BY state, city NULLS LAST para garantir que o valor acumulado de cada estado esteja logo após todas as cidades daquele estado, e que o valor acumulado final apareça no fim da saída.

SELECT state, city, SUM((s.retail_price - p.wholesale_price) * s.quantity) AS profit 
 FROM products AS p, sales AS s
 WHERE s.product_ID = p.product_ID
 GROUP BY ROLLUP (state, city)
 ORDER BY state, city NULLS LAST
 ;
+-------+---------+--------+
| STATE | CITY    | PROFIT |
|-------+---------+--------|
| CA    | SF      |     13 |
| CA    | SJ      |     26 |
| CA    | NULL    |     39 |
| FL    | Miami   |     48 |
| FL    | Orlando |     96 |
| FL    | NULL    |    144 |
| PR    | SJ      |    192 |
| PR    | NULL    |    192 |
| NULL  | NULL    |    375 |
+-------+---------+--------+

Copy

Algumas linhas de valor acumulado contêm valores NULL. Por exemplo, a última linha da tabela contém um valor NULL para a cidade e um valor NULL para o estado porque os dados são para todas as cidades e estados, não para uma cidade e estado específico.