MODE() WITHIN GROUP in SQL: the Most Frequent Value in One Expression

Вопрос «какое значение встречается чаще всего?» всплывает постоянно: самый ходовой статус заказа, самая частая страна регистрации, любимый отдел у каждого менеджера. По привычке его решают связкой из группировки, подсчёта и LIMIT 1 — а потом удивляются, что на ничьих результат пляшет от запуска к запуску. В PostgreSQL для этого есть отдельный инструмент: MODE() WITHIN GROUP (ORDER BY col) возвращает статистическую моду — самое частое значение столбца — одним аккуратным выражением, без подзапросов и оконных функций. Это ordered-set агрегат, и порядок в ORDER BY решает, кто победит при равенстве частот.

Базовый синтаксис

Мода — это значение, которое встречается чаще остальных. В PostgreSQL она оформлена как ordered-set агрегат, поэтому без двух вещей не обойтись: обязательны и WITHIN GROUP, и ORDER BY.

-- Most frequent order status across the whole table
SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

-- Most common country among registered users
SELECT MODE() WITHIN GROUP (ORDER BY country) AS top_country
FROM users;

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

• ORDER BY здесь не сортирует результат. Он называет столбец, по которому считается мода, и заодно задаёт правило для ничьих.
• NULL в подсчёте частот не участвует — ровно как и в любом обычном агрегате.
• Работает с любым сортируемым типом: текст, числа, даты, enum.

Мода по группам

Глобальная мода нужна редко — куда чаще интересен разрез: самый частый статус у каждого пользователя, доминирующий отдел у каждого менеджера. Достаточно добавить GROUP BY, и агрегат посчитает моду отдельно внутри каждой группы.

-- Most frequent status per user
SELECT user_id,
       MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;

-- Dominant department per manager
SELECT manager_id,
       MODE() WITHIN GROUP (ORDER BY dept) AS main_dept
FROM employees
GROUP BY manager_id;

Выражение ведёт себя как обычная колонка в SELECT, так что рядом спокойно уживаются другие агрегаты той же группировки — COUNT(*), SUM(amount) и прочие — без единого лишнего джойна.

Разрешение ничьих

А вот и ключевая деталь, ради которой всё затевалось. Если несколько значений делят первое место по частоте, MODE() отдаёт то, что идёт первым по заданному ORDER BY. Результат становится детерминированным — в отличие от LIMIT 1 без явной сортировки, который при равенстве вернёт что попало.

-- On a tie, the alphabetically smallest status wins
SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

-- Force the tie to resolve toward the largest amount instead
SELECT MODE() WITHIN GROUP (ORDER BY amount DESC) AS top_amount
FROM orders;

Подводный камень, на котором спотыкаются почти все: DESC в ORDER BY — это не «самое редкое значение». Мода по-прежнему выбирается по максимальной частоте; DESC влияет лишь на то, какое из одинаково частых значений выиграет ничью. Хотите найти редкое значение — это совсем другой запрос, с COUNT(*) и сортировкой по возрастанию.

Сравнение с GROUP BY + COUNT + LIMIT 1

Классический рецепт моды — посчитать частоты и снять верхнюю строку:

-- Old-school: count, sort, take the top row
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
ORDER BY cnt DESC
LIMIT 1;

Работает, спору нет, но рядом с MODE() у него вылезают слабые места:

• На ничьей LIMIT 1 берёт произвольную строку, пока вы не допишете второй ключ сортировки — результат недетерминирован.
• Чтобы получить моду по группам, придётся тащить оконную функцию (ROW_NUMBER() OVER (PARTITION BY ...)) или коррелированный подзапрос — заметно больше кода и ниже читаемость.
• Поставить рядом другие агрегаты той же группировки в одном простом SELECT уже не выйдет.

Та же «мода по пользователю», но через окно, разрастается вот во что:

-- Per-group mode the hard way
SELECT user_id, status AS usual_status
FROM (
  SELECT user_id, status, COUNT(*) AS cnt,
         ROW_NUMBER() OVER (PARTITION BY user_id
                            ORDER BY COUNT(*) DESC, status) AS rn
  FROM orders
  GROUP BY user_id, status
) t
WHERE rn = 1;

MODE() WITHIN GROUP (ORDER BY status) с GROUP BY user_id делает ровно то же самое — одной строкой.

Различия между СУБД

MODE() как ordered-set агрегат прописан в стандарте SQL, и его понимают PostgreSQL, Oracle и DB2. А вот в ряде ходовых движков его попросту нет, и об этом стоит знать заранее:

• MySQL и SQLite: функции MODE() нет. Берите GROUP BY ... ORDER BY COUNT(*) DESC LIMIT 1 или оконный вариант выше.
• ClickHouse: здесь свои инструменты — topK(1)(col) возвращает массив с самым частым значением, а anyHeavy(col) даёт приближённую моду на больших потоках.

-- ClickHouse: most frequent value as a 1-element array
SELECT topK(1)(status) AS top_status FROM orders;

Итог простой: если ваша СУБД знает MODE() WITHIN GROUP, это самый короткий, читаемый и предсказуемый способ достать самое частое значение — особенно с GROUP BY, где альтернатива неизбежно раздувается в подзапрос с оконной функцией.