Материјализирани погледи

Статистика на случаи по полициска станица

create materialized view case_statistics_by_station as
select 
    ps.p_id,
    ps.p_address as station_address,
    sia.city,
    count(cc.c_id) as total_cases,
    count(case when cc.c_status = 'A' then 1 end) as active_cases,
    count(CASE when cc.c_status = 'Z' then 1 end) as closed_cases,
    count(sc.c_id) as solved_cases
from Police_station as ps
left join Crime_case as cc on ps.p_id = cc.p_id
left join Solved_case as sc on cc.c_id = sc.c_id
left join Sector_of_interal_affairs as sia on ps.s_id = sia.s_id
group by ps.p_id, ps.p_address, sia.city;

Анализа на типови на криминал

create materialized view crime_type_analysis as
select
    tc.t_id,
    tc.t_name,
    count(cbtc.c_id) as total_cases,
    count(sc.c_id) as solved_cases,
        count(distinct af.accused_pe_id) as total_accused
from Type_of_crime as tc
left join Case_belongs_to_type_of_crime as cbtc on tc.t_id = cbtc.t_id
left join Crime_case as cc on cbtc.c_id = cc.c_id
left join Solved_case as sc on cc.c_id = sc.c_id
left join Accused_for as af on cc.c_id = af.c_id
group by tc.t_id, tc.t_name;

Анализа на перформансот на секој службеник

create materialized view police_performance as
select 
    'Officer' as role_type,
    o.pe_id,
    p.first_name,
    p.last_name,
    o.o_badge_no as badge_number,
    o.o_date_of_employment as date_of_employment,
    ps.p_address as station_address,
    count(distinct cc.c_id) as cases_managed
from Officer as o
join People as p on o.pe_id = p.pe_id
left join Police_station ps on o.pe_id = ps.pe_id
left join Crime_case cc on ps.p_id = cc.p_id
group by o.pe_id, p.first_name, p.last_name, o.o_badge_no, o.o_date_of_employment, ps.p_address

union all

select 
    'Policeman' as role_type,
    pm.pe_id,
    p.first_name,
    p.last_name,
    pm.badge_no as badge_number,
    pm.p_date_of_employment as date_of_employment,
    ps.p_address as station_address,
    count(distinct s.c_id) as cases_handled
from Policeman as pm
join People as p on pm.pe_id = p.pe_id
left join Police_station as ps on pm.p_id = ps.p_id
left join Statements as s on pm.pe_id = s.pe_id
group by pm.pe_id, p.first_name, p.last_name, pm.badge_no, pm.p_date_of_employment, ps.p_address;

Сумирање на типови на докази

create materialized view evidence_summary as
select 
    e.e_type,
    count(*) as total_evidence,
    count(case when e.is_found = true then 1 end) as found_evidence,
    count(case when e.is_found = false then 1 end) as missing_evidence
from Evidence as e
group by e.e_type;