Напредни извештаи од базата (SQL)

Пример извештаи

• Број на казнети патници според линија во изминатата година
• Мерење побарувачка на секоја линија во првиот квартал од оваа година
• Проток на патници според постојка
• Најпрометен период во денот (интервали од 1 час) според линија - од податоци од ланската година
• Период од денот во кој најмногу се купува билети (часовни и дневни)
• Вкупна заработувачка од билети и казни на претпријатието во минатата календарска година

1. Број на казнети патници според линија во изминатата година

select li_ime, count(li_ime)
from linija l
left join instancanalinija as i on l.li_id = i.li_id
left join kontroli as ko on i.inl_id = ko.inl_id
left join kazna as ka on ko.kontrola_id = ka.kontrola_id
where extract(year from ka.kz_datum) = extract(year from (now() - interval'1 year'))
group by li_ime
order by count(li_ime) desc

2. Најпрометен период во денот (интервали од 1 час) според линија - од податоци од ланската година

select li_ime_given as li_ime, interval_1h , broj_pati
from(
        select li_ime as li_ime_given, interval_1h , count(li_ime||'-'||interval_1h) broj_pati
        from(
                select *, 
                (to_char(extract (hour from vozenje_start), '00') || ':00 -' || to_char(extract (hour from (vozenje_start + '1 hours'::interval))::int, '00') || ':00') as interval_1h
                from project.linija li 
                left join project.instancanalinija inl on li.li_id = inl.li_id 
                left join project.vozenje v on inl.inl_id = v.inl_id 
                where v.vozenje_start between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
        )
        group by li_ime, interval_1h 
        order by li_ime, interval_1h
)
where broj_pati = (
        select max(broj_pati)
        from(
                select li_ime, interval_1h , count(li_ime||'-'||interval_1h) broj_pati
                from(
                        select *, 
                        (to_char(extract (hour from vozenje_start), '00') || ':00 -' || to_char(extract (hour from (vozenje_start + '1 hours'::interval))::int, '00') || ':00') as interval_1h
                        from project.linija li 
                        left join project.instancanalinija inl on li.li_id = inl.li_id 
                        left join project.vozenje v on inl.inl_id = v.inl_id 
                        where li_ime = li_ime_given
                )
                group by li_ime, interval_1h 
                order by li_ime, interval_1h 
        )
        group by li_ime
)

3. Мерење побарувачка на секоја линија во првиот квартал од оваа година

select distinct l.li_id, l.li_ime, count(v.vozenje_id)
from linija l
left join instancanalinija inl on inl.li_id = l.li_id
left join vozenje v on v.inl_id = inl.inl_id and v.vozenje_start between date_trunc('year', now()) 
and date_trunc('month', date_trunc('year', now())+ interval '7 months')
group by l.li_id, l.li_ime

4. Проток на патници според постојка и име на линија во вториот квардтал од минатата година

select p_ime, li_ime, count(patnik_k_id) as broj_patnici
from postojka as p
join postojkanalinija as pnl on p.p_id = pnl.p_id
join vozenje as v on pnl.pnl_id = v.kacuva_pnl_id
join patnik as pa on v.patnik_k_id = pa.k_id
join linija as l on pnl.li_id = l.li_id
where v.vozenje_start between (make_date(extract(year from (now() - '1 years'::interval))::int, 4, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 6, 30)) 
group by p_ime, li_ime
order by count(patnik_k_id) desc

5. Период од денот (интервали од 1 час) во кој најмногу се купува билети (часовни и дневни)

select a.tb_ime, a.interval_1h , a.broj_pati
from(
        select tb_ime, interval_1h , count(tb_ime||'-'||interval_1h) broj_pati
        from(
                select *, 
                (to_char(extract (hour from b.b_datum_na_kupuvanje), '00') || ':00 -' || to_char(extract (hour from (b.b_datum_na_kupuvanje  + '1 hours'::interval))::int, '00') || ':00') as interval_1h
                from tipbilet t 
                left join bilet b on b.tb_id = t.tb_id 
                where b.b_datum_na_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
        )
        group by tb_ime, interval_1h 
        order by tb_ime, interval_1h
) a
where broj_pati = (
        select max(broj_pati)
        from(
                select tb_ime, interval_1h , count(tb_ime||'-'||interval_1h) broj_pati
                from(
                        select *, 
                        (to_char(extract (hour from b.b_datum_na_kupuvanje), '00') || ':00 -' || to_char(extract (hour from (b.b_datum_na_kupuvanje  + '1 hours'::interval))::int, '00') || ':00') as interval_1h
                        from tipbilet t 
                        left join bilet b on b.tb_id = t.tb_id 
                        where b.b_datum_na_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
                        and tb_ime = a.tb_ime
                )
                group by tb_ime, interval_1h 
                order by tb_ime, interval_1h
        )
--        group by tb_ime
) and tb_ime in ('Часовен', 'Дневен')

6. Вкупна заработувачка од билети и казни на претпријатието во минатата календарска година

select
CASE 
  WHEN sum_kz IS NOT NULL AND sum_b IS NOT NULL THEN COALESCE(sum_kz::int, 0) + COALESCE(sum_b::int, 0)
  WHEN sum_kz IS NOT NULL THEN COALESCE(sum_kz::int, 0)
  WHEN sum_b IS NOT NULL THEN COALESCE(sum_b::int, 0)
  ELSE 0
END
from (
select 
(
select sum(kz_iznos)
from kazna k
where k.kz_plateno = true and (k.kz_datum between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and
                                                                        (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31)))
                                                                        and (k.kz_datum_plateno between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and
                                                                        (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31)))
)  sum_kz,
(
select sum(tb_cena)
from bilet b 
join tipbilet tb on tb.tb_id = b.tb_id 
where b.b_datum_na_kupuvanje between (make_date(extract(year from (now() - '1 years'::interval))::int, 1, 1)) and
                                                                        (make_date(extract(year from (now() - '1 years'::interval))::int, 12, 31))
)  sum_b
)