-- 平均点
with tmp_avg as (
    select
        avg(POINT) as avg_point
    from
        TEST_RESULTS
    where
        TEST_ID = '100'
),
-- 標準偏差
tmp_stdev as (
    select
        POW(sum(POW(POINT - tmp_avg.avg_point, 2)) / count(USER_ID), 0.5) as stdev_point
    from
        TEST_RESULTS,
        tmp_avg
    where
        TEST_ID = '100'
    group by
        TEST_ID
)
select
    r.USER_ID as USER,
    r.POINT as PT,
    case
        when s.stdev_point <> 0 then round((((r.POINT - a.avg_point) * 10) / s.stdev_point) + 50, 1)
        when s.stdev_point = 0 then 50
        end as DEV_VAL
from
    TEST_RESULTS r,
    tmp_avg a,
    tmp_stdev s
where
    TEST_ID = '100'
order by
    DEV_VAL desc,
    USER_ID;