平均値を求めるのが一般的ですが、中央値が必要な場合もあります。
両方について説明しよう。
平均値
式で平均値を算出する:
avg = (x1 + x2 + x3 + ... + xn) / n
このサンプルデータを使用してみましょう。
SELECT * FROM data;
1
2
3
4
5
9
この数字を使って、平均値を計算してみましょう。
(1 + 2 + 3 + 4 + 5 + 9) / 6 = 24 / 6 = 4
上の式をSQLで書きましょう。
SELECT SUM(score) / COUNT(score) FROM data;
4
(1 + 2 + 3 + 4 + 5 + 9 ) / 6 なので、これは正しいです。
正確な値を使用するために、実際にはfloatを使用する必要があります
SELECT CAST(SUM(score) AS FLOAT) / COUNT(score) FROM data;
4.0
幸いなことに、SQL にはクエリを簡素化する標準メソッドがあります。
SELECT AVG(score) FROM data;
4.0
中央値
中央値を求めることが難しくなります。
中央値は、数を大きさの順に並べたときの真ん中の数です。計算方法は次の通りです。
数が奇数個(n)の場合、中央値は (n+1)/2 番目の数値です。
数が偶数個(n)の場合、中央値は n/2 番目と (n/2)+1 番目の数値の平均です。
この例では値が6つあるので偶数です。真ん中から2桁必要です: 3, 4
平均値が計算します: ( 3 + 4 ) / 2 = 3.5
SQL で同じことをしましょう:
SELECT AVG(score) AS median
FROM (
SELECT score
FROM (
SELECT score, ROW_NUMBER() OVER (ORDER BY score) as row, COUNT(*) OVER () as total_rows
FROM data
) sub
WHERE row in ((total_rows + 1) / 2, (total_rows + 2) / 2)
);
3.5
このクエリでは:
1. ROW_NUMBER() 関数を使用して、スコアで並べ替えた各行に行番号を割り当てます。
2. COUNT(*) OVER() 関数を使用して、行の総数を取得します。
3. WHERE 句は、行の総数が奇数の場合は中央の行を選択し、行の総数が偶数の場合は中央の2つの行を選択します。
4. 最後に、外部クエリが AVG(score) を取得し、これが中央値となります。
まとめ
中央値を見つけるのは一見難しいが、バラバラにするとより理にかなっている。