Tabulações com a Sub-Cláusula CUBE e a Função GROUPING no Oracle

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (2)  (0)

Veja neste artigo a operação CUBE utilizada no Oracle para produzir resultados de tabulações cruzadas de uma forma diferente da realizada pela instrução GROUP BY comum. Adicionalmente, o artigo aborda a função GROUPING e como utilizá-la.

1. Introdução

A sub-cláusula CUBE é utilizada no Oracle em instruções SELECT com GROUP BY. Ela possibilita o cálculo de subtotais e totais para diferentes níveis dos dados agregados.

Este artigo introduz a utilização básica desta operação ainda pouco conhecida pelos desenvolvedores que trabalham com o SGBD Oracle. Para facilitar as explicações, faremos uso da tabela exemplo “T_VENDAS” cujos dados são apresentados na Figura 1. O script para criação da tabela está disponibilizado na Listagem 1. Nesta tabela, cada registro representa um cliente diferente de uma loja de departamentos hipotética. O campo “Id” corresponde à identificação do cliente. Os campos “Sexo”, “Idade” e “UF” descrevem propriedades dos clientes, enquanto o campo e “Valor” indica o valor gasto na compra realizada por cada cliente.

Tabela T_VENDAS

Figura 1: Tabela T_VENDAS

Listagem 1: Script para criar e popular a tabela “T_VENDAS”

//criação da tabela

CREATE TABLE t_vendas (
id NUMBER PRIMARY KEY,
sexo CHAR(1), 
idade CHAR(5),
UF CHAR(2),
VALOR NUMBER (5,2)
)
/  

//inserindo dados na tabela
INSERT INTO t_vendas VALUES(1,'F','<18','SP',115);
INSERT INTO t_vendas VALUES(2,'M','26-30','RJ',120); 
INSERT INTO t_vendas VALUES(3,'F','26-30','RJ',49);
INSERT INTO t_vendas VALUES(4,'F','18-25','MG',235); 
INSERT INTO t_vendas VALUES(5,'F','31-40','SP',101); 
INSERT INTO t_vendas VALUES(6,'M','<18','RJ',200); 
INSERT INTO t_vendas VALUES(7,'F','26-30','MG',55); 
INSERT INTO t_vendas VALUES(8,'F','<18','RJ',5);
INSERT INTO t_vendas VALUES(9,'M','18-25','RJ',68);
INSERT INTO t_vendas VALUES(10,'M','<18','SP',15); 
COMMIT;

2. CUBE

CUBE é uma operação que pode ser entendida como uma espécie de “versão tridimensional” da operação de GROUP BY. Ela produz um maior número de resultados do que o produzido com em um SELECT com GROUP BY comum.

Vamos então comparar os dois tipos de instrução: (i) SELECT com GROUP BY x (ii) SELECT com GROUP BY e CUBE. Na Listagem 2, apresentamos o comando SELECT com GROUP BY que contabiliza a soma dos valores gastos nas compras dentro de grupos formados pelos campos “Sexo” e “UF”. O resultado da execução da instrução é apresentado na Figura 2.

Listagem 2: SELECT com GROUP BY

SELECT SEXO, UF, SUM(VALOR) 
FROM T_VENDAS
GROUP BY SEXO, UF
ORDER BY 1,2 
 do SELECT com GROUP BY (Listagem 2)

Figura 2: do SELECT com GROUP BY (Listagem 2)

Na Listagem 3, realizamos uma pequena modificação nesta instrução através da inserção da sub-cláusula CUBE. Isto força a execução da operação de CUBE que adiciona algumas linhas de subtotais e uma linha de total geral no resultado do comando SELECT. Veja o resultado da execução deste SELECT na Figura 3 e compare com o resultado do SELECT com GROUP BY comum apresentado na Listagem 1.

Listagem 3: SELECT com GROUP BY e CUBE

SELECT SEXO, UF, SUM(VALOR) 
FROM T_VENDAS
GROUP BY CUBE (SEXO, UF)
ORDER BY 1,2 
Resultado do SELECT com GROUP BY e CUBE (Listagem 3)

Figura 3: Resultado do SELECT com GROUP BY e CUBE (Listagem 3)

Em resumo, os efeitos produzidos pelo CUBE foram os seguintes:

  • Foram geradas 11 linhas de resultado, contra apenas 5 do SELECT comum com GROUP BY.
  • Nas linhas 4 e 7, são apresentados subtotais para cada valor do campo “Sexo”. Ou seja: Sexo Feminino gastou R$ 560,00 (linha 4) e Sexo Masculino gastou R$ 403,00 (linha 7).
  • Nas linhas 8, 9 e 10 são apresentados subtotais para cada valor do campo “UF”. Ou seja: os clientes de MG gastaram R$ 290,00 (linha 8), os do RJ gastaram R$ 442,00 (linha 9) e os de SP R$ 231,00 (linha 10).
  • Na linha 11 apresenta-se o total geral gasto por todos os clientes: R$ 963,00. É o mesmo valor que seria obtido pela instrução “SELECT SUM(valor) FROM t_vendas”.

A partir do exemplo, podemos concluir que a função CUBE calcula subtotais para todas as combinações possíveis das colunas selecionadas. Para que o conceito fique mais claro, veja o exemplo apresentado na Listagem 4 (resultados na Figura 4), onde o campo “Idade” também foi introduzido na tabulação

Listagem 4: SELECT com GROUP BY e CUBE – introdução do campo “Idade”

SELECT IDADE, SEXO, UF, SUM(VALOR) 
FROM T_VENDAS
GROUP BY CUBE (IDADE, SEXO, UF)
ORDER BY 1,2, 3 
Resultado do SELECT da Listagem 4

Figura 4: Resultado do SELECT da Listagem 4

A seguir são relacionadas as regras de sintaxe para a sub-cláusula CUBE:

  • A palavra-chave “CUBE” faz parte da cláusula “GROUP BY” e deve ser utilizada após a palavra-chave “GROUP BY”.
  • A palavra-chave “CUBE” deve ser seguida por uma lista de campos (expressão de grupo) indicada entre parêntesis.

3. Função GROUPING

A função GROUPING serve para identificar se uma linha é agregada ou “super-agregada” em instruções SELECT que realizam operações de CUBE ou ROLLUP. A função realiza uma ação muito simples: ela retorna 1 quando identifica uma linha super-agregada para um determinado campo e 0 caso contrário. Veja os exemplos apresentados nas Listagens 5 e 6 para que a explicação fique mais clara.

Listagem 5: SELECT com GROUP BY, CUBE e GROUPING no campo UF

SELECT GROUPING(UF), SEXO, UF, SUM(VALOR) 
FROM T_VENDAS
GROUP BY CUBE (SEXO, UF)
ORDER BY 2,3 
Resultado do SELECT com GROUP BY, CUBE e GROUPING (Listagem 5)

Figura 5: Resultado do SELECT com GROUP BY, CUBE e GROUPING (Listagem 5)

Listagem 6: SELECT com GROUP BY, CUBE e GROUPING nos campos UF e SEXO

SELECT GROUPING(SEXO), GROUPING(UF), SEXO, UF, SUM(VALOR) 
FROM T_VENDAS
GROUP BY CUBE (SEXO, UF)
ORDER BY 3,4 
Resultado do SELECT com GROUP BY, CUBE e GROUPING (Listagem 6)

Figura 6: Resultado do SELECT com GROUP BY, CUBE e GROUPING (Listagem 6)

4. Comentários Finais

Assim encerramos o artigo sobre a operação de CUBE e a função GROUPING do Oracle. Ambas as funcionalidades são utilizadas para tornar os comandos SELECT com GROUP BY mais flexíveis e poderosos, mantendo porém a simplicidade da sintaxe. Veja também o artigo sobre a operação ROLLUP em: http://www.devmedia.com.br/oracle-conhecendo-a-sub-clausula-rollup/26707

Até a próxima!


 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?