Большинству отчетов нужна определенная логика: помечайте заказы как большие или маленькие, подсчитывайте оплаченные и ожидающие заказы рядом или сначала отображайте срочные товары. SQL CASE WHEN обрабатывает все это непосредственно внутри запроса и работает во всех основных базах данных.

Мы выполнили каждый запрос здесь, в SQLite 3.50, и показанные результаты получены в результате этого запуска. CASE является частью стандартного SQL, поэтому в этих примерах он ведет себя одинаково в PostgreSQL, MySQL, SQL Server и Oracle.

Что такое СЛУЧАЙ КОГДА?

CASE — это выражение, а не отдельная команда. Он оценивает условия по порядку и возвращает значение, поэтому его можно использовать везде, где разрешено значение: в SELECT, WHERE, GROUP BY, ORDER BY или внутри функций. Он всегда заканчивается на END.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

Пример таблицы

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Обратите внимание, что в заказе 6 нет суммы. Этот ряд имеет значение позже.

Поиск ДЕЛА

Это более гибкая форма, поскольку каждый WHEN имеет собственное полное условие:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Посмотрите на последний ряд. В заказе 6 нет общей суммы, но он помечен как «маленький». Оба сравнения с NULL были неверны, поэтому оно провалилось в ELSE. Мы покажем, как это исправить, в разделе NULL. Всегда присваивайте результату имя, используя AS, иначе вы можете получить неудобное имя столбца.

Простой СЛУЧАЙ

Когда вы сравниваете один столбец с фиксированными значениями, более короткая форма читается лучше:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

Порядок имеет значение

CASE останавливается при первом истинном условии, и это наиболее распространенная логическая ошибка. Поменяйте местами два условия из нашего предыдущего запроса:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Теперь ничто не может быть «большим». Общее число 2500 также составляет не менее 500, поэтому первая ветвь выигрывает, а вторая никогда не достигается. Никаких ошибок не появляется, только неправильные метки. Для диапазонов сначала укажите наиболее ограничительное условие или запишите полные диапазоны, например total >= 500 AND total < 1000.

Что происходит без ELSE?

В нашем простом примере CASE выше заказ 5 был отменен, и ни одна ветвь не обработала его, поэтому результатом было NULL. Иногда это именно то, что вам нужно, как показано в следующем разделе. Но при создании меток обычно лучше использовать чистый ELSE 'other', поскольку он делает неожиданные значения видимыми, а не оставляет пробелы в отчете.

Сводные отчеты с SUM

Это один из наиболее полезных шаблонов в повседневном SQL. Обертывание CASE в агрегат превращает строки в столбцы, по одной строке на каждого клиента:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

Теперь один запрос дает как оплаченный доход, так и количество отложенных ордеров, без необходимости использования отдельных запросов или объединений. Этот шаблон работает практически в каждой базе данных, в отличие от специального синтаксиса PIVOT, который предлагают лишь некоторые. Если GROUP BY для вас новичок, прочтите SQL GROUP BY и HAVING.

Условные подсчеты

COUNT пропускает NULL, поэтому CASE без ELSE учитывает только соответствующие строки:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Будьте осторожны: если вы добавите ELSE 0 внутрь COUNT, будет учитываться каждая строка, поскольку 0 не равен NULL. Используйте ELSE 0 с SUM и оставьте его с COUNT. PostgreSQL и SQLite также поддерживают COUNT(*) FILTER (WHERE ...), что хорошо читается, но доступно не везде.

Группировка по CASE

Вы можете создавать свои собственные сегменты, а затем суммировать их:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Группировка по псевдониму работает в SQLite, PostgreSQL и MySQL. SQL Server не позволяет этого, поэтому вам придется повторить все выражение или сначала поместить запрос в CTE или подзапрос.

Пользовательская сортировка с помощью ORDER BY

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

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Отложенные ордера идут первыми, что удобно при рабочей очереди. Второй столбец сортировки, id, сохраняет строки в предсказуемом порядке внутри каждой группы.

ДЕЛО в ОБНОВЛЕНИИ

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

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status сохраняет другие строки без изменений. Не учитывать это опасно, поскольку несовпадающим строкам будет присвоено значение NULL. Без предложения WHERE этот оператор также затрагивает каждую строку, поэтому в больших таблицах добавьте фильтр и рассмотрите возможность переноса изменения в транзакцию, чтобы можно было проверить результат перед фиксацией. Обратите также внимание, что порядок 6 не изменился, поскольку его нулевая сумма не удовлетворяла total < 500.

НУЛЕВЫЕ ловушки

NULL вызывает две отдельные проблемы с CASE. Во-первых, простая форма сравнения использует равенство, и ничто не равно NULL, даже само NULL:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

Итак, CASE col WHEN NULL никогда не совпадает. Вместо этого используйте форму поиска с помощью IS NULL. Во-вторых, как мы видели на примере порядка 6, значение NULL ускользает от сравнения и попадает в ELSE. Исправление состоит в том, чтобы сначала проверить это:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Заказ 6 теперь получает честную маркировку. Наше руководство по значениям SQL NULL полностью описывает это поведение.

CASE против других вариантов

Последний вариант заслуживает размышления. Если CASE вырастет до тридцати ветвей и появится в пяти запросах, переместите сопоставление в таблицу и присоединитесь к ней. Тогда изменение — это обновление одной строки, а не изменение кода в пяти местах.

Распространенные ошибки

• Неправильный порядок условий. Широкое условие сначала скрывает более узкие.
• Забывание END. Это наиболее частая синтаксическая ошибка.
• Использование WHEN NULL. Оно никогда не совпадает, поэтому используйте WHEN col IS NULL.
• Добавление ELSE 0 внутрь COUNT. Подсчитывается все строки.
• Пропуск ELSE в UPDATE. Он может стирать значения NULL.
• Фильтрация по CASE в WHERE. Это может остановить использование индекса. См. наше руководство по оптимизации SQL-запросов.

Итоги

CASE WHEN — это небольшая функция, которая выполняет большую работу. Запомните три правила: побеждает первая истинная ветвь, несовпадающие строки становятся NULL без ELSE, а NULL требует собственной проверки. Для практики возьмите отчет, который вы сейчас создаете с помощью нескольких запросов, и попытайтесь создать его как один запрос, используя SUM(CASE ...).

Часто задаваемые вопросы

Продолжайте учиться