Напредни бази на податоци

Фаза 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