DevMedia
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
Este é um post disponível para assinantes MVP
###
SQL Magazine 95 - Índice

Linguagem SQL: geração de relatórios - Revista SQL Magazine 95 - Parte 2

Uso de linguagem SQL para criação de relatórios, desde recursos básicos até os mais avançados. Este é o segundo artigo de uma série e neste texto apresento técnicas de uso de visões e consultas aninhadas, que dão às consultas um grande poder de

[fechar]

Você não gostou da qualidade deste conteúdo?

(opcional) Você poderia comentar o que não lhe agradou?

Confirmo meu voto negativo

Este é o segundo artigo de uma série que pretende apresentar recursos da linguagem SQL que ajudam na criação de relatórios. Repetindo o que escrevi no primeiro artigo:

 “É consenso entre os especialistas que SQL não é a melhor linguagem para criação de consultas complexas. Apesar de esta ser uma verdade inegável, é muito comum nos depararmos com situações em que ele, o SQL, é a única ferramenta que temos à mão para criação de um relatório. Isso ocorre muitas vezes por questão de custos, já que nem sempre é economicamente viável optar pela compra de uma solução especializada. Este cenário é muito comum no dia-a-dia das empresas e, no fim das contas, somos forçados a usá-lo.

Por esta razão, eu considero importante que todo profissional de banco de dados conheça os principais recursos do SQL para atender as solicitações dos seus usuários”.

Neste sentido, é importante relembrarmos uma definição básica ao se trabalhar com consultas SQL que é a definição de expressão SQL. A definição que temos para o termo expressão é bastante simples: uma expressão retorna um valor. Os tipos utilizados em uma expressão variam bastante, cobrindo diferentes tipos de dados como string, numeric e boolean. Na verdade, quase tudo que inserirmos depois do SELECT ou FROM em um comando SQL pode ser considerado uma expressão. Caso você queria encontrar um item ou grupo de itens em particular em seu banco de dados, você precisará fazer uso de uma ou mais condições em suas queries. Podemos definir condições em consultas SQL utilizando a cláusula where.

Vimos no artigo anterior vários recursos úteis da linguagem SQL que nos ajudam na criação de relatórios. Neste texto atual, apresentaremos recursos da linguagem SQL para geração de objetos especiais para manipulação dos dados, como visões e consultas aninhadas. O leitor com alguma familiaridade com SQL já deve ter visto tais recursos, por isso acrescentaremos aqui um estudo de performance para auxiliá-lo a explorar estas técnicas de forma otimizada.

Este material foi extraído de uma palestra sobre recursos oferecidos pelo DB2, porém iremos priorizar nestes artigos os recursos e as sintaxes que sejam padrão ANSI SQL, ou seja, que estão ou deveriam estar disponíveis em qualquer sistema gerenciador de bancos de dados (SGBD).

Neste artigo, vamos falar do uso dos seguintes recursos SQL:

·         Visões;

·         Consultas aninhadas na cláusula WHERE;

·         Consultas aninhadas na cláusula FROM;

·         Consultas aninhadas na cláusula SELECT.

 

Na teoria de banco de dados, uma visão (view) consiste de uma consulta armazenada acessível como uma tabela virtual em um banco de dados relacional ou um conjunto de documentos em um banco de dados orientado a documentos, composto pelo conjunto de resultados de uma consulta. Ao contrário de tabelas comuns (tabelas base) em um banco de dados relacional, uma visão não faz parte do esquema físico: é uma tabela virtual dinâmica computada a partir de dados no banco de dados. Alterar os dados em uma tabela altera os dados mostrados nas invocações subsequentes da visão. Em alguns bancos de dados NoSQL as visões são a única forma de consulta a dados.

Visões podem oferecer vantagens sobre tabelas nos seguintes aspectos:

·         Visões podem representar apenas um subconjunto dos dados contidos em uma tabela;

·         Visões podem juntar e simplificar várias tabelas em uma tabela virtual única;

·         Visões podem funcionar como tabelas agregadas, através dos mecanismos de agregação do banco de dados (sum, average, etc.) e apresenta os resultados calculados como parte dos dados;

·         Visões podem esconder a complexidade dos dados, por exemplo, uma visão poderia se chamar Vendas2000 ou Vendas2001, particionando transparentemente a tabela básica real;

·         Visões ocupam muito pouco espaço de armazenamento, o banco de dados contém apenas a definição de uma visão e não uma cópia de todos os dados que ele apresenta;

