segunda-feira, 28 de dezembro de 2009

Selecionando o que não está lá

Um problema interessante em SQL é o de selecionar o que não está na base. Esse problema se manifesta de diversas maneiras e eu vou analisar uma. Os exemplos abaixo usam a sintaxe do Oracle.

Digamos que eu tenha uma tabela de eventos, criativamente denominada EVENTOS, com as seguintes colunas:

  1. DATA DATE
  2. DESC VARCHAR2(200)

Se eu quiser montar um calendário com o número de eventos por dia, posso escrever uma consulta como esta:

select count(1), data
from eventos
group by data

Essa consulta não vai mostrar os dias em que nada acontece. Isto é, ela não seleciona o que não está na base.

Se eu quiser mostrar todos os dias e um zero nos dias sem eventos, tenho que criar dados para poder selecioná-los. Freqüentemente, essa questão é resolvida com código, mas eu acredito que umas poucas linhas a mais em SQL economizam muitas linhas de Java ou qualquer que seja a linguagem da aplicação.

Em primeiro lugar, preciso de uma consulta que produza os dias que me interessam. A consulta abaixo, por exemplo, produz uma linha para cada dia do mês de fevereiro de 2008.

select rownum as dia
from eventos
where rownum<=to_char(last_day(to_date('022008', 'MMYYYY')), 'DD')

Qualquer tabela serve, desde que tenha tantas linhas quantos forem os dias que for preciso representar.

Agora, basta fazer um join com os dados:

select count(data), dia
from (
 select rownum as dia
 from eventos
 where rownum<=to_char(last_day(to_date('022008', 'MMYYYY')), 'DD')
) dias left outer join (
 select data
 from eventos
 where data between to_date('01022008', 'DDMMYYYY')
       and to_date('28022008', 'DDMMYYYY')
) eventos on to_char(eventos.data, 'DD')=dias.dia
group by dia
order by dia

O segundo where não pode estar no select principal, porque excluiria novamente os dias sem dados.

Para selecionar um período maior, é preciso um pouco de aritmética.

select count(data), to_date('11022008')+dia-1
from (
 select rownum as dia
 from eventos
 where rownum<=to_date('17032008', 'DDMMYYYY')
       -to_date('11022008', 'DDMMYYYY')+1
) dias left outer join (
 select data
 from eventos  
 where data between to_date('11022008', 'DDMMYYYY')
       and to_date('17032008', 'DDMMYYYY')
) eventos on eventos.data-to_date('11022008', 'DDMMYYYY')+1=dias.dia
group by dia
order by dia

O primeiro where aumenta o número de dias para a diferença entre as datas do período. Para juntar as tabelas, é preciso subtrair da data do evento o primeiro dia e assim calcular a quantos dias do início do período cada evento está.

A solução para o problema, portanto, está em criar o que se quer selecionar.

2 comentários:

Marcus Aurelius disse...

Para gerar dados também dá para usar funções que retornam TABLEs PIPELINED, como tu mostrou aqui, né?

http://alquerubim.blogspot.com/2009/12/usando-listas-como-tabelas-em-plsql.html

Fiz um teste agora e parece funcionar bem. Além disso, a função geradora de dados fica bem legível.

forinti disse...

Também pensei nisso, mas não sei se há outros bancos que tenham esse tipo de função. E hoje em dia até o MySQL tem selects aninhados.

(Aviso aos incautos: se queres um banco livre, usa Postgresql!)

Por outro lado, com funções dá para fazer algo bem mais sucinto (e legível).