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

EXP raises Euler's number e to a power, and LN takes the natural logarithm in base e. They are inverses of each other, and that pairing is exactly what turns multiplication into addition, making geometric means and growth rates clean to compute.

What EXP and LN compute

EXP(x) returns e^x, where e is roughly 2.718281828. LN(x) is the inverse: it answers "what power must I raise e to in order to get x".

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

Key properties to lean on:

• EXP and LN undo each other: LN(EXP(x)) = x and EXP(LN(x)) = x for x > 0.
• The log turns a product into a sum: LN(a * b) = LN(a) + LN(b).
• The result in PostgreSQL is double precision, so expect tiny rounding artifacts like 0.9999999998.

Geometric mean via exp(avg(ln))

Multiplying many numbers directly overflows almost any type fast. The exp(avg(ln(x))) trick computes the geometric mean while staying in a safe range: take logs, average them in the ordinary arithmetic sense, then exponentiate back.

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

The geometric mean resists outliers better than the arithmetic one, which is why it is preferred for salaries, prices, and ratios. The same approach gives a typical order size per country:

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;

Growth rates and continuous compounding

The difference of logs is the log return, the continuous growth rate between two values. LN(new / old) gives a quantity you can add across periods, and EXP of the sum recovers the overall multiplier.

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;

Add all the log_growth values and apply EXP to the sum to get the total growth multiplier over the whole span, even when the monthly figures bounce around.

Gotcha: LN(0) and LN of negatives

LN is defined only for strictly positive numbers. LN(0) heads to negative infinity and raises an error in PostgreSQL, while LN of a negative number is undefined outright.

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

Guard with a filter or NULLIF so a single zero does not kill the whole aggregate:

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

Gotcha: dialects diverge. PostgreSQL and Oracle throw on LN(0), whereas MySQL returns NULL for both LN(0) and LN(-1). ClickHouse goes further and yields -inf or nan with no error at all. Never rely on the default behavior; always clean the input first.

Changing base and pairing with LOG

The natural log lets you build a logarithm in any base with the formula LN(x) / LN(b). That is the same result as the built-in LOG(b, x) in 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

Keep the differences in mind:

• PostgreSQL: LOG(x) is the base-10 logarithm, while LOG(b, x) takes base b.
• MySQL: LOG(x) is the natural log (like LN), and LOG(b, x) changes the base; separate LOG2 and LOG10 also exist.
• ClickHouse: log(x) is the natural log, with log2 and log10 for the rest.

Remember the essentials: EXP and LN are inverses, exp(avg(ln)) gives a geometric mean, LN(new/old) is a growth rate, and the input to LN must be strictly positive.