PostgreSQL11.5の話題です。
double precision型の0.5をroundで四捨五入すると1になって欲しいところ0になります。
ap2=# SELECT round( CAST( 0.5 AS double precision ) ) ; round ------- 0
double precision型はfloat8(倍精度浮動小数点数)なので、誤差が生じるのは分かるのですが…10進数の0.5は2進数で誤差無く表現できると思っているので、なぜroundで四捨五入すると0になるのか分かりませんでした。
ap2=# SELECT round( 0.5 ) AS "round( 0.5 )" , pg_typeof( 0.5 ) AS "pg_typeof( 0.5 )"; -[ RECORD 1 ]----+-------- round( 0.5 ) | 1 pg_typeof( 0.5 ) | numeric
numeric型でしたら、ちゃんと四捨五入して1になるので、numericに型変換すればOKです。
知らずにdouble precision型の数字に対してroundしてしまうこともあるかと思うので、roundするときはとりあえずnumericに型変換しておこうと思いました。
気がついた契機は以下のとおりです。
ErogameScapeでは中央値を計算することが多いのですが、PERCENTILE_CONT関数というとても便利な関数が追加されていたので、SQLを書き換えていました。
gamelistテーブルにはmedianという列があって、ゲームの中央値を格納しています。
このmedidnの列が、どういうわけか…INTEGER型で定義されています(本当に、どういうわけでしょうか…)
あるゲームに62点と63点がつけられた場合、中央値は62.5点になります。
が、medianがINTEGER型になってしまっているので、62.5を四捨五入して63を格納することにしています。
62点と63点の中央値を求めるSQLは以下のとおりです。
ap2=# SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) AS "62と63の中央値をPERCENTILE_CONT関数で求める" ap2-# FROM ( ap2(# SELECT 62 AS tokuten ap2(# UNION ALL ap2(# SELECT 63 AS tokuten ap2(# ) AS t ap2-# ; -[ RECORD 1 ]-------------------------------+----- 62と63の中央値をPERCENTILE_CONT関数で求める | 62.5これをroundすると…
ap2=# SELECT round( PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) ) AS "63になって欲しいけど62になる" ap2-# FROM ( ap2(# SELECT 62 AS tokuten ap2(# UNION ALL ap2(# SELECT 63 AS tokuten ap2(# ) AS t; -[ RECORD 1 ]----------------+--- 63になって欲しいけど62になる | 62PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten)の戻り値の型を調べると、
ap2=# SELECT pg_typeof(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten)) AS "戻り値の型はdouble precision" ap2-# FROM ( ap2(# SELECT 62 AS tokuten ap2(# UNION ALL ap2(# SELECT 63 AS tokuten ap2(# ) AS t; -[ RECORD 1 ]----------------+----------------- 戻り値の型はdouble precision | double precision
double precisionなので、62.5をroundで四捨五入すると62になってしまいます。
そこで、PERCENTILE_CONTの戻り値をnumericに型変換してからroundで四捨五入します。
ap2=# SELECT round( CAST( PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tokuten) AS numeric ) ) AS "numericにCASTしてからなら63になってくれる" ap2-# FROM ( ap2(# SELECT 62 AS tokuten ap2(# UNION ALL ap2(# SELECT 63 AS tokuten ap2(# ) AS t; -[ RECORD 1 ]-----------------------------+--- numericにCASTしてからなら63になってくれる | 63double precisionの0.5をroundで四捨五入すると0になる根本理由を知りたいのですが…浮動小数点のことから勉強し直さないと駄目でまったく感覚がなくなっているので…諦めることにしました…