--頻出するので先に'100'に絞っておく
WITH FIX_RESULTS AS
(SELECT *
FROM TEST_RESULTS
WHERE TEST_ID = '100')
SELECT
USER_ID AS USER,
POINT AS PT,
/*
偏差値は次の手順で求められる。
A:点数の平均値を求める
B:個々の点数と平均値の差（個々の点数 － 平均値）を求める
C:分散（個々の点数と平均値の差の二乗の平均）を求める
D:標準偏差（分散の正の平方根）を求める
E:個々の点数と平均値との差に10を掛ける
F:個々の点数と平均値との差に10を掛けた値を標準偏差で割る
DEV_VAL:標準偏差で割った値に50を足して偏差値を求める
*/
/*
(SELECT AVG(POINT) FROM FIX_RESULTS) AS A,
POINT - (SELECT AVG(POINT) FROM FIX_RESULTS) AS B,
POWER(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS),2) AS C1,
(SELECT AVG(POWER(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS),2)) FROM TEST_RESULTS WHERE TEST_ID = '100') AS C2,
SQRT((SELECT AVG(POWER(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS),2)) FROM FIX_RESULTS)) AS D,
10*(POINT - (SELECT AVG(POINT) FROM TEST_RESULTS)) AS E,
10*(POINT - (SELECT AVG(POINT) FROM TEST_RESULTS)) /
SQRT((SELECT AVG(POWER(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS),2)) FROM FIX_RESULTS))
AS F,
*/
CASE
WHEN SQRT((SELECT AVG(POWER(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS),2)) FROM FIX_RESULTS))
= 0 THEN 50
ELSE
ROUND(
10*(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS)) /
SQRT((SELECT AVG(POWER(POINT - (SELECT AVG(POINT) FROM FIX_RESULTS),2)) FROM FIX_RESULTS))
+50
,1)
END
AS DEV_VAL
FROM
FIX_RESULTS
ORDER BY DEV_VAL DESC, USER_ID
;