PostgreSQL 少し凝った平均の算出方法で小数点以下も扱う

5月の記事で最大と最小を除いた平均の求め方について書きました。
ajya.hatenablog.jp

記事では、以下のテーブルの場合の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となりますが、この計算の仕方は、整数しか戻りません。

ノートパソコンでコーディング中
John Schnobrich

小数点以下も求める方法

環境にもよるのかもしれませんが、試した環境では1.0をかけると小数点以下も求められるようになりました。

SELECT 
  CASE WHEN COUNT(regno) >= 3 THEN (SUM(remarks) - MAX(remarks) - MIN(remarks)) * 1.0 / (COUNT(regno) - 2)
            ELSE -1
   END AS average
FROM score_table WHERE id = 1 GROUP BY id;

この方法で小数点以下も求められますが、このままでは小数点以下がある場合、6.3333333333333333のように多すぎます。
小数点以下がある場合、小数点第1位までにするために、round関数で四捨五入しました。

SELECT 
  CASE WHEN COUNT(regno) >= 3 THEN round((SUM(remarks) - MAX(remarks) - MIN(remarks)) * 1.0 / (COUNT(regno) - 2), 1)
            ELSE -1
   END AS average
FROM score_table WHERE id = 1 GROUP BY id;