Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

12/26/22 00:25:43 (3 years ago)

Author:

201050

Comment:

--

AdvancedReports

@@ -28 +28 @@
 }}}
 
-== 2.
+== 2. Извештај за првите три прашања со најмногу одговори од студенти и професори посебно, потоа од тие прашања ја пресметуваме вкупната сума на реакции на одговорите(заедно од професори и студенти) и ги филтритаме само оние кои имаат просечен или надпросечен број на реакции
+{{{
+create view broj_reakcii_na_izbrani_odgovori as (
+select sum(q4.br_reakcii) as br_reakcii_total,  q4.o_id, q4.pra_id  from (
+select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
+select *  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+where o.s_id is not null
+group by p.pra_id
+order by count(o.o_id) desc
+limit 3 ) as q1
+union 
+select q2.prasanje  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+where o.p_id is not null
+group by p.pra_id
+order by count(o.o_id) desc
+limit 3 ) as q2 
+) as q3
+join prasanja p2 on q3.prasanje = p2.pra_id 
+join odgovori o on o.pra_id =p2.pra_id
+join dava_reakcija dr on o.o_id = dr.o_id
+group by o.o_id, o.pra_id
+union
+select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
+select *  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+where o.s_id is not null
+group by p.pra_id
+order by count(o.o_id) desc
+limit 3 ) as q1
+union 
+select q2.prasanje  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+where o.p_id is not null
+group by p.pra_id
+order by count(o.o_id) desc
+limit 3 ) as q2 
+) as q3
+join prasanja p2 on q3.prasanje = p2.pra_id 
+join odgovori o on o.pra_id =p2.pra_id
+join reagira_na rn on o.o_id = rn.o_id
+group by o.o_id, o.pra_id
+) as q4
+group by q4.o_id , q4.pra_id
+);
+
+
+create view broj_reakcii_na_odgovor as (
+select sum(q4.br_reakcii) as br_reakcii_total,  q4.o_id, q4.pra_id  from (
+select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
+select q1.prasanje  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+group by p.pra_id
+ ) as q1
+union 
+select q2.prasanje  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+group by p.pra_id
+        ) as q2 
+) as q3
+join prasanja p2 on q3.prasanje = p2.pra_id 
+join odgovori o on o.pra_id =p2.pra_id
+join dava_reakcija dr on o.o_id = dr.o_id
+group by o.o_id, o.pra_id
+union
+select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
+select q1.prasanje  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+group by p.pra_id
+) as q1
+union 
+select q2.prasanje  from (
+select p.pra_id as prasanje from prasanja p 
+join odgovori o on o.pra_id =p .pra_id
+group by p.pra_id
+) as q2 
+) as q3
+join prasanja p2 on q3.prasanje = p2.pra_id 
+join odgovori o on o.pra_id =p2.pra_id
+join reagira_na rn on o.o_id = rn.o_id
+group by o.o_id, o.pra_id
+) as q4
+group by q4.o_id , q4.pra_id
+);
+
+select brnio.br_reakcii_total, p.* from broj_reakcii_na_izbrani_odgovori brnio
+join prasanja p on brnio.pra_id  = p.pra_id 
+where brnio.br_reakcii_total >= (select avg(brno.br_reakcii_total) from broj_reakcii_na_odgovor brno)
+}}}
 
 == 3.Извештај за пронаоѓање на најдобрите професори т.е. професорите кои имаат одговори со најдобри рејтингзи, имаат поставено најмногу материјали и предаваат најмногу курсеви.\\