Digamos que eu tenha uma tabela de eventos, criativamente denominada EVENTOS, com as seguintes colunas:
- DATA DATE
- 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.
Para gerar dados também dá para usar funções que retornam TABLEs PIPELINED, como tu mostrou aqui, né?
ResponderExcluirhttp://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.
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.
ResponderExcluir(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).