Integer Division in SQL: DIV, MOD, and the Cast Trap

Целочисленное деление возвращает только частное и отбрасывает дробный хвост: 7 / 2 даёт 3, а не 3.5. В SQL это не отдельный оператор, а поведение обычного /, которое включается, когда оба операнда целые, и которым удобно раскладывать строки по корзинам, нарезать счётчики на блоки или превращать секунды в минуты вместе с остатком. Звучит как школьная арифметика, но именно здесь тихо рассыпается аналитика: достаточно одного лишнего приведения типа, и то же 7 / 2 внезапно становится 3.5. Дальше эта половинка протекает в отчёты, в раскладку по корзинам, в шардинг по хешу — и ловить её приходится уже постфактум.

Коварство в том, что результат целочисленного деления зависит не от вашего намерения, а от типов операндов и от конкретной СУБД. В PostgreSQL / усекает дробь только когда оба числа целые; в MySQL тот же / всегда отдаёт дробь, а за усечением идут к отдельному оператору DIV; в ClickHouse целое частное даёт функция intDiv. Поэтому один и тот же запрос на трёх движках выдаёт три разных ответа, и переносить его вслепую опасно.

Ниже разберём три вещи, на которых чаще всего спотыкаются: как / ведёт себя с отрицательными числами (усечение к нулю, а не floor), как нечаянное приведение к numeric или float ломает деление, и как функция div(a, b) вместе с остатком mod даёт предсказуемый и переносимый результат.

Деление двух целых в PostgreSQL

В PostgreSQL оператор / ориентируется на типы операндов. Если оба целые, результат тоже целый, а дробная часть отсекается в сторону нуля (именно усечение, а не floor):

SELECT 7 / 2;     -- 3
SELECT -7 / 2;    -- -3, not -4
SELECT 100 / 30;  -- 3

Это поведение приходится очень кстати, когда данные надо разложить по корзинам. Скажем, сгруппируем пользователей блоками по 100 идентификаторов:

SELECT
  id / 100 AS bucket,
  count(*) AS users
FROM users
GROUP BY id / 100
ORDER BY bucket;

А вот и первые грабли: усечение идёт к нулю, а не вниз. На положительных числах разницы не видно, но -7 / 2 отдаёт -3, тогда как математический floor выдал бы -4. Если вам нужно именно округление вниз для отрицательных значений, не полагайтесь на / — вызывайте floor() явно и спите спокойно.

Ловушка приведения типов

Самый частый промах — нечаянно превратить операнд в число с плавающей точкой. Одного numeric или ::float хватает, чтобы деление поплыло в дробь:

SELECT 7 / 2;            -- 3   (int / int)
SELECT 7 / 2.0;          -- 3.5 (int / numeric)
SELECT 7::float / 2;     -- 3.5
SELECT amount / 2 FROM orders;  -- amount NUMERIC -> fractional result

Коварство в том, что литерал 2.0 или колонка типа NUMERIC выглядят безобидно, а тип результата тихо подтягивается к дробному. Если amount объявлен как NUMERIC, то amount / 2 уже дробное по умолчанию. Хотите получить число «полных половинок» — усекайте результат осознанно, через DIV или floor(), а не надейтесь на интуицию операндов.

Функция DIV(a, b)

PostgreSQL держит для таких случаев функцию div(a, b) — она всегда выполняет целочисленное деление и возвращает частное, усечённое к нулю, какими бы ни были типы аргументов:

SELECT div(7, 2);        -- 3
SELECT div(7.9, 2.0);    -- 3   (works on numeric too)
SELECT div(-7, 2);       -- -3

Это надёжнее голого /, потому что результат не зависит от того, целые операнды или дробные — намерение записано прямо в коде. Особенно удобно для денежных колонок типа NUMERIC, где / подвёл бы. Посчитаем, сколько целых сотен помещается в каждый заказ:

SELECT
  id,
  amount,
  div(amount, 100) AS full_hundreds
FROM orders;

Оператор DIV в MySQL

Здесь начинается главная развилка переносимости. В MySQL / всегда возвращает дробь, даже для пары целых: SELECT 7 / 2 отдаёт 3.5. За целочисленным делением тут ходят к отдельному оператору DIV:

-- MySQL
SELECT 7 DIV 2;     -- 3
SELECT 100 DIV 30;  -- 3
SELECT id DIV 100 AS bucket FROM users;

В ClickHouse уживаются обе формы: функция intDiv(7, 2) для целого частного и привычный 7 / 2 для деления в плавающей точке. Вывод напрашивается сам: один и тот же 7 / 2 на трёх движках даёт три разных ответа, и про это стоит помнить, когда вы переносите запросы между СУБД.

DIV вместе с MOD: частное и остаток

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

SELECT
  amount,
  div(amount, 100)  AS whole_hundreds,
  mod(amount, 100)  AS remainder
FROM orders;

Классика жанра — развернуть секунды в минуты и секунды одним запросом, без процедур и временных колонок:

SELECT
  id,
  extract(epoch FROM (now() - created_at))::int AS age_seconds,
  div(extract(epoch FROM (now() - created_at))::int, 60) AS minutes,
  mod(extract(epoch FROM (now() - created_at))::int, 60) AS seconds
FROM orders;

Ещё один ход — раскидать сотрудников по сменам по кругу (round-robin), опираясь на остаток:

SELECT
  name,
  dept,
  mod(id, 3) AS shift   -- 0, 1, 2
FROM employees
ORDER BY shift, name;

И напоследок самые скользкие грабли: знак результата mod в PostgreSQL повторяет знак делимого, поэтому mod(-7, 3) даёт -1, а не 2, как привыкли в математике. Для round-robin это безобидно, а вот для хеш-шардинга отрицательный остаток уведёт строку не в ту корзину. Нужен гарантированно неотрицательный остаток — оборачивайте: mod(mod(x, n) + n, n).

Соберём суть в одну строку: в PostgreSQL / усекает только когда оба операнда целые, в MySQL для того же эффекта нужен оператор DIV, а функция div(a, b) остаётся самым переносимым и предсказуемым способом получить целое частное в обоих мирах.