Relatórios no Oracle

Considere uma tabela PESSOAS com estas colunas:

  (NASC DATE, SEXO VARCHAR2(1), NOME VARCHAR2(100))

A coluna NASC guarda a data de nascimento e a coluna SEXO pode conter 'M' ou 'F'. Se eu quiser montar um relatório com o número de aniversários para cada dia do ano, vou ter que fazer uns contorcionismos, mas se eu quiser, ademais, separar o número de aniversários por sexo, a consulta vai ficar ilegível.

Escondido na documentação da Oracle estava um tipo de join que permite resolver esse problema com relativa facilidade: o partitioned outer join. Esse join tem a seguinte forma:

select *
from a 
     left outer join b partition by (cz) on a.ca=b.ca

E isso significa que será executado um cross-join dos valores distintos de CZ com as linhas da tabela A e, a seguir, o resultado desse cross-join será usado no outer-join com a tabela B.

De volta ao problema original, inicio com uma contagem dos aniversários por dia e por sexo:

  select
    to_char(nasc,'MMDD') nasc, 
    sexo, 
    count(1) total
  from pessoas
  group by to_char(nasc,'MMDD'), sexo

Além disso, preciso de uma tabela com todos os dias do ano:

  select to_char(trunc(sysdate, 'YYYY')+rownum-1, 'MMDD') dia
  from all_tables
  where rownum<367

E junto tudo com o partitioned outer join:

select dia, sexo, nvl(total, 0)
from (
  select to_char(trunc(sysdate, 'YYYY')+rownum-1, 'MMDD') dia
  from all_tables
  where rownum<367
) dias
  left outer join (
    select to_char(nasc,'MMDD') nasc, sexo, count(1) total
    from pessoas
    group by to_char(nasc,'MMDD'), sexo) p partition by (sexo) on p.nasc=dia
order by dia, sexo

Essa consulta produz duas linhas para cada dia do ano: uma para o número de aniversários de homens e outro para o número de aniversários de mulheres:

0101	F	2
0101	M	0
0102	F	1
0102	M	3

Armado desta nova ferramenta, decidi resolver um problema ainda maior: enumerar os aniversariantes de cada dia. Comecei com uma lista dos aniversariantes para cada dia:

  select nasc, sexo, substr(SYS_CONNECT_BY_PATH(nome,', '),3) nomes  
  from (
    select 
      nasc, nome, sexo,
      row_number() over (partition by nasc, sexo order by nome) ordem,
      row_number() over (partition by nasc, sexo order by nome desc) medro 
    from (
      select to_char(nasc, 'MMDD') nasc, sexo, nome from pessoas
    ) 
  )
  where medro=1
  start with ordem=1
  connect by prior nasc=nasc and prior sexo=sexo and prior ordem=ordem-1

Juntando com a lista dos dias do ano, descubro o seguinte:

select dia, sexo, nvl(nomes,'-') nomes
from (
  select to_char(trunc(sysdate, 'YYYY')+rownum-1, 'MMDD') dia
  from all_tables
  where rownum<367
) dias
  left outer join (
    select nasc, sexo, substr(SYS_CONNECT_BY_PATH(nome,', '),3) nomes  
    from (
      select 
        nasc, nome, sexo,
        row_number() over (partition by nasc, sexo order by nome) ordem,
        row_number() over (partition by nasc, sexo order by nome desc) medro 
      from (
        select to_char(nasc, 'MMDD') nasc, sexo, nome from pessoas
      ) 
    )
    where medro=1
    start with ordem=1
    connect by prior nasc=nasc and prior sexo=sexo and prior ordem=ordem-1
  ) p partition by (sexo) on p.nasc=dia
order by dia, sexo

Não é das coisas mais simples, mas ele produz bastante para o seu tamanho. O resultado é parecido com:

0101	F	Helena, Maria
0101	M	-
0102	F	Fernanda
0102	M	Henrique, João, Pedro

Por enquanto, apenas o Oracle oferece esse join.