PostgreSQL 少し凝った平均の算出方法

PostgreSQLで単純に平均を求めるなら、avg関数を使えばできます。
www.postgresql.jp

今回、最大と最小の値を除いて平均を求めなければならなくなりました。

プログラムで計算する考え

PostgreSQLからデータを読み込んで、

1. 最大と最小の値を削除する
2. 削除後の値の数を数える
3. 削除後の値を合計する
4. 削除後の値の合計を、削除後の値の数で割る

で計算できます。
ただし、最大と最小の値を削除するので、最低値が3個必要です。
値が3個ないと、最大と最小の値を削除後の値の数が0になって、0での除算が発生して、計算ができません。

Fabian Irsara

SQLで計算する考え

プログラムで計算してもいいけれど、SQLでできないかと以下のように考えました。

1. sum関数で値の合計を求める
2. 値の合計から、max関数で求めた値とmin関数で求めた値を引く
3. count関数で求めた値の数から2を引いて、減算済みの合計を割る

以下のテーブルの場合のSQLを作成しました。

id	score
1	10
1	7
1	2
2	5

SELECT 
   CASE WHEN count(id) >= 3 THEN (sum(score) - max(score) - min(score) / (count(id) - 2 )
            ELSE -1
   END AS average
FROM score_table WHERE id = 1 GROUP BY id;

値が最低必要な3個以上あれば計算を実行し、なければ-1となります。

注意すべき点

count関数は、対象の行が存在しない場合結果を返さないので、考慮が必要です。
www.postgresql.jp