Напредни извештаи од базата (SQL и складирани процедури)

Извештај за заработка на секоја фирма и генерален интерес за секое тромесечие од претходната година

select distinct(f.firm_id) as firma,
    case when tabelaQuarter.quarter=1 then tabelaSuma.broj_kupeni else 0 end as prv_kvartal_kupeni,
    case when tabelaQuarter.quarter=2 then tabelaSuma.broj_kupeni  else 0 end as vtor_kvartal_kupeni,
    case when tabelaQuarter.quarter=3 then tabelaSuma.broj_kupeni  else 0 end as tret_kvartal_kupeni,
    case when tabelaQuarter.quarter=4 then tabelaSuma.broj_kupeni  else 0 end as cetvrt_kvartal_kupeni,
      
    (case when tabelaQuarter.quarter=1 then tabelaSuma.sum else 0 end) as prv_kvartal_suma,
    (case when tabelaQuarter.quarter=2 then tabelaSuma.sum  else 0 end) as vtor_kvartal_suma,
    (case when tabelaQuarter.quarter=3 then tabelaSuma.sum  else 0 end) as tret_kvartal_suma,
    (case when tabelaQuarter.quarter=4 then tabelaSuma.sum  else 0 end) as cetvrt_kvartal_suma
       
    from firm as f
        join auditorium as a on a.firm_id = f.firm_id
    join gives as g on g.auditorium_id = a.auditorium_id
    join movieprojection as mp on mp.projection_id = g.projection_id
    join reservation as r on r.projection_id = mp.projection_id
    join(
    select extract(quarter from reservation_date) as quarter
    from reservation
    ) as tabelaQuarter on tabelaQuarter.quarter is not null
    
    join(
    select distinct(f2.firm_id) as firma, count(distinct r.reservation_id) as broj_kupeni,sum(mp.projection_price) as sum
    from reservation as r
    left join movieprojection as mp on mp.projection_id = r.projection_id
    left join gives as g on g.projection_id = mp.projection_id
    left join auditorium as a on a.auditorium_id = g.auditorium_id 
    left join firm as f2 on f2.firm_id = a.firm_id
    group by f2.firm_id
    ) as tabelaSuma on tabelaSuma.firma = f.firm_id
    where ((extract(year from now())::integer-1)=(extract(year from r.reservation_date)::integer))
    group by f.firm_id,tabelaQuarter.quarter,tabelaSuma.broj_kupeni,tabelaSuma.sum
    order by f.firm_id

Извештај за прикажан интерес за секој филм , колку пати е резервирана проекција за тој филм во последни две недели и просечна заработка од карти за таа проекција за тој филм

   select distinct mp.projection_id,m.movie_name,vkupna_brojka.vkupno,prosek.prosek_zarabotka
from movieprojection as mp
join movie as m on mp.movie_id=m.movie_id

join(   select distinct r.reservation_id, count(r.projection_id) as vkupno
         from reservation as r
          join movieprojection as mp2 on r.projection_id=mp2.projection_id 
          join movie as m2 on mp2.movie_id=m2.movie_id
          where r.reservation_date between now() - interval '14 days' and now()
          group by 1
 )as vkupna_brojka on vkupna_brojka.vkupno is not null
 
 join(   select distinct r2.reservation_id,mp3.projection_id, avg(suma.sum1) as prosek_zarabotka
         from reservation as r2
          join movieprojection as mp3 on r2.projection_id=mp3.projection_id 
          
          join (select mp4.projection_id , sum(mp4.projection_price) as sum1
                 from movieprojection as mp4
                 group by 1
          )as suma on suma.sum1 is not null
          join movie as m3 on mp3.movie_id=m3.movie_id
          group by 1,2
          order by prosek_zarabotka desc
 )as prosek on prosek.prosek_zarabotka is not null

Извештај за вкупно продадени карти по жанрови

 select g.genre_id,g.genre_name,
 case when tabela.prodadena_karta >=1 then tabela.prodadena_karta else 0 end as prodadeni_karti
 from genre as g
 join is_ as is1 on g.genre_id=is1.genre_id
 join movie as m on is1.movie_id=m.movie_id
 join movieprojection as mp on m.movie_id=mp.movie_id
 join reservation as r on mp.projection_id=r.projection_id
 join (
     select g2.genre_id,g2.genre_name,count(p1.payment_id) as prodadena_karta 
 from genre as g2
 join is_ as is2 on g2.genre_id=is2.genre_id
 join movie as m2 on is2.movie_id=m2.movie_id
 join movieprojection as mp2 on m2.movie_id=mp2.movie_id
 join reservation as r2 on mp2.projection_id=r2.projection_id
 join payment as p1 on r2.reservation_id=p1.reservation_id 
 group by 1,2
 
 )as tabela on g.genre_id=tabela.genre_id

