1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| select date_format(startdate, '%Y-%m-%d %H:%i:%s') as Time,
(1 - (s1 + s2 + s3) / (s4 + s5 + s6)) * 100 as succ_rate
from (
select MGW,
startdate,
sum(v1) as s1,
sum(v2) as s2,
sum(v3) as s3,
sum(v4) as s4,
sum(v5) as s5,
sum(v6) as s6
from (
select avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsRej' then valeur else null end) as v1,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsRej' then valeur else null end) as v2,
avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsRej' then valeur else null end) as v3,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfAal2TermsReq' then valeur else null end) as v4,
avg(case when indicateur like '%Vmgw%' and compteur = 'pmNrOfIpTermsReq' then valeur else null end) as v5,
avg(case when indicateur like '%TdmTermGrp%' and compteur = 'pmNrOfTdmTermsReq' then valeur else null end) as v6
from data
where startdate like $P{date_debut}
and MGW like $P{par_mgw}
group by startdate, MGW
) as x
) as y
; |
Partager