BD2 Gabarito P1

Prévia do material em texto

<p>create or replace function resultado(p_pesquisa int, p_bairros varchar[], p_cidades varchar[])</p><p>returns table (pergunta int, histograma float[][]) as $$</p><p>declare</p><p>begin</p><p>return query</p><p>with</p><p>t1 as (select b.numero as bairro</p><p>from bairro b inner join cidade c on b.cidade = c.numero</p><p>where (p_bairros is null or array_position(p_bairros,b.nome) is not null)</p><p>and (p_cidades is null or array_position(p_cidades,c.nome) is not null)),</p><p>t2 as (select t22.pesquisa, t22.pergunta, t22.resposta, count(*) as frequencia</p><p>from</p><p>(entrevista t21 natural join t1)</p><p>inner join escolha t22 on t21.numero = t22.entrevista</p><p>where t22.pesquisa = p_pesquisa</p><p>group by t22.pesquisa, t22.pergunta, t22.resposta),</p><p>t3 as (select t33.pesquisa, t33.pergunta, t33.numero, 0::int as frequencia</p><p>from (entrevista t31 natural join t1)</p><p>inner join escolha t32 on t32.entrevista = t31.numero</p><p>right join resposta t33 on t33.pesquisa = t32.pesquisa and t33.pergunta = t32.pergunta</p><p>and t33.numero = t32.resposta</p><p>where t33.pesquisa = p_pesquisa and t32.pesquisa is null),</p><p>t4 as (select * from t2 union select * from t3),</p><p>t5 as (select t4.pesquisa, t4.pergunta, sum(frequencia) as total</p><p>from t4</p><p>group by t4.pesquisa, t4.pergunta)</p><p>-- https://www.postgresql.org/docs/13/sql-expressions.html</p><p>select t4.pergunta,</p><p>array_agg(array[t4.resposta,(coalesce(t4.frequencia*1.0/nullif(t5.total,0),0))::float] order by</p><p>t4.resposta) as histograma</p><p>from t4 natural join t5</p><p>group by t4.pesquisa, t4.pergunta;</p><p>end;</p><p>$$ language plpgsql;</p>