SQLで標準偏差と偏差値を求める [SQLServer, MySQL] (STDEVP, STDDEV_POP)

学校の成績関連のシステムの開発をしていると、時どき偏差値（へんさち）を求めることがあります。
そこで今回はSQLで偏差値を求めるのに必要な標準偏差（ひょうじゅんへんさ）の取得と、偏差値を求める方法についてのメモを残しておきます。

1 偏差値とは
2 偏差値を求める手順
3 偏差値を求める
4 SQLで標準偏差と偏差値を求める
- 4.1 サンプルテーブルの作成とデータの挿入
- 4.2 標準偏差と偏差値を求めるSQL

偏差値とは

偏差値を求める前に偏差値とはどういうものなのかについて、簡単に記載しておきます。

Wikipediaでは偏差値について次のように記載されています。

偏差値（へんさち、英: standard score）とは、ある数値がサンプルの中でどれくらいの位置にいるかを表した無次元数。平均値が50、標準偏差が10となるように標本変数を規格化したものである。

出典: 偏差値

上記の説明ではピンとこない人もいるかもしれませんので補足すると

例えば、テストの得点をもとに偏差値を表す場合

偏差値とは、テストを受けた全員の中で自分や友人などの特定の人物が、どれくらいの位置にいるかを表す数値です。
偏差値では、平均点が偏差値50になるように変換しますので、その基準の50からどの程度離れているかによって、相対的に位置を判断することができます。
平均が50ですので、偏差値が50より上であれば平均より優れている、50より下であれば偏差値より劣っているということになります。
偏差値では、同一の得点であっても平均点が違うことによって結果が変わります。
例えば、数学と英語のテストがともに70点だったとします。この場合は得点だけでは全体の中で平均より上なのか、平均より下なのかがわかりません。
しかし偏差値を使用することで、50よりも上か下かで結果の優劣を判断することができるのです。

偏差値を求める手順

偏差値を求める手順は次のようになります。

平均点を求める
平均点との差を求める
平均点との差の平方数を求める
分散を求める
標準偏差を求める
平均点との差に10を掛ける
平均点との差に10を掛けた値を標準偏差で割る
50を足して偏差値を求める

偏差値を求める

では実際に偏差値を求めてみます。
まずはSQLを使わずに偏差値を求めます。

偏差値を求める例として、10人の学生がテストを受けた結果の得点を用意します。

No	学生	得点
1	Aさん	70
2	Bさん	40
3	Cさん	80
4	Dさん	30
5	Eさん	60
6	Fさん	70
7	Gさん	40
8	Hさん	60
9	Iさん	50
10	Jさん	100

1. 平均点を求める

平均点は全員の得点の合計を人数で割ることで求めることができます。
上記のサンプルデータでは

70 ＋ 40 ＋ 80 ＋ 30 ＋ 60 ＋ 70 ＋ 40 ＋ 60 ＋ 50 ＋ 100 ＝ 600
600 ÷ 10 ＝ 60

となり、平均点は60点になります。

2. 平均点との差を求める

得点と平均点との差を求めます。

学生	得点	平均点との差
Aさん	70	10
Bさん	40	-20
Cさん	80	20
Dさん	30	-30
Eさん	60	0
Fさん	70	10
Gさん	40	-20
Hさん	60	0
Iさん	50	-10
Jさん	100	40

3. 平均点との差の平方数を求める

平均点との差の平方数は「平均点との差 × 平均点との差」（平均点との差の2乗）で求めることができます。

学生	平均点との差	平方数
Aさん	10	100
Bさん	-20	400
Cさん	20	400
Dさん	-30	900
Eさん	0	0
Fさん	10	100
Gさん	-20	400
Hさん	0	0
Iさん	-10	100
Jさん	40	1600

4. 分散を求める

分散は平均点との差の平方数の平均で求めることができます。
サンプルデータでは

100 ＋ 400 ＋ 400 ＋ 900 ＋ 0 ＋ 100 ＋ 400 ＋ 0 ＋ 100 ＋ 1600 ＝ 4000
4000 ÷ 10 ＝ 400

となり、分散は400になります。

5. 標準偏差を求める

標準偏差を求める前に、標準偏差について、簡単に記載しておきます。

Wikipediaでは標準偏差について次のように記載されています。

標準偏差（ひょうじゅんへんさ、英: standard deviation, SD）は、日本工業規格では、分散の正の平方根と定義している。データや確率変数の散らばり具合（ばらつき）を表す数値の一つ。物理学、経済学、社会学などでも使う。例えば、ある試験でクラス全員が同じ点数、すなわち全員が平均値の場合、データにはばらつきがないので、標準偏差は 0 になる。

出典: 標準偏差

「データや確率変数の散らばり具合（ばらつき）を表す数値」なので
標準偏差とは、テストの得点などが平均値の近くに集中しているか、集中していないかを数値で表したものであり、標準偏差が小さければ平均値の近くに集まっている。標準偏差が大きければ、平均値より遠い位置に散らばっていると判断することができます。

標準偏差は分散の平方根（√）で求めることができます。

20 × 20 ＝ 400なので、分散400の平方根は20になります。

6. 平均点との差に10を掛ける

得点と平均点との差に10を掛けた値を求めます。

学生	平均点との差	10を掛ける
Aさん	10	100
Bさん	-20	-200
Cさん	20	200
Dさん	-30	-300
Eさん	0	0
Fさん	10	100
Gさん	-20	-200
Hさん	0	0
Iさん	-10	-100
Jさん	40	400

