SQL LOG: Base-10 and Arbitrary-Base Logarithms, and the MySQL Natural-Log Trap

LOG в PostgreSQL считает логарифм числа — то есть степень, в которую нужно возвести основание, чтобы получить аргумент. У функции два режима, и переключает их само число аргументов: LOG(x) берёт логарифм по основанию 10, а LOG(b, x) — по любому основанию, какое укажете первым аргументом. Пригождается это там, где величины разбегаются на порядки: лог-шкалы для графиков, корзины «порядка величины», подсчёт числа цифр.

Звучит безобидно, но ровно на форме LOG(x) прячется одна из самых коварных несовместимостей между СУБД: в PostgreSQL это десятичный логарифм, а в MySQL — натуральный, по основанию e. Один и тот же вызов молча возвращает разные числа, и к этой развилке мы ещё вернёмся отдельным разделом.

Две формы LOG в PostgreSQL

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

SELECT
  LOG(100)    AS a,   -- 2   (10^2 = 100)
  LOG(1000)   AS b,   -- 3   (10^3 = 1000)
  LOG(2, 8)   AS c,   -- 3   (2^3 = 8)
  LOG(2, 1024) AS d;  -- 10  (2^10 = 1024)

Что стоит держать в голове:

• LOG(x) — это логарифм по основанию 10, а не натуральный. Запомните этот факт намертво, через абзац станет ясно почему.
• LOG(b, x) читается дословно: «логарифм x по основанию b».
• Результат имеет тип numeric, поэтому точность щедрая, но за неё приходится платить: считается он медленнее, чем double precision.
• Аргумент обязан быть строго положительным. LOG(0) и LOG(-5) не возвращают вежливый NULL, а валят запрос ошибкой домена.

Главная ловушка: LOG в MySQL — это натуральный логарифм

Вот то самое больное место, о котором я предупреждал, — и спотыкаются о него чаще всего при переезде запросов с одного движка на другой. В MySQL LOG(x) с единственным аргументом считает натуральный логарифм, по основанию e, а вовсе не десятичный. Один и тот же вызов выдаёт разные числа в разных СУБД, причём молча — никакой ошибки, просто другой результат.

-- PostgreSQL: LOG(100) = 2     (base 10)
-- MySQL:      LOG(100) = 4.605  (base e, natural log)
SELECT LOG(100) AS surprise;

Лекарство одно — не полагаться на умолчания, а проговаривать намерение явно:

• Десятичный логарифм: в PostgreSQL это LOG(x), в MySQL — LOG10(x). Функция LOG10 есть в обоих движках и трактуется однозначно, так что выбирайте именно её.
• Натуральный логарифм: берите LN(x) — он означает основание e и в PostgreSQL, и в MySQL, без разночтений.
• Произвольное основание: и тут, и там работает LOG(b, x) с одинаковым порядком аргументов, поэтому такой вызов переносится без правок.

Грабли: никогда не доверяйте однострочному LOG(x) в коде, который живёт сразу на нескольких движках. Пишите LOG10(x) для основания 10 и LN(x) для основания e — и смысл будет одинаков везде, куда бы запрос ни уехал.

Лог-шкалы и подсчёт цифр

Зачем всё это на практике? Логарифмы сжимают величины, которые разлетаются на порядки: суммы заказов, число событий, размеры файлов. На лог-шкале расстояние от «10 до 100» ровно такое же, как от «100 до 1000», — и хвост из редких гигантских значений перестаёт расплющивать всю картину. Удобно и для гистограмм, и для группировки по порядку величины.

SELECT
  FLOOR(LOG(amount))::int AS magnitude,   -- 0, 1, 2, 3 ...
  COUNT(*)                AS orders
FROM orders
WHERE status = 'paid' AND amount > 0
GROUP BY FLOOR(LOG(amount))::int
ORDER BY magnitude;

Заказы тут раскладываются по корзинам порядка величины: 0 — это 1..9, 1 — 10..99, 2 — 100..999, и так далее. Из той же логики растёт ещё один полезный фокус — подсчёт числа цифр в целом: цифр ровно FLOOR(LOG10(n)) + 1.

SELECT
  id,
  salary,
  FLOOR(LOG(salary))::int + 1 AS digits   -- digits in the salary
FROM employees
WHERE salary > 0
ORDER BY salary DESC;

Грабли: всегда отсекайте неположительные значения — через WHERE amount > 0 или NULLIF. Хватит одного нуля или отрицательного возврата, чтобы весь запрос рухнул с ошибкой домена, причём упадёт он на проде, а не на ваших аккуратных тестовых данных. Подстраховка проста: LOG(NULLIF(amount, 0)) подменит ноль на NULL вместо аварии.

Связь с LN и смена основания

LOG и LN — близкая родня: оба берут логарифм, расходятся лишь основанием. LN(x) — основание e, LOG(x) — основание 10, LOG(b, x) — основание b. А любую другую базу легко собрать из них по формуле смены основания: делите логарифм аргумента на логарифм нужного основания, и неважно, в какой базе считаете оба.

SELECT
  LN(100)            AS natural_log,   -- 4.605
  LOG(100)           AS log10,         -- 2
  LN(8) / LN(2)      AS log2_via_ln,   -- 3
  LOG(2, 8)          AS log2_builtin;  -- 3

Как обстоят дела в разных диалектах:

• PostgreSQL: LOG(x) — основание 10; LOG(b, x) — любое; LN(x) — натуральный.
• MySQL: LOG(x) — натуральный (основание e); для основания 10 нужен LOG10(x); есть и готовый LOG2(x).
• ClickHouse: log(x) и ln(x) — натуральные; для основания 10 берите log10(x), для основания 2 — log2(x). Формы log(b, x) тут нет вовсе, так что произвольную базу собирайте руками: log(x) / log(b).

Унесите с собой три вещи. В PostgreSQL LOG(x) — это основание 10. В MySQL тот же LOG(x) — натуральный логарифм. А чтобы между ними не путаться, пишите LOG10 и LN явно — тогда запрос будет означать одно и то же в любом движке.