Напредни извештаи

Извештаите се извршуваат со SET search_path TO project; Двата извештаи се во прикачената датотека advanced_reports.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

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).