SQLiteの数値関数まとめ：ROUND・ABS・CEIL・FLOOR・四則演算

SQLite には意外と数学関数がそろっている

SQLite はミニマルさで有名ですが、数値系の関数は一通り揃っています。四捨五入、絶対値、天井・床関数、べき乗、平方根、対数、三角関数、乱数まで全部入りです。数学関数の大半は SQLite 3.35(2021 年)で追加されたので、Python や Node に同梱されているもの、ブラウザの WebSQL の流れを汲むもの、公式 CLI など、最近の環境ならどれでもそのまま使えます。

詳しく見ていく前に、まずはざっと雰囲気を掴んでみましょう。

SELECT
    ROUND(3.14159, 2)   AS rounded,
    ABS(-42)            AS absolute,
    CEIL(2.1)           AS ceiling,
    FLOOR(2.9)          AS floor_val,
    POWER(2, 10)        AS two_to_ten,
    SQRT(144)           AS root;

6つの関数、結果は1行。ここから先は、それぞれの関数が何のためにあるのか、そしてハマりどころをひとつずつ見ていきます。

ROUND: 一番出番が多い関数

ROUND(value, digits) は、指定した小数点以下の桁数で四捨五入します。第2引数は省略可能で、省略すると最も近い整数に丸められます（ただし戻り値は浮動小数点のままです）。

SELECT
    ROUND(3.14159)       AS no_digits,
    ROUND(3.14159, 2)    AS two_places,
    ROUND(3.14159, 4)    AS four_places,
    ROUND(2.5)           AS half_up,
    ROUND(-2.5)          AS half_down;

ここで押さえておきたいポイントをいくつか挙げます。

• ROUND(3.14159) は 3 ではなく 3.0 を返します。整数で受け取りたいときは CAST(ROUND(x) AS INTEGER) を使うか、単純に切り捨てたいだけなら CAST(x AS INTEGER) を使ってください。
• SQLite の丸めは「ゼロから遠ざける方向の四捨五入(round half away from zero)」です。つまり 2.5 は 3 に、-2.5 は -3 に丸められます。一部のデータベースは銀行家丸め(round half to even)を採用していますが、SQLite は採用していません。
• digits 引数には負の数も指定できます。たとえば ROUND(1234.5, -2) は 100 の位で丸められて 1200 になります。

実務では、金額表示のための ROUND(price, 2) がいちばん出番が多いはずです。

ROUND と CAST は別物

丸めるつもりで CAST(x AS INTEGER) を使ってしまい、痛い目を見るケースはよくあります。

SELECT
    CAST(2.9 AS INTEGER)   AS cast_truncates,
    ROUND(2.9)             AS round_rounds,
    CAST(-2.9 AS INTEGER)  AS cast_negative,
    ROUND(-2.9)            AS round_negative;

CAST はゼロ方向への切り捨てで、小数部分をバッサリ捨てるだけです。一方 ROUND は四捨五入で最も近い整数に丸めます。2.9 だと結果は丸ごと 1 違ってきます。自分が欲しい挙動はどっちなのか、ちゃんと選んで使い分けましょう。

ABS と SIGN で絶対値・符号を扱う

ABS(x) は絶対値を返します。SIGN(x) は符号に応じて -1、0、1 のいずれかを返します。

SELECT
    ABS(-17)    AS abs_neg,
    ABS(17)     AS abs_pos,
    ABS(0)      AS abs_zero,
    SIGN(-17)   AS sign_neg,
    SIGN(0)     AS sign_zero,
    SIGN(42.5)  AS sign_pos;

ABS は何かと出番の多い関数で、「2つの値がどれだけ離れているか」を求めるクエリで重宝します。SIGN のほうは使う場面こそ限られますが、行を方向ごとに分類したいとき（入金と出金、利益と損失など）に、わざわざ CASE を書かずに済むので便利です。

CEIL、FLOOR、TRUNC の使い方

これらは四捨五入ではなく、整数寄りの値を返してくれる関数です。CEIL は常に切り上げ、FLOOR は常に切り下げ、TRUNC は常にゼロ方向へ丸めます。

SELECT
    CEIL(2.1)    AS ceil_pos,
    CEIL(-2.1)   AS ceil_neg,
    FLOOR(2.9)   AS floor_pos,
    FLOOR(-2.9)  AS floor_neg,
    TRUNC(2.9)   AS trunc_pos,
    TRUNC(-2.9)  AS trunc_neg;

負の数の扱いには注意してください。FLOOR(-2.9) は -3（ゼロから離れる方向）ですが、TRUNC(-2.9) は -2（ゼロに近づく方向）になります。負の数では FLOOR と TRUNC の挙動が食い違うので、選択を誤ると典型的な off-by-one バグの原因になります。

CEILING は CEIL のエイリアスです。読みやすい方を使えば OK です。

sqlite の整数除算ではここに注意

これは関数ではなく / 演算子の話ですが、実は数値関数のどれよりも初心者が引っかかりやすいポイントです。

