Upozornění: Tento dokument slouží jako příklad práce s otevřenými daty a nepředstavuje oficiální stanovisko Ministerstva financí.
Úvod¶
V tomto návodu se pomocí podíváme na výdaje státního rozpočtu z aplikace MONITOR pomocí odvětvového třídění a to sice v jazyce SQL.
Nahrání dat do databáze¶
Data musíme mít nejdříve v databázi, kam je dostaneme například pomocí našeho NodeJS skriptu. My jsme si je uložili do PostgreSQL databáze, do schématu remote_monitor.
Práce s daty¶
Odvětvové třídění je dělení výdajů pomocí takzvaných paragrafů. Ty jsou hierarchicky uspořádané do pododdílů, oddílů a skupin. Jak vypadá třídění paragrafů můžete zjistit v tabulce c_paragraf pomocí následujícího příkazu:
SELECT * FROM remote_monitor.c_paragraf LIMIT 10;
Nás může zajímat například pododdíl 331, který slučuje paragrafy týkající se výdajů na kulturu:
SELECT * FROM remote_monitor.c_paragraf WHERE pododdil = '331';
Nás by ale teď zajímalo, kolik která instituce na kulturu dává. Na to máme data v tabulce mis_ris. Její strukturu si můžeme vypsat třeba pomocí schématu information_schema následovně:
SELECT
table_name,
column_name,
data_type
FROM
information_schema.columns
WHERE
table_schema = 'remote_monitor' AND table_name = 'mis_ris';
Z dokumentace Monitoru zjistíme, co znamenají pole zu_rozsch, zu_rozpzm a zu_rozkz a následně si tabulku napojíme na předchozí číselník paragrafů. Dále si můžeme připojit číselník účetních jednotek `c_ucjed`, abychom věděli, které IČO patří které organizaci. Jak vidíme, nejvíce na kulturu dává očekávaně Ministerstvo kultury, pak Ministerstvo školství, ale vcelku překvapivě je na třetí příčce Ministerstvo zemědělství.
SELECT
P.pododdil,
zc_ico,
MAX(U.ucjed_nazev) AS nazev,
SUM(zu_rozsch) AS rozpocet_schvaleny,
SUM(zu_rozpzm) AS rozpocet_upraveny,
SUM(zu_rozkz) AS rozpocet_skutecnost
FROM remote_monitor.mis_ris AS D
-- připojení rozpočtových dat z tabulky mis_ris. zde pozor na pole start_date a end date, ktere urcuji platnost
LEFT JOIN remote_monitor.c_paragraf AS P ON SUBSTR(D.func_area,1,4) = P.paragraf AND MAKE_DATE(D.year,1,1) BETWEEN P.start_date AND P.end_date
-- připojení číselníku účetních jednotek
LEFT JOIN remote_monitor.c_ucjed AS U ON U.ico = D.zc_ico AND MAKE_DATE(D.year,1,1) BETWEEN U.start_date AND U.end_date
-- zde omezíme na pododdíl kultury a konec roku 2012
WHERE P.pododdil = '331' AND year = 2019 AND month = 12
-- sčítáme všechny položky v pododdílu, takže musíme říct, podle kterých údajů se má seskupovat. zde jsou to první dva sloupce
GROUP BY 1,2
Jistě vás napadne, na co asi Ministerstvo zemědělství dává tolik peněz v odvětví kultury. Tak si zobrazíme větší detail, v rozpadu přímo na paragraf. A vidíme, že se jedná o muzea. Ministerstvo zemědělství totiž nejspíš touto částkou financuje zemědělská muzea, které jsou po celé ČR.
SELECT
P.paragraf,
MAX(P.paragraf_nazev) AS paragraf_nazev,
SUM(zu_rozsch) AS rozpocet_schvaleny,
SUM(zu_rozpzm) AS rozpocet_upraveny,
SUM(zu_rozkz) AS rozpocet_skutecnost
FROM remote_monitor.mis_ris AS D
LEFT JOIN remote_monitor.c_paragraf AS P ON SUBSTR(D.func_area,1,4) = P.paragraf AND MAKE_DATE(D.year,1,1) BETWEEN P.start_date AND P.end_date
WHERE P.pododdil = '331' AND year = 2019 AND month = 12 AND zc_ico = '00020478'
GROUP BY 1