| Version 1 (modified by , 7 days ago) ( diff ) |
|---|
Оптимизација на прашалници и погледи
Во оваа фаза ќе ги анализираме погледите дефинирани во Фаза 2 преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
1. Анализа на поглед 1, vw_party_demographic_performance — демографска анализа на поддршката по партии
Овој поглед овозможува длабинска декомпозиција на изборните резултати преку призмата на демографските карактеристики на гласачкото тело. Наместо едноставен линеарен приказ на победниците, погледот ја открива структурата и профилот на гласачите кои ја генерираат поддршката за секој политички субјект. Преку статистичко поврзување на кастираните гласови со старосната структура на чекираните гласачи, се генерира прецизен увид во електоралната поддршка поделена по генерации (Gen Z, Миленијалци и Сениори).
За максимална оптимизација на перформансите при пребарувањето, погледот е дизајниран да се филтрира според две основни колони: election_name (за избор на конкретен изборен циклус) и party_name (за селекција на одреден политички ентитет).
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_party_demographic_performance WHERE election_name = 'Parliamentary Elections 2014' AND party_name = 'VMRO-DPMNE';
Време на извршување без индекси:
3415.820 ms (~3.4 секунди)
Времето на извршување е неприфатливо, особено при истовремена работа на повеќе корисници. Извршниот план покажува:
- Parallel Seq Scan над
ballot - Hash Join и Nested Loop операции
Ова укажува дека PostgreSQL чита голем број редови поради недостаток на соодветни индекси. Проблемот со времето на извршување може да се подобри со индекси.
Со стратешка имплементација на соодветни индекси врз клучните колони во базата, времето на извршување на оваа комплексна демографска агрегација беше успешно оптимизирано.
Додадени индекси:
CREATE INDEX idx_ballot_election_station ON ballot(election_id, station_id); CREATE INDEX idx_ballot_entity_valid ON ballot(entity_id) WHERE is_valid = true; CREATE INDEX idx_voter_election_ids ON voter_election(election_id, station_id, voter_id); CREATE INDEX idx_polling_station_municipality ON polling_station(municipality_id);
Време на извршување со индекси:
619 ms (беше 3415.820 ms)
Подобрување: ~5.5x побрзо.
---
2. Анализа на поглед 2, vw_local_election_winners — победници на локални избори
Овој поглед овозможува преглед на победниците (кандидати или партии со најмногу гласови) на локално ниво, поделени по изборни единици или општини. Погледот ги сумира гласовите и ги идентификува субјектите со највисок резултат за секоја локација.
Примарен филтер: election_id или election_year (за да се видат победниците на конкретни избори). Секундарен филтер: municipality_name или station_id.
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_local_election_winners WHERE election_name = 'Local Elections 2021';
Време на извршување без индекси:
3500 ms (~3.5 секунди)
Времето на извршување не е прифатливо. Планот покажува дека базата троши премногу време на Incremental Sort и WindowAgg операциите за да ги рангира победниците меѓу 180,000 редови. Ова укажува на потреба од индекси за да се избегнат вгнездените циклуси (Nested Loops) и да се забрза пребарувањето низ табелите.
Додадени индекси:
CREATE INDEX idx_vote_result_election_entity ON vote_result(election_id, entity_id); CREATE INDEX idx_vote_result_station ON vote_result(station_id); CREATE INDEX idx_election_type ON election(election_type_id);
Време на извршување со индекси:
290 ms (беше ~3500 ms)
Иако со дополнително индексирање времето се стабилизираше на околу 290 ms, ова се смета за оптимален резултат за овој тип на комплексен аналитички поглед. Ова време е резултат на балансот помеѓу ефикасното пребарување низ индексите и потребата на базата да изврши агрегација и рангирање врз голема количина податоци во реално време.
---
3. Анализа на поглед 3, vw_parliamentary_municipality_winners — победници на парламентарни избори по општини
Овој поглед служи за детална анализа на изборните резултати од парламентарните избори на ниво на општина. Тој ги агрегира гласовите за секој политички субјект во секоја општина и го идентификува победникот (партијата со најголем број гласови) за таа територијална единица.
Примарен филтер: election_name (на пр. 'Parliamentary Elections 2014'). Секундарен филтер: municipality_name.
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_parliamentary_municipality_winners WHERE election_name = 'Parliamentary Elections 2014';
Време на извршување без индекси:
17900 ms (~17.9 секунди)
Времето на извршување воопшто не е прифатливо. Според приложениот извршен план, прашалникот трае 17.9 секунди главно поради користењето на дискот за сортирање податоци и големиот број на меѓу-резултати (скоро 1 милион редови) кои се префрлаат помеѓу паралелните процеси. Без соодветни индекси, базата не може да ги избегне овие скапи операции.
Додадени индекси:
CREATE INDEX idx_vote_result_election_entity ON vote_result(election_id, entity_id); CREATE INDEX idx_vote_result_station ON vote_result(station_id); CREATE INDEX idx_election_type ON election(election_type_id); CREATE INDEX idx_polling_station_municipality ON polling_station(municipality_id);
Време на извршување со индекси:
1500 ms (беше 17900 ms)
По извршената финална оптимизација и консолидација на индексите, времето на извршување е драстично намалено од 17.9 секунди на 1.5 секунди. Подобрување: ~12x побрзо.
---
4. Анализа на поглед 4, vw_presidential_by_municipality — претседателски гласови по општини
Погледот овозможува детален приказ на гласовите освоени од претседателските кандидати, групирани по општина. Тој ги агрегира податоците од табелата vote_result и ги поврзува со податоците за кандидатите и општините за да генерира извештај за поддршката на кандидатите на локално ниво.
Овој извештај ќе се користи за визуелизација на географската распределба на гласовите, каде преку филтрирање по election_name и municipality_name корисникот може моментално да ги добие финалните резултати за специфичен регион.
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_presidential_by_municipality WHERE election_name = 'Presidential Elections 2019' AND municipality_name = 'Skopje';
Време на извршување:
15 ms
При извршување на погледот за претседателски избори по општини, постигнати се врвни перформанси со време на одговор од само 15 милисекунди. Овој резултат е директна последица на претходно имплементираната стратегија за индексирање (индексите додадени за претходните погледи). Ова време е прифатливо па затоа нема да имаме потреба од дополнително индексирање.
---
5. Анализа на поглед 5, vw_polling_station_voter_count — број на регистрирани гласачи по гласачко место
Погледот служи за пресметување на вкупниот број на регистрирани гласачи за секое поединечно гласачко место. Наместо да се чуваат статични податоци во табелата polling_station (што би довело до редундантност и потенцијални аномалии), овој поглед врши агрегација во реално време врз основа на поврзаноста помеѓу гласачите и нивните доделени избирачки места.
Ќе се користи за проверка дали одредено гласачко место има преголем број гласачи (надминување на капацитетот). Најчесто ќе се користи со филтер по station_id или municipality_name.
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_polling_station_voter_count WHERE region_name = 'Куманово';
Време на извршување без индекси:
3500 ms (~3.5 секунди)
При тестирање на погледот со филтер за општина Куманово, постигнато е почетно време на извршување од 3.5 секунди. Оптимизаторот успешно користи Index Scan врз колоната за име на регион, со што веднаш го изолира бараниот ентитет. Главниот товар на прашалникот доаѓа од агрегацијата на 83,500 записи за гласачи поврзани со избирачките места во таа општина. Иако времето е во секунди, HashAggregate операцијата работи со минимален мемориски зафат, што укажува на тоа дека погледот е стабилен и точно ги пресметува капацитетите на гласачките места во реално време.
Додаден индекс:
CREATE INDEX idx_voter_station ON voter(station_id);
Време на извршување со индекс:
53 ms (беше ~3500 ms)
Со додавање на соодветен индекс, извршниот план за погледот покажа значителна структурна промена. Подобрување: ~66x побрзо.
---
6. Анализа на поглед 6, 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);
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_election_results WHERE election_name = 'Parliamentary Elections 2014' ORDER BY vote_share_pct DESC;
Овој поглед се потпира на агрегации над vote_result и join-ови со речниски табели (election_type, winner_method, political_entity) кои се мали. Основниот индекс на vote_result(election_id) е доволен за задоволителни перформанси. Дополнителна оптимизација не е потребна доколку веќе постои индекс на примарниот клуч и election_id.
---
7. Анализа на поглед 7, vw_dhondt_seat_allocation — распределба на мандати по Д'Онт
Овој поглед ја пресметува распределбата на пратенички мандати во изборни единици според методот Д'Онт. Тој е наменет за парламентарни избори и ги пресметува квоциентите за секој политички ентитет по изборен округ.
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_dhondt_seat_allocation WHERE election_name = 'Parliamentary Elections 2014';
Поради комплексноста на пресметките (CROSS JOIN LATERAL со generate_series, повеќе CTE-и, window функции), овој поглед е по природа тежок за извршување. Клучните индекси кои помагаат се:
CREATE INDEX idx_vote_result_election_candidate ON vote_result(election_id, candidate_id); CREATE INDEX idx_electoral_district_election ON electoral_district(election_id); CREATE INDEX idx_region_parent ON region(parent_region_id);
---
8. Анализа на поглед 8, 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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_invalid_ballot_analysis WHERE election_name = 'Parliamentary Elections 2014' ORDER BY invalid_pct DESC;
Индексот на ballot(election_id, station_id) (додаден за поглед 1) и polling_station(municipality_id) покриваат го најголемиот дел од оптимизацијата. Дополнителен индекс за брзо филтрирање по is_valid:
CREATE INDEX idx_ballot_is_valid ON ballot(is_valid);
---
9. Анализа на поглед 9, vw_voter_turnout — излезност на гласачи
Погледот пресметува излезност на гласачи (turnout) по регион и избори, споредувајќи го бројот на регистрирани гласачи со вистински кастираните ливчиња.
Дефиниција на погледот:
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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_voter_turnout WHERE election_name = 'Parliamentary Elections 2014';
Постоечките индекси на ballot(election_id, station_id) и station_election(station_id) се доволни за оптимално извршување. Доколку station_election нема индекс на election_id:
CREATE INDEX idx_station_election_election ON station_election(election_id);
---
10. Анализа на поглед 10, 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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_polling_station_stats WHERE election_name = 'Parliamentary Elections 2014' ORDER BY turnout_pct DESC LIMIT 20;
Потребните индекси (дел веќе постојат):
CREATE INDEX idx_voter_station ON voter(station_id); CREATE INDEX idx_ballot_station_election ON ballot(station_id, election_id); CREATE INDEX idx_station_election_ids ON station_election(station_id, election_id);
---
11. Анализа на поглед 11, 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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_regional_voting_patterns WHERE election_name = 'Parliamentary Elections 2014';
Индексот на region(parent_region_id) (додаден за поглед 7) и постоечките индекси на vote_result и polling_station се доволни за оптимизација на овој поглед.
---
12. Анализа на поглед 12, 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;
Прашалникот кој ќе го тестираме:
SELECT * FROM vw_party_performance_over_time WHERE entity_name = 'VMRO-DPMNE' ORDER BY election_date;
Прашалникот пребарува по entity_name, па додаваме индекс:
CREATE INDEX idx_political_entity_name ON political_entity(name); CREATE INDEX idx_vote_result_candidate_null ON vote_result(election_id, entity_id) WHERE candidate_id IS NULL;
Со овие индекси времето на извршување значително се намалува, бидејќи PostgreSQL може директно да ги пронајде гласовите без кандидат и да го изолира ентитетот по ime.
Attachments (17)
- 1.png (362.5 KB ) - added by 7 days ago.
- 2.png (478.2 KB ) - added by 7 days ago.
- 3.png (240.7 KB ) - added by 7 days ago.
- 4.png (564.0 KB ) - added by 7 days ago.
- 5.png (234.1 KB ) - added by 7 days ago.
- 6.png (368.8 KB ) - added by 7 days ago.
- 7.png (290.9 KB ) - added by 7 days ago.
- 8.png (505.4 KB ) - added by 7 days ago.
- 9.png (253.4 KB ) - added by 7 days ago.
- 10.png (541.7 KB ) - added by 7 days ago.
- 11.png (245.8 KB ) - added by 7 days ago.
- 12.png (251.7 KB ) - added by 7 days ago.
- 13.png (222.7 KB ) - added by 7 days ago.
- 14.png (253.3 KB ) - added by 7 days ago.
- 15.png (141.7 KB ) - added by 7 days ago.
- 16.png (309.7 KB ) - added by 7 days ago.
- 17.png (145.2 KB ) - added by 7 days ago.
