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

Прикажување на најбараната категорија од страна на членовите во последните три месеци (месец по месец)

select mesectri.ime_kategorija, mesectri.pozajmeni_za_prethodni_tri_meseci,
mesecdva.ime_kategorija, mesecdva.pozajmeni_za_prethodni_dva_meseci,
meseceden.ime_kategorija, meseceden.pozajmeni_za_prethoden_mesec 
from 
(
                select kat.ime_kategorija,count(kat.ime_kategorija) as pozajmeni_za_prethoden_mesec
                from pozajmica as p
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                join e_od as e on k.id_na_kniga = e.id_knigi
                join kategorija as kat on e.id_na_kategorija = kat.id_na_kategorija
                where p.datum_na_zemanje between now() - interval'1 months' and now() 
                group by 1
                order by pozajmeni_za_prethoden_mesec desc 
) as meseceden
join 
(
                select kat.ime_kategorija,count(kat.ime_kategorija) as pozajmeni_za_prethodni_dva_meseci
                from pozajmica as p
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                join e_od as e on k.id_na_kniga = e.id_knigi
                join kategorija as kat on e.id_na_kategorija = kat.id_na_kategorija
                where p.datum_na_zemanje between now() - interval'2 months' and now() - interval'1 month' 
                group by 1
                order by pozajmeni_za_prethodni_dva_meseci desc 
) as mesecdva on meseceden.ime_kategorija = mesecdva.ime_kategorija
join 
(
                select kat.ime_kategorija,count(kat.ime_kategorija) as pozajmeni_za_prethodni_tri_meseci
                from pozajmica as p
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                join e_od as e on k.id_na_kniga = e.id_knigi
                join kategorija as kat on e.id_na_kategorija = kat.id_na_kategorija
                where p.datum_na_zemanje between now() - interval'3 months' and now() - interval'2 months' 
                group by 1
                order by pozajmeni_za_prethodni_tri_meseci desc 
) as mesectri on mesecdva.ime_kategorija = mesectri.ime_kategorija;

Прикажување на членот што има прочитано највеќе книги во последните три месеци (месец по месец)

select mesectri.ime, mesectri.prezime,  mesecTri.pozajmuvanja_pomegju_2_3_meseci_nanazad,meseceden.ime, meseceden.prezime, meseceden.pozajmuvanja_za_prethodniot_mesec,
mesecdva.ime, mesecdva.prezime, mesecdva.pozajmuvanja_pomegju_1_2_meseci_nanazad
from
(
                select ch.ime,ch.prezime,count(ch.ime) as pozajmuvanja_za_prethodniot_mesec
                from pozajmica as p
                join chlen as chl on p.id_chovek_prave = chl.id_chovek
                join chovek as ch on chl.id_chovek = ch.id_chovek
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                where p.datum_na_zemanje between now() - interval'1 month' and now()
                group by 1,2
                order by pozajmuvanja_za_prethodniot_mesec desc
) as meseceden
join
(
                select ch.ime,ch.prezime,(count(ch.id_chovek)) as pozajmuvanja_pomegju_1_2_meseci_nanazad
                from pozajmica as p
                join chlen as chl on p.id_chovek_prave = chl.id_chovek
                join chovek as ch on chl.id_chovek = ch.id_chovek
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                where p.datum_na_zemanje between now() - interval'2 months' and now() - interval '1 month'
                group by 1,2
                order by pozajmuvanja_pomegju_1_2_meseci_nanazad desc
) as mesecdva on meseceden.ime = mesecdva.ime
join 
(
                select ch.ime,ch.prezime,(count(ch.id_chovek)) as pozajmuvanja_pomegju_2_3_meseci_nanazad
                from pozajmica as p
                join chlen as chl on p.id_chovek_prave = chl.id_chovek
                join chovek as ch on chl.id_chovek = ch.id_chovek
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                where p.datum_na_zemanje between now() - interval'3 months' and now() - interval '2 months'
                group by 1,2
                order by pozajmuvanja_pomegju_2_3_meseci_nanazad desc
) as mesectri on mesecdva.ime = mesectri.ime

Прикажување колку позајмици има направено одредена возрасна група корисници во последните 3 месеци (месец по месец)

select count(p.id_na_pozajmica) as meseceden,
(
                select count(p.id_na_pozajmica) as pozajmici
                
                from pozajmica as p
                
                join chlen as chl on p.id_chovek_prave = chl.id_chovek
                join chovek as ch on chl.id_chovek = ch.id_chovek
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                
                where age(((case when substr(ch.embg,5,1) = '9' then '1' || substr(ch.embg,5,3)
                else '2' || substr(ch.embg,5,3) end) || '-' || substr(ch.embg,3,2) || '-' || substr(ch.embg,1,2))::date) between interval '20 years' and interval '22 years'
                and p.datum_na_zemanje between now() - interval'2 months' and now() - interval '1 month'

) as mesecdva,
(
                select count(p.id_na_pozajmica) as pozajmici
                
                from pozajmica as p
                
                join chlen as chl on p.id_chovek_prave = chl.id_chovek
                join chovek as ch on chl.id_chovek = ch.id_chovek
                join knigi as k on p.id_na_kniga = k.id_na_kniga
                
                where age(((case when substr(ch.embg,5,1) = '9' then '1' || substr(ch.embg,5,3)
                else '2' || substr(ch.embg,5,3) end) || '-' || substr(ch.embg,3,2) || '-' || substr(ch.embg,1,2))::date) between interval '20 years' and interval '22 years'
                and p.datum_na_zemanje between now() - interval'3 months' and now() - interval'2 months'

) as mesectri
from pozajmica as p