SELECT
    5 / 2           AS int_div,
    5 / 2.0         AS real_div,
    5.0 / 2         AS also_real,
    CAST(5 AS REAL) / 2  AS explicit_cast;

両辺が整数同士の場合、SQLite は整数除算を行い、小数点以下を切り捨てます。片方でも REAL になった瞬間、式全体が実数として扱われます。対策はシンプルで、少なくとも一方を浮動小数点数にすればOKです。2 の代わりに 2.0 と書くか、明示的にキャストしてあげましょう。

これが特に厄介なのがカラム参照のとき。total_cents / 100 は整数を返してしまいます。本当に欲しかったドル金額を得るには total_cents / 100.0 と書く必要があります。

MOD関数と % 演算子による剰余計算

MOD(x, y) は x / y の余りを返します。% 演算子もまったく同じ動きをします:

SELECT
    MOD(17, 5)   AS mod_func,
    17 % 5       AS mod_op,
    MOD(-17, 5)  AS mod_neg,
    MOD(17, 0)   AS mod_zero;

MOD(17, 5) も 17 % 5 も結果は 2 です。ただし注意したいのが、SQLite ではゼロ除算で剰余を取ると NULL が返ってくる点。エラーにはなりません。多くの言語とは違う挙動なので、気になる場合は事前に除数をチェックするか、CASE WHEN y = 0 THEN ... END で囲んでおきましょう。

関数版と演算子版はどちらを使っても構いません。短く書ける % の方が好まれることが多いですね。

POWER、SQRT、EXP、LOG

べき乗や平方根を扱うときに使う関数です。

SELECT
    POWER(2, 10)    AS two_to_the_ten,
    POW(2, 0.5)     AS sqrt_via_power,
    SQRT(2)         AS sqrt_two,
    EXP(1)          AS e,
    LOG(100)        AS log10_100,
    LN(EXP(1))      AS ln_of_e;

ハマりどころをいくつか挙げておきます。

• POW は POWER のエイリアスです。
• SQLite の LOG(x) は 常用対数（底10） です。自然対数は LN(x)。引数を2つ渡す LOG(b, x) は底 b の対数になります（多くの言語では log が自然対数なので注意。ここは SQL の流儀が勝った形です）。
• SQRT に負の値を渡すとエラーではなく NULL が返ります。
• POWER(0, 0) は慣例的に 1 を返します。

このあたりは、複利計算、デシベルへの正規化、距離計算など、指数や幾何学が絡む場面で役に立ちます。

RANDOM と RANDOMBLOB で乱数生成

RANDOM() は符号付き64bit整数の全範囲から、ランダムな値を返します。

SELECT
    RANDOM()                          AS raw_random,
    ABS(RANDOM()) % 100               AS zero_to_99,
    ABS(RANDOM()) % 100 + 1           AS one_to_100,
    ABS(RANDOM() / 9223372036854775807.0) AS unit_interval;

範囲付きの数値を取り出したいときは、RANDOM() が符号付きなので ABS で囲んでから % で剰余を取ります。0〜1 の実数が欲しい場合は、64bit 整数の最大値で割るのが定番です。SQLite には 0〜1 を返す RAND() のような組み込み関数はないので、自分で組み立てる形になります。

RANDOMBLOB(n) は n バイトのランダムなデータを返してくれるので、セッショントークンの生成やテストデータの作成に便利です。表示可能な文字列にしたいときは HEX() と組み合わせます。

SELECT HEX(RANDOMBLOB(8)) AS token;

呼び出すたびに新しい値が返ります。RANDOM() が同じ行の中で同じ数を返すと思わない方がいいでしょう。1つの式の中であっても、呼び出しごとに独立した値になります。

実践: 組み合わせて使ってみる

簡単なサンプルとして、商品テーブルで距離を計算したり、価格を丸めたりしてみましょう。

CREATE TABLE products (id INTEGER, name TEXT, price_cents INTEGER, weight_g REAL);
INSERT INTO products VALUES
    (1, 'マグカップ', 1299, 350.5),
    (2, 'ノート',      850, 180.2),
    (3, 'ペン',        199,  12.0);

SELECT
    name,
    ROUND(price_cents / 100.0, 2)  AS price_dollars,
    CEIL(weight_g / 100)           AS weight_buckets_100g,
    POWER(weight_g, 0.5)           AS sqrt_weight
FROM products;

price_cents / 100.0 の .0 がポイントです。これを付けることで割り算が実数として扱われ、ROUND で小数点以下2桁にフォーマットできます。もし .0 を付けずに 1299 / 100 と書くと、結果は 12.99 ではなく 12 になってしまいます。

次は日付と時刻

数値計算は数値関数の出番ですが、日付や時刻には専用のツールが必要です。SQLite は日時を text、real、integer のいずれかで保存し、それらを解析・フォーマット・計算するためのコンパクトながら実用的な関数群を用意しています。次の章では、それらを順に見ていきましょう。