ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

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

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

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

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

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

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

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

ノートパソコンでコーディング中
unsplash-logoFabian 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