| Version 3 (modified by , 13 days ago) ( diff ) |
|---|
Креирање и пополнување на базата
DDL скрипта за бришење и повторно креирање на табелите
DML (seed) скрипта за пополнување на податоците
Погледи (Views)
Фаза 2б — Погледи (Views)
vw_election_results
Го прикажува збирниот изборен резултат по политички ентитет за секој избор — вклучувајќи вкупен број гласови, процент, и дали ентитетот го поминал 5% цензусот. Ја имплементира бизнис логиката за јавно објавување на официјални изборни резултати и овозможува брза споредба на перформансите на партиите и коалициите по избор.
CREATE OR REPLACE VIEW public.vw_election_results AS
WITH election_totals AS (
SELECT vote_result.election_id,
sum(vote_result.votes) AS total_valid_votes
FROM vote_result
GROUP BY vote_result.election_id
),
entity_votes AS (
SELECT vote_result.election_id,
vote_result.entity_id,
sum(vote_result.votes) AS total_votes
FROM vote_result
WHERE vote_result.candidate_id IS NULL
GROUP BY vote_result.election_id, vote_result.entity_id
),
calc AS (
SELECT ev.election_id,
ev.entity_id,
ev.total_votes,
el.total_valid_votes,
ev.total_votes::numeric * 100.0 / NULLIF(el.total_valid_votes, 0)::numeric AS vote_pct
FROM entity_votes ev
JOIN election_totals el USING (election_id)
)
SELECT e.election_id,
e.name AS election_name,
e.election_date,
et.type_name AS election_type,
pe.entity_id,
pe.name AS entity_name,
pe.type AS entity_type,
c.total_votes,
c.total_valid_votes,
round(c.vote_pct, 2) AS vote_share_pct,
c.vote_pct >= 5.0 AS passed_threshold,
wm.method_name AS winner_method
FROM calc c
JOIN election e USING (election_id)
JOIN election_type et ON et.election_type_id = e.election_type_id
JOIN winner_method wm ON wm.method_id = e.winner_method_id
JOIN political_entity pe USING (entity_id);
vw_dhondt_seat_allocation
Ја пресметува распределбата на парламентарни мандати по изборна единица користејќи D'Hondt метод, со вграден 5% праг и поддршка за коалиции. Ја имплементира законски задолжителната пропорционална распределба на мандати за парламентарни избори и овозможува транспарентен увид во тоа колку мандати освоил секој ентитет по регион.
CREATE OR REPLACE VIEW public.vw_dhondt_seat_allocation AS
WITH district_votes AS (
SELECT ed.district_id, ed.election_id, ed.region_id, ed.seats_available,
vr.entity_id, sum(vr.votes) AS district_votes
FROM electoral_district ed
JOIN election e_1 ON e_1.election_id = ed.election_id
JOIN vote_result vr ON vr.election_id = ed.election_id
JOIN polling_station ps ON ps.station_id = vr.station_id
JOIN region r2 ON r2.region_id = ps.municipality_id
WHERE e_1.winner_method_id = 8
AND vr.candidate_id IS NULL
AND (r2.region_id = ed.region_id OR r2.parent_region_id = ed.region_id)
GROUP BY ed.district_id, ed.election_id, ed.region_id, ed.seats_available, vr.entity_id
),
district_totals AS (
SELECT district_id, sum(district_votes) AS total_district_votes FROM district_votes GROUP BY district_id
),
eligible AS (
SELECT dv.*, dt.total_district_votes,
dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric) AS vote_pct
FROM district_votes dv JOIN district_totals dt USING (district_id)
WHERE (dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric)) >= 5.0
),
dhondt AS (
SELECT e_1.*, d.divisor,
e_1.district_votes::numeric / d.divisor::numeric AS quotient
FROM eligible e_1 CROSS JOIN LATERAL generate_series(1, e_1.seats_available) d(divisor)
),
ranked AS (
SELECT *, rank() OVER (PARTITION BY district_id ORDER BY quotient DESC) AS rn FROM dhondt
),
seats AS (
SELECT district_id, election_id, region_id, seats_available, entity_id,
district_votes, total_district_votes, vote_pct,
count(*) FILTER (WHERE rn <= seats_available) AS seats_won
FROM ranked
GROUP BY district_id, election_id, region_id, seats_available, entity_id,
district_votes, total_district_votes, vote_pct
)
SELECT s.election_id, e.name AS election_name, e.election_date,
s.district_id, r.name AS region_name, s.seats_available,
s.entity_id, pe.name AS entity_name, pe.type AS entity_type,
s.district_votes AS total_votes, s.total_district_votes,
round(s.vote_pct, 2) AS vote_share_pct, s.seats_won
FROM seats s
JOIN election e USING (election_id)
JOIN region r ON r.region_id = s.region_id
JOIN political_entity pe USING (entity_id)
ORDER BY s.election_id, s.district_id, s.seats_won DESC;
vw_invalid_ballot_analysis
Прикажува статистика за невалидни гласачки ливчиња по избор и општина — вкупен број ливчиња, број невалидни и процент. Ја имплементира логиката за надзор на квалитетот на изборниот процес, овозможувајќи лесна идентификација на општини со висок процент невалидни ливчиња кои бараат дополнителна анализа.
CREATE OR REPLACE VIEW public.vw_invalid_ballot_analysis AS
SELECT e.election_id,
e.name AS election_name,
r.region_id,
r.name AS region_name,
count(*) AS total_ballots,
count(*) FILTER (WHERE NOT b.is_valid) AS invalid_ballots,
round(count(*) FILTER (WHERE NOT b.is_valid)::numeric * 100.0
/ NULLIF(count(*), 0)::numeric, 2) AS invalid_pct
FROM ballot b
JOIN election e ON e.election_id = b.election_id
JOIN polling_station ps ON ps.station_id = b.station_id
JOIN region r ON r.region_id = ps.municipality_id
GROUP BY e.election_id, e.name, r.region_id, r.name;
vw_local_election_winners
Го прикажува победникот на локалните избори (тип 3) по општина — партијата со најмногу гласови во секоја општина. Ја имплементира бизнис логиката за прогласување победник на локалните избори по принцип на релативно мнозинство, и овозможува брз преглед на политичката карта по општини.
CREATE OR REPLACE VIEW public.vw_local_election_winners AS
WITH totalvotesperparty AS (
SELECT vr.election_id, e.name AS election_name, r.name AS municipality_name,
pe.name AS party_name, sum(vr.votes) AS total_sum_votes
FROM vote_result vr
JOIN election e ON vr.election_id = e.election_id
JOIN political_entity pe ON vr.entity_id = pe.entity_id
JOIN polling_station ps ON vr.station_id = ps.station_id
JOIN region r ON ps.municipality_id = r.region_id
WHERE e.election_type_id = 3
GROUP BY vr.election_id, e.name, r.name, pe.name
),
rankedwinners AS (
SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_sum_votes DESC) AS rnk
FROM totalvotesperparty
)
SELECT election_name, municipality_name,
party_name AS winner_name,
total_sum_votes AS votes_received,
'Winner'::text AS status
FROM rankedwinners
WHERE rnk = 1;
vw_parliamentary_municipality_winners
Го прикажува доминантниот политички ентитет по општина за парламентарни избори (тип 2), врз основа на агрегирани гласови по гласачко место. Ја имплементира логиката за визуелизација на изборната карта на парламентарни избори — корисна за анализа на регионалната доминација на партиите и медиумско известување.
CREATE OR REPLACE VIEW public.vw_parliamentary_municipality_winners AS
WITH aggregatedvotes AS (
SELECT e.election_id, e.name AS election_name, r.name AS municipality_name,
pe.name AS party_name, sum(vr.votes) AS total_votes
FROM vote_result vr
JOIN election e ON vr.election_id = e.election_id
JOIN political_entity pe ON vr.entity_id = pe.entity_id
JOIN polling_station ps ON vr.station_id = ps.station_id
JOIN region r ON ps.municipality_id = r.region_id
WHERE e.election_type_id = 2
GROUP BY e.election_id, e.name, r.name, pe.name
),
rankedwinners AS (
SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_votes DESC) AS pos
FROM aggregatedvotes
)
SELECT election_name, municipality_name,
party_name AS dominant_party,
total_votes AS winning_votes,
'Municipality Winner'::text AS status
FROM rankedwinners
WHERE pos = 1;
vw_party_demographic_performance
Ги прикажува гласовите по политички ентитет и гласачко место разбиени по генерациски кохорти: Gen Z (роден ≥1996), Милениалци (1981–1995) и Сениори (<1981). Ја имплементира логиката за демографска анализа на изборна поддршка, овозможувајќи партиите и истражувачите да ја разберат старосната структура на гласачкото тело по регион.
CREATE OR REPLACE VIEW public.vw_party_demographic_performance AS
WITH VoterDemographics AS (
SELECT
ve.election_id,
ve.station_id,
p.date_of_birth,
EXTRACT(YEAR FROM p.date_of_birth) as birth_year
FROM public.voter_election ve
JOIN public.voter v ON ve.voter_id = v.voter_id
JOIN public.person p ON v.person_id = p.person_id
),
BallotStats AS (
SELECT
b.election_id,
b.station_id,
b.entity_id,
b.ballot_id
FROM public.ballot b
WHERE b.is_valid = true
)
SELECT
e.name AS election_name,
r.name AS region_name,
pe.name AS party_name,
ps.name AS station_name,
COALESCE(COUNT(CASE WHEN vd.birth_year >= 1996 THEN 1 END), 0) AS gen_z_votes,
COALESCE(COUNT(CASE WHEN vd.birth_year >= 1981 AND vd.birth_year <= 1995 THEN 1 END), 0) AS millennial_votes,
COALESCE(COUNT(CASE WHEN vd.birth_year < 1981 THEN 1 END), 0) AS senior_votes,
COUNT(bs.ballot_id) AS total_valid_votes
FROM BallotStats bs
JOIN public.election e ON bs.election_id = e.election_id
JOIN public.polling_station ps ON bs.station_id = ps.station_id
JOIN public.region r ON ps.municipality_id = r.region_id
JOIN public.political_entity pe ON bs.entity_id = pe.entity_id
LEFT JOIN VoterDemographics vd ON bs.election_id = vd.election_id
AND bs.station_id = vd.station_id
GROUP BY e.name, r.name, pe.name, ps.name;
vw_party_performance_over_time
Ги следи гласовите и процентот на поддршка за секој политички ентитет низ повеќе изборни циклуси, подредени по ентитет и датум. Ја имплементира логиката за временска анализа на трендови во изборна поддршка — суштинска алатка за политичка аналитика, истражување и споредба на резултати меѓу последователни избори.
CREATE OR REPLACE VIEW public.vw_party_performance_over_time AS
WITH party_votes AS (
SELECT e.election_id, e.name AS election_name, e.election_date,
pe.entity_id, pe.name AS entity_name, sum(vr.votes) AS total_votes
FROM vote_result vr
JOIN election e ON e.election_id = vr.election_id
JOIN political_entity pe ON pe.entity_id = vr.entity_id
WHERE vr.candidate_id IS NULL
GROUP BY e.election_id, e.name, e.election_date, pe.entity_id, pe.name
),
totals AS (
SELECT election_id, sum(total_votes) AS total_election_votes FROM party_votes GROUP BY election_id
)
SELECT pv.election_id, pv.election_name, pv.election_date,
pv.entity_id, pv.entity_name, pv.total_votes, t.total_election_votes,
round(pv.total_votes::numeric * 100.0 / NULLIF(t.total_election_votes, 0::numeric), 2) AS vote_share_pct
FROM party_votes pv JOIN totals t USING (election_id)
ORDER BY pv.entity_name, pv.election_date;
vw_polling_station_stats
Дава детална статистика по гласачко место за секој избор — регистрирани гласачи, фрлени ливчиња, валидни, невалидни и процент на излезност. Ја имплементира логиката за оперативен мониторинг на изборниот ден, овозможувајќи увид во активноста на секоја поединечна станица во реално време.
CREATE OR REPLACE VIEW public.vw_polling_station_stats AS
SELECT e.election_id,
e.name AS election_name,
ps.station_id,
ps.name AS station_name,
count(DISTINCT v.voter_id) AS registered_voters,
count(b.ballot_id) AS ballots_cast,
count(b.ballot_id) FILTER (WHERE b.is_valid) AS valid_ballots,
count(b.ballot_id) FILTER (WHERE NOT b.is_valid) AS invalid_ballots,
round(count(b.ballot_id)::numeric * 100.0
/ NULLIF(count(DISTINCT v.voter_id), 0)::numeric, 2) AS turnout_pct
FROM polling_station ps
JOIN station_election se ON se.station_id = ps.station_id
JOIN election e ON e.election_id = se.election_id
LEFT JOIN ballot b ON b.station_id = ps.station_id AND b.election_id = e.election_id
LEFT JOIN voter v ON v.station_id = ps.station_id
GROUP BY e.election_id, e.name, ps.station_id, ps.name;
vw_polling_station_voter_count
Прикажува точен број на регистрирани гласачи по гласачко место и регион, пресметан директно од табелата voter. Служи за верификација на конзистентноста на податоците — споредувајќи го овој поглед со полето registered_voter во polling_station може да се откријат евентуални несогласувања во бројачите.
CREATE OR REPLACE VIEW public.vw_polling_station_voter_count AS
SELECT r.name AS region_name,
ps.name AS station_name,
count(v.voter_id) AS actual_registered_voters
FROM polling_station ps
JOIN region r ON ps.municipality_id = r.region_id
LEFT JOIN voter v ON ps.station_id = v.station_id
GROUP BY r.name, ps.name;
vw_presidential_by_municipality
Го прикажува водечкиот кандидат на претседателски избори (тип 1) по општина, врз основа на вкупни гласови. Ја имплементира логиката за регионална анализа на претседателски избори — овозможува визуелизација на изборната карта и увид во тоа кој кандидат доминира во кои општини.
CREATE OR REPLACE VIEW public.vw_presidential_by_municipality AS
WITH cityvotes AS (
SELECT e.name AS election_name, r.name AS municipality_name,
pe.name AS candidate_name, sum(vr.votes) AS total_votes
FROM vote_result vr
JOIN election e ON vr.election_id = e.election_id
JOIN political_entity pe ON vr.entity_id = pe.entity_id
JOIN polling_station ps ON vr.station_id = ps.station_id
JOIN region r ON ps.municipality_id = r.region_id
WHERE e.election_type_id = 1
GROUP BY e.name, r.name, pe.name
),
rankedcities AS (
SELECT *, rank() OVER (PARTITION BY election_name, municipality_name ORDER BY total_votes DESC) AS pos
FROM cityvotes
)
SELECT election_name, municipality_name, candidate_name,
total_votes, 'City Winner'::text AS level
FROM rankedcities
WHERE pos = 1;
vw_regional_voting_patterns
Го прикажува доминантниот политички ентитет по регион за сите типови избори — за претседателски ги агрегира гласовите на државно ниво, а за останатите на општинско ниво. Ја имплементира унифицирана логика за регионална анализа на изборни обрасци применлива на повеќе типови избори истовремено.
CREATE OR REPLACE VIEW public.vw_regional_voting_patterns AS
WITH regionhierarchy AS (
SELECT ps.station_id,
r.name AS municipality_name,
parent.name AS sub_region_name,
grandparent.name AS country_name
FROM polling_station ps
JOIN region r ON ps.municipality_id = r.region_id
LEFT JOIN region parent ON r.parent_region_id = parent.region_id
LEFT JOIN region grandparent ON parent.parent_region_id = grandparent.region_id
),
candidatevotes AS (
SELECT e.election_id, e.name AS election_name, e.election_type_id,
pe.name AS candidate_name,
CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END AS final_region,
sum(vr.votes) AS total_votes_sum
FROM vote_result vr
JOIN election e ON vr.election_id = e.election_id
JOIN political_entity pe ON vr.entity_id = pe.entity_id
JOIN regionhierarchy rh ON vr.station_id = rh.station_id
GROUP BY e.election_id, e.name, e.election_type_id, pe.name,
CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END
),
rankedwinners AS (
SELECT *, rank() OVER (PARTITION BY election_id, final_region ORDER BY total_votes_sum DESC) AS rnk
FROM candidatevotes
)
SELECT election_name, final_region AS region_name,
candidate_name AS dominant_entity,
total_votes_sum AS winning_votes,
'Winner'::text AS status
FROM rankedwinners
WHERE rnk = 1 AND final_region IS NOT NULL;
vw_voter_turnout
Дава сеопфатна статистика за излезност на гласачи по избор и општина — регистрирани гласачи, фрлени ливчиња, валидни, невалидни, процент на излезност и процент на невалидни. Ја имплементира логиката за официјално известување за излезност, задолжително за Државната изборна комисија, со поддршка за FULL JOIN за да се прикажат и општини без гласови.
CREATE OR REPLACE VIEW public.vw_voter_turnout AS
WITH registered AS (
SELECT se.election_id, ps.municipality_id AS region_id,
sum(ps.registered_voter) AS registered_voters
FROM station_election se JOIN polling_station ps ON ps.station_id = se.station_id
GROUP BY se.election_id, ps.municipality_id
),
ballots AS (
SELECT b.election_id, ps.municipality_id AS region_id,
count(*) AS ballots_cast,
count(*) FILTER (WHERE b.is_valid) AS valid_ballots,
count(*) FILTER (WHERE NOT b.is_valid) AS invalid_ballots
FROM ballot b JOIN polling_station ps ON ps.station_id = b.station_id
GROUP BY b.election_id, ps.municipality_id
),
combined AS (
SELECT COALESCE(r.election_id, b.election_id) AS election_id,
COALESCE(r.region_id, b.region_id) AS region_id,
r.registered_voters,
b.ballots_cast, b.valid_ballots, b.invalid_ballots,
b.ballots_cast::numeric * 100.0 / NULLIF(r.registered_voters, 0)::numeric AS turnout_pct_raw,
b.invalid_ballots::numeric * 100.0 / NULLIF(b.ballots_cast, 0)::numeric AS invalid_pct_raw
FROM registered r FULL JOIN ballots b ON r.election_id = b.election_id AND r.region_id = b.region_id
)
SELECT e.election_id, e.name AS election_name, e.election_date,
et.type_name AS election_type,
c.region_id, rg.name AS region_name,
COALESCE(c.registered_voters, 0::bigint) AS registered_voters,
COALESCE(c.ballots_cast, 0::bigint) AS ballots_cast,
COALESCE(c.valid_ballots, 0::bigint) AS valid_ballots,
COALESCE(c.invalid_ballots, 0::bigint) AS invalid_ballots,
round(c.turnout_pct_raw, 2) AS turnout_pct,
round(c.invalid_pct_raw, 2) AS invalid_pct
FROM combined c
JOIN election e USING (election_id)
JOIN election_type et ON et.election_type_id = e.election_type_id
JOIN region rg ON rg.region_id = c.region_id
WHERE c.registered_voters IS NOT NULL OR c.ballots_cast IS NOT NULL
ORDER BY e.election_date, round(c.turnout_pct_raw, 2) DESC;
Attachments (3)
- ddl.sql (15.2 KB ) - added by 4 weeks ago.
- dml.sql (135.3 KB ) - added by 4 weeks ago.
- views.sql (15.7 KB ) - added by 4 weeks ago.
Download all attachments as: .zip
