MODE в SQL: как найти самое частое значение в столбце

В аналитике часто всплывает простой вопрос:

Какое значение встречается чаще всего?

Например:

• самый частый статус заказа;
• самая популярная страна регистрации;
• самый частый отдел у менеджера;
• самый ходовой тариф;
• самый распространённый способ оплаты.

Такое значение называется модой. Мода — это значение, которое встречается в наборе данных чаще остальных.

Обычно новички решают эту задачу через GROUP BY, COUNT(*), сортировку и LIMIT 1. Это рабочий способ, но у него есть неприятные нюансы: запрос быстро разрастается, а при равенстве частот результат может стать непредсказуемым.

В PostgreSQL для этой задачи есть отдельный инструмент:

MODE() WITHIN GROUP (ORDER BY column_name)

Он возвращает самое частое значение столбца одним аккуратным выражением.

Что такое мода

Мода — это самое частое значение.

Допустим, в таблице заказов есть такие статусы:

status
paid
paid
pending
cancelled
paid
pending

Здесь чаще всего встречается paid. Значит, мода по колонке status — это paid.

В PostgreSQL это можно получить так:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Результат:

top_status
paid

Запрос читается так:

Найди значение status, которое встречается чаще всего.

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

У MODE в PostgreSQL необычная форма записи:

MODE() WITHIN GROUP (ORDER BY column_name)

Например:

SELECT MODE() WITHIN GROUP (ORDER BY country) AS top_country
FROM users;

Так можно найти самую частую страну среди пользователей.

Важные части здесь две:

• MODE() — сама агрегатная функция;
• WITHIN GROUP (ORDER BY country) — колонка, по которой считаем моду.

Почему нельзя просто написать так?

SELECT MODE(country)
FROM users;

Потому что в PostgreSQL MODE относится к ordered-set агрегатам. Такие агрегаты требуют специальной записи через WITHIN GROUP.

На практике это нужно просто запомнить как шаблон:

SELECT MODE() WITHIN GROUP (ORDER BY some_column)
FROM some_table;

ORDER BY внутри MODE — это не обычная сортировка результата

Вот момент, который часто сбивает с толку.

В запросе:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

ORDER BY status не сортирует итоговую таблицу результата.

Он делает две вещи:

1. Указывает колонку, по которой нужно искать самое частое значение.
2. Задаёт правило выбора при ничьей.

То есть ORDER BY внутри WITHIN GROUP — это часть агрегата, а не финальная сортировка строк.

Обычная сортировка результата пишется снаружи, в конце запроса:

SELECT
  user_id,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id
ORDER BY user_id;

Здесь внутренний ORDER BY status нужен для MODE, а внешний ORDER BY user_id сортирует готовый результат.

MODE по всей таблице

Самый простой случай — найти самое частое значение во всей таблице.

Например, самый частый статус заказа:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Или самую частую страну регистрации:

SELECT MODE() WITHIN GROUP (ORDER BY country) AS top_country
FROM users;

Или самый популярный тариф:

SELECT MODE() WITHIN GROUP (ORDER BY plan_name) AS top_plan
FROM subscriptions;

Это удобно для быстрых аналитических вопросов: «что встречается чаще всего?»

MODE по группам

Глобальная мода по всей таблице нужна не всегда. Чаще хочется получить самое частое значение в каждой группе.

Например:

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

Для этого добавляем обычный GROUP BY.

SELECT
  user_id,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;

Такой запрос вернёт по одной строке на пользователя:

user_id	usual_status
1	paid
2	pending
3	cancelled

На человеческом языке:

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

Пример: главный отдел менеджера

Допустим, в таблице employees есть сотрудники, менеджеры и отделы.

SELECT
  manager_id,
  MODE() WITHIN GROUP (ORDER BY department) AS main_department
FROM employees
GROUP BY manager_id;

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

Если у менеджера больше всего сотрудников из отдела Sales, результатом будет Sales.

MODE можно сочетать с другими агрегатами

MODE ведёт себя как обычный агрегат. Поэтому рядом с ним можно спокойно использовать COUNT, SUM, AVG и другие агрегатные функции.

Например, по каждому пользователю покажем:

• сколько у него заказов;
• общую сумму;
• самый частый статус заказа.

SELECT
  user_id,
  COUNT(*) AS orders_count,
  SUM(amount) AS total_amount,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;

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

В одном запросе можно получить и обычные агрегаты, и моду.

Что происходит при равенстве частот

Теперь самый важный нюанс.

Что будет, если два значения встречаются одинаково часто?

Например:

status
paid
paid
pending
pending

Здесь paid встречается 2 раза и pending тоже встречается 2 раза.

Кто победит?

В MODE() WITHIN GROUP победит значение, которое идёт первым по правилу ORDER BY.

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Если сортировать статусы по алфавиту, paid будет раньше pending, значит результатом станет paid.

Это делает результат предсказуемым.

Как управлять ничьей

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

SELECT MODE() WITHIN GROUP (ORDER BY amount) AS top_amount
FROM orders;

