SQL CASE WHEN

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

Если неформально формализовать, операторы CASE в SQL эквивалентны конструкции if-then в других языках программирования. Они позволяют обрабатывать несколько сценариев (или случаев) в ваших данных, оценивать их на истинность и выводить соответствующее значение для каждого случая.

На этой странице мы разберем, как использовать операторы SQL CASE и продемонстрируем, почему они ценны для современных команд, работающих с данными.

Как использовать операторы SQL CASE

Операторы CASE WHEN создаются в операторах SELECT вместе с другими полями, которые вы выбираете. Общий синтаксис для операторов SQL CASE WHEN следующий:

case when [сценарий 1] then [результат 1]
     when [сценарий 2] then [результат 2]
    -- …сколько угодно сценариев
     when [сценарий n] then [результат n]
     else [результат по умолчанию] -- этот else является необязательным
end as <новое_имя_поля>

Некоторые замечания о функциональности операторов CASE:

• Сценарии в операторах CASE оцениваются в порядке их перечисления. Что это значит? Это значит, что если несколько сценариев оцениваются как истинные, то результат возвращается для самого раннего из них.
• Результаты в каждом сценарии должны быть одного и того же типа данных; если сценарий 1 приводит к строке, все остальные сценарии также должны быть строками.
• Часто команды, работающие с данными, опускают финальный else сценарий, так как else [результат по умолчанию] является необязательным и по умолчанию равен else null.
• В общем, производительность операторов CASE в операторах select относительно эффективна (по сравнению с другими функциями SQL, такими как агрегаты или сложные соединения с использованием AND и OR); это не значит, что сравнение большого количества сценариев эффективно (или разумно), но это, вероятно, не станет узким местом в ваших моделях данных.
• Результаты операторов CASE WHEN также могут быть переданы в агрегатные функции, такие как MAX, MIN и COUNT, или даже в функции работы с датами (например, date_trunc('month', <case when statement>).

Давайте рассмотрим практический пример использования оператора CASE.

Пример SQL CASE WHEN

select
    order_id,
    round(amount) as amount,
    case when amount between 0 and 10 then 'low'
         when amount between 11 and 20 then 'medium'
         else 'high'
    end as order_value_bucket
from {{ ref('orders') }}

Этот простой запрос, использующий таблицу orders из Jaffle Shop, вернет новое поле, которое распределяет сумму заказа по критериям:

order_id	amount	order_value_bucket
1	10	low
2	20	medium
3	1	low
4	25	high
5	17	medium

Синтаксис SQL CASE WHEN в Snowflake, Databricks, BigQuery и Redshift

Поскольку это основа SQL, большинство, если не все, современные хранилища данных поддерживают возможность добавления операторов CASE WHEN в свои запросы. Snowflake, Databricks, Google BigQuery и Amazon Redshift поддерживают операторы CASE и имеют одинаковый синтаксис для них.

Примеры использования CASE WHEN

Примеры использования операторов CASE в моделях dbt и временных запросах практически бесконечны; в результате мы не будем (и не сможем) создавать исчерпывающий список мест, где вы можете встретить операторы CASE.

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

• Создание булевых значений из ваших существующих данных (например, case when cnt > 1 then true else false end as is_active)
• Установление соответствий между сырыми данными и более общими категориями данных (см. пример ранее на странице); обратите внимание, что если вы создаете много сценариев CASE WHEN для соответствия, которое не меняется со временем, вам, вероятно, стоит импортировать это соответствие либо как собственную модель dbt, либо как источник данных (хороший пример использования seeds)
• Если вы создаете один и тот же оператор CASE WHEN во всех своих моделях, рассмотрите возможность абстрагирования этого оператора CASE WHEN в отдельную модель или в макрос
• Генерация более удобных для бизнес-пользователей значений столбцов, которые могут быть легко поняты бизнес-пользователями