SQL cvičení: podobnost lokálních a celostátních výsledků

[kokes]

with okresni as (
	SELECT
		okres, psp_nuts.nazev,
		sum(poc_hlasu) hlasu_okres
	FROM
		volby.psp_okrsky_hlasy
		inner join volby.psp_nuts using(datum)
	WHERE
		datum = '2021-10-08' and psp_nuts.num_nuts = okres
	GROUP BY
		1, 2
), republika as (
	SELECT
		kstrana,
		sum(poc_hlasu) hlasu_celkem
	FROM
		volby.psp_okrsky_hlasy
	WHERE
		datum = '2021-10-08'
	GROUP BY
		1
), pomery as (
	SELECT
		okres, nazev,
		kstrana,
		sum(poc_hlasu) hlasu,
		max(okresni.hlasu_okres) hlasu_okres,
		max(republika.hlasu_celkem) as hlasu_celkem,
		max(psp_strany.zkratkak30) as strana,
		round(100*max(republika.hlasu_celkem)::numeric/(select sum(poc_hlasu) from volby.psp_okrsky_hlasy where datum='2017-10-20'), 2) as pomer_cr,
		round(100*sum(poc_hlasu)::numeric / max(okresni.hlasu_okres), 2) as pomer_okres
	FROM
		volby.psp_okrsky_hlasy hl
		join okresni using(okres)
		join volby.psp_strany using(kstrana)
		join republika using(kstrana)
	WHERE
		hl.datum = '2021-10-08' and psp_strany.datum = '2021-10-08'
	GROUP BY
		1,
		2, 3
)

-- select * from pomery

select okres, nazev, sum(pow(pomer_cr-pomer_okres, 2)) from pomery group by 1, 2
order by 3 asc

napric vsema volbama

with okresni as (
	SELECT
		datum, okres, psp_nuts.nazev,
		sum(poc_hlasu) hlasu_okres
	FROM
		volby.psp_okrsky_hlasy
		inner join volby.psp_nuts using(datum)
	WHERE psp_nuts.num_nuts = okres
	GROUP BY
		1, 2, 3
), republika as (
	SELECT
		datum, kstrana,
		sum(poc_hlasu) hlasu_celkem
	FROM
		volby.psp_okrsky_hlasy
	GROUP BY
		1, 2
), pomery as (
	SELECT
		datum, okres, nazev,
		kstrana,
		sum(poc_hlasu) hlasu,
		max(okresni.hlasu_okres) hlasu_okres,
		max(republika.hlasu_celkem) as hlasu_celkem,
		max(psp_strany.zkratkak30) as strana,
		round(100*max(republika.hlasu_celkem)::numeric/(select sum(poc_hlasu) from volby.psp_okrsky_hlasy where datum='2017-10-20'), 2) as pomer_cr,
		round(100*sum(poc_hlasu)::numeric / max(okresni.hlasu_okres), 2) as pomer_okres
	FROM
		volby.psp_okrsky_hlasy hl
		join okresni using(okres, datum)
		join volby.psp_strany using(kstrana, datum)
		join republika using(kstrana, datum)
	GROUP BY
		1,
		2, 3, 4
)

-- select * from pomery

select datum, okres, nazev, sum(pow(pomer_cr-pomer_okres, 2))::numeric(10,2) RSS from pomery group by 1, 2, 3
order by 4 desc

[kokes]

A pro prezidentské volby (první kolo, po obcích)

with hlasy_okrsky as (
	SELECT
		datum, okres, obec, okrsek,
		generate_series(1, array_length(hlasy, 1)) ckand,
		unnest(hlasy) hlasu_kand,
		pl_hl_celk as hlasu_okrsek
	FROM
		volby.prezident_okrsky
	WHERE kolo = 1 -- prvni kolo
), hlasy_obce as (
	SELECT
		datum, okres, obec, ckand,
		sum(hlasu_kand) hlasu_kand,
		sum(hlasu_okrsek) hlasu_obec
	FROM hlasy_okrsky
	GROUP BY 1, 2, 3, 4
), republika as (
	SELECT
		datum, ckand,
		sum(hlasy_k1) hlasu_k1,
		sum(hlasy_k2) hlasu_k2,
		sum(hlc.hlasy_vsichni_k1) hlasy_vsichni_k1,
		sum(hlc.hlasy_vsichni_k2) hlasy_vsichni_k2		
	FROM
		volby.prezident_kandidati
		INNER JOIN (select datum, sum(hlasy_k1) hlasy_vsichni_k1, sum(hlasy_k2) hlasy_vsichni_k2 from volby.prezident_kandidati group by 1) hlc USING(datum)
	GROUP BY
		1, 2
), dohromady as (
	SELECT *
	FROM hlasy_obce
	INNER JOIN republika USING(datum, ckand)
	LEFT JOIN volby.prezident_obce USING(datum, okres, obec)
	WHERE datum = '2023-01-13' -- datum
)