Если хотите, чтобы при равенстве частот побеждало максимальное значение, используйте DESC:

SELECT MODE() WITHIN GROUP (ORDER BY amount DESC) AS top_amount
FROM orders;

Важно: DESC не ищет самое редкое значение.

Это частая ошибка.

Запрос:

SELECT MODE() WITHIN GROUP (ORDER BY amount DESC) AS top_amount
FROM orders;

по-прежнему ищет самое частое значение. DESC влияет только на выбор победителя, если несколько значений встречаются одинаково часто.

Например, если 100 и 200 встречаются по 5 раз, то:

SELECT MODE() WITHIN GROUP (ORDER BY amount) AS top_amount
FROM orders;

выберет 100, а:

SELECT MODE() WITHIN GROUP (ORDER BY amount DESC) AS top_amount
FROM orders;

выберет 200.

Но оба запроса ищут именно моду, то есть самое частое значение.

Как найти самое редкое значение

Самое редкое значение — это уже не мода. Для такой задачи нужен обычный подсчёт частот.

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count ASC, status
LIMIT 1;

Здесь логика другая:

1. Группируем строки по статусу.
2. Считаем количество строк в каждой группе.
3. Сортируем по количеству по возрастанию.
4. Берём первую строку.

Если нужно получить все редкие значения при ничьей, одного LIMIT 1 уже мало. Тогда лучше использовать оконные функции или подзапрос с минимальным количеством.

Что происходит с NULL

MODE в PostgreSQL не учитывает NULL при подсчёте частот.

Допустим, в колонке есть значения:

country
NULL
NULL
Vietnam
Vietnam
Spain

Мода будет считаться среди известных значений. В этом примере результатом станет Vietnam.

SELECT MODE() WITHIN GROUP (ORDER BY country) AS top_country
FROM users;

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

Это поведение похоже на многие агрегаты в SQL: SUM, AVG, MIN, MAX тоже обычно игнорируют NULL.

С какими типами работает MODE

MODE работает с типами, которые можно сортировать.

Например:

• текст;
• числа;
• даты;
• временные метки;
• enum-типы.

Текстовый пример:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Числовой пример:

SELECT MODE() WITHIN GROUP (ORDER BY rating) AS top_rating
FROM reviews;

Пример с датой:

SELECT MODE() WITHIN GROUP (ORDER BY created_at::date) AS top_order_date
FROM orders;

Последний запрос найдёт дату, на которую пришлось больше всего заказов.

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

До знакомства с MODE многие пишут так:

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC
LIMIT 1;

Этот запрос действительно находит самый частый статус.

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

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC, status
LIMIT 1;

Теперь при равенстве частот победит статус, который идёт раньше по алфавиту.

MODE делает эту идею короче:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Почему MODE удобнее для групп

Для одной общей моды старый способ ещё выглядит терпимо.

Но если нужно найти самое частое значение внутри каждой группы, запрос через GROUP BY, COUNT и LIMIT 1 уже не подходит напрямую.

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

Через оконную функцию это можно написать так:

SELECT
  user_id,
  status AS usual_status
FROM (
  SELECT
    user_id,
    status,
    COUNT(*) AS status_count,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY COUNT(*) DESC, status
    ) AS row_num
  FROM orders
  GROUP BY user_id, status
) AS ranked_statuses
WHERE row_num = 1;

Запрос рабочий, но для новичка он тяжёлый:

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

С MODE то же самое выглядит намного спокойнее:

SELECT
  user_id,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;

Один GROUP BY, один агрегат, один понятный результат.

Пример: любимый способ оплаты по стране

Допустим, есть таблица платежей:

CREATE TABLE payments (
  id bigint,
  country text,
  payment_method text,
  amount numeric(10, 2)
);

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

SELECT
  country,
  MODE() WITHIN GROUP (ORDER BY payment_method) AS top_payment_method,
  COUNT(*) AS payments_count,
  SUM(amount) AS total_amount
FROM payments
GROUP BY country
ORDER BY country;

Результат может быть таким:

country	top_payment_method	payments_count	total_amount
Argentina	card	120	5400.00
Brazil	pix	340	18700.00
Uruguay	card	85	3900.00

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

Пример: самый частый статус заказа по пользователю

Допустим, в orders есть такие колонки:

CREATE TABLE orders (
  id bigint,
  user_id bigint,
  status text,
  amount numeric(10, 2),
  created_at timestamp
);

Запрос:

SELECT
  user_id,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status,
  COUNT(*) AS orders_count,
  ROUND(AVG(amount), 2) AS avg_amount
FROM orders
GROUP BY user_id
ORDER BY user_id;

Он покажет:

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

И всё это без подзапроса.

Когда лучше не использовать MODE

MODE хорош, когда вам нужно именно одно самое частое значение.

Но он не подходит, если нужно увидеть всю таблицу частот.

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

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC, status;

Так вы увидите не только победителя, но и остальные значения:

status	status_count
paid	500
pending	120
cancelled	30

MODE вернёт только paid. Иногда этого достаточно, а иногда нужна полная картина.