·         Dependendo do SGBD utilizado, visões podem fornecer a segurança extra;

·         Visões podem limitar o grau de exposição de uma ou mais tabelas para o mundo exterior.

 

Assim como funções (function, na programação) podem fornecer abstração, usuários do banco de dados podem criar abstrações usando visões. Em outro paralelo com as funções, os usuários do banco de dados podem manipular visões aninhadas, assim, uma visão pode agregar dados de outras visões. Sem o uso de visões a normalização de bases de dados além da segunda forma normal se tornaria muito mais difícil. Visões podem tornar mais fácil a criação de junções sem perdas de decomposição.

Assim como as linhas em uma tabela base não têm qualquer ordem definida, as linhas disponíveis através de uma visão não aparecem com qualquer classificação padrão.

A visão é uma tabela relacional e o modelo relacional define uma tabela como um conjunto de linhas. Uma vez que as tabelas não são ordenadas - por definição - as linhas de uma visão também não são ordenadas. Portanto, uma cláusula ORDER BY na definição de exibição não tem sentido.

O padrão SQL ANSI 2003 não permite que uma cláusula ORDER BY em uma consulta SQL na instrução CREATE VIEW, assim como não é permitida na instrução CREATE TABLE. No entanto, dados ordenados podem ser obtidos a partir de uma visão, da mesma forma como qualquer outra tabela - como parte de uma instrução de consulta.

No entanto, alguns SGBDs (como Oracle e SQL Server) permitem uma visão a ser criada com uma cláusula ORDER BY em uma subquery, afetando como os dados são exibidos.

Em uma consulta SQL ao banco de dados, uma subconsulta (consulta aninhada dentro de outra consulta) é uma consulta que utiliza os valores da consulta externa em sua cláusula WHERE. A subconsulta é avaliada uma vez para cada linha processada pela consulta externa.

Você pode usar uma subconsulta para os seguintes fins:

·         Definir um conjunto de linhas que precisam ser inseridos em uma tabela;

·         Definir um conjunto de resultados que será usado para criação de uma visão;

·         Definir um ou mais valores de uma instrução de atualização (UPDATE);

·         Fornecimento de valores para as cláusulas WHERE, HAVING e START WITH para instruções SELECT, UPDATE e DELETE.

Base de dados e considerações

Continuaremos a usar a mesma base de dados apresentada no primeiro artigo. Este banco de dados contém dados fictícios de demanda de produtos farmacêuticos. A Figura 1 mostra o diagrama desta base.

Visões

As visões (do inglês views) são talvez os objetos mais comuns nos bancos de dados, mais até do que as próprias tabelas. De certo modo, as visões são tabelas virtuais, ou seja, elas se parecem com tabelas, mas as visões buscam dados que estão fisicamente armazenados em uma ou mais tabelas. Para o usuário, as visões se parecem tanto com as tabelas que fica difícil dizer com qual objeto se está trabalhando.

Já para os administradores de bancos de dados (DBAs), as visões são um excelente recurso para gerenciamento da segurança dos dados. Afinal, elas são objetos que funcionam somente para leitura, ou em outras palavras, não se consegue alterar dados através de visões (existem tipos especiais de visões que são exceção, mas não serão discutidas aqui). Além disso, o administrador pode criar visões de modo a restringir o acesso apenas a determinados campos da tabela, impedindo por exemplo a leitura de campos especiais. Portanto, se você não é o DBA do banco de dados com o qual costuma trabalhar, provavelmente você já acessou visões pensando que lia tabelas.

As visões funcionam como declarações de SELECT precompiladas, que são executadas quando o objeto correspondente é solicitado. Virtualmente, qualquer SELECT pode ser salvo como visão. Basta para isso usar a sintaxe “CREATE VIEW nome AS”, seguida da declaração em questão.

Uma exceção importante é que as visões não aceitam a cláusula ORDER BY. Até existem recursos para se burlar esta limitação, mas aí entramos no terreno das famosas “gambiarras”. O melhor mesmo é respeitar o limite, a menos que seja realmente necessário.

Outra situação muito comum de uso de visões é a de guardar de uma maneira simples consultas e/ou subconsultas que são executadas com muita frequência. Esta abordagem é interessante porque pode simplificar bastante o trabalho dos desenvolvedores, reduzindo a complexidade das declarações SQL.

