EXP и LN в PostgreSQL: зачем логарифмы нужны в SQL-аналитике

EXP и LN на первый взгляд выглядят как функции из школьной математики, которые редко нужны в обычных SQL-запросах. Но в аналитике они внезапно становятся очень практичными.

С их помощью считают:

• среднее геометрическое;
• темпы роста;
• накопленную доходность;
• изменения метрик во времени;
• показатели, где значения нельзя просто перемножить напрямую.

Главная идея такая: логарифмы превращают умножение в сложение.

Это особенно полезно, когда произведение чисел становится слишком большим, слишком маленьким или теряет точность. Например, если перемножать сотни коэффициентов роста, зарплат, цен или конверсий, обычное произведение быстро выходит из удобного числового диапазона. А сумма логарифмов ведёт себя гораздо спокойнее.

Что делает EXP

EXP(x) возвращает число e в степени x.

Число e — это математическая константа, примерно равная 2.718281828. Её часто называют основанием натурального логарифма.

SELECT
    EXP(0) AS exp_zero,
    EXP(1) AS exp_one,
    EXP(2) AS exp_two;

Результат будет примерно таким:

exp_zero | exp_one  | exp_two
---------+----------+---------
1        | 2.71828  | 7.38906

Почему EXP(0) равно 1? Потому что любое ненулевое число в нулевой степени даёт 1.

Почему EXP(1) равно примерно 2.71828? Потому что это и есть само число e.

Проще сказать так:

EXP(x) = e^x

Что делает LN

LN(x) — это натуральный логарифм.

Он отвечает на вопрос:

В какую степень нужно возвести e, чтобы получить x?

Например:

SELECT
    LN(1) AS ln_one,
    LN(EXP(1)) AS ln_exp_one,
    LN(EXP(2)) AS ln_exp_two;

Результат:

ln_one | ln_exp_one | ln_exp_two
-------+------------+-----------
0      | 1          | 2

LN(1) равен 0, потому что e^0 = 1.

LN(EXP(1)) возвращает 1, потому что сначала мы возвели e в степень 1, а потом логарифм вернул нас обратно.

EXP и LN работают как обратные операции:

LN(EXP(x)) = x
EXP(LN(x)) = x

Но для второй формулы важно условие: x должен быть больше нуля, потому что натуральный логарифм определён только для положительных чисел.

Почему логарифмы полезны в SQL

Допустим, у нас есть несколько коэффициентов роста:

1.10
1.20
0.95
1.05

Если хотим получить общий множитель роста, можно перемножить их:

1.10 * 1.20 * 0.95 * 1.05

На четырёх числах всё нормально. Но если таких чисел тысячи или миллионы, прямое произведение может стать неудобным: слишком большим, слишком маленьким или неточным.

Логарифмы позволяют сделать иначе.

Есть важное свойство:

LN(a * b) = LN(a) + LN(b)

То есть вместо перемножения чисел можно сложить их логарифмы.

А потом вернуться обратно через EXP.

a * b = EXP(LN(a) + LN(b))

В SQL это даёт очень красивый приём:

SELECT EXP(SUM(LN(value))) AS product_value
FROM metrics
WHERE value > 0;

Так мы считаем произведение через сумму логарифмов.

На практике чаще используют не само произведение, а среднее геометрическое.

Среднее геометрическое: зачем оно нужно

Обычное среднее арифметическое считается так:

(a + b + c) / 3

Оно хорошо подходит для многих задач, но плохо ведёт себя на коэффициентах, процентах роста и значениях, которые отличаются в разы.

Например, есть три заказа:

100
100
10000

Обычное среднее будет сильно утянуто большим заказом.

(100 + 100 + 10000) / 3 = 3400

Но 3400 плохо описывает «типичный» заказ, если два заказа из трёх равны 100.

Среднее геометрическое реагирует на такие выбросы спокойнее. Оно считается через произведение и корень:

(a * b * c)^(1 / 3)

Но напрямую перемножать значения опасно, особенно на больших таблицах. Поэтому в SQL используют формулу:

EXP(AVG(LN(x)))

Она означает:

1. Взять логарифм каждого значения.
2. Посчитать обычное среднее этих логарифмов.
3. Вернуться в исходный масштаб через EXP.

Среднее геометрическое в PostgreSQL

Допустим, у нас есть таблица сотрудников, и мы хотим посчитать типичную зарплату по отделам.

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

Здесь важно условие:

WHERE salary > 0

LN нельзя применять к нулю и отрицательным числам. Поэтому мы заранее оставляем только положительные зарплаты.

