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.
