1か月前公開・1か月前更新・0 pv・12 min read

標準偏差を理解するまで・DBで利用する際のポイント備忘録

PostgreSQLSQLOracleSQLServer

https://cdn.magicode.io/media/notebox/5da28d7c-21b1-4e44-b9d1-91a2da5c504c.jpeg

データ分析の基本：平均値・平均偏差・標準偏差・Zスコア・偏差値の理解とDB設計

はじめに

データ分析や統計処理において、「平均」「標準偏差」「偏差値」などの指標はそれはもうよく出てきます。
実際、要件定義段階とかでは」この値が欲しいとか単語単位で出てくるのですが
中身を作る我々は計算手順を理解する必要がありますよね

この記事では、初心者にもわかりやすいかどうかは分かりませんが
自分が理解した順で段階的に解説します。 DBでのクエリやSQLは各自で試したり調整してみてください。

平均値の求め方
平均偏差と標準偏差の違い
Zスコアと偏差値の計算方法
データベースでの実装例（SQL）
パフォーマンス・メンテナンス・データ型の選び方

`1. 平均値（Mean）`

まず記載するほどでもないですが
基本となるのが「平均値」からなのでここからです。

平均値の公式
合計 ÷ 個数

\mu = \frac{1}{N} \sum_{i=1}^{N} x_i

例えば、点数の合計が 400 点、人数が 5 人であれば：

平均値＝ 400 ÷ 5 ＝ 80

`2. 平均偏差（Mean Absolute Deviation, MAD）`

平均からの“ずれ”の平均を求めたものです。
たまにこっちの値が欲しいと要望があったりするので
標準偏差との違いが大事ですね

平均偏差の公式

各値と平均値の差の「絶対値の合計」÷ 個数

平均偏差＝（|A - 平均| ＋ |B - 平均| ＋ …）÷ 人数

\text{MAD} = \frac{1}{N} \sum_{i=1}^{N} \left| x_i - \mu \right|

特徴：
- 外れ値に影響されにくい
- 簡単にばらつきの大きさを把握できる

`3. 標準偏差（Standard Deviation）`

平均からの「二乗の差の平均の平方根」を求めるものです。
平均偏差とは計算方法が異なります。

標準偏差の公式（日本語）

√（（各値と平均値の差）² の合計 ÷ 個数）

\sigma = \sqrt{ \frac{1}{N} \sum_{i=1}^{N} (x_i - \mu)^2 }

特徴：
- 統計学ではこちらが標準的
- Zスコアや偏差値の計算で使われる

`3.5 なぜ二乗を使用して値をとるのか`

標準偏差が「偏差の二乗の平均から平方根を取る」という形で定義されている理由には、
数学的・統計的な意味と実用的な目的があるそうです。

✅ なぜ標準偏差では「偏差の二乗」を使うのか？
◆理由1：正負の偏差が打ち消し合うのを防ぐため
単純に「平均からの差（偏差）」を足し合わせると、プラスとマイナスが打ち消し合って0になることがあります。

例：

データ：90, 100, 110

平均：100

偏差：-10, 0, +10 → 合計すると 0

→ ばらつきを測るには不適切！

👉 そこで、「絶対値」または「二乗」を使って打ち消しを防ぎます。

◆理由2：二乗には数学的なメリットがある

➤ 二乗には微分・統計処理上の利点があります：
二乗は滑らか（微分可能）な関数であるため、解析や最小化問題（例えば最小二乗法）に使いやすい。

統計学では分布（特に正規分布）と相性がよく、解析しやすい。

正規分布に従うデータでは、標準偏差を使ったZスコアや信頼区間の計算が理論的に正しい結果を導きます。

◆理由3：絶対値より理論的に扱いやすい

「平均偏差（絶対値）」でもばらつきは測れますが、絶対値は微分できないため、数式処理や最適化には不向き
標準偏差は二乗を使うことで、**数学的な滑らかさ（連続性・微分可能性）**を保ち、理論的に扱いやすくしています。

✅ 標準偏差と平均偏差の違い（まとめ）

指標	使用する偏差	数学的特徴	向いている場面
平均偏差	絶対値	直感的にわかりやすい	初学者向け、説明用
標準偏差	二乗してから平方根を取る	微分可能・統計理論で中心的な指標	正規分布、回帰分析など理論ベースの解析に最適

🔍 補足：分散と標準偏差の関係
分散（Variance）は、標準偏差を平方しないままの値です：

分散（Variance）

