← Все курсы · Курс SQL

GROUP BY с CASE: группировка по правилу вместо столбца

Майкл Носито · Обновлено в августе 2026 г. · Каждый запрос на этой странице был выполнен до его публикации.

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

Что вы делаете: напишите правило в виде CASE В SELECTи повторите это в GROUP BY. Полоса существует на протяжении всего запроса и больше нигде.

Краткая версия. Группируйте по выражению, а не по псевдониму, и ваш запрос будет выполняться в каждой базе данных.

Стол

CREATE TABLE orders(order_id INT, customer_id INT, amount INT, status TEXT);
INSERT INTO orders VALUES
(101,1,120,'shipped'),(102,1,80,'shipped'),(103,2,200,'cancelled'),
(104,2,50,'shipped'),(105,3,300,'shipped'),(106,4,90,'pending'),
(107,4,60,'shipped'),(108,5,150,'cancelled');

Запрос

SELECT CASE WHEN amount >= 200 THEN 'large'
            WHEN amount >= 100 THEN 'medium'
            ELSE 'small' END AS band,
       COUNT(*)   AS orders,
       SUM(amount) AS total
FROM orders
GROUP BY CASE WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END
ORDER BY total DESC;

Два заказа по 200 и 300 штук — это большой размер. Двое из 120 и 150 — средние. Остальные четыре — 80, 50, 90 и 60 — малы. Восемь строк внутри, три строки снаружи, и 500 + 280 + 270 = 1050, что составляет общую сумму таблицы. Это дополнение стоит проверять каждый раз.

Куда может идти CASE, по базе данных

Версия CTE является предпочтительной, если правило появляется более одного раза:

WITH banded AS (
  SELECT order_id, amount,
         CASE WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END AS band
  FROM orders
)
SELECT band, COUNT(*) AS orders, SUM(amount) AS total
FROM banded
GROUP BY band
ORDER BY total DESC;

Правило пишется один раз, поэтому оно не может дрейфовать между SELECT и GROUP BY. Две копии правила, которые должны согласовываться, — это именно то, что через шесть месяцев наполовину редактируют.

Группа, которая исчезает

Добавьте четвертую полосу для всего, что имеет значение 500 или выше, и запрос вернет три строки, а не четыре. GROUP BY сообщает о группах, которые существуют в данных; полоса, в которую ничего не попало, не дает строки, а дает ноль.

Для отчета, в котором всегда должна отображаться каждая полоса, укажите список и присоединитесь к нему:

WITH bands(band, sort_order) AS (
  VALUES ('small',1), ('medium',2), ('large',3), ('huge',4)
),
banded AS (
  SELECT CASE WHEN amount >= 500 THEN 'huge'
              WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END AS band,
         amount
  FROM orders
)
SELECT b.band,
       COUNT(x.amount)              AS orders,
       COALESCE(SUM(x.amount), 0)   AS total
FROM bands b
LEFT JOIN banded x ON x.band = b.band
GROUP BY b.band, b.sort_order
ORDER BY b.sort_order;

Теперь отображается огромный размер с 0 и 0. Примечание. COUNT(x.amount) скорее, чем COUNT(*): после LEFT JOIN несовпадающая полоса по-прежнему создает одну строку, и COUNT(*) сообщит 1 для полосы, ничего не содержащей.

Сортировка полос в разумном порядке

В алфавитном порядке большое ставится перед средним, а затем маленьким, что бессмысленно. Два способа исправить:

-- sort by a number, hide the number
ORDER BY CASE WHEN amount >= 200 THEN 3
              WHEN amount >= 100 THEN 2
              ELSE 1 END DESC

-- or carry a sort_order column alongside the band, as above

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

Группировка по правилу и столбцу вместе

SELECT status,
       CASE WHEN amount >= 200 THEN 'large'
            WHEN amount >= 100 THEN 'medium'
            ELSE 'small' END AS band,
       COUNT(*) AS orders
FROM orders
GROUP BY status,
         CASE WHEN amount >= 200 THEN 'large'
              WHEN amount >= 100 THEN 'medium'
              ELSE 'small' END
ORDER BY status, band;

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

Как применить это в своей работе

1. Убедитесь, что итоговые суммы вашей группы соответствуют разгруппированной сумме. Это один дополнительный запрос, который улавливает недостающую информацию. ELSE.
2. Переместите любое правило, использованное более одного раза, в CTE, чтобы было единое определение.
3. Напишите в комментарии политику границ: значение, равное границе, увеличивается или уменьшается?
4. Решите, должны ли появляться пустые полосы, и если да, то явно создайте список полос.
5. Предоставьте группам преднамеренный порядок сортировки, а не алфавитный.

Единственная привычка, которую нужно сохранить

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

Сколько мест в вашей отчетности определяет, что считается крупным заказом?