Что делает запрос:

1. Группирует сотрудников по отделам.
2. Для каждой зарплаты считает LN(salary).
3. Считает среднее значение логарифмов через AVG.
4. Возвращает результат обратно через EXP.

Итог — среднее геометрическое по зарплатам внутри каждого отдела.

Для новичка можно запомнить так:

если нужно среднее для величин, которые перемножаются или растут коэффициентами, часто помогает EXP(AVG(LN(x))).

Пример с заказами

Среднее геометрическое удобно не только для зарплат. Например, можно посчитать типичный размер заказа по странам.

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
ORDER BY typical_order DESC;

Здесь мы считаем не обычный средний чек, а более устойчивую оценку типичного заказа.

Если в стране было много заказов на 20, 25, 30, а один заказ случайно оказался на 10000, обычный AVG резко вырастет. Среднее геометрическое будет спокойнее.

Это не значит, что оно всегда лучше арифметического. Это другой инструмент. Он особенно хорош для коэффициентов, цен, зарплат, заказов и метрик, где значения могут отличаться на порядки.

Темпы роста через LN

Логарифмы очень удобны для расчёта роста во времени.

Допустим, вы считаете выручку по месяцам.

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

Здесь LAG(revenue) берёт выручку предыдущего месяца.

Выражение:

LN(revenue / LAG(revenue) OVER (ORDER BY month_start))

считает логарифмический темп роста между текущим и предыдущим месяцем.

Если выручка выросла с 100 до 120, отношение будет 1.2.

Если упала со 120 до 100, отношение будет 0.8333.

Логарифм превращает эти изменения в удобную величину, которую можно складывать по периодам.

Почему лог-рост удобнее обычных процентов

Обычные проценты иногда обманывают интуицию.

Например, метрика выросла со 100 до 200. Это рост на 100%.

Потом она упала с 200 до 100. Это падение на 50%.

В итоге мы вернулись туда же, но проценты выглядят несимметрично: +100% и -50%.

У логарифмов такая пара будет симметричнее:

SELECT
    LN(200.0 / 100.0) AS up_growth,
    LN(100.0 / 200.0) AS down_growth;

Результат будет примерно таким:

up_growth | down_growth
----------+------------
0.693147  | -0.693147

Рост в два раза и падение в два раза дают одинаковые значения по модулю, но с разными знаками.

Именно поэтому лог-доходности любят в финансах и аналитике роста: их можно аккуратно складывать за разные периоды.

Общий рост через сумму логарифмов

Допустим, у нас есть помесячные коэффициенты роста:

1.10
0.95
1.20
1.05

Общий множитель роста — это их произведение.

Через логарифмы его можно посчитать так:

WITH growth_rates(rate) AS (
    VALUES
        (1.10),
        (0.95),
        (1.20),
        (1.05)
)
SELECT
    EXP(SUM(LN(rate))) AS total_multiplier
FROM growth_rates;

Смысл:

1. Каждый коэффициент превращаем в логарифм.
2. Логарифмы складываем.
3. Через EXP возвращаемся к обычному множителю.

Это особенно полезно, когда периодов много.

Главная ловушка: LN принимает только положительные числа

LN нельзя считать от нуля и отрицательных чисел.

SELECT LN(0);

В PostgreSQL такой запрос завершится ошибкой.

SELECT LN(-5);

Этот запрос тоже завершится ошибкой.

Математически это понятно: не существует такой вещественной степени, в которую нужно возвести положительное число e, чтобы получить отрицательное число.

Поэтому перед использованием LN всегда проверяйте вход.

Для зарплат:

WHERE salary > 0

Для заказов:

WHERE amount > 0

Для коэффициентов роста:

WHERE rate > 0

Это должно стать рефлексом:

перед LN(x) убедись, что x > 0.

Как защититься от нулей через NULLIF

Иногда нули лучше не фильтровать в WHERE, а превратить в NULL.

Например:

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

Что делает NULLIF(salary, 0)?

• если salary равна 0, вернёт NULL;
• если salary не равна 0, вернёт саму salary.

А агрегат AVG пропускает NULL.

То есть нулевые зарплаты не уронят запрос, а просто не попадут в расчёт.

Но с этим приёмом нужно быть аккуратным. Если ноль — это важное бизнес-значение, которое нельзя игнорировать, лучше не прятать его через NULLIF, а отдельно решить, что с ним делать.

Например, если сумма заказа равна нулю из-за промокода, исключать её из расчёта типичного заказа может быть неправильно. Всё зависит от задачи.

А что с отрицательными значениями

