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
| with data as (select 'nom1' sel , 610 val from dual union all
select 'nom1' sel , 610 val from dual union all
select 'nom1' sel , 600 val from dual union all
select 'nom2' sel , 610 val from dual union all
select 'nom2' sel , 600 val from dual union all
select 'nom3' sel , 610 val from dual union all
select 'nom3' sel , 605 val from dual union all
select 'nom3' sel , 600 val from dual union all
select 'nom4' sel , 610 val from dual union all
select 'nom4' sel , 610 val from dual union all
select 'nom4' sel , 610 val from dual union all
select 'nom4' sel , 605 val from dual union all
select 'nom4' sel , 605 val from dual union all
select 'nom4' sel , 600 val from dual union all
select 'nom4' sel , 600 val from dual ),
stats as (select distinct
sel
, val
, count(val) over (partition by sel, val) / count(sel) over (partition by sel ) prop
, max(val) over (partition by sel) m_val
from data ),
pre_c as (select sel, val, stddev(prop) over (partition by sel) eqt , m_val, max(prop) over (partition by sel) m_prop, prop
from stats )
select sel, val
from pre_c
where val=m_val
and eqt > 0.2 |
Partager