\mathrm{Var}(x) = \frac{1}{N} \sum_{i=1}^{N} (x_i - \mu)^2

標準偏差（Standard Deviation）
分散を出している場合はこれの平方根をとればOK

\sigma = \sqrt{ \mathrm{Var}(x) } = \sqrt{ \frac{1}{N} \sum_{i=1}^{N} (x_i - \mu)^2 }

`4. Zスコア（標準得点）`

値が平均からどのくらい離れているかを「標準偏差を単位として」示したものです。

Zスコアの公式

値－平均値 ÷ 標準偏差

Z = \frac{x - \mu}{\sigma}

例：平均が 70 点、標準偏差が 10 点、スコアが 80 点の場合：

Zスコア＝（80 － 70）÷ 10 ＝ 1.0

`5. 偏差値（Deviance Score）`

Zスコアを元にして、平均50・標準偏差10になるように変換した指標です。
これはよく求められたり指標のスケールを変更して評価基準にそった値にしたりすることがあります。

偏差値の公式
Zスコア × 10 ＋ 50

偏差値 = Z \times 10 + 50

偏差値＝ ((値－平均) ÷ 標準偏差) × 10 ＋ 50

例：Zスコアが1.0なら、偏差値は：

偏差値＝ 1.0 × 10 ＋ 50 ＝ 60

6. データベースでの実装（PostgreSQL例）

平均・標準偏差の算出

SELECT
  AVG(score) AS avg_score,
  STDDEV_POP(score) AS stddev_score
FROM test_scores;

## 各スコアのZスコア・偏差値を算出

WITH stats AS (
  SELECT
    AVG(score) AS avg_score,
    STDDEV_POP(score) AS stddev_score
  FROM test_scores
)
SELECT
  student_id,
  score,
  ROUND((score - stats.avg_score) / stats.stddev_score, 2) AS z_score,
  ROUND(((score - stats.avg_score) / stats.stddev_score) * 10 + 50, 1) AS deviation_score
FROM test_scores, stats;

標準偏差は多くのデータベースで 集計関数またはウィンドウ関数 としてサポートされています。
以下には、自分が使用しているデータベース（ORACLE、SQL Server、PostgreSQL）での使い方をまとめます。

🔸 ORACLE

集計関数：`STDDEV_POP` / `STDDEV_SAMP`

関数	内容
`STDDEV_POP(x)`	母集団の標準偏差（全体を対象）
`STDDEV_SAMP(x)`	標本の標準偏差（母数が不明な場合）

例：クラス全体の標準偏差を計算

SELECT
  AVG(score) AS avg_score,
  STDDEV_POP(score) AS stddev_score
FROM test_scores;

ウィンドウ関数（個別Zスコア計算）

SELECT
  student_id,
  score,
  ROUND((score - AVG(score) OVER ()) / STDDEV_POP(score) OVER (), 2) AS z_score
FROM test_scores;

🔸 SQL Server

集計関数：STDEVP / STDEV

関数	内容
`STDEVP(x)`	母集団の標準偏差（※非推奨）
`STDEV(x)`	標本の標準偏差（こちらが一般的）

例：平均と標準偏差の取得

SELECT
  AVG(score) AS avg_score,
  STDEVP(score) AS stddev_score
FROM test_scores;

ウィンドウ関数：Zスコアの算出

SELECT
  student_id,
  score,
  ROUND((score - AVG(score) OVER ()) / STDEVP(score) OVER (), 2) AS z_score
FROM test_scores;

🔸 PostgreSQL

関数：STDDEV_POP / STDDEV_SAMP

関数	内容
`STDDEV_POP(x)`	母集団の標準偏差
`STDDEV_SAMP(x)`	標本の標準偏差（デフォルト）
`STDDEV(x)`	`STDDEV_SAMP` のエイリアス

集計関数での使用

SELECT
  AVG(score) AS avg_score,
  STDDEV_POP(score) AS stddev_score
FROM test_scores;

ウィンドウ関数でのZスコア

SELECT
  student_id,
  score,
  ROUND((score - AVG(score) OVER ()) / STDDEV_POP(score) OVER (), 2) AS z_score
FROM test_scores;

✅ 標準偏差をDBで扱うときの注意点
関数が母集団か標本かを確認すること（Zスコア・偏差値では母集団ベース推奨）

データの件数が少ない場合は SAMP の方が実態に合うことも
ウィンドウ関数で計算する場合、クエリの実行コストが高くなることに注意

🔁 Zスコア・偏差値を定期更新するなら？

