端数処理(丸め)と言えば四捨五入、切り捨て、切り上げですが、SQLiteには四捨五入(round関数)はあっても切り捨て・切り上げの関数がありません。
ユーザー定義関数で実装するというのもひとつの手ですが、今回はSQLiteの標準関数の組み合わせでどうにかしてみようと思います。
また、用途により扱いを変える必要のある負の数や.5の処理についても考えてみます。
なお、今回扱うのは原則として、小数点以下を取っ払って整数に丸める場合のみです。
任意の精度(丸め幅)で丸める場合、最初に丸め幅で割ってから計算し、最後に丸め幅をかけましょう。
準備
とりあえず、サンプルの動作確認のため、簡単なテーブルを作っておきます。
CREATE TABLE test (value REAL NOT NULL); INSERT INTO test VALUES( 2.0); INSERT INTO test VALUES( 1.8); INSERT INTO test VALUES( 1.5); INSERT INTO test VALUES( 1.2); INSERT INTO test VALUES( 1.0); INSERT INTO test VALUES( 0.8); INSERT INTO test VALUES( 0.5); INSERT INTO test VALUES( 0.2); INSERT INTO test VALUES( 0.0); INSERT INTO test VALUES(-0.2); INSERT INTO test VALUES(-0.5); INSERT INTO test VALUES(-0.8); INSERT INTO test VALUES(-1.0); INSERT INTO test VALUES(-1.2); INSERT INTO test VALUES(-1.5); INSERT INTO test VALUES(-1.8); INSERT INTO test VALUES(-2.0);
| value |
|---|
| 2.0 |
| 1.8 |
| 1.5 |
| 1.2 |
| 1.0 |
| 0.8 |
| 0.5 |
| 0.2 |
| 0.0 |
| -0.2 |
| -0.5 |
| -0.8 |
| -1.0 |
| -1.2 |
| -1.5 |
| -1.8 |
| -2.0 |
四捨五入
ただの四捨五入なら先述の通り標準関数にあるのですが、これにもいくらかのバリエーションがあるのです。
通常の四捨五入
おさらいも兼ねて、round関数による四捨五入をまず見てみましょう。
SELECT value, round(value) [round] FROM test;
| value | round |
|---|---|
| 2.0 | 2.0 |
| 1.8 | 2.0 |
| 1.5 | 2.0 |
| 1.2 | 1.0 |
| 1.0 | 1.0 |
| 0.8 | 1.0 |
| 0.5 | 1.0 |
| 0.2 | 0.0 |
| 0.0 | 0.0 |
| -0.2 | 0.0 |
| -0.5 | -1.0 |
| -0.8 | -1.0 |
| -1.0 | -1.0 |
| -1.2 | -1.0 |
| -1.5 | -2.0 |
| -1.8 | -2.0 |
| -2.0 | -2.0 |
-0.5や-1.5の扱いを見る限り、SQLiteでは絶対値に対して四捨五入を行っているようです。
普段は直感的にもわかりやすいこの方法で充分かと思われます。
しかし、それでは困るというケースもあるかと思います。
端数が0.5のとき常に正の無限大へ丸める
数直線で言うと、負の数でも例外なく「ちょうど真ん中は右側に寄せる」という処理です。
実装としては、逆に負の数で端数が0.5のときに例外的に1を足すようにしています。
SELECT value, round(value) + CASE WHEN value >= 0.0 OR value - round(value) != 0.5 THEN 0.0 ELSE 1.0 END [round] FROM test;
| value | round |
|---|---|
| 2.0 | 2.0 |
| 1.8 | 2.0 |
| 1.5 | 2.0 |
| 1.2 | 1.0 |
| 1.0 | 1.0 |
| 0.8 | 1.0 |
| 0.5 | 1.0 |
| 0.2 | 0.0 |
| 0.0 | 0.0 |
| -0.2 | 0.0 |
| -0.5 | 0.0 |
| -0.8 | -1.0 |
| -1.0 | -1.0 |
| -1.2 | -1.0 |
| -1.5 | -1.0 |
| -1.8 | -2.0 |
| -2.0 | -2.0 |
なんだか強引な気もしますが、これよりスマートな方法は思いつかなかったです。
端数が0.5のとき常に負の無限大へ丸める
数直線で言うと、「ちょうど真ん中は左側に寄せる」という処理です。
四捨五入と呼べるか微妙だし、おそらくこれ単体では使い道のないものですが、後で使います。
SELECT value, round(value) - CASE WHEN value <= 0.0 OR round(value) - value != 0.5 THEN 0.0 ELSE 1.0 END [round] FROM test;
| value | round |
|---|---|
| 2.0 | 2.0 |
| 1.8 | 2.0 |
| 1.5 | 1.0 |
| 1.2 | 1.0 |
| 1.0 | 1.0 |
| 0.8 | 1.0 |
| 0.5 | 0.0 |
| 0.2 | 0.0 |
| 0.0 | 0.0 |
| -0.2 | 0.0 |
| -0.5 | -1.0 |
| -0.8 | -1.0 |
| -1.0 | -1.0 |
| -1.2 | -1.0 |
| -1.5 | -2.0 |
| -1.8 | -2.0 |
| -2.0 | -2.0 |
端数が0.5のとき偶数へ丸める(最近接偶数への丸め)
特定の分野で行われる丸めですね。
「bankers' rounding」とか「JIS丸め」なんて呼ばれているようです。
SELECT value, round(value) + CASE WHEN abs(value - round(value)) != 0.5 OR abs(round(value) % 2) = 0 THEN 0.0 ELSE CASE WHEN value > 0 THEN -1.0 ELSE 1.0 END END [round] FROM test;
| value | round |
|---|---|
| 2.0 | 2.0 |
| 1.8 | 2.0 |
| 1.5 | 2.0 |
| 1.2 | 1.0 |
| 1.0 | 1.0 |
| 0.8 | 1.0 |
| 0.5 | 0.0 |
| 0.2 | 0.0 |
| 0.0 | 0.0 |
| -0.2 | 0.0 |
| -0.5 | 0.0 |
| -0.8 | -1.0 |
| -1.0 | -1.0 |
| -1.2 | -1.0 |
| -1.5 | -2.0 |
| -1.8 | -2.0 |
| -2.0 | -2.0 |
切り捨て
切り捨てには、小数部分をごっそり取り除いて0に近い整数に丸める「0への丸め」と、その数以下で最大の整数に丸める「負の無限大への丸め」があります。
0への丸め(絶対値の切り捨て)
他のデータベースエンジンではtrunk関数として提供されていることがありますね。
これは、あきれ返るほど簡単なのです。
SELECT value, CAST(value AS INT) [trunc] FROM test;
| value | trunc |
|---|---|
| 2.0 | 2 |
| 1.8 | 1 |
| 1.5 | 1 |
| 1.2 | 1 |
| 1.0 | 1 |
| 0.8 | 0 |
| 0.5 | 0 |
| 0.2 | 0 |
| 0.0 | 0 |
| -0.2 | 0 |
| -0.5 | 0 |
| -0.8 | 0 |
| -1.0 | -1 |
| -1.2 | -1 |
| -1.5 | -1 |
| -1.8 | -1 |
| -2.0 | -2 |
式を見ても結果を見てもわかるとおり、この方法ではREAL型からINT型へ型が変換されます。
REAL型が必要なら改めてキャストしましょう。
負の無限大への丸め(床関数)
そもそも四捨五入は、「0.5を足して床関数を適用する」という方法で実装されることが多いです。
逆に言うと、床関数は「0.5を引いて四捨五入」すればよいのです。
先述した「端数が0.5のとき常に正の無限大へ丸める四捨五入」を元に書いてみます。
SELECT value, round(value - 0.5) + CASE WHEN value >= 0.5 OR value - round(value - 0.5) != 1.0 THEN 0.0 ELSE 1.0 END [floor] FROM test;
| value | floor |
|---|---|
| 2.0 | 2.0 |
| 1.8 | 1.0 |
| 1.5 | 1.0 |
| 1.2 | 1.0 |
| 1.0 | 1.0 |
| 0.8 | 0.0 |
| 0.5 | 0.0 |
| 0.2 | 0.0 |
| 0.0 | 0.0 |
| -0.2 | -1.0 |
| -0.5 | -1.0 |
| -0.8 | -1.0 |
| -1.0 | -1.0 |
| -1.2 | -2.0 |
| -1.5 | -2.0 |
| -1.8 | -2.0 |
| -2.0 | -2.0 |
切り上げ
切り上げには、絶対値の部分を切り上げする「無限大への丸め」と、その数以上で最小の整数に丸める「正の無限大への丸め」があります。
無限大への丸め(絶対値の切り上げ)
SELECT value, CAST(value AS INT) + CASE WHEN value > CAST(value AS INT) THEN 1 WHEN value < CAST(value AS INT) THEN -1 ELSE 0 END [round] FROM test;
| value | round |
|---|---|
| 2.0 | 2 |
| 1.8 | 2 |
| 1.5 | 2 |
| 1.2 | 2 |
| 1.0 | 1 |
| 0.8 | 1 |
| 0.5 | 1 |
| 0.2 | 1 |
| 0.0 | 0 |
| -0.2 | -1 |
| -0.5 | -1 |
| -0.8 | -1 |
| -1.0 | -1 |
| -1.2 | -2 |
| -1.5 | -2 |
| -1.8 | -2 |
| -2.0 | -2 |
0への丸めと同様、REAL型からINT型へ型が変換されます。
REAL型が必要なら改めてキャストしましょう。
正の無限大への丸め(天井関数)
床関数の逆なので、逆の方法を使うことになります。
先述した「端数が0.5のとき常に負の無限大へ丸める四捨五入」を元に書いてみます。
SELECT value, round(value + 0.5) - CASE WHEN value <= -0.5 OR round(value + 0.5) - value != 1.0 THEN 0.0 ELSE 1.0 END [ceil] FROM test;
| value | ceil |
|---|---|
| 2.0 | 2.0 |
| 1.8 | 2.0 |
| 1.5 | 2.0 |
| 1.2 | 2.0 |
| 1.0 | 1.0 |
| 0.8 | 1.0 |
| 0.5 | 1.0 |
| 0.2 | 1.0 |
| 0.0 | 0.0 |
| -0.2 | 0.0 |
| -0.5 | 0.0 |
| -0.8 | 0.0 |
| -1.0 | -1.0 |
| -1.2 | -1.0 |
| -1.5 | -1.0 |
| -1.8 | -1.0 |
| -2.0 | -2.0 |
うーん、自分で書いていて実に気持ち悪い。
floor関数一つあれば全部もっとわかりやすく実装できたのに。