SQLiteで端数処理(整数)

端数処理(丸め)と言えば四捨五入、切り捨て、切り上げですが、SQLiteには四捨五入(round関数)はあっても切り捨て・切り上げの関数がありません。
しかも、整数に対する四捨五入はないのです。

ユーザー定義関数で実装するというのもひとつの手ですが、今回はSQLiteの標準関数の組み合わせでどうにかしてみようと思います。
また、用途により扱いを変える必要のある負の数や5の処理についても考えてみます。

今回は、1の位で四捨五入して、10の倍数に丸めることを考えます。

1. 準備
2. 四捨五入
   1. 通常の四捨五入
   2. 端数が5のとき常に正の無限大へ丸める
   3. 端数が5のとき常に負の無限大へ丸める
   4. 端数が5のとき偶数(20の倍数)へ丸める(最近接偶数への丸め)
3. 切り捨て
   1. 0への丸め(絶対値の切り捨て)
   2. 負の無限大への丸め(床関数)
4. 切り上げ
   1. 無限大への丸め(絶対値の切り上げ)
   2. 正の無限大への丸め(天井関数)

準備

とりあえず、サンプルの動作確認のため、簡単なテーブルを作っておきます。

CREATE TABLE test (value INT NOT NULL);
INSERT INTO test VALUES( 20);
INSERT INTO test VALUES( 18);
INSERT INTO test VALUES( 15);
INSERT INTO test VALUES( 12);
INSERT INTO test VALUES( 10);
INSERT INTO test VALUES(  8);
INSERT INTO test VALUES(  5);
INSERT INTO test VALUES(  2);
INSERT INTO test VALUES(  0);
INSERT INTO test VALUES( -2);
INSERT INTO test VALUES( -5);
INSERT INTO test VALUES( -8);
INSERT INTO test VALUES(-10);
INSERT INTO test VALUES(-12);
INSERT INTO test VALUES(-15);
INSERT INTO test VALUES(-18);
INSERT INTO test VALUES(-20);

四捨五入

四捨五入といえばround関数ですが、整数の四捨五入は出来ないので一工夫が必要です。

通常の四捨五入

まずはround関数を素直に活用した方法です。
割るときに.0をつけて小数計算にしているのがポイントです。

SELECT value, round(value/10.0)*10 [round] FROM test;

結果は小数になっていますので、気になる人は整数型に適宜キャストすればよいでしょう。
-5や-15の扱いを見る限り、SQLiteでは絶対値に対して四捨五入を行っているようです。

普段は直感的にもわかりやすいこの方法で充分かと思われます。
しかし、それでは困るというケースもあるかと思います。

端数が5のとき常に正の無限大へ丸める

数直線で言うと、負の数でも例外なく「ちょうど真ん中は右側に寄せる」という処理です。
実装としては、逆に負の数で端数が5のときに例外的に10を足すようにしています。

SELECT value, round(value/10.0)*10 + CASE WHEN value >= 0 OR value - round(value/10.0)*10 != 5 THEN 0 ELSE 10 END [round] FROM test;

端数が5のとき常に負の無限大へ丸める

数直線で言うと、「ちょうど真ん中は左側に寄せる」という処理です。
四捨五入と呼べるか微妙だし、おそらくこれ単体では使い道のないものですが、後で使います。

SELECT value, round(value/10.0)*10 - CASE WHEN value <= 0 OR round(value/10.0)*10 - value != 5 THEN 0 ELSE 10 END [round] FROM test;

端数が5のとき偶数(20の倍数)へ丸める(最近接偶数への丸め)

特定の分野で行われる丸めですね。
「bankers' rounding」とか「JIS丸め」なんて呼ばれているようです。

SELECT value, round(value/10.0)*10 + CASE WHEN abs(value - round(value/10.0)*10) != 5 OR abs(round(value/10.0)*10 % 20) = 0 THEN 0 ELSE CASE WHEN value > 0 THEN -10 ELSE 10 END END [round] FROM test;

切り捨て

切り捨てには、端数部分をごっそり取り除いて0に近いほうに丸める「0への丸め」と、その数以下で最大の10の倍数に丸める「負の無限大への丸め」があります。
整数では余剰計算が役立ちます。

0への丸め(絶対値の切り捨て)

SELECT value, value - value % 10 [trunc] FROM test;

余りを引くという、ある意味非常に素直な方法です。
負の数では余りもマイナスになるので正の数と同じように引けば0方向へ丸まります。

負の無限大への丸め(床関数)

SELECT value, value - value % 10 - CASE WHEN value % 10 < 0 THEN 10 ELSE 0 END [floor] FROM test;

負の余剰があったときはさらに10を引くというわけです。

切り上げ

切り上げには、絶対値の部分を切り上げする「無限大への丸め」と、その数以上で最小の整数に丸める「正の無限大への丸め」があります。

無限大への丸め(絶対値の切り上げ)

SELECT value, value - value % 10 + CASE WHEN value % 10 > 0 THEN 10 WHEN value % 10 < 0 THEN -10 ELSE 0 END [round] FROM test;

切り捨てでは「余りを切り捨てる」という発想だったのが、「余りを切り捨てたら切り上げる」という考え方に変わっているわけです。

正の無限大への丸め(天井関数)

SELECT value, value - value % 10 + CASE WHEN value % 10 > 0 THEN 10 ELSE 0 END [ceil] FROM test;

シンプルになりました。
切り捨てのときにやっていた負の無限大への丸めの逆と考えれば納得ですね。

これでひとまず、SQLiteでの端数処理の話は終わりです。