大量データや頻繁な分析処理では、事前にバッチでZスコアや偏差値を計算し、テーブルに保存する設計も実用的です。

日次バッチなどで再計算
分析対象に応じて集計単位（学年別・地域別など）を調整

✅ ケース別アプローチメモ（実務向け）

ケース	方法
毎日更新・集団不変	事前に集約してテーブルに保持（バッチ更新 or トリガ）
都度スコア変更がある	ビューで計算 or SQL内に計算式を組み込む
集団ごとに分けて見たい	`PARTITION BY` を使ってクエリで対応（遅い時だけキャッシュ）
一定期間の履歴が必要	テーブルに記録し、履歴比較ができるようにする

7. パフォーマンス・設計上の注意点

標準偏差などはウィンドウ関数を使用するとクエリが重くなる可能性があります。
特にデータ量が多い・集団が大きい場合は注意が必要です。

設計の選択肢

方法	特徴
SQLで都度計算	最新データに即応。SQLが複雑・遅くなる場合も
バッチで事前計算して保持	パフォーマンス良。再計算の管理が必要

8. データ型(カラム)の選び方

統計量には精度の高い小数点が必要なため、データベースの列型選びも重要です。

データ型	特徴	用途例
`INTEGER`	整数のみ	生の点数（整数の得点など）
`NUMERIC(5,2)` など	小数点を正確に保持（高精度）	平均・標準偏差・偏差値
`FLOAT` / `REAL`	高速だが誤差あり	分析用の一時処理など

💡 標準偏差や偏差値には NUMERIC か DECIMAL系を使うのが安全です。

9. 各DBで標準偏差・分散・Zスコアを計算・保持する構成

対象DB：

Oracle
SQL Server
PostgreSQL

対象機能：

平均、分散、標準偏差の計算
Zスコアの保存
トリガーでの自動更新
VIEW による可読性向上
UDF（ユーザー定義関数）による再利用性の確保

🧱 想定テーブル構成

これは試すDBによって換装してください。

CREATE TABLE scores (
    id SERIAL PRIMARY KEY,
    user_id INT,
    score NUMERIC(5,1)
);

🔍 各DBでの対応(例)

🟠 Oracle

ビュー：平均・標準偏差・Zスコア付き

CREATE OR REPLACE VIEW vw_scores_stats AS
SELECT
  id,
  user_id,
  score,
  AVG(score) OVER () AS avg_score,
  STDDEV_POP(score) OVER () AS stddev_score,
  (score - AVG(score) OVER ()) / NULLIF(STDDEV_POP(score) OVER (), 0) AS z_score
FROM scores;

関数（Zスコアを計算）

CREATE OR REPLACE FUNCTION calc_z(p_score NUMBER)
RETURN NUMBER IS
  v_avg NUMBER;
  v_std NUMBER;
BEGIN
  SELECT AVG(score), STDDEV_POP(score)
  INTO v_avg, v_std
  FROM scores;

  RETURN (p_score - v_avg) / NULLIF(v_std, 0);
END;
/

トリガー（Zスコア列がある場合の更新例）

ALTER TABLE scores ADD z_score NUMBER;

CREATE OR REPLACE TRIGGER trg_update_z
BEFORE INSERT OR UPDATE ON scores
FOR EACH ROW
BEGIN
  :NEW.z_score := calc_z(:NEW.score);
END;
/

🟢 SQL Server

ビュー

CREATE VIEW vw_scores_stats AS
SELECT
  id,
  user_id,
  score,
  AVG(score) OVER () AS avg_score,
  STDEVP(score) OVER () AS stddev_score,
  (score - AVG(score) OVER ()) / NULLIF(STDEVP(score) OVER (), 0) AS z_score
FROM scores;

関数

CREATE FUNCTION dbo.calc_z (@score FLOAT)
RETURNS FLOAT
AS
BEGIN
  DECLARE @avg FLOAT, @std FLOAT;
  SELECT @avg = AVG(score), @std = STDEVP(score) FROM scores;

  RETURN (@score - @avg) / NULLIF(@std, 0);
END;

トリガー

ALTER TABLE scores ADD z_score FLOAT;

CREATE TRIGGER trg_update_z
ON scores
AFTER INSERT, UPDATE
AS
BEGIN
  UPDATE s
  SET z_score = dbo.calc_z(s.score)
  FROM scores s
  INNER JOIN inserted i ON s.id = i.id;
END;

🟣 PostgreSQL

ビュー