join chlen as chl on p.id_chovek_prave = chl.id_chovek
join chovek as ch on chl.id_chovek = ch.id_chovek
join knigi as k on p.id_na_kniga = k.id_na_kniga

where age(((case when substr(ch.embg,5,1) = '9' then '1' || substr(ch.embg,5,3)
else '2' || substr(ch.embg,5,3) end) || '-' || substr(ch.embg,3,2) || '-' || substr(ch.embg,1,2))::date) between interval '20 years' and interval '22 years'
and p.datum_na_zemanje between now() - interval'1 month' and now()

Прикажување на најдобро оценетата книга

select k.naslov, avg(ocena) as prosecna_ocena from ocena as o

join knigi as k on o.ocena_id_kniga = k.id_na_kniga
join pozajmica as p on k.id_na_kniga = p.id_na_kniga

group by 1
order by prosecna_ocena desc

Прикажување на најчитаната книга на еден автор во последните три месеци (месец по месец)

select mesectri.naslov, mesectri.ime,mesectri.pozajmuvanja_pomegju_2_3_meseci_nanazad,
mesecdva.naslov, mesecdva.ime,mesecdva.pozajmuvanja_pomegju_1_2_meseci_nanazad, meseceden.naslov, meseceden.ime,meseceden.pozajmuvanja_za_posledniot_mesec
 from
(
                select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja_za_posledniot_mesec from
                pozajmica as p
                
                join knigi as k on p.id_na_kniga=k.id_na_kniga
                join izdava as i on k.id_na_kniga = i.id_knigi
                join avtor as a on i.id_avtor = a.id_na_avtor
                where a.ime like '%Goce%' and p.datum_na_zemanje between now() - interval'1 month' and now()
                
                group by 1,2
                order by pozajmuvanja_za_posledniot_mesec desc
                                fetch first row only

) as meseceden
join
(
                select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja_pomegju_1_2_meseci_nanazad from
                pozajmica as p
                
                join knigi as k on p.id_na_kniga=k.id_na_kniga
                join izdava as i on k.id_na_kniga = i.id_knigi
                join avtor as a on i.id_avtor = a.id_na_avtor
                where a.ime like '%Goce%' and p.datum_na_zemanje between now() - interval'2 months' and now() - interval'1month'
                
                group by 1,2
                order by pozajmuvanja_pomegju_1_2_meseci_nanazad desc
                                fetch first row only

) as mesecdva on meseceden.ime = mesecdva.ime
join
(
                select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja_pomegju_2_3_meseci_nanazad from
                pozajmica as p
                
                join knigi as k on p.id_na_kniga=k.id_na_kniga
                join izdava as i on k.id_na_kniga = i.id_knigi
                join avtor as a on i.id_avtor = a.id_na_avtor
                where a.ime like '%Goce%' and p.datum_na_zemanje between now() - interval'3 months' and now() - interval'2 months'
                
                group by 1,2
                order by pozajmuvanja_pomegju_2_3_meseci_nanazad desc
                fetch first row only
) as mesectri on mesecdva.ime = mesectri.ime

Прикажување колку често корисниците се најавувале во даден временски интервал

select mesectri.ime, mesectri.prezime, meseceden.najavuvanja_vo_prethoden_mesec,
mesecdva.najavuvanja_izmegju_1_2_meseci_nanazad, mesectri.najavuvanja_izmegju_2_3_meseci_nanazad
from 
(
                select ch.ime,ch.prezime,(count(a.log_id)) as najavuvanja_vo_prethoden_mesec
                from authentication as a
                
                join chovek as ch on a.id_chovek = ch.id_chovek
                where a.timestamp_log between now() - interval '1 month' and now()
                
                group by 1,2
                order by najavuvanja_vo_prethoden_mesec desc
) as meseceden
join
(
                select ch.ime,ch.prezime,(count(a.log_id)) as najavuvanja_izmegju_1_2_meseci_nanazad
                from authentication as a
                
                join chovek as ch on a.id_chovek = ch.id_chovek
                where a.timestamp_log between now() - interval '2 months' and now() - interval '1 month'
                
                group by 1,2
                order by najavuvanja_izmegju_1_2_meseci_nanazad desc
) as mesecdva on meseceden.ime = mesecdva.ime and meseceden.prezime = mesecdva.prezime
join
(
                select ch.ime,ch.prezime,case when count(a.log_id) is null then 0 else count(a.log_id) end as najavuvanja_izmegju_2_3_meseci_nanazad
                from authentication as a
                
                join chovek as ch on a.id_chovek = ch.id_chovek
                where a.timestamp_log between now() - interval '3 months' and now() - interval '2 months'
                
                group by 1,2
                order by najavuvanja_izmegju_2_3_meseci_nanazad desc