Числовые функции 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;

Шесть функций — одна строка результата. Дальше по странице разберём, для чего нужно каждое семейство и на какие подводные камни стоит обратить внимание.

ROUND — то, чем будете пользоваться чаще всего

ROUND(value, digits) округляет число до заданного количества знаков после запятой. Второй аргумент необязательный — если его опустить, получите округление до ближайшего целого, но всё равно в виде числа с плавающей точкой:

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.0, а не 3. Если нужно именно целое число, используйте CAST(ROUND(x) AS INTEGER) или просто CAST(x AS INTEGER) для отбрасывания дробной части.
• SQLite применяет правило «округление от нуля»: 2.5 округляется до 3, а -2.5 — до -3. В некоторых СУБД работает банковское округление (до ближайшего чётного), но в SQLite такого нет.
• Аргумент digits может быть отрицательным: ROUND(1234.5, -2) округлит до сотен и даст 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 разница получится в целую единицу. Выбирайте то, что реально нужно по логике.

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 — настоящая рабочая лошадка: пригодится, когда надо посчитать «насколько далеко друг от друга эти два значения». SIGN встречается реже, но удобен, если нужно раскидать строки по направлению (приход или расход, прибыль или убыток) без громоздкого CASE.

CEIL, FLOOR и TRUNC в SQLite

Эти функции возвращают целые значения, но без округления к ближайшему. 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 расходятся, и выбор не той функции — классический источник ошибки на единицу.

CEILING — это просто синоним CEIL. Пишите так, как вам удобнее читать.

Целочисленное деление в SQLite — главная ловушка

Формально это даже не функция, а оператор /, но новичков он сбивает с толку чаще, чем все математические функции 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, как всё выражение становится вещественным. Лечится это просто: хотя бы один операнд должен быть с плавающей точкой — либо пишем 2.0 вместо 2, либо явно кастуем через CAST.

Особенно неприятно это вылезает при работе со столбцами: 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.
• LOG(x) в SQLite — это логарифм по основанию 10. LN(x) — натуральный логарифм. LOG(b, x) с двумя аргументами — логарифм по основанию b. (В отличие от многих языков, где log означает натуральный логарифм, тут победила SQL-конвенция.)
• SQRT от отрицательного числа возвращает NULL, а не ошибку.
• POWER(0, 0) по соглашению возвращает 1.

Всё это пригодится при расчёте сложных процентов, нормализации в децибелы, вычислении расстояний — везде, где встречается геометрия или экспоненциальная математика.

Функция RANDOM и RANDOMBLOB в SQLite

RANDOM() возвращает знаковое 64-битное целое число в полном диапазоне значений:

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 (потому что RANDOM() возвращает знаковое число) и применяем %. Если же нужно вещественное число от 0 до 1, делим на максимальное 64-битное целое. Встроенной функции RAND(), выдающей значение от 0 до 1, в SQLite нет — её придётся собрать самому.

RANDOMBLOB(n) возвращает n байт случайных данных — удобно для генерации сессионных токенов или тестовых данных. В связке с HEX() получаем уже читаемую строку:

SELECT HEX(RANDOMBLOB(8)) AS token;

Каждый вызов возвращает новое значение. Не рассчитывайте, что RANDOM() дважды подряд выдаст одно и то же число даже в пределах одной строки — каждый вызов независим от остальных.

Собираем всё вместе

Небольшой практический пример: посчитаем расстояния и округлим цены в таблице товаров.

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: точка с нулём превращает деление в вещественное, а ROUND уже доводит результат до двух знаков после запятой. Без .0 выражение 1299 / 100 дало бы 12, а вовсе не 12.99 — это и есть классическое целочисленное деление в SQLite.

Что дальше: дата и время

С математикой числовые функции SQLite справляются отлично. А вот для дат и времени нужен отдельный набор инструментов — SQLite хранит их как текст, вещественное или целое число и предоставляет небольшой, но вполне рабочий набор функций для разбора, форматирования и арифметики над датами. Об этом и поговорим в следующей главе.