Changes between Initial Version and Version 1 of AdvancedTopics

Timestamp:

06/14/26 21:38:20 (7 days ago)

Author:

231066

Comment:

--

AdvancedTopics

@@ +1 @@
+= Напредни бази на податоци =
+= Фаза 5 — Напредна тема: OLAP Податочни Коцки =
+== Проект: Платформа за евиденција на избори ==
+
+Вовед
+Online Analytical Processing (OLAP) е категорија на технологија за бази на податоци која овозможува брза, повеќедимензионална анализа на големи количини на податоци. За разлика од традиционалните OLTP (Online Transaction Processing) системи кои се оптимизирани за вметнување и ажурирање на поединечни записи, OLAP системите се дизајнирани за комплексни аналитички прашалници кои агрегираат податоци преку повеќе димензии истовремено.
+
+Во контекст на овој систем за евиденција на избори, примарните податоци се инхерентно аналитички — број на гласови, стапки на излезност, доделување мандати и перформанси на кандидати се мерки кои треба да се анализираат преку повеќе димензии: време, географија, политички ентитет и тип на избори.
+
+Извршувањето на овие аналитички прашалници директно врз сировите табели (vote_result: 1,4М записи, ballot: 19М записи, voter_election: 18М записи) би резулторило со неприфатливо долго време за одговор, особено кога се агрегира преку повеќе димензии.
+
+= Зошто OLAP податочни коцки? =
+
+Во нашата постоечка шема имаме следни аналитички табели:
+
+|| Табела || Записи || Употреба ||
+|| vote_result || 1.4M || Агрегирани резултати по станица ||
+|| ballot || 19M || Поединечни гласачки ливчиња ||
+|| voter_election || 18M || Евиденција за излезност ||
+|| candidate_party || 2.4K || Номинации на кандидати ||
+|| candidate_list_item || 1M || Позиции на кандидатски листи ||
+
+Без OLAP, прашалник „Колкав е уделот на гласови на ВМРО-ДПМНЕ по региони во изборите 2024?" би барал:
+
+Спојување на vote_result (1,4М) со polling_station
+
+Спојување со region (563 записи) и political_entity
+
+Групирање и агрегирање на живо при секое барање
+
+Со OLAP податочните коцки, истиот прашалник чита неколку стотини однапред пресметани записи — моментално.
+
+= Техничка имплементација =
+
+==== Материјализирани погледи (Materialized Views)
+Материјализираниот поглед во PostgreSQL е објект на база на податоци кој ги складира резултатите од прашалникот физички на диск, за разлика од регуларниот поглед кој го повторно извршува прашалникот при секој пристап.
+
+Синтакса:
+{{{
+CREATE MATERIALIZED VIEW mv_name AS
+SELECT ... WITH DATA;
+}}}
+
+Освежување на коцките:
+{{{
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name;
+}}}
+
+==== Операторот ROLLUP
+ROLLUP е проширување на GROUP BY кое автоматски генерира подзбирни записи за хиерархиски групирања. За димензии (A, B, C), ROLLUP произведува:
+
+{{{
+GROUP BY (A, B, C)  — најфин зрнест запис
+GROUP BY (A, B)     — подзбир без C
+GROUP BY (A)        — подзбир без B и C
+GROUP BY ()         — вкупен збир
+}}}
+
+NULL вредностите во излезот означуваат групирани (rolled-up) димензии. На пример, NULL во колоната region_name значит дека записот претставува агрегација преку сите региони.
+
+Функцијата GROUPING() враќа цел број кој означува кои димензии се групирани во секој запис:
+
+GROUPING(...) = 0 — најфин зрнест запис
+
+GROUPING(...) > 0 — некои димензии се групирани
+
+==== Просторни индекси (GIST)
+Секоја материјализирана коцка е индексирана на најчесто филтрираните колони. Уникатен индекс е потребен на секоја коцка за поддршка на CONCURRENT освежување.
+
+= Податочни коцки =
+
+=== 1. mv_election_results_cube ===
+
+==== Опис:
+Примарна аналитичка коцка на системот. Обезбедува комплетен повеќедимензионален поглед на резултатите од гласањето преку секоја комбинација на избори, географија и политички ентитет.
+
+==== Зошто е потребна:
+Сировата табела vote_result содржи 1,4М записи. Секој прашалник кој прашува „Кој ентитет победи во одредена општина?" бара спојување на vote_result со polling_station, region, election и political_entity со агрегирање. Без коцката, ова скенира 1,4М записи при секое извршување. Со коцката, истиот прашалник чита неколку стотини однапред пресметани записи моментално.
+
+==== Димензии и Хиерархија:
+|| Димензија || Хиерархија ||
+|| Време || election_date → election_year ||
+|| Географија || земја → плански регион → општина ||
+|| Ентитет || entity_type → entity_name ||
+|| Избори || election_type → election_name ||
+
+==== Мерки:
+|| Мерка || Опис ||
+|| total_votes || Сума на гласови за ентитетот ||
+|| vote_share_pct || Процент на вкупните гласови ||
+|| stations_reporting || Број на станици кои известуваат ||
+
+==== Нивоа генерирани со ROLLUP:
+
+Ниво 0 (најфино): гласови по ентитет по општина по избори
+
+Ниво 1: гласови по ентитет по плански регион по избори
+
+Ниво 2: гласови по ентитет по земја по избори
+
+Ниво 3: гласови по ентитет низ сите избори
+
+Ниво 4 (вкупно): вкупни гласови низ сите димензии
+
+==== Создадени индекси:
+{{{
+CREATE INDEX idx_cube1_election ON mv_election_results_cube(election_id);
+CREATE INDEX idx_cube1_entity   ON mv_election_results_cube(entity_id);
+CREATE INDEX idx_cube1_country  ON mv_election_results_cube(country_id);
+CREATE INDEX idx_cube1_region   ON mv_election_results_cube(planning_region_id);
+CREATE INDEX idx_cube1_grouping ON mv_election_results_cube(grouping_level);
+CREATE INDEX idx_cube1_year     ON mv_election_results_cube(election_year);
+CREATE INDEX idx_cube1_type     ON mv_election_results_cube(election_type);
+CREATE UNIQUE INDEX idx_cube1_unique ON mv_election_results_cube(election_id, entity_id, country_id, planning_region_id, grouping_level);
+}}}
+
+==== Примери на поддржани прашалници:
+
+„Кој ентитет освои највеќе гласови по регион на парламентарните избори 2024?"
+
+„Како се менуваат резултатите на СДСМ низ сите избори?"
+
+„Споредба на вкупните гласови МК vs Србија"
+
+=== 2. mv_turnout_cube ===
+
+==== Опис:
+Ги анализира стапките на учество на гласачите преку географија и времето. Ја поддржува апликацијата со информации за тоа колку регистрирани гласачи навистина гласале на секои избори.
+
+==== Зошто е потребна:
+Пресметувањето на излезноста бара комбинирање на три одделни големи табели: voter (10М записи) за регистрирани гласачи, voter_election (18М записи) за фактичко учество, и ballot (19М записи) за неважечки ливчиња. Спојувањето на овие три табели при секое барање е непрактично. Коцката ги однапред пресметува сите метрики за излезност на секое географско ниво.
+
+==== Димензии и Хиерархија:
+|| Димензија || Хиерархија ||
+|| Време || election_date → election_year ||
+|| Географија || земја → плански регион → општина ||
+|| Избори || election_type → election_name ||
+
+==== Мерки:
+|| Мерка || Опис ||
+|| registered_voters || Регистрирани гласачи во подрачјето ||
+|| voters_checked_in || Гласачи кои навистина гласале ||
+|| invalid_ballots || Неважечки ливчиња ||
+|| turnout_pct || checked_in / registered × 100 ||
+|| invalid_pct || invalid / checked_in × 100 ||
+
+==== Нивоа генерирани со ROLLUP:
+
+Ниво 0 (најфино): излезност по општина по избори
+
+Ниво 1: излезност по плански регион
+
+Ниво 2: излезност по земја по избори
+
+Ниво 3: излезност по избори
+
+Ниво 4 (вкупно): вкупна излезност низ се
+
+==== Создадени индекси:
+{{{
+CREATE INDEX idx_cube2_election ON mv_turnout_cube(election_id);
+CREATE INDEX idx_cube2_country  ON mv_turnout_cube(country_id);
+CREATE INDEX idx_cube2_region   ON mv_turnout_cube(planning_region_id);
+CREATE INDEX idx_cube2_grouping ON mv_turnout_cube(grouping_level);
+CREATE INDEX idx_cube2_year     ON mv_turnout_cube(election_year);
+CREATE INDEX idx_cube2_type     ON mv_turnout_cube(election_type);
+CREATE UNIQUE INDEX idx_cube2_unique ON mv_turnout_cube(election_id, country_id, planning_region_id, grouping_level);
+}}}
+
+==== Примери на поддржани прашалници:
+
+„Која општина имаше највисока излезност на парламентарните избори 2020?"
+
+„Како се менува излезноста низ времето во Македонија наспрема Србија?"
+
+„Кој тип на избори историски има најниска стапка на неважечки ливчиња?"
+
+=== 3. mv_candidate_cube ===
+
+==== Опис:
+Обезбедува преглед на кариерата на поединечни кандидати низ сите избори. Следи на колку избори учествувал секој кандидат, кои партии ги претставувал и на кои позиции бил на кандидатски листи.
+
+==== Зошто е потребна:
+Анализата на кандидатите бара спојување на candidate (500К записи) со person (12М записи), candidate_party, candidate_list_item (1М записи) и election. Пресметувањето на статистики за кариера на живо при секое барање скенира милиони записи. Коцката ги однапред пресметува овие метрики на ниво на кандидат, партија и избори.
+
+==== Димензии и Хиерархија:
+|| Димензија || Хиерархија ||
+|| Кандидат || candidate_id → name → gender ||
+|| Партија || party_id → party_name ||
+|| Избори || election_type → election_year ||
+
+==== Мерки:
+|| Мерка || Опис ||
+|| elections_contested || Број на различни избори ||
+|| parties_represented || Број на различни партии ||
+|| best_list_position || Најдобра позиција на листа ||
+|| worst_list_position || Најлоша позиција на листа ||
+|| avg_list_position || Просечна позиција на листа ||
+
+==== Нивоа генерирани со ROLLUP:
+
+Ниво 0 (најфино): статистики по кандидат по партија по избори
+
+Ниво 1: по кандидат по партија (сите избори)
+
+Ниво 2: по кандидат (сите партии, сите избори) — преглед на кариера
+
+Ниво 3 (вкупно): агрегат низ сите кандидати
+
+==== Создадени индекси:
+{{{
+CREATE INDEX idx_cube3_candidate ON mv_candidate_cube(candidate_id);
+CREATE INDEX idx_cube3_party     ON mv_candidate_cube(party_id);
+CREATE INDEX idx_cube3_election  ON mv_candidate_cube(election_id);
+CREATE INDEX idx_cube3_grouping  ON mv_candidate_cube(grouping_level);
+CREATE INDEX idx_cube3_year      ON mv_candidate_cube(election_year);
+CREATE UNIQUE INDEX idx_cube3_unique ON mv_candidate_cube(candidate_id, party_id, election_id, grouping_level);
+}}}
+
+==== Примери на поддржани прашалници:
+
+„Кои кандидати учествувале на најмногу избори?"
+
+„Која партија историски номинирала најмногу кандидати?"
+
+„Каква е родовата застапеност на кандидатите по тип на избори?"
+
+=== 4. mv_party_dominance_cube ===
+
+==== Опис:
+Ја анализира силата и конкурентниот рангинг на поединечни партии (не коалиции) низ сите избори и земји. Директно ги споредува перформансите на партиите независно дали настапувале самостојно или во коалиција.
+
+==== Зошто е потребна:
+Табелата vote_result ги складира гласовите на ниво на ентитет — гласовите за коалиции се припишуваат на коалицискиот ентитет, не на поединечните партии. Анализата на перформансите на конкретна партија низ сите избори бара филтрирање, спојување и агрегирање со пресметување на ранг преку прозорски функции (window functions). Коцката ги однапред пресметува рангинзите и уделите на гласови на ниво на партија.
+
+==== Димензии и Хиерархија:
+|| Димензија || Хиерархија ||
+|| Партија || party_id → party_name → ideology ||
+|| Избори || election_type → election_year ||
+|| Географија || country_id → country_name ||
+
+==== Мерки:
+|| Мерка || Опис ||
+|| total_votes || Гласови добиени од партијата ||
+|| vote_share_pct || Процент на вкупните гласови ||
+|| rank_in_election || Конкурентски ранг (1 = најмногу) ||
+
+==== Нивоа генерирани со ROLLUP:
+
+Ниво 0 (најфино): гласови по партија по избори по земја
+
+Ниво 1: гласови по партија низ сите избори по земја
+
+Ниво 2: гласови по партија низ сите земји (вкупна кариера)
+
+Ниво 3 (вкупно): сите партии, сите избори
+
+==== Создадени индекси:
+{{{
+CREATE INDEX idx_cube4_party     ON mv_party_dominance_cube(party_id);
+CREATE INDEX idx_cube4_election  ON mv_party_dominance_cube(election_id);
+CREATE INDEX idx_cube4_country   ON mv_party_dominance_cube(country_id);
+CREATE INDEX idx_cube4_grouping  ON mv_party_dominance_cube(grouping_level);
+CREATE INDEX idx_cube4_year      ON mv_party_dominance_cube(election_year);
+CREATE INDEX idx_cube4_rank      ON mv_party_dominance_cube(rank_in_election);
+CREATE UNIQUE INDEX idx_cube4_unique ON mv_party_dominance_cube(party_id, election_id, country_id, grouping_level);
+}}}
+
+==== Примери на поддржани прашалници:
+
+„Каков е историскиот удел на гласови на ВМРО-ДПМНЕ на сите македонски парламентарни избори?"
+
+„Која партија постојано се рангира прва во Србија од 2012?"
+
+„Споредба на промената на гласови меѓу изборите 2020 и 2024 по партија"
+
+=== 5. mv_electoral_district_cube ===
+
+==== Опис:
+Највисоко аналитички комплексната коцка — ја интегрира алокацијата на мандати по изборни единици со пресметките на D'Hondt методот на ниво на однапред агрегирање. Директно ја поддржува апликацијата за приказ на распределбата на мандати.
+
+==== Зошто е потребна:
+Анализата на алокацијата на мандати е најскапиот пресметковен прашалник во системот. Бара спојување на electoral_district со election_participant, vote_result, polling_station и region, потоа примена на D'Hondt алгоритмот со CROSS JOIN LATERAL и generate_series. Коцката ги однапред пресметува алокациите на мандати за сите парламентарни избори и сите единици.
+
+==== Имплементација на D'Hondt во коцката:
+
+Пресметај гласови по ентитет по единица
+
+Филтрирај ентитети над 5% изборен праг
+
+Генерирај делители 1..seats_available по ентитет
+
+Пресметај количници: гласови / делител
+
+Рангирај ги сите количници по единица
+
+Додели мандати на топ-N количници
+
+Оваа логика се извршува еднаш при создавање на коцката и резултатите се складираат трајно.
+
+==== Димензии и Хиерархија:
+|| Димензија || Хиерархија ||
+|| Избори || election_type → election_year ||
+|| Единица || district_id → district_name ||
+|| Ентитет || entity_type → entity_name ||
+
+==== Мерки:
+|| Мерка || Опис ||
+|| total_votes || Гласови за ентитетот во единицата ||
+|| total_district_votes || Вкупни гласови во единицата ||
+|| vote_share_pct || Процент во рамки на единицата ||
+|| seats_won || Мандати доделени со D'Hondt ||
+|| entities_competing || Број на ентитети во единицата ||
+
+==== Нивоа генерирани со ROLLUP:
+
+Ниво 0 (најфино): мандати по ентитет по единица по избори
+
+Ниво 1: мандати по ентитет низ сите единици по избори
+
+Ниво 2: мандати по ентитет низ сите избори
+
+Ниво 3 (вкупно): сите ентитети, сите избори
+
+==== Создадени индекси:
+{{{
+CREATE INDEX idx_cube5_election ON mv_electoral_district_cube(election_id);
+CREATE INDEX idx_cube5_district ON mv_electoral_district_cube(district_id);
+CREATE INDEX idx_cube5_entity   ON mv_electoral_district_cube(entity_id);
+CREATE INDEX idx_cube5_grouping ON mv_electoral_district_cube(grouping_level);
+CREATE INDEX idx_cube5_seats    ON mv_electoral_district_cube(seats_won);
+CREATE UNIQUE INDEX idx_cube5_unique ON mv_electoral_district_cube(election_id, district_id, entity_id, grouping_level);
+}}}
+
+==== Примери на поддржани прашалници:
+
+„Колку мандати освои секој ентитет по единица на парламентарните избори 2024?"
+
+„Кој ентитет освоил највеќе мандати низ сите парламентарни избори историски?"
+
+„Како се сменила распределбата на мандати меѓу 2020 и 2024 во Скопскиот регион?"
+
+= Стратегија за освежување =
+
+Материјализираните погледи се снимки — не се ажурираат автоматски кога основните податоци се менуваат. Во производствен систем за избори, стратегијата за освежување би била:
+
+За активно гласање (ден на избори): освежување на секои 30 минути
+
+По затворање на изборите: едно финално освежување за официјални резултати
+
+Историски избори: без освежување, податоците се непроменливи
+
+Команди за освежување:
+{{{
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_election_results_cube;
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_turnout_cube;
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_candidate_cube;
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_party_dominance_cube;
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_electoral_district_cube;
+}}}
+
+= Резиме =
+
+|| Коцка || Изворни табели || Димензии || Клучна мерка ||
+|| mv_election_results_cube || vote_result, election, region, political_entity || Избори × Географија × Ентитет × Време || vote_share_pct ||
+|| mv_turnout_cube || voter_election, voter, ballot, polling_station || Избори × Географија × Време || turnout_pct ||
+|| mv_candidate_cube || candidate_party, candidate_list_item, candidate, person || Кандидат × Партија × Избори × Време || elections_contested ||
+|| mv_party_dominance_cube || vote_result, political_entity, political_party || Партија × Избори × Географија × Време || rank_in_election ||
+|| mv_electoral_district_cube || electoral_district, election_participant, vote_result || Единица × Ентитет × Избори || seats_won ||