od
od copied to clipboard
kokes
SQL cvičení: podobnost lokálních a celostátních výsledků
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
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)
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