Párování komunálek na rejstřík a dál

[kokes]

Můžeme využít toho, že při kandidatuře do komunálek je povinnost bydliště v místě kandidatury (ověřit přesnou podmínku), takže bychom měli být schopni nějak propojit kandidátky (ideálně ty výherní/lidi nahoře) s rejstříkem, a tím pádem s VZ/RS/ReD/atd.

WITH komunal AS (
    SELECT
        *
    FROM
        volby.komunalni_kandidati
        INNER JOIN volby.komunalni_obce USING (datum,
            okres,
            kodzastup)
    WHERE
        datum = '2022-09-23'
        AND nazevzast = 'Praha 5'
        AND mandat
        -- TODO: priblizny vek sedi?
    LIMIT 1000
)
SELECT
    nazev.ico, subjekty.pravni_forma, subjekty.esa2010, nazev.nazev, komunal.*, ang.*
FROM
    justice.angazovane_osoby ang
    inner join justice.nazev using(ico)
    inner join komunal on lower(komunal.jmeno) = lower(ang.jmeno) and lower(komunal.prijmeni) = lower(ang.prijmeni) and komunal.nazevobce = ang.adresa_obec
    INNER JOIN res.subjekty USING(ico)
where nazev.datum_vymaz is null
and pravni_forma not in ('Společenství vlastníků jednotek', 'Příspěvková organizace')
and esa2010 not in ('Veřejné podniky nefinanční')
LIMIT 1000;

CREATE INDEX angazovane_osoby_jmeno_prijmeni_adresa_obec_idx
ON justice.angazovane_osoby
(lower(prijmeni), lower(jmeno), adresa_obec);

[kokes]

Hodně moc pomohl index na angažovaný osoby (duh)

CREATE INDEX angazovane_osoby_jmeno_prijmeni_adresa_obec_idx
ON justice.angazovane_osoby
(lower(prijmeni), lower(jmeno), adresa_obec);

[kokes]

šlo by to napárovat na mv ze smluv

CREATE materialized VIEW smlouvy.mv_ucastnici AS
SELECT
    ico,
    -- redundantni
    max(
        uc.nazev) nazev,
    sum(
        coalesce(hodnota_s_dph, hodnota_bez_dph)) AS smlouvy_hodnota,
    count(
        *) pocet_smluv
FROM
    smlouvy.ucastnici uc
    INNER JOIN smlouvy.smlouvy sm ON sm.id_verze = uc.smlouva
WHERE
    platny_zaznam
GROUP BY
    ico

[kokes]

Po celý republice to pak hodí desítky tisíc záznamů. Celkem bordel, ale jistě to půjde značně zlepšovat. Jde o asi 14k subjektů

WITH komunal AS (
    SELECT
        *
    FROM
        volby.komunalni_kandidati
        INNER JOIN volby.komunalni_obce USING (datum,
            okres,
            kodzastup)
    WHERE
        datum = '2022-09-23'
--      AND nazevzast like 'Praha%'
        AND mandat
        -- TODO: priblizny vek sedi?
), prehled as (
    SELECT
        nazev.ico, sub.pravni_forma, sub.esa2010, nazev.nazev, uc.smlouvy_hodnota, uc.pocet_smluv, komunal.*, ang.*
    FROM
        justice.angazovane_osoby ang
        inner join justice.nazev on nazev.ico = ang.ico
        inner join komunal on lower(komunal.jmeno) = lower(ang.jmeno) and lower(komunal.prijmeni) = lower(ang.prijmeni) and komunal.nazevobce = ang.adresa_obec
        INNER JOIN res.subjekty sub on sub.ico = nazev.ico
        INNER JOIN smlouvy.mv_ucastnici uc on uc.ico = nazev.ico
    where nazev.datum_vymaz is null
    and pravni_forma not in ('Společenství vlastníků jednotek', 'Příspěvková organizace')
    and esa2010 not in ('Veřejné podniky nefinanční', 'Ústřední vládní instituce (kromě fondů sociálního zabezpečení)', 'Místní vládní instituce (kromě fondů sociálního zabezpečení)')
    ORDER by smlouvy_hodnota desc nulls last
)

select * from prehled limit 1000