Changes between Initial Version and Version 1 of AdvancedReports

Timestamp:

09/19/26 01:44:15 (12 days ago)

Author:

183164

Comment:

--

AdvancedReports

@@ +1 @@
+= Напредни извештаи =
+
+Извештаите се извршуваат со SET search_path TO project; Двата извештаи се во прикачената датотека [attachment:advanced_reports.sql].
+
+Во релационата алгебра се користи проширената нотација: σ (селекција), π (генерализирана проекција, со пресметани атрибути), ⋈ (спојување), ⟕ (лево надворешно спојување), ρ (преименување), γ (групирање и агрегатни функции), τ (подредување) и ← (доделување на привремена релација).
+
+== Квартален учинок во решавањето на пријавите по категорија ==
+
+Општината сака да знае колку ефикасно ги решава различните видови комунални проблеми и дали се подобрува со текот на времето. Извештајот за секој квартал и за секоја категорија прикажува: вкупен број на пријави, број на решени и одбиени пријави, процент на решени, просечно време до првиот одговор (од поднесување до статус „примена“, во часови), просечно време до решавање (во денови), промена на времето до решавање во однос на претходниот квартал, и рангирање на категориите во кварталот од најбавната кон најбрзата. Со извештајот се откриваат категориите каде е потребно повеќе ресурси, и се следи долгорочниот тренд на квартално, полугодишно и годишно ниво.
+
+=== Решение во SQL ===
+
+{{{#!sql
+WITH report_times AS (
+    SELECT r.report_id, r.category_id, r.status, r.created_at,
+           date_trunc('quarter', r.created_at) AS quarter,
+           MIN(l.changed_at) FILTER (WHERE l.status = 'received') AS received_at,
+           MIN(l.changed_at) FILTER (WHERE l.status = 'resolved') AS resolved_at
+    FROM reports r
+    JOIN status_logs l ON l.report_id = r.report_id
+    GROUP BY r.report_id
+),
+per_quarter AS (
+    SELECT quarter, category_id,
+           COUNT(*)                                     AS total,
+           COUNT(*) FILTER (WHERE status = 'resolved')  AS resolved,
+           COUNT(*) FILTER (WHERE status = 'rejected')  AS rejected,
+           AVG(EXTRACT(EPOCH FROM received_at - created_at) / 3600)  AS avg_response_hours,
+           AVG(EXTRACT(EPOCH FROM resolved_at - created_at) / 86400) AS avg_resolution_days
+    FROM report_times
+    GROUP BY quarter, category_id
+)
+SELECT to_char(pq.quarter, 'YYYY-"Q"Q')                         AS quarter,
+       c.name                                                   AS category,
+       pq.total, pq.resolved, pq.rejected,
+       ROUND(100.0 * pq.resolved / pq.total, 1)                 AS resolved_pct,
+       ROUND(pq.avg_response_hours, 1)                          AS avg_response_hours,
+       ROUND(pq.avg_resolution_days, 1)                         AS avg_resolution_days,
+       ROUND(pq.avg_resolution_days - prev.avg_resolution_days, 1) AS change_vs_prev_quarter,
+       RANK() OVER (PARTITION BY pq.quarter
+                    ORDER BY pq.avg_resolution_days DESC NULLS LAST)      AS slowest_rank
+FROM per_quarter pq
+JOIN categories c ON c.category_id = pq.category_id
+LEFT JOIN per_quarter prev
+       ON prev.category_id = pq.category_id
+      AND prev.quarter = pq.quarter - INTERVAL '3 months'
+ORDER BY pq.quarter, slowest_rank;
+}}}
+
+=== Решение во релациона алгебра ===
+
+{{{
+Rec ← report_id γ MIN(changed_at)→received_at ( σ status='received' (status_logs) )
+
+Res ← report_id γ MIN(changed_at)→resolved_at ( σ status='resolved' (status_logs) )
+
+RT  ← π report_id, category_id, created_at,
+        quarter(created_at)→quarter,
+        received_at, resolved_at,
+        (1 if status='resolved' else 0)→is_resolved,
+        (1 if status='rejected' else 0)→is_rejected
+      ( reports ⟕ Rec ⟕ Res )
+
+PQ  ← quarter, category_id γ COUNT(report_id)→total,
+                              SUM(is_resolved)→resolved,
+                              SUM(is_rejected)→rejected,
+                              AVG(received_at − created_at)→avg_response,
+                              AVG(resolved_at − created_at)→avg_resolution
+      ( RT )
+
+Prev ← ρ Prev(prev_of, category_id, prev_avg)
+       ( π quarter + 3 months, category_id, avg_resolution (PQ) )
+
+PQK ← π quarter, category_id, COALESCE(avg_resolution, −1)→sort_key (PQ)
+
+Rank ← a.quarter, a.category_id γ COUNT(b.category_id)→slower_count
+       ( ρ a(PQK) ⟕ a.quarter = b.quarter ∧ b.sort_key > a.sort_key ρ b(PQK) )
+
+Result ← τ quarter, slowest_rank
+         ( π quarter, name, total, resolved, rejected,
+             100 · resolved / total→resolved_pct,
+             avg_response, avg_resolution,
+             avg_resolution − prev_avg→change_vs_prev_quarter,
+             1 + slower_count→slowest_rank
+           ( ( PQ ⋈ Rank ⋈ categories )
+             ⟕ PQ.quarter = Prev.prev_of ∧ PQ.category_id = Prev.category_id Prev ) )
+}}}
+
+Рангот (RANK) е изразен преку лево надворешно самоспојување: рангот на една категорија е 1 плус бројот на категории во истиот квартал со подолго време на решавање. Категориите без решени пријави добиваат клуч −1, што одговара на NULLS LAST.
+
+== Жаришта на повторувачки проблеми ==
+
+Општината сака да ги открие локациите каде истиот вид проблем постојано се повторува, бидејќи тоа укажува на потреба од трајно решение (на пример реконструкција на улица, дополнителни контејнери или замена на инсталации) наместо постојани интервенции. Градот се дели на мрежа од ќелии со големина околу 500 метри (координатите се заокружуваат на 0,005 степени). За секоја ќелија и категорија се пресметува: број на пријави, број на различни граѓани кои пријавиле, број на различни квартали во кои има пријави, број на моментално отворени пријави, просечно време на решавање и датум на последната пријава. Се прикажуваат само ќелиите со најмалку 3 пријави во најмалку 2 различни квартали, рангирани според бројот на пријави помножен со бројот на квартали. Одбиените пријави не се сметаат. Извештајот се користи на годишно и повеќегодишно ниво за планирање на инвестициите.
+
+=== Решение во SQL ===
+
+{{{#!sql
+WITH resolution AS (
+    SELECT report_id, MIN(changed_at) FILTER (WHERE status = 'resolved') AS resolved_at
+    FROM status_logs
+    GROUP BY report_id
+),
+located AS (
+    SELECT r.report_id, r.category_id, r.citizen_id, r.status, r.created_at, r.location_text,
+           ROUND(r.latitude  / 0.005) * 0.005 AS cell_lat,
+           ROUND(r.longitude / 0.005) * 0.005 AS cell_lon,
+           res.resolved_at
+    FROM reports r
+    JOIN resolution res ON res.report_id = r.report_id
+    WHERE r.latitude IS NOT NULL
+      AND r.status <> 'rejected'
+)
+SELECT RANK() OVER (ORDER BY COUNT(*) * COUNT(DISTINCT date_trunc('quarter', l.created_at)) DESC) AS hotspot_rank,
+       l.cell_lat, l.cell_lon,
+       c.name                                                    AS category,
+       MAX(l.location_text)                                      AS example_address,
+       COUNT(*)                                                  AS reports,
+       COUNT(DISTINCT l.citizen_id)                              AS distinct_citizens,
+       COUNT(DISTINCT date_trunc('quarter', l.created_at))       AS quarters_with_reports,
+       COUNT(*) FILTER (WHERE l.status NOT IN ('resolved', 'rejected')) AS open_now,
+       ROUND(AVG(EXTRACT(EPOCH FROM l.resolved_at - l.created_at) / 86400), 1) AS avg_resolution_days,
+       to_char(MAX(l.created_at), 'DD.MM.YYYY')                  AS last_report
+FROM located l
+JOIN categories c ON c.category_id = l.category_id
+GROUP BY l.cell_lat, l.cell_lon, c.name
+HAVING COUNT(*) >= 3
+   AND COUNT(DISTINCT date_trunc('quarter', l.created_at)) >= 2
+ORDER BY hotspot_rank, reports DESC;
+}}}
+
+=== Решение во релациона алгебра ===
+
+{{{
+Res ← report_id γ MIN(changed_at)→resolved_at ( σ status='resolved' (status_logs) )
+
+L   ← π report_id, category_id, citizen_id, created_at, location_text,
+        round(latitude / 0.005) · 0.005→cell_lat,
+        round(longitude / 0.005) · 0.005→cell_lon,
+        quarter(created_at)→quarter,
+        resolved_at,
+        (1 if status ∉ {'resolved','rejected'} else 0)→is_open
+      ( σ latitude ≠ NULL ∧ status ≠ 'rejected' (reports) ⟕ Res )
+
+G   ← cell_lat, cell_lon, category_id
+      γ COUNT(report_id)→reports,
+        COUNT-DISTINCT(citizen_id)→distinct_citizens,
+        COUNT-DISTINCT(quarter)→quarters_with_reports,
+        SUM(is_open)→open_now,
+        AVG(resolved_at − created_at)→avg_resolution,
+        MAX(location_text)→example_address,
+        MAX(created_at)→last_report
+      ( L )
+
+H   ← π cell_lat, cell_lon, category_id, reports, distinct_citizens,
+        quarters_with_reports, open_now, avg_resolution, example_address,
+        last_report, reports · quarters_with_reports→score
+      ( σ reports ≥ 3 ∧ quarters_with_reports ≥ 2 (G) )
+
+Rank ← a.cell_lat, a.cell_lon, a.category_id γ COUNT(b.category_id)→higher_count
+       ( ρ a(H) ⟕ b.score > a.score ρ b(H) )
+
+Result ← τ hotspot_rank, reports desc
+         ( π 1 + higher_count→hotspot_rank, cell_lat, cell_lon, name,
+             example_address, reports, distinct_citizens,
+             quarters_with_reports, open_now, avg_resolution, last_report
+           ( H ⋈ Rank ⋈ categories ) )
+}}}
+
+Во SQL решението групирањето е според името на категоријата, а во релационата алгебра според category_id. Двете се еквивалентни, бидејќи името на категоријата е единствено (category_name → category_id).
+
+== Користење на вештачка интелигенција ==
+
+[wiki:AdvancedReportsAIUsage Користење на вештачка интелигенција за напредните извештаи]