Changes between Version 2 and Version 3 of DatabaseCreation

Timestamp:

06/03/26 02:57:15 (13 days ago)

Author:

231071

Comment:

--

DatabaseCreation

@@ -9 +9 @@
 === Погледи (Views)
 [[html(<a href="https://develop.finki.ukim.mk/projects/EDB/attachment/wiki/DatabaseCreation/views.sql">views.sql</a>)]]
+
+= Фаза 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;
+}}}