CREATE VIEW vw_scores_stats AS
SELECT
  id,
  user_id,
  score,
  AVG(score) OVER () AS avg_score,
  STDDEV_POP(score) OVER () AS stddev_score,
  (score - AVG(score) OVER ()) / NULLIF(STDDEV_POP(score) OVER (), 0) AS z_score
FROM scores;

関数

CREATE OR REPLACE FUNCTION calc_z(p_score NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
  v_avg NUMERIC;
  v_std NUMERIC;
BEGIN
  SELECT AVG(score), STDDEV_POP(score)
  INTO v_avg, v_std
  FROM scores;

  RETURN (p_score - v_avg) / NULLIF(v_std, 0);
END;
$$ LANGUAGE plpgsql;

トリガー

ALTER TABLE scores ADD COLUMN z_score NUMERIC;

CREATE OR REPLACE FUNCTION trg_update_z()
RETURNS TRIGGER AS $$
BEGIN
  NEW.z_score := calc_z(NEW.score);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_z
BEFORE INSERT OR UPDATE ON scores
FOR EACH ROW EXECUTE FUNCTION trg_update_z();

📌 補足

上記でも説明したように統計値のリアルタイム再計算はコストが高い可能性があるため、
定期バッチ処理や集約テーブルによる保持も検討してください。

分散が必要な場合は VAR_POP(score) を使ってください（Oracle・PostgreSQL対応）。

OracleとPostgreSQLでは NULLIF(..., 0) を使って 0 除算回避しています。

実運用では分析対象（期間・カテゴリ）ごとの集計を行うように拡張できます。

一部は構想のみで実際に試してない部分があるので上手くいかないときは各自で調整してくださいね。

まとめ

統計指標の基本からZスコア・偏差値の意味、そしてデータベース設計までまとめました。

正直、学生時代は大人になったら使わないか。とか思っていたり
値自体を見て計算式を忘れてたり実際使う時が来て
あー。ちゃんと覚えておけばよかった何てこといっぱいありますよね～(笑)

計算方法だけでなく処理負荷を理解することでより適切に構築できるように一緒に頑張りましょう！！

標準偏差を理解するまで・DBで利用する際のポイント備忘録

データ分析の基本：平均値・平均偏差・標準偏差・Zスコア・偏差値の理解とDB設計

はじめに

`1. 平均値（Mean）`

`2. 平均偏差（Mean Absolute Deviation, MAD）`

`3. 標準偏差（Standard Deviation）`

`3.5 なぜ二乗を使用して値をとるのか`

`4. Zスコア（標準得点）`

`5. 偏差値（Deviance Score）`

6. データベースでの実装（PostgreSQL例）

平均・標準偏差の算出

🔸 ORACLE

集計関数：`STDDEV_POP` / `STDDEV_SAMP`

例：クラス全体の標準偏差を計算

🔸 SQL Server

🔸 PostgreSQL

7. パフォーマンス・設計上の注意点

8. データ型(カラム)の選び方

9. 各DBで標準偏差・分散・Zスコアを計算・保持する構成

🧱 想定テーブル構成

🔍 各DBでの対応(例)

📌 補足

まとめ

Discussion

コメントにはログインが必要です。

標準偏差を理解するまで・DBで利用する際のポイント備忘録

データ分析の基本：平均値・平均偏差・標準偏差・Zスコア・偏差値の理解とDB設計

はじめに

1. 平均値（Mean）

2. 平均偏差（Mean Absolute Deviation, MAD）

3. 標準偏差（Standard Deviation）

3.5 なぜ二乗を使用して値をとるのか

4. Zスコア（標準得点）

5. 偏差値（Deviance Score）

6. データベースでの実装（PostgreSQL例）

平均・標準偏差の算出

🔸 ORACLE

集計関数：STDDEV_POP / STDDEV_SAMP

例：クラス全体の標準偏差を計算

🔸 SQL Server

🔸 PostgreSQL

7. パフォーマンス・設計上の注意点

8. データ型(カラム)の選び方

9. 各DBで標準偏差・分散・Zスコアを計算・保持する構成

🧱 想定テーブル構成

🔍 各DBでの対応(例)

📌 補足

まとめ

Discussion

コメントにはログインが必要です。

`1. 平均値（Mean）`

`2. 平均偏差（Mean Absolute Deviation, MAD）`

`3. 標準偏差（Standard Deviation）`

`3.5 なぜ二乗を使用して値をとるのか`

`4. Zスコア（標準得点）`

`5. 偏差値（Deviance Score）`

集計関数：`STDDEV_POP` / `STDDEV_SAMP`