SQL EXP and LN: the Exponential, the Natural Log, and Geometric Means

Стоит перемножить сотню зарплат в лоб, как любой числовой тип захлёбывается переполнением, а на больших наборах ещё и теряет младшие разряды. Спасает старый математический фокус: EXP возводит число Эйлера e в степень, LN берёт натуральный логарифм по основанию e, и эта пара взаимно обратных функций превращает умножение в сложение. На этом приёме держатся аккуратные средние геометрические, честные темпы роста и любая аналитика, где величины растягиваются на порядки. Ниже разберём, как обе функции ведут себя в PostgreSQL, где они выручают и об какой угол об них легче всего удариться.

Что считают EXP и LN

EXP(x) возвращает e^x, где e примерно равно 2.718281828 — то самое основание, к которому естественно тяготеют процессы непрерывного роста. LN(x) идёт в обратную сторону и отвечает на вопрос: в какую степень нужно возвести e, чтобы получить x. Проще всего убедиться, что одно отменяет другое, прогнав обе функции в одном запросе.

SELECT
  EXP(1)        AS e,        -- 2.7182818...
  EXP(0)        AS one,      -- 1
  LN(EXP(1))    AS back,     -- 1
  LN(1)         AS zero;     -- 0

Три свойства, на которые удобно опираться:

• EXP и LN гасят друг друга: LN(EXP(x)) = x и EXP(LN(x)) = x при x > 0.
• Логарифм сворачивает произведение в сумму: LN(a * b) = LN(a) + LN(b).
• В PostgreSQL результат имеет тип double precision, так что не удивляйтесь крошечным хвостам округления вроде 0.9999999998 там, где ждали ровную единицу.

Среднее геометрическое через exp(avg(ln))

Вернёмся к проблеме из первого абзаца. Среднее геометрическое по определению — это корень n-й степени из произведения, а перемножать значения напрямую нельзя: произведение вылетает за границы типа задолго до того, как вы доберётесь до корня. Приём exp(avg(ln(x))) обходит ловушку, не выходя из безопасного диапазона: логарифмируем каждое число, усредняем логарифмы обычным арифметическим AVG, а затем экспонентой возвращаемся к исходному масштабу. Сумма логов растёт линейно и никогда не разбухает так, как сырое произведение, — именно поэтому формула работает и на десяти строках, и на десяти миллионах.

SELECT
  dept,
  EXP(AVG(LN(salary))) AS geo_mean_salary
FROM employees
WHERE salary > 0
GROUP BY dept
ORDER BY geo_mean_salary DESC;

Среднее геометрическое глуше реагирует на выбросы, чем арифметическое, поэтому его и предпочитают для зарплат, цен и любых коэффициентов: один директорский оклад не утянет статистику в небеса так, как утянул бы обычный AVG. Тот же шаблон даёт типичный размер заказа в разрезе стран:

SELECT
  u.country,
  EXP(AVG(LN(o.amount))) AS typical_order
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.amount > 0
GROUP BY u.country;

Темпы роста и непрерывное начисление

Если логарифмы умеют складываться вместо умножения, грех не пустить это в дело для роста во времени. Разность логарифмов — это лог-доходность, непрерывный темп роста между двумя точками. LN(new / old) даёт величину, которую спокойно складываешь по периодам, а EXP от накопленной суммы возвращает общий множитель. Удобство в том, что лог-доходности симметричны: рост вдвое и падение вдвое дают противоположные по знаку, но равные по модулю значения, чего нельзя сказать о наивных процентах.

WITH monthly AS (
  SELECT
    date_trunc('month', created_at) AS m,
    SUM(amount)                     AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT
  m,
  revenue,
  LN(revenue / LAG(revenue) OVER (ORDER BY m)) AS log_growth
FROM monthly
ORDER BY m;

Просуммируйте все log_growth и примените EXP к итогу — получите общий множитель роста за весь отрезок, даже если помесячные цифры лихорадило вверх-вниз. Складывать проценты напрямую так нельзя, а логарифмы складываются честно.

Ловушка: LN(0) и LN от отрицательных

Вот где приём кусается, и кусается больно. LN определён только для строго положительных чисел. LN(0) уходит в минус бесконечность и в PostgreSQL роняет весь запрос ошибкой времени выполнения, а логарифм от отрицательного бессмыслен в принципе — отрицательных степеней e, дающих минус, не существует.

SELECT LN(0);    -- ERROR: cannot take logarithm of zero
SELECT LN(-5);   -- ERROR: cannot take logarithm of a negative number

Достаточно одного нулевого оклада, одной строки с нулевой суммой — и вся агрегация падает. Прикрывайтесь фильтром в WHERE или оборачивайте аргумент в NULLIF, чтобы единственный ноль превратился в NULL, который AVG тихо пропустит:

SELECT
  dept,
  EXP(AVG(LN(NULLIF(salary, 0)))) AS geo_mean
FROM employees
GROUP BY dept;

Готча: диалекты ведут себя по-разному, и на этом легко обжечься при переносе кода. PostgreSQL и Oracle бросают ошибку на LN(0), MySQL же молча возвращает NULL и для LN(0), и для LN(-1). ClickHouse идёт ещё дальше и отдаёт -inf или nan без всякой ошибки, отравляя последующие вычисления. Не закладывайтесь на поведение по умолчанию — чистите вход сами.

Смена основания и пара с LOG

Натуральный логарифм — это ещё и универсальный конструктор: логарифм по любому основанию собирается формулой LN(x) / LN(b). Результат тот же, что у встроенного LOG(b, x) в PostgreSQL, но формула выручает там, где нужного варианта функции под рукой нет.

SELECT
  LN(8) / LN(2)  AS log2_via_ln,   -- 3
  LOG(2, 8)      AS log2_builtin,  -- 3
  LOG(1000)      AS log10;         -- 3 in PostgreSQL

И снова диалекты расходятся — держите различия в голове:

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

Соберём главное в одну строку: EXP и LN взаимно обратны, exp(avg(ln)) даёт среднее геометрическое, LN(new/old) — это темп роста, а вход в LN обязан быть строго положительным. Запомните последнее как рефлекс — и фокус с логарифмами будет работать на вас, а не против.