Прикажување на трите најрезервирани филмски жанрови според бројот на резервации на филмските проекции кои резервации се направени во последните 2 месеци гледано од моменталниот датум

select g.genre_id ,g.genre_name, count(r) as rezervacii from genre as g
     join is_ as i on i.genre_id = g.genre_id
     join movie as m on m.movie_id  = i.movie_id
     join movieprojection as mp on mp.movie_id = m.movie_id
     join reservation as r on r.projection_id = mp.projection_id
     and r.reservation_date between now() - interval '2 month' and now()
     group by g.genre_id
     order by rezervacii desc
     limit 3;

Го селектираме и genre_id за да може Query-то да го употребиме во нашата Spring Boot Application

Извештај за клиент според тромесечие, купени карти и придонес

      select distinct u.user_name,u.user_surname,
    case when tabela.quarter=1 then tabela.broj_kupeni else 0 end as prv_kvartal_kupeni,
        case when tabela.quarter=2 then tabela.broj_kupeni  else 0 end as vtor_kvartal_kupeni,
    case when tabela.quarter=3 then tabela.broj_kupeni  else 0 end as tret_kvartal_kupeni,
    case when tabela.quarter=4 then tabela.broj_kupeni else 0 end as cetvrt_kvartal_kupeni,
    -------------------------------------------------------------------------------
    case when tabela.quarter=1 then tabela.suma else 0 end as prv_kvartal_suma,
    case when tabela.quarter=2 then tabela.suma  else 0 end as vtor_kvartal_suma,
    case when tabela.quarter=3 then tabela.suma  else 0 end as tret_kvartal_suma,
    case when tabela.quarter=4 then tabela.suma else 0 end as cetvrt_kvartal_suma
    
    
        from reservation as r
        join client as c on c.user_id = r.user_id
        join movieprojection as mp on mp.projection_id = r.projection_id 
        join users as u on u.user_id = c.user_id
        join(
        select distinct c2.user_id,extract(quarter from r2.reservation_date) as quarter,sum(mp.projection_price) as suma,
        count(r2.reservation_id) as broj_kupeni
        from reservation as r2
        join movieprojection as mp on mp.projection_id  = r2.projection_id
        join client as c2 on c2.user_id = r2.user_id
        group by 1,2
        ) as tabela on c.user_id = tabela.user_id

Извештај за секоја сала,за секоја година со најмногу одржани филмски проекции и вкупен број на резервации за таа сала

select distinct a.auditorium_name , q4.god as godina,a.auditorium_capacity,vkupna_brojka.vkupno,q4.broj_odrzani_filmovi as broj_odrzani_filmovi
 from 
 (
  select * from 
  (
    select q1.godina as god , max(q1.broj_odrzani_filmovi) as maksimum from 
   ( 
     select extract(year from r.reservation_date)as godina ,r.reservation_id as rez ,count(r.projection_id) as broj_odrzani_filmovi
     from reservation as r
     join seat as s2 on r.seat_id=s2.seat_id
     join auditorium as a2 on s2.auditorium_id=a2.auditorium_id
     join movieprojection as mp1 on r.projection_id=mp1.projection_id
     group by 1,2
 ) as q1
    group by godina
        
 ) as q2
      join 
 (
      select extract (year from r.reservation_date)as godina , r.reservation_id as rez,count(r.projection_id) as broj_odrzani_filmovi
      from reservation as r
      join seat as s3 on r.seat_id=s3.seat_id
      join auditorium as a3 on s3.auditorium_id=a3.auditorium_id
      join movieprojection as mp2 on r.projection_id=mp2.projection_id
      group by 1,2
 )as q3
  on q2.god=q3.godina and q2.maksimum=q3.broj_odrzani_filmovi
 
 )as q4

 join( select distinct r3.reservation_id, count(r3.projection_id) as vkupno
   from reservation as r3
   join movieprojection as mp5 on r3.projection_id=mp5.projection_id 
   join movie as m4 on mp5.movie_id=m4.movie_id
   group by 1
 )as vkupna_brojka on vkupna_brojka.vkupno is not null

 join auditorium as a on a.auditorium_id=q4.rez
 order by godina desc 

Преку овој извештај вработените може да проверат која сала е најмногу оптеретена и да направат распределба на проекциите во однос на капацитетот на седишта што го нуди салата