SQL-запросы. Условный оператор CASE...WHEN...THEN

Категория: Код

– Автор: Игорь (Администратор)

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

Как бы хорошо вы не спроектировали вашу базу данных, всегда будут возникать задачи, где без условных операторов не обойтись. Например, вместо огромных цифр получить словесное описание, или же в зависимости от наличия (отсутствия) данных добавить в строку дополнительные символы, или как более сложный пример - в зависимости от разных нюансов отнести запись к той или иной группе. Ситуаций масса.

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

Однако, в рамках данной статьи я рассмотрю именно второй вариант, так как у него не возникает проблем с ситуациями вроде CASE WHEN NULL (null в рамках БД не является конкретным значением, поэтому его нельзя использовать в switch подобном операторе). Кроме того, в обыденной жизни чаще всего встречаются задачи именно для второго варианта - вычисление через логические выражения. Поэтому лучше сразу научиться и продолжать использовать именно его.

Обычно, он описывается так (синтаксис может меняться в зависимости от БД):

CASE
    WHEN bool_expression1 THEN value1
    .....
    WHEN bool_expressionN THEN valueN
    ELSE valueElse
END

где

bool_expressionX - это логическое условие

valueX - это то значение, которое будет подставлено, если выполнено соответствующее логическое условие

valueElse - это значение, которое будет подставлено, если ранее не выполнилось ни одно условие.

После такой небольшой справки, перейдем к практике.

Примечание: Кстати, стоит знать, что обычно этот оператор можно применять не только в select, но и в любом месте, где можно использовать поля. Например, при join таблиц или же даже фильтрации (having) при группировке (group by).

Условный оператор CASE...WHEN...THEN

Чтобы лучше понять условный оператор CASE...WHEN...THEN, представим себе небольшую задачу. Допустим, у вас есть таблица с данными о клиентах и их суммарном количестве покупок. И стоит задача динамически формировать скидку. Можно было бы, конечно, вручную выставлять скидку. Но, она у вас пороговая, причем пороги жестко зашиты (что-то вроде - сумма больше 1000 получи скидку в 2%, а больше 5000 - получи 5%) и вы бы хотели этот процесс автоматизировать, чтобы не пришлось искать ошибки и каждый раз копаться в базе данных (накопил клиент нужную сумму - скидка автоматически появилась).

Возьмем условную таблицу client с тремя клиентами. Для примера их будет вполне достаточно.

Теперь, зададим несколько условий автоматического предоставления скидки исходя из задачи. При этом считаем, что клиенту в любом случае предоставляется максимальная скидка.

1. Сумма от 1000 - скидка 2%

2. Сумма от 5000 - скидка 5%

3. Сумма от 10000 - скидка 8%

4. Количество заказов от 10 - скидка 7%

5. Количество заказов от 20 - скидка 8%

Как видите, скидка зависит от двух факторов от суммы и от количества. Теперь, попробуем из них создать условия исходя от скидки, то есть правила наоборот, чтобы их можно было использовать в sql-запросе. Получим следующее:

1. 2% - Сумма от 1000 до 4999 и количество заказов меньше 10.

2. 5% - Сумма от 5000 до 9999 и количество заказов меньше 10.

3. 7% - Количество заказов от 10 до 19 и сумма меньше 10000

4. 8% - Количество от 20 или сумма от 10000

Теперь, осталось только записать. Получим следующий sql-запрос

-- Выводим имя и остальные данные
select name, order_count, total_sum,
-- А теперь выводим скидку
CASE
    -- Первое правило 2%
    WHEN c.total_sum >= 1000 and c.total_sum <= 4999 and c.order_count < 10
          THEN 2
    -- Второе правило 5%
    WHEN c.total_sum >= 5000 and c.total_sum <= 9999 and c.order_count < 10
          THEN 5
    -- Третье правило 7%
    WHEN c.total_sum < 10000 and c.order_count >= 10 and c.order_count <= 19
          THEN 7
    -- Четвертое правило 8%
    WHEN c.total_sum >= 10000 or c.order_count >= 20
          THEN 8
    -- Ни одно правило не выполнено, значит скидка 0.
    ELSE 0
END as discount
from client c

В результате выполнения получим следующую таблицу:

Как видите, два клиента получили скидку 8 процентов, а один клиент получил скидку в 2 процента. При этом с каждым заказом процент будет автоматически вычисляться и вам ничего не нужно будет корректировать. К примеру, если у Петя сумма увеличится до 5000, то его скидка автоматически поднимется до 5% (как минимум, так как есть еще число заказов).

Теперь, вы знаете как применять условный оператор CASE...WHEN...THEN и, надеюсь, больше с этим у вас не возникнет проблем.