Changes between Version 2 and Version 3 of AdvancedReports


Ignore:
Timestamp:
12/26/22 23:04:54 (2 years ago)
Author:
201051
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • AdvancedReports

    v2 v3  
    44select q3.maximum, p3.*
    55from (
    6 select max(q2.count) as maximum, q2.pra_id
    7 from(
    8 select distinct q.count, q.pra_id as pra_id
    9 from
    10 (
    11         select count(distinct o.o_id), p.pra_id as pra_id from prasanja p
    12         join odgovori o on p.pra_id = o.pra_id
    13         where o.p_id is not null and
    14         p.pra_datum between now()-interval '4 months' and now()
    15         group by p.pra_id
    16 
    17 ) as q
    18 join prasanja p on q.pra_id = p.pra_id
    19 join odgovori o on o.pra_id = p.pra_id
    20 where q.count >=2 and o.o_validen is true
    21 ) as q2
    22 join prasanja p2 on p2.pra_id = q2.pra_id
    23 group by q2.pra_id
    24 order by maximum desc
    25 limit 1
     6        select max(q2.count) as maximum, q2.pra_id
     7        from(
     8                select distinct q.count, q.pra_id as pra_id
     9                from(
     10                        select count(distinct o.o_id), p.pra_id as pra_id from prasanja p
     11                        join odgovori o on p.pra_id = o.pra_id
     12                        where o.p_id is not null and
     13                        p.pra_datum between now()-interval '4 months' and now()
     14                        group by p.pra_id
     15
     16                ) as q
     17                join prasanja p on q.pra_id = p.pra_id
     18                join odgovori o on o.pra_id = p.pra_id
     19                where q.count >=2 and o.o_validen is true
     20        ) as q2
     21        join prasanja p2 on p2.pra_id = q2.pra_id
     22        group by q2.pra_id
     23        order by maximum desc
     24        limit 1
    2625) as q3
    2726join prasanja p3 on q3.pra_id = p3.pra_id
    … …  
    3231create view broj_reakcii_na_izbrani_odgovori as (
    3332select sum(q4.br_reakcii) as br_reakcii_total,  q4.o_id, q4.pra_id  from (
    34 select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
    35 select *  from (
    36 select p.pra_id as prasanje from prasanja p
    37 join odgovori o on o.pra_id =p .pra_id
    38 where o.s_id is not null
    39 group by p.pra_id
    40 order by count(o.o_id) desc
    41 limit 3 ) as q1
    42 union
    43 select q2.prasanje  from (
    44 select p.pra_id as prasanje from prasanja p
    45 join odgovori o on o.pra_id =p .pra_id
    46 where o.p_id is not null
    47 group by p.pra_id
    48 order by count(o.o_id) desc
    49 limit 3 ) as q2
    50 ) as q3
    51 join prasanja p2 on q3.prasanje = p2.pra_id
    52 join odgovori o on o.pra_id =p2.pra_id
    53 join dava_reakcija dr on o.o_id = dr.o_id
    54 group by o.o_id, o.pra_id
    55 union
    56 select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
    57 select *  from (
    58 select p.pra_id as prasanje from prasanja p
    59 join odgovori o on o.pra_id =p .pra_id
    60 where o.s_id is not null
    61 group by p.pra_id
    62 order by count(o.o_id) desc
    63 limit 3 ) as q1
    64 union
    65 select q2.prasanje  from (
    66 select p.pra_id as prasanje from prasanja p
    67 join odgovori o on o.pra_id =p .pra_id
    68 where o.p_id is not null
    69 group by p.pra_id
    70 order by count(o.o_id) desc
    71 limit 3 ) as q2
    72 ) as q3
    73 join prasanja p2 on q3.prasanje = p2.pra_id
    74 join odgovori o on o.pra_id =p2.pra_id
    75 join reagira_na rn on o.o_id = rn.o_id
    76 group by o.o_id, o.pra_id
     33        select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
     34                select *  from (
     35                        select p.pra_id as prasanje from prasanja p
     36                        join odgovori o on o.pra_id =p .pra_id
     37                        where o.s_id is not null
     38                        group by p.pra_id
     39                        order by count(o.o_id) desc
     40                        limit 3
     41                ) as q1
     42                union
     43                select q2.prasanje  from (
     44                        select p.pra_id as prasanje from prasanja p
     45                        join odgovori o on o.pra_id =p .pra_id
     46                        where o.p_id is not null
     47                        group by p.pra_id
     48                        order by count(o.o_id) desc
     49                        limit 3
     50                ) as q2
     51        ) as q3
     52        join prasanja p2 on q3.prasanje = p2.pra_id
     53        join odgovori o on o.pra_id =p2.pra_id
     54        join dava_reakcija dr on o.o_id = dr.o_id
     55        group by o.o_id, o.pra_id
     56        union
     57        select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
     58                select *  from (
     59                        select p.pra_id as prasanje from prasanja p
     60                        join odgovori o on o.pra_id =p .pra_id
     61                        where o.s_id is not null
     62                        group by p.pra_id
     63                        order by count(o.o_id) desc
     64                        limit 3
     65                ) as q1
     66                union
     67                select q2.prasanje  from (
     68                        select p.pra_id as prasanje from prasanja p
     69                        join odgovori o on o.pra_id =p .pra_id
     70                        where o.p_id is not null
     71                        group by p.pra_id
     72                        order by count(o.o_id) desc
     73                        limit 3
     74                ) as q2
     75        ) as q3
     76        join prasanja p2 on q3.prasanje = p2.pra_id
     77        join odgovori o on o.pra_id =p2.pra_id
     78        join reagira_na rn on o.o_id = rn.o_id
     79        group by o.o_id, o.pra_id
    7780) as q4
    7881group by q4.o_id , q4.pra_id
    … …  
    8285create view broj_reakcii_na_odgovor as (
    8386select sum(q4.br_reakcii) as br_reakcii_total,  q4.o_id, q4.pra_id  from (
    84 select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
    85 select q1.prasanje  from (
    86 select p.pra_id as prasanje from prasanja p
    87 join odgovori o on o.pra_id =p .pra_id
    88 group by p.pra_id
    89  ) as q1
    90 union
    91 select q2.prasanje  from (
    92 select p.pra_id as prasanje from prasanja p
    93 join odgovori o on o.pra_id =p .pra_id
    94 group by p.pra_id
    95         ) as q2
    96 ) as q3
    97 join prasanja p2 on q3.prasanje = p2.pra_id
    98 join odgovori o on o.pra_id =p2.pra_id
    99 join dava_reakcija dr on o.o_id = dr.o_id
    100 group by o.o_id, o.pra_id
    101 union
    102 select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
    103 select q1.prasanje  from (
    104 select p.pra_id as prasanje from prasanja p
    105 join odgovori o on o.pra_id =p .pra_id
    106 group by p.pra_id
    107 ) as q1
    108 union
    109 select q2.prasanje  from (
    110 select p.pra_id as prasanje from prasanja p
    111 join odgovori o on o.pra_id =p .pra_id
    112 group by p.pra_id
    113 ) as q2
    114 ) as q3
    115 join prasanja p2 on q3.prasanje = p2.pra_id
    116 join odgovori o on o.pra_id =p2.pra_id
    117 join reagira_na rn on o.o_id = rn.o_id
    118 group by o.o_id, o.pra_id
     87        select count(dr.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
     88                select q1.prasanje  from (
     89                        select p.pra_id as prasanje from prasanja p
     90                        join odgovori o on o.pra_id =p .pra_id
     91                        group by p.pra_id
     92                ) as q1
     93                union
     94                select q2.prasanje  from (
     95                        select p.pra_id as prasanje from prasanja p
     96                        join odgovori o on o.pra_id =p .pra_id
     97                        group by p.pra_id
     98                ) as q2
     99        ) as q3
     100        join prasanja p2 on q3.prasanje = p2.pra_id
     101        join odgovori o on o.pra_id =p2.pra_id
     102        join dava_reakcija dr on o.o_id = dr.o_id
     103        group by o.o_id, o.pra_id
     104        union
     105        select count(rn.reakcija) as br_reakcii, o.o_id , o.pra_id  from (
     106                select q1.prasanje  from (
     107                        select p.pra_id as prasanje from prasanja p
     108                        join odgovori o on o.pra_id =p .pra_id
     109                        group by p.pra_id
     110                ) as q1
     111                union
     112                select q2.prasanje  from (
     113                        select p.pra_id as prasanje from prasanja p
     114                        join odgovori o on o.pra_id =p .pra_id
     115                        group by p.pra_id
     116                ) as q2
     117        ) as q3
     118        join prasanja p2 on q3.prasanje = p2.pra_id
     119        join odgovori o on o.pra_id =p2.pra_id
     120        join reagira_na rn on o.o_id = rn.o_id
     121        group by o.o_id, o.pra_id
    119122) as q4
    120123group by q4.o_id , q4.pra_id