7. 平均点との差に10を掛けた値を標準偏差で割る

得点と平均点との差に10を掛けた値を標準偏差で割った値を求めます。

学生	差×10	標準偏差で割る
Aさん	100	5
Bさん	-200	-10
Cさん	200	10
Dさん	-300	-15
Eさん	0	0
Fさん	100	5
Gさん	-200	-10
Hさん	0	0
Iさん	-100	-5
Jさん	400	20

8. 50を足して偏差値を求める

得点と平均点との差に10を掛けた値を標準偏差で割った値に50を足した値を求めます。

学生	÷標準偏差	50を足す
Aさん	5	55
Bさん	-10	40
Cさん	10	60
Dさん	-15	35
Eさん	0	50
Fさん	5	55
Gさん	-10	40
Hさん	0	50
Iさん	-5	45
Jさん	20	70

以上で計算は終了です。各学生の偏差値を算出することができました。

学生	偏差値
Aさん	55
Bさん	40
Cさん	60
Dさん	35
Eさん	50
Fさん	55
Gさん	40
Hさん	50
Iさん	45
Jさん	70

SQLで標準偏差と偏差値を求める

標準偏差と偏差値をSQLで求めるためのサンプルテーブルを作成し、サンプルデータを登録します。

サンプルテーブルの作成とデータの挿入

-- テーブルの作成
CREATE TABLE WRITTEN_TEST
(
STUDENT nvarchar(3) NOT NULL,
SCORE int NOT NULL
);
-- データの挿入
INSERT INTO WRITTEN_TEST
    (STUDENT, SCORE)
VALUES
    ('Aさん', 70),
    ('Bさん', 40),
    ('Cさん', 80),
    ('Dさん', 30),
    ('Eさん', 60),
    ('Fさん', 70),
    ('Gさん', 40),
    ('Hさん', 60),
    ('Iさん', 50),
    ('Jさん', 100);

-- テーブルの作成

CREATE TABLE WRITTEN_TEST

(

STUDENT nvarchar(3) NOT NULL,

SCORE int NOT NULL

);

-- データの挿入

INSERT INTO WRITTEN_TEST

(STUDENT, SCORE)

VALUES

('Aさん', 70),

('Bさん', 40),

('Cさん', 80),

('Dさん', 30),

('Eさん', 60),

('Fさん', 70),

('Gさん', 40),

('Hさん', 60),

('Iさん', 50),

('Jさん', 100);

上記のWRITTEN_TESTテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに挿入する方法については、以下の記事を参照してください。

標準偏差と偏差値を求めるSQL

標準偏差と偏差値を求めます。
SQLには標準偏差を取得する関数が用意されているので関数を使用して取得します。
以下、SQLServerでの標準偏差と偏差値を求めるSQLです。

SELECT
    STUDENT AS [学生],
    SCORE AS [得点],
    AVERAGE AS [平均点],
    STANDARD_DEVIATION AS [標準偏差],
    -- 得点と平均点との差に10を掛けて標準偏差で割り50を足し偏差値を求める
    (((SCORE - AVERAGE) * 10) / STANDARD_DEVIATION) + 50 AS [偏差値]
FROM
    (
    SELECT
        STUDENT,
        SCORE,
        -- AVG関数で平均点を求める
        (SELECT AVG(SCORE) FROM WRITTEN_TEST) AS AVERAGE,
        -- STDEVP関数で標準偏差を求める
        (SELECT STDEVP(SCORE) FROM WRITTEN_TEST) AS STANDARD_DEVIATION
    FROM
        WRITTEN_TEST
    ) AS WRITTEN_TEST
ORDER BY
    STUDENT;

SELECT

STUDENT AS [学生],

SCORE AS [得点],

AVERAGE AS [平均点],

STANDARD_DEVIATION AS [標準偏差],

-- 得点と平均点との差に10を掛けて標準偏差で割り50を足し偏差値を求める

(((SCORE - AVERAGE) * 10) / STANDARD_DEVIATION) + 50 AS [偏差値]

FROM

(

SELECT

STUDENT,

SCORE,

-- AVG関数で平均点を求める

(SELECT AVG(SCORE) FROM WRITTEN_TEST) AS AVERAGE,

-- STDEVP関数で標準偏差を求める

(SELECT STDEVP(SCORE) FROM WRITTEN_TEST) AS STANDARD_DEVIATION

FROM

WRITTEN_TEST

) AS WRITTEN_TEST

ORDER BY

STUDENT;

SQLServerではSTDEVP関数で標準偏差を取得することができます。

実行結果

MySQLで取得する場合は以下のSQLになります。

SELECT
    STUDENT AS `学生`,
    SCORE AS `得点`,
    AVERAGE AS `平均点`,
    STANDARD_DEVIATION AS `標準偏差`,
    -- 得点と平均点との差に10を掛けて標準偏差で割り50を足し偏差値を求める
    (((SCORE - AVERAGE) * 10) / STANDARD_DEVIATION) + 50 AS `偏差値`
FROM
    (
    SELECT
        STUDENT,
        SCORE,
        -- AVG関数で平均点を求める
        (SELECT AVG(SCORE) FROM WRITTEN_TEST) AS AVERAGE,
        -- STDEVP関数で標準偏差を求める
        (SELECT STDDEV_POP(SCORE) FROM WRITTEN_TEST) AS STANDARD_DEVIATION
    FROM
        WRITTEN_TEST
    ) AS WRITTEN_TEST
ORDER BY
    STUDENT;