Vamos ver um exemplo. Digamos que uma consulta muito frequente no nosso banco de dados seja a identificação do ranking de produtos mais vendidos no mês anterior. Esta consulta é usada para uma série de relatórios e, para não termos que reescrevê-la em todos os relatórios, vamos tratá-la como uma visão.

Primeiramente vamos definir esta visão. Precisamos criar uma consulta de agregação que calcule a demanda por produto ocorrida no mês passado. Para isso, vamos precisar das tabelas de demanda e de produto (veja na Figura 1). Usaremos a função de agregação SUM() para totalizar a demanda e agruparemos os dados por produtos. Para tornar a visão mais útil, é importante incluir o código dos produtos, uma vez que todas as junções com outras tabelas e/ou visões serão feitas através destes códigos.

Como as visões não consideram ordenação de dados (e nem devem fazê-lo), vamos esquecer a questão do ranking e apresentar apenas a demanda de cada produto.

Para filtrar as datas, precisamos definir algo que seja genérico, do contrário vamos ter que criar uma nova visão a cada mudança do mês. Existem muitas maneiras de se criar este filtro e cada SGBD traz seus próprios registros e funções para trabalhar com datas.  (Veja informações adicionais a este respeito na seção LEITURA RECOMANDADA).

Eu preferi construir este filtro usando recursos o mais próximos possíveis do padrão ANSI SQL, então usamos funções escalares como MONTH(), YEAR(),  RIGHT() e a função CAST(), que é usada para conversão de tipos de dados. Usaremos também o registro especial CURRENT_TIMESTAMP, que retorna a hora atual do sistema com precisão de (pelo menos) milissegundos, além de estar implementado numa grande variedade de SGDBs relacionais, como DB2, ORACLE, SQL SERVER, FIREBIRD e POSTGRES.

A ideia é selecionar datas que estejam entre o dia 1º e o último dia do mês anterior. Como não é muito simples identificar o último dia de cada mês, fica mais fácil dizer que a data desejada tem que ser maior ou igual ao 1º dia do mês anterior e menor do que o 1º dia do mês atual. Algo do tipo "Data >= 01/MesAnterior AND Data < 01/MesAtual".

Usando as funções e registros de sistema citados acima, o filtro de datas ficará parecido com o apresentado na Listagem 1.

 

Listagem 1. Parametrização do filtro de datas do mês anterior.

Data >= CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR) -- ano

+RIGHT('0'+CAST((MONTH(CURRENT_TIMESTAMP)-1) AS VARCHAR),2) -- mes

+ '01' -- dia

AND

Data < CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR) -- ano

+ RIGHT('0'+CAST((MONTH(CURRENT_TIMESTAMP)) AS VARCHAR),2) -- mes + '01' -- dia

 

O leitor mais atento vai observar que a lógica da Listagem 1 tem uma falha: não funciona durante o mês de Janeiro. Na realidade, este problema com datas é mais fácil de se resolver usando funções definidas pelo usuário (ou UDF), pois vai facilitar inclusive o entendimento da declaração SQL, já que esta lógica vai estar encapsulada numa função. Porém as UDFs serão estudadas apenas no próximo artigo da série. Por isso voltaremos a esta questão quando tratarmos do assunto. Por hora, vamos considerar que esta solução é adequada.

Colocando tudo para funcionar e definindo a consulta como uma visão, temos o SQL apresentado na Listagem 2.

 

Listagem 2. Criação da visão com demanda por produto no mês anterior.

CREATE VIEW vwDemandaProdutoMesPassado AS

SELECT P.pkProduto, P.Produto, SUM(T.ValorReal) AS Demanda

  FROM tblDemanda T

  INNER JOIN tblProduto P ON T.fkProduto = P.pkProduto

  WHERE T.Data >= CAST(YEAR(CURRENT_TIMESTAMP) AS VARCHAR)

     + RIGHT('0'+CAST(MONTH(CURRENT_TIMESTAMP) - 1 AS VARCHAR), 2)

     + '01'

"

A exibição deste artigo foi interrompida

Este post está disponível para assinantes MVP.



É DBA de SQL Server & DB2. Profissional certificado IBM IT Specialist Level 2 - Expert, publica o blog wCrivelini e o podcast Databasecast. Autor convidado de diversas comunidades técnicas no Brasil e no exterior, como SQL Server [...]

O que você achou deste post?
Serviços

Mais posts