Changes between Version 14 and Version 15 of AdvancedReports

Timestamp:

02/02/22 15:26:12 (3 years ago)

Author:

193060

Comment:

--

AdvancedReports

@@ -82 +82 @@
 }}}
 
-== 
-Прикажување колку позајмици има направено одредена возрасна група корисници во последните 3 месеци (месец по месец)
+== Прикажување колку позајмици има направено одредена возрасна група корисници во последните 3 месеци (месец по месец)
 {{{#!sql
 select count(p.id_na_pozajmica) as meseceden,
@@ -138 +137 @@
 == Прикажување на најчитаната книга на еден автор во последните три месеци (месец по месец)
 {{{#!sql
-select k.naslov, a.ime, count(p.id_na_pozajmica) as pozajmuvanja 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'6 weeks' and now()
-
-group by 1,2
-order by pozajmuvanja 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
 }}}
 
 == Прикажување колку често корисниците се најавувале во даден временски интервал
 {{{#!sql
-select ch.ime,ch.prezime,(count(a.log_id)) as najavuvanja
-from authentication as a
-
-join chovek as ch on a.id_chovek = ch.id_chovek
-where a.timestamp_log between now() - interval '2 weeks' and now()
-
-group by 1,2
-order by najavuvanja desc
-}}}
+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 
+}}}