Ukázka agregací z github.com/kokes/od

prehled_od.sql

select * from ares.res where ico in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------

select * from ares.or_udaje where ico in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------

select * from ares.or_angos_fo where ico in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------

select nazev_ang, count(*) from ares.or_angos_fo where ico in (64581, 45274649, 60108916) group by 1 order by 2 desc;
------------------------------------------------
------------------------------------------------

select * from ares.or_angos_po where ico in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------

select * from cedr.dotace where idprijemce in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------

select zadavatelnazev, sum(celkovasmluvnicenabezdph) from zakazky.etrziste_vz where dodavatelico in (64581, 45274649, 60108916) group by 1 order by 2 desc;
------------------------------------------------
------------------------------------------------


select count(distinct smlouva) from smlouvy.smlouvy_ucastnici where ico in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------


select * from ares.or_angos_po where ico_ang in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------

select res.* from ares.or_angos_po left join ares.res using(ico)
where ico_ang in (64581, 45274649, 60108916);
------------------------------------------------
------------------------------------------------


select count(distinct smlouva) from smlouvy.smlouvy_ucastnici where ico in (select ico from ares.or_angos_po where ico_ang in (64581, 45274649, 60108916));
------------------------------------------------
------------------------------------------------



select distinct ico, nazev from ares.or_angos_fo left join ares.res using(ico)
where jmeno_prijmeni = 'jaroslav faltýnek';
------------------------------------------------
------------------------------------------------


select count(distinct smlouva) from smlouvy.smlouvy_ucastnici where ico in (select distinct ico from ares.or_angos_fo where jmeno_prijmeni = 'jaroslav faltýnek');
------------------------------------------------
------------------------------------------------



with recursive vsechno as (
	SELECT ico, dod, ddo
	FROM ares.or_angos_fo
	where jmeno_prijmeni = 'andrej babiš'
	
	union
	select ang.ico, vsechno.dod, vsechno.ddo from ares.or_angos_po ang, vsechno
	where ang.ico_ang = vsechno.ico and (tsrange(ang.dod, ang.ddo) && tsrange(vsechno.dod, vsechno.ddo))
)
select distinct ico, res.nazev from vsechno
left join ares.res using(ico)
limit 1000;
------------------------------------------------
------------------------------------------------


with poslanci as (
	SELECT
	    lower(jmeno || ' ' || prijmeni)
	FROM
	    psp.poslanci_osoby os
	    INNER JOIN psp.poslanci_zarazeni za USING (id_osoba)
	    INNER JOIN psp.poslanci_organy org ON org.id_organ = za.id_of
	WHERE
		za.cl_funkce = 0
	    AND org.zkratka = 'PSP8'
)
select * from poslanci;
------------------------------------------------
------------------------------------------------


with poslanci as (
	SELECT
	    lower(jmeno || ' ' || prijmeni) as jmeno_prijmeni
	FROM
	    psp.poslanci_osoby os
	    INNER JOIN psp.poslanci_zarazeni za USING (id_osoba)
	    INNER JOIN psp.poslanci_organy org ON org.id_organ = za.id_of
	WHERE
		za.cl_funkce = 0
	    AND org.zkratka = 'PSP8'
)
select distinct on (ico) res.ico, res.nazev, or_angos_fo.dod, or_angos_fo.ddo, or_angos_fo.nazev_ang, jmeno_prijmeni from ares.or_angos_fo inner join poslanci using(jmeno_prijmeni) left join ares.res using(ico);
------------------------------------------------
------------------------------------------------



with politici as (
	SELECT
	    lower(jmeno || ' ' || prijmeni) as jmeno_prijmeni,
	    narozeni as datum_narozeni, array_agg(distinct nazev_organu_cz) as clenstvi
	FROM
	    psp.poslanci_osoby os
	    INNER JOIN psp.poslanci_zarazeni za USING (id_osoba)
	    INNER JOIN psp.poslanci_organy org ON org.id_organ = za.id_of
	WHERE
		za.cl_funkce = 0
	    AND org.nazev_organu_cz in ('Senát', 'Vláda České republiky', 'Poslanecká sněmovna')
	    AND od_organ >= '2010-01-01'
	    AND narozeni <> '1900-01-01'
	    
	GROUP BY jmeno, prijmeni, narozeni
), relevantni as (
	SELECT ico, jmeno_prijmeni, clenstvi, array_agg(funkce) as funkce, array_agg(row(dod, ddo)) as angazovanost from
	ares.or_angos_fo inner join politici using(jmeno_prijmeni, datum_narozeni)
	where (ddo is null or ddo > '2013-01-01')
	group by ico, jmeno_prijmeni, clenstvi
)


select r.*, res.nazev, res.datum_vzniku, res.datum_zaniku,
po.dod as dod_matka, po.ddo as ddo_matka, po.nazev_ang, po.funkce, po.nazev, po.stat, po.sidlo
from relevantni r
left join ares.res res using(ico)
inner join ares.or_angos_po po using(ico)

where stat <> 'Česká republika'
and (po.ddo is null or po.ddo > '2013-01-01');
------------------------------------------------
------------------------------------------------



with socdem as (
	select
	*,
	jmeno || ' ' || prijmeni as celejmeno
	from volby.komunalni_kandidati
	where ostrana = 7 and okres = 1100 and extract(year from datum) = 2014
), vztahy as (
	select * from socdem
	
	inner join ares.or_angos_fo fo on fo.jmeno_prijmeni = lower(socdem.celejmeno)
	left join ares.res using(ico)
	
	where abs(('2014-10-10'::date - datum_narozeni)/365 - vek) < 2
)
select ico, nazev, count(*) from vztahy group by 1, 2 order by 3 desc;
------------------------------------------------
------------------------------------------------



with topky as (	
	SELECT
	    vstrana, zkratkav8
	FROM
	    volby.komunalni_strany
	WHERE
		extract(year from datum) = 2018 and
	    (zkratkav8 LIKE 'TOP%' or slozeni like '721')
)

select *
from volby.komunalni_kandidati kan
where extract(year from datum) = 2018
and okres::char = '2'
and poradimand > 0
and ((ostrana in (select vstrana from topky)) or (nstrana in (select vstrana from topky)) or (pstrana in (select vstrana from topky)))
limit 1000;
------------------------------------------------
------------------------------------------------