Когда нужно вернуть все значения при ничьей

MODE возвращает одно значение. Если несколько значений делят первое место, он выберет одно по ORDER BY.

Но иногда нужно показать всех победителей.

Например, если paid и pending встречаются одинаково часто, вы хотите вернуть оба.

Тогда лучше использовать подсчёт частот и оконную функцию:

SELECT
  status,
  status_count
FROM (
  SELECT
    status,
    COUNT(*) AS status_count,
    RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_num
  FROM orders
  GROUP BY status
) AS ranked_statuses
WHERE rank_num = 1;

Такой запрос вернёт все значения, которые заняли первое место по частоте.

То есть правило простое:

• нужно одно самое частое значение — используйте MODE;
• нужны все победители при ничьей — считайте частоты отдельно.

MODE в PostgreSQL

В PostgreSQL MODE записывается так:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Для групп:

SELECT
  user_id,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;

Это один из ordered-set агрегатов. К той же семье относятся, например, агрегаты для процентилей.

Главная особенность записи — обязательный блок WITHIN GROUP.

MODE в Oracle и DB2

В Oracle и DB2 тоже есть поддержка MODE как ordered-set агрегата.

Идея такая же: функция возвращает самое частое значение, а WITHIN GROUP указывает порядок и правило выбора при равенстве.

Если вы пишете переносимый SQL, всё равно проверяйте синтаксис и детали поведения в документации конкретной СУБД. Но сама идея MODE() WITHIN GROUP не является исключительно PostgreSQL-приёмом.

MODE в MySQL и SQLite

В MySQL и SQLite встроенной функции MODE() в таком виде нет.

Поэтому обычно используют классический способ:

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC, status
LIMIT 1;

Для моды по группам понадобится оконная функция:

SELECT
  user_id,
  status AS usual_status
FROM (
  SELECT
    user_id,
    status,
    COUNT(*) AS status_count,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY COUNT(*) DESC, status
    ) AS row_num
  FROM orders
  GROUP BY user_id, status
) AS ranked_statuses
WHERE row_num = 1;

В MySQL это удобно писать в версиях, где доступны оконные функции. В SQLite современные версии тоже поддерживают оконные функции, поэтому такой подход возможен.

MODE в ClickHouse

В ClickHouse нет такого же MODE() WITHIN GROUP, как в PostgreSQL.

Для похожих задач часто используют topK.

SELECT topK(1)(status) AS top_status
FROM orders;

Важно: topK(1)(status) возвращает массив с одним элементом, а не обычное скалярное значение.

Результат может выглядеть так:

top_status
['paid']

Если нужно достать первый элемент массива, используют функции работы с массивами конкретного запроса и версии ClickHouse.

Для больших потоков данных также встречается anyHeavy, но это не полный аналог PostgreSQL MODE. Его стоит воспринимать как отдельный инструмент ClickHouse для поиска часто встречающегося значения, а не как точную замену во всех случаях.

Типичные ошибки

Ошибка 1. Думать, что ORDER BY сортирует результат

Внутри MODE запись:

MODE() WITHIN GROUP (ORDER BY status)

не сортирует итоговые строки. Она задаёт колонку для поиска моды и правило выбора при равенстве частот.

Для сортировки результата нужен отдельный ORDER BY в конце запроса.

Ошибка 2. Думать, что DESC ищет самое редкое значение

Такой запрос:

SELECT MODE() WITHIN GROUP (ORDER BY status DESC) AS top_status
FROM orders;

всё равно ищет самое частое значение.

DESC влияет только на ничью между одинаково частыми значениями.

Ошибка 3. Использовать LIMIT 1 без второго ключа сортировки

Плохо:

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC
LIMIT 1;

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

Лучше:

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC, status
LIMIT 1;

Или в PostgreSQL короче:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Ошибка 4. Ждать от MODE полную таблицу частот

MODE возвращает только победителя.

Если нужно увидеть все значения и их частоты, используйте GROUP BY и COUNT(*).

SELECT
  status,
  COUNT(*) AS status_count
FROM orders
GROUP BY status
ORDER BY status_count DESC, status;

Что важно запомнить

MODE возвращает самое частое значение в столбце.

В PostgreSQL базовый синтаксис такой:

SELECT MODE() WITHIN GROUP (ORDER BY status) AS top_status
FROM orders;

Для моды внутри групп добавляется обычный GROUP BY:

SELECT
  user_id,
  MODE() WITHIN GROUP (ORDER BY status) AS usual_status
FROM orders
GROUP BY user_id;

ORDER BY внутри WITHIN GROUP не сортирует итоговый результат. Он указывает колонку для поиска моды и задаёт правило выбора при равенстве частот.

DESC не ищет самое редкое значение. Мода всё равно выбирается по максимальной частоте.

Если нужна полная таблица частот, используйте GROUP BY и COUNT(*).

Если нужно одно самое частое значение в PostgreSQL, MODE() WITHIN GROUP — самый короткий и читаемый способ. Особенно он хорош в группировках, где альтернатива быстро превращается в подзапрос с оконной функцией.