NULLIF(x, 0) защищает только от нуля. От отрицательных чисел он не спасает.

Если значения могут быть отрицательными, нужен фильтр:

SELECT
    EXP(AVG(LN(amount))) AS geo_mean_amount
FROM payments
WHERE amount > 0;

Или условие внутри CASE:

SELECT
    EXP(AVG(
        CASE
            WHEN amount > 0 THEN LN(amount)
        END
    )) AS geo_mean_amount
FROM payments;

Если amount <= 0, выражение CASE вернёт NULL, и AVG пропустит такую строку.

Такой вариант удобен, когда вы не хотите выбрасывать строки из всего запроса через WHERE, но хотите исключить их только из конкретного расчёта.

EXP тоже может переполниться

Обычно больше внимания достаётся LN, потому что он падает на нуле и отрицательных числах. Но EXP тоже не всемогущ.

Если передать в EXP слишком большое число, результат может оказаться слишком большим для типа данных.

SELECT EXP(1000);

В зависимости от типа и контекста такой расчёт может привести к переполнению.

На практике это означает: если сумма логарифмов получилась огромной, обратное преобразование через EXP тоже может стать проблемой.

Для обычной аналитики вроде средних геометрических и темпов роста это встречается нечасто, но при работе с очень большими числами или длинными произведениями об этом стоит помнить.

Небольшие хвосты округления — это нормально

Если вы работаете с числами с плавающей точкой, не удивляйтесь маленьким хвостам округления.

Например, математически мы ожидаем ровно 1:

SELECT LN(EXP(1.0)) AS result;

Но в вычислениях с плавающей точкой иногда можно увидеть значение вроде:

0.9999999999999999

или:

1.0000000000000002

Это не ошибка PostgreSQL, а обычное свойство компьютерной арифметики.

Если вы сравниваете такие значения, не стоит проверять точное равенство. Лучше сравнивать с небольшой погрешностью.

SELECT
    ABS(LN(EXP(1.0)) - 1.0) < 0.000001 AS is_close;

Результат:

is_close
--------
true

Смена основания логарифма

LN — это логарифм по основанию e.

Но иногда нужен логарифм по другому основанию. Например, по основанию 2 или 10.

Логарифм по любому основанию можно выразить через LN:

log_b(x) = LN(x) / LN(b)

Например, логарифм 8 по основанию 2:

SELECT LN(8) / LN(2) AS log2_value;

Результат:

log2_value
----------
3

Почему 3? Потому что:

2^3 = 8

В PostgreSQL для этого также есть LOG.

SELECT
    LN(8) / LN(2) AS log2_via_ln,
    LOG(2, 8) AS log2_builtin;

Оба выражения дадут 3.

LOG и LN: не перепутайте

В PostgreSQL LN(x) — натуральный логарифм.

А LOG(x) в PostgreSQL — десятичный логарифм, то есть логарифм по основанию 10.

SELECT
    LN(EXP(1)) AS natural_log,
    LOG(1000) AS decimal_log;

Результат:

natural_log | decimal_log
------------+------------
1           | 3

LOG(1000) равен 3, потому что:

10^3 = 1000

Если нужно явно указать основание, используйте форму:

SELECT LOG(2, 8) AS log2_value;

Результат:

log2_value
----------
3

Отличия в других СУБД

С логарифмами нужно быть осторожным при переносе SQL между базами.

В PostgreSQL:

SELECT LN(10);

— натуральный логарифм.

SELECT LOG(1000);

— десятичный логарифм.

SELECT LOG(2, 8);

— логарифм 8 по основанию 2.

В MySQL поведение другое: LOG(x) — это натуральный логарифм, то есть близко к LN(x). Для десятичного логарифма есть LOG10(x), а для основания 2 — LOG2(x).

В ClickHouse функция log(x) тоже означает натуральный логарифм. Для других оснований есть отдельные функции вроде log2 и log10.

Разница неприятная именно потому, что названия похожи. Один и тот же LOG(x) в разных СУБД может означать разные вещи.

Поэтому при переносе запросов проверяйте:

• что означает LOG(x);
• есть ли отдельная LN(x);
• в каком порядке передаются аргументы в LOG(base, value);
• что происходит с LN(0) и LN(-1).

Разное поведение на плохих входах

Особенно важно проверить нули и отрицательные числа.

В PostgreSQL LN(0) и LN(-5) приводят к ошибке.

В некоторых других СУБД результат может быть не ошибкой, а NULL, -inf или nan.

Это опасно: запрос может не упасть, но дальше расчёты станут мусорными.

