Artigos Banco de Dados Cálculos avançados em SQL - Revista SQL Magazine 104

De que se trata o artigo

A SQL se refere a uma linguagem de pesquisa declarativa para banco de dados relacional. Seu uso vai desde a criação até a manipulação de uma base de dados. Neste artigo abordaremos o uso da linguagem SQL para cálculos avançados. Para isso, descreveremos o uso da cláusula OVER e seus complementos.

Em que situação o tema é útil

O artigo é útil para todos profissionais que trabalham com consultas em bancos de dados relacionais. O tópico discutido neste artigo ajudará o leitor a criar consultas e relatórios sofisticados incluindo recursos poderosos de sumarização de dados e estatísticas. O artigo mostra também o impacto destas funcionalidades na performance das consultas.

Resumo DevMan

A linguagem SQL sempre se mostrou muito poderosa, mas nem sempre ideal para determinadas situações como, por exemplo, a criação de relatórios muito complexos. No entanto, os SGBDs mais conceituados e, consequentemente, o padrão ANSI SQL procuram adequar a linguagem SQL para auxiliar nesta tarefa de criação de relatórios mais complexos, até mesmo porque em muitas situações a linguagem SQL é a unica opção do desenvolvedor.

Uma destas soluções é a cláusula OVER, presente na maioria dos SGBDs e também parte do padrão ANSI SQL. Através da cláusula OVER teremos acesso a cálculos cumulativos de forma bem poderosa e diferente do que tradicionalmente é feito. As funções de agregação tradicionais atuam sobre uma massa de dados extraída de base de dados enquanto a cláusula OVER atua sobre o resultado obtido pelas funções de agregação. Em outras palavras, a cláusula OVER será executada após todas as funções de agregação, filtros de dados e filtros de dados agrupados já tenham sido executados.

Pode-se ainda utilizar a cláusula OVER para ranquear os resultados de uma pesquisa, através da função especial RANK(), ou ainda informar a quantidade de registros existentes através da função especial ROW_NUMBER().

Recentemente publiquei uma série de cinco artigos descrevendo o uso de recursos da linguagem SQL para geração de relatórios. Porém, não houve tempo e nem espaço para descrever tudo o que há de interessante na linguagem SQL. De certo modo, podemos considerar este artigo como sendo parte daquela série publicada nas edições 94, 95, 96, 97 e 98 da SQL Magazine.

Apresentaremos agora um recurso pouco conhecido e muito poderoso que existe na maioria dos SGBDs: a cláusula OVER, que oferece uma série de funcionalidades que facilitam a execução de cálculos complexos. O diferencial é que esta cláusula ajuda em cálculos envolvendo os registros de uma listagem, de modo semelhante aos cálculos que fazemos em uma planilha eletrônica.

A cláusula OVER faz parte do padrão ANSI SQL e, portanto, a maioria dos SGBDs suporta este recurso. Sua sintaxe é praticamente idêntica no DB2 (que usaremos ao escrever este artigo) e também no Oracle. No caso do SQL Server, até recentemente havia um suporte limitado a esta cláusula, mas a versão 2012 promete suportá-la com todos os recursos padrão ANSI SQL, como acontece com DB2/ORACLE já há bastante tempo.

Voltamos a lembrar que é consenso entre os especialistas que a linguagem SQL não é a melhor opção para geração de relatórios sofisticados. Porém, é muito comum que esta seja a única opção que temos em mãos. Por esta razão, devemos conhecer alguns truques da linguagem a fim de resolver os problemas do dia-a-dia.

A seguir, apresentamos exemplos de sintaxes, cálculos especiais usando os recursos desta ilustre desconhecida cláusula OVER. Nos exemplos apresentados aqui, usaremos novamente um banco de dados fictício que traz informações de demanda de produtos farmacêuticos. Esta base tem cerca de 2 milhões de transações e foi criada no DB2 v10.1 LUW. A Figura 1 mostra o diagrama desta base.

Figura 1. Modelo de dados da base de exemplo.

Definição

A cláusula OVER fornece cálculo cumulativos baseados nas linhas de um relatório e seu comportamento é muito diferente de tudo que estamos acostumados a usar. Entenda esta diferença a partir de agora.

As funções de agregação usadas com a cláusula GROUP BY, por exemplo, fazem cálculos sobre os valores de uma massa de dados extraída da base de dados. Assim, podemos calcular somas, médias e contagens sobre toda a massa de dados que atende aos critérios definidos na cláusula WHERE e depois agrupamos os resultados conforme os critérios que escrevemos na cláusula GROUP BY. Depois disso, teremos finalmente uma listagem dos resultados.

A cláusula OVER, por sua vez, só é executada depois que esta listagem estiver pronta. Se sua declaração SQL inclui filtros de dados (WHERE), agrupamentos (GROUP BY) e filtros de dados agrupados (HAVING), tudo isso vai ser calculado antes de rodar o OVER. E ao usarmos esta cláusula, estamos executando um cálculo especial que define uma nova coluna nesta listagem. A propósito, podemos definir quantas novas colunas quisermos, mas cada uma delas usará um cálculo com OVER.

Um caso clássico de uso da cláusula OVER é a definição de um ranking, ou seja, identificação da posição de um registro dentro de uma listagem.

A cláusula OVER é usada de duas maneiras principais:

• Junto com uma cláusula ORDER BY: para se definir um cálculo baseado na ordenação de um critério considerando toda a listagem;

• Junto com cláusulas PARTITION BY e OVER BY: para se definir um cálculo baseado na ordenação de um critério particionando o resultado conforme valores de uma ou mais colunas da listagem.

É muito mais fácil entender estas descrições através de exemplos práticos, que são apresentados a seguir.

Usando OVER + ORDER BY

Para ilustrar este caso, vamos considerar um relatório que exiba resultados de demanda para os 10 produtos mais vendidos no mês de Outubro/2012. O relatório terá cinco colunas:

• o nome do produto (Produto);

• a demanda do produto no mês passado (Demanda);

• a demanda de todos os produtos do mês passado (DemandaTotal);

• a participação de cada produto na demanda total (Particip);

• a participação acumulada dos principais produtos (AcumParticip);

O campo participação é calculado como a razão entre demanda do produto e demanda total (que usa a fórmula Particip = 100.0 * Demanda/DemandaTotal).

O problema é calcular a participação acumulada, que precisa somar a participação do produto considerando a participação de todos os produtos que o precedem na listagem.

Antigamente este tipo de cálculo era feito usando consultas aninhadas na cláusula SELECT. Joe Celko (veja seção Referências) mostra exemplos deste tipo em seu livro. A partir da definição do padrão ANSI SQL 99, foi recomendada a cláusula OVER para este tipo de cálculo e desde então os principais SGBDs do mercado vêm incoporando estas funcionalidades.

Para fins ilustrativos, mostraremos a solução do relatório proposto usando a abordagem da consulta aninhada. Mas existe um complicador neste relatório: para calcular a tal participação acumulada, precisamos antes ter os cálculos de participação individual.

...

Quer ler esse conteúdo completo? Tenha acesso completo