SELECT
datum, okres, obec, nazevobce as nazev_obce,
max(hlasu_obec) hlasu_obec,
sum(pow(hlasu_kand::numeric/hlasu_obec - hlasu_k1::numeric/hlasy_vsichni_k1, 2))::numeric(10, 8) rss
FROM dohromady
GROUP BY 1, 2, 3, 4
ORDER BY 6 asc
LIMIT 10000

případně napříč všema volbama (pro porovnání vývoje obce)

with hlasy_okrsky as (
	SELECT
		datum, okres, obec, okrsek,
		generate_series(1, array_length(hlasy, 1)) ckand,
		unnest(hlasy) hlasu_kand,
		pl_hl_celk as hlasu_okrsek
	FROM
		volby.prezident_okrsky
	WHERE kolo = 1 -- prvni kolo
), hlasy_obce as (
	SELECT
		datum, okres, obec, ckand,
		sum(hlasu_kand) hlasu_kand,
		sum(hlasu_okrsek) hlasu_obec
	FROM hlasy_okrsky
	GROUP BY 1, 2, 3, 4
), republika as (
	SELECT
		datum, ckand,
		sum(hlasy_k1) hlasu_k1,
		sum(hlasy_k2) hlasu_k2,
		sum(hlc.hlasy_vsichni_k1) hlasy_vsichni_k1,
		sum(hlc.hlasy_vsichni_k2) hlasy_vsichni_k2		
	FROM
		volby.prezident_kandidati
		INNER JOIN (select datum, sum(hlasy_k1) hlasy_vsichni_k1, sum(hlasy_k2) hlasy_vsichni_k2 from volby.prezident_kandidati group by 1) hlc USING(datum)
	GROUP BY
		1, 2
), dohromady as (
	SELECT *
	FROM hlasy_obce
	INNER JOIN republika USING(datum, ckand)
	LEFT JOIN volby.prezident_obce USING(datum, okres, obec)
), rss as (
	SELECT
	datum, okres, obec, nazevobce as nazev_obce,
	max(hlasu_obec) hlasu_obec,
	sum(pow(hlasu_kand::numeric/hlasu_obec - hlasu_k1::numeric/hlasy_vsichni_k1, 2))::numeric(10, 8) rss
	FROM dohromady
	GROUP BY 1, 2, 3, 4
)

select
*,
row_number() over(partition by datum order by rss asc) poradi
from rss

tenhle druhej dotaz je pekelně pomalej (asi kvůli tomu row_number nad vším)

[kokes]

a ještě prezidentský po okresech

with hlasy_okrsky as (
	SELECT
		datum, okres, obec, okrsek,
		generate_series(1, array_length(hlasy, 1)) ckand,
		unnest(hlasy) hlasu_kand,
		pl_hl_celk as hlasu_okrsek
	FROM
		volby.prezident_okrsky
	WHERE kolo = 1 -- prvni kolo
), hlasy_obce as (
	SELECT
		datum, okres, obec, ckand,
		sum(hlasu_kand) hlasu_kand,
		sum(hlasu_okrsek) hlasu_obec
	FROM hlasy_okrsky
	GROUP BY 1, 2, 3, 4
), hlasy_okresy as (
	SELECT
		datum, okres, ckand,
		sum(hlasu_kand) hlasu_kand,
		sum(hlasu_okrsek) hlasu_okres
	FROM hlasy_okrsky
	GROUP BY 1, 2, 3
), republika as (
	SELECT
		datum, ckand,
		sum(hlasy_k1) hlasu_k1,
		sum(hlasy_k2) hlasu_k2,
		sum(hlc.hlasy_vsichni_k1) hlasy_vsichni_k1,
		sum(hlc.hlasy_vsichni_k2) hlasy_vsichni_k2		
	FROM
		volby.prezident_kandidati
		INNER JOIN (select datum, sum(hlasy_k1) hlasy_vsichni_k1, sum(hlasy_k2) hlasy_vsichni_k2 from volby.prezident_kandidati group by 1) hlc USING(datum)
	GROUP BY
		1, 2
), dohromady as (
	SELECT republika.*, hlasy_okresy.okres, hlasy_okresy.ckand, hlasy_okresy.hlasu_kand, hlasy_okresy.hlasu_okres, nuts.nazev
	FROM hlasy_okresy
	INNER JOIN republika USING(datum, ckand)
	INNER JOIN volby.prezident_nuts nuts on nuts.datum = hlasy_okresy.datum and nuts.num_nuts = hlasy_okresy.okres
), rss as (
	SELECT
	datum, okres, nazev,
	max(hlasu_okres) hlasu_okres,
	sum(pow(hlasu_kand::numeric/hlasu_okres - hlasu_k1::numeric/hlasy_vsichni_k1, 2))::numeric(10, 8) rss
	FROM dohromady
	GROUP BY 1, 2, 3
	ORDER BY 5 asc
)

select
*,
row_number() over(partition by datum order by rss asc) poradi
from rss