Например, если где-то появился nan, он может протащиться через следующие вычисления и испортить итоговый отчёт. Поэтому лучше не полагаться на поведение конкретного движка, а явно чистить вход:

WHERE value > 0

или:

CASE
    WHEN value > 0 THEN LN(value)
END

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

Практический пример: рост выручки по месяцам

Соберём полный пример.

Есть таблица заказов:

id | created_at          | amount | status
---+---------------------+--------+--------
1  | 2024-01-10 12:00:00 | 1000   | paid
2  | 2024-01-15 09:00:00 | 2000   | paid
3  | 2024-02-03 18:00:00 | 4500   | paid
4  | 2024-03-20 13:00:00 | 3000   | paid

Хотим посчитать выручку по месяцам и логарифмический рост к предыдущему месяцу.

WITH monthly AS (
    SELECT
        date_trunc('month', created_at) AS month_start,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
)
SELECT
    month_start,
    revenue,
    LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
    CASE
        WHEN LAG(revenue) OVER (ORDER BY month_start) > 0
             AND revenue > 0
        THEN LN(revenue / LAG(revenue) OVER (ORDER BY month_start))
    END AS log_growth
FROM monthly
ORDER BY month_start;

Здесь мы защищаемся от деления на ноль и от логарифма неположительного числа.

Да, выражение получилось длиннее. Но зато запрос не развалится на месяце, где предыдущая выручка равна нулю.

В реальном проекте такой расчёт часто выносят в отдельный CTE, чтобы не повторять LAG.

WITH monthly AS (
    SELECT
        date_trunc('month', created_at) AS month_start,
        SUM(amount) AS revenue
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
),
with_prev AS (
    SELECT
        month_start,
        revenue,
        LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue
    FROM monthly
)
SELECT
    month_start,
    revenue,
    prev_revenue,
    CASE
        WHEN prev_revenue > 0 AND revenue > 0
        THEN LN(revenue / prev_revenue)
    END AS log_growth
FROM with_prev
ORDER BY month_start;

Так читать гораздо приятнее: сначала считаем выручку, потом добавляем предыдущий месяц, потом считаем рост.

Практический пример: средний коэффициент конверсии

Допустим, у нас есть коэффициенты изменения конверсии по дням.

day        | conversion_multiplier
-----------+----------------------
2024-01-01 | 1.05
2024-01-02 | 0.98
2024-01-03 | 1.12
2024-01-04 | 1.01

Обычное среднее может быть не лучшим выбором, потому что коэффициенты перемножаются. Для них часто уместнее среднее геометрическое.

SELECT
    EXP(AVG(LN(conversion_multiplier))) AS avg_multiplier
FROM daily_conversion
WHERE conversion_multiplier > 0;

Если результат равен, например, 1.04, это можно читать как «типичный дневной множитель роста около 1.04», то есть примерно плюс 4% в день.

Чтобы получить процент, можно вычесть 1 и умножить на 100.

SELECT
    (EXP(AVG(LN(conversion_multiplier))) - 1) * 100 AS avg_growth_percent
FROM daily_conversion
WHERE conversion_multiplier > 0;

Когда не стоит использовать EXP и LN

EXP и LN не нужны в каждом отчёте.

Если вы считаете обычную среднюю сумму заказа, обычное среднее время доставки или обычное количество пользователей, чаще достаточно AVG, SUM, COUNT.

Логарифмы полезны, когда:

• значения перемножаются;
• нужны коэффициенты роста;
• важен рост во времени;
• значения отличаются на порядки;
• прямое произведение может переполниться;
• нужно среднее геометрическое.

Если же задача простая и арифметическая, не усложняйте запрос без необходимости.

Главное из статьи

EXP(x) возвращает e в степени x.

LN(x) возвращает натуральный логарифм числа x.

EXP и LN — обратные функции: одна возвращает из логарифмического масштаба, другая переводит в него.

Логарифм превращает произведение в сумму: это помогает считать произведения, темпы роста и среднее геометрическое.

Среднее геометрическое в SQL обычно считают формулой:

EXP(AVG(LN(value)))

Для темпа роста между двумя значениями часто используют:

LN(new_value / old_value)

LN можно применять только к строго положительным числам. Перед LN(value) проверяйте, что value > 0.

Нули и отрицательные значения нужно фильтровать через WHERE, CASE или аккуратно обрабатывать через NULLIF.

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

Главное правило: если в аналитике появляются произведения, коэффициенты и рост во времени, вспоминайте пару LN и EXP. Она позволяет считать аккуратно там, где прямое умножение быстро становится неудобным или опасным.