Categorias:

Sintaxe de consulta

GROUP BY ROLLUP

GROUP BY ROLLUP é uma extensão da cláusula GROUP BY que produz linhas de subtotal (além das linhas agrupadas). Linhas de subtotal são linhas que agregam ainda mais, cujos valores são derivados do cálculo das mesmas funções agregadas que foram usadas para produzir as linhas agrupadas.

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 (GROUP BY GROUPING SETS), você poderá pensar em um agrupamento ROLLUP como equivalente a uma série de conjuntos de agrupamento, e que é essencialmente uma especificação mais curta. Os elementos N de uma especificação ROLLUP correspondem a N+1 GROUPING SETS.

Consulte também:

GROUP BY GROUPING SETS

Sintaxe

SELECT ...
FROM ...
[ ... ]
GROUP BY ROLLUP ( groupRollup [ , groupRollup [ , ... ] ] )
[ ... ]
Copy

Onde:

groupRollup ::= { <column_alias> | <position> | <expr> }
Copy
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. Por exemplo, no exemplo abaixo, para o agregado em nível estadual, a coluna da cidade é NULL; isto porque o valor na coluna de lucro não corresponde a uma cidade. Da mesma forma, no total final, que agrega dados de todos os estados e todas as cidades, a receita não é de um estado específico ou de uma cidade específica, portanto tanto as colunas do estado como as da cidade 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, portanto, se você estiver unindo dados entre estados e cidades, a cláusula deverá ser

    ...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ê invertesse a ordem de cidade e estado na cláusula ROLLUP, o resultado seria incorreto, pelo menos em parte, porque tanto a Califórnia quanto Porto Rico têm uma cidade chamada San José (“SJ”), e você provavelmente não gostaria de combinar a receita dos dois San Joses 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).

Exemplos

Comece criando e carregando uma tabela com informações sobre as vendas de uma cadeia de lojas que possui filiais em diferentes 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

Executar uma consulta de acúmulo que mostra lucro por cidade, estado e total em todos os estados.

O exemplo abaixo mostra uma consulta que tem três “níveis”:

  • Cada cidade.

  • Cada estado.

  • Todas as receitas combinadas.

Este exemplo usa ORDER BY state, city NULLS LAST para garantir que o rollup de cada estado venha imediatamente 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.

Linguagem: Português