Инструкция CASE (многомерные выражения)
Позволяет возвращать указанные значения из нескольких сравнений согласно условию. Существуют два типа инструкции CASE.
Простая инструкция CASE, сравнивающая выражение с набором простых выражений и возвращающая определенные значения.
Инструкция CASE для поиска, вычисляющая набор выражений логического типа и возвращающая определенные значения.
Синтаксис
Аргументы
input_expression
Многомерное выражение, результатом вычисления которого является скалярное значение.
when_true_result_expression
Скалярное значение, которое возвращается, если результатом предложения WHEN является TRUE.
else_result_expression
Скалярное значение, возвращаемое в том случае, если при вычислении ни одно из предложений WHEN не вернуло TRUE.
Boolean_expression
Многомерное выражение, результатом вычисления которого является скалярное значение.
Remarks
Если нет предложения ELSE, а все предложения WHEN дали значение false, тогда результатом будет пустая ячейка.
Простое выражение CASE
MDX вычисляет простое выражение CASE, разрешающее input_expression скалярному значению. Это скалярное значение затем сравнивается с скалярным значением when_expression. Если два скалярных значения совпадают, оператор CASE возвращает значение when_true_expression. Если скалярные значения не совпадают, вычисляется следующее предложение WHEN. Если все предложения WHEN имеют значение false, то возвращается значение else_result_expression из предложения Else (при наличии).
Выражение CASE для поиска
Для более сложных вычислений можно использовать выражение CASE для поиска. Оно позволяет узнать, принадлежит ли значение входного выражения диапазону значений. Многомерное выражение вычисляет предложения WHEN, чтобы они появились в инструкции CASE.
Выражение CASE в SQL: объяснение на примерах
Перевод статьи «SQL Case Statement Tutorial – With When-Then Clause Example Queries».
Данные для примера
Представьте, что вы преподаете литературу в школе. ваши ученики должны написать сочинение.
Вы создали следующую таблицу, чтобы отслеживать, кто из учеников уже сдал сочинение (там же проставляются оценки). Если ученик еще не сдал сочинение, в графе оценок значится NULL.
| STUDENT_ID | NAME | SUBMITTED_ESSAY | GRADE |
|---|---|---|---|
| 1 | Джон | TRUE | 86 |
| 2 | Саид | TRUE | 90 |
| 3 | Алиса | FALSE | NULL |
| 4 | Ной | TRUE | 68 |
| 5 | Элеанор | TRUE | 95 |
| 6 | Акико | FALSE | NULL |
| 7 | Отто | TRUE | 76 |
| 8 | Джамал | TRUE | 85 |
| 9 | Кьяра | TRUE | 88 |
| 10 | Клементина | FALSE | NULL |
Как написать выражение CASE в SQL
Вместо этого вы можете использовать выражение CASE и вывоить разные сообщения, основываясь на статусе в submitted_essay.
Базовая структура выражения CASE :
В приведенном выше примере мы выбрали имена учеников, а затем вывели разные сообщения в столбце status, основываясь на значении submitted_essay. Результирующая таблица выглядит так:
| NAME | STATUS |
|---|---|
| Акико | сдай сочинение! |
| Клементина | сдай сочинение! |
| Алиса | сдай сочинение! |
| Саид | сочинение сдано! |
| Элеанор | сочинение сдано! |
| Отто | сочинение сдано! |
| Ной | сочинение сдано! |
| Кьяра | сочинение сдано! |
| Джон | сочинение сдано! |
| Джамал | сочинение сдано! |
Усложняем пример
В этом примере кода мы вывели имена учеников, их оценки, а также комментарии, соответствующие оценкам.
Обратите внимание, что в этом случае предложение ELSE призвано захватить все сочинения с оценками NULL (т. е. еще не сданные сочинения), но в других ситуациях для проверки, является ли значение null, вы могли бы использовать IS NULL.
В таблице представлены результаты этого запроса:
| NAME | ESSAY_GRADE | TEACHER_COMMENT |
|---|---|---|
| Акико | NULL | сдай сочинение! |
| Клементина | NULL | сдай сочинение! |
| Алиса | NULL | сдай сочинение! |
| Саид | 90 | молодец |
| Элеанор | 95 | молодец |
| Отто | 76 | можешь лучше |
| Ной | 68 | можешь лучше |
| Кьяра | 88 | молодец |
| Джон | 86 | молодец |
| Джамал | 85 | молодец |
Заключение
Выражения CASE легко понять и изучить. Их применение — это лаконичный способ внести ясность в ваши SQL-запросы.
Выражение CASE (Transact-SQL)
Оценка списка условий и возвращение одного из нескольких возможных выражений результатов.
Выражение CASE имеет два формата:
простое выражение CASE для определения результата сравнивает выражение с набором простых выражений;
поисковое выражение CASE для определения результата вычисляет набор логических выражений.
Оба формата поддерживают дополнительный аргумент ELSE.
Выражение CASE может использоваться в любой инструкции или предложении, которые допускают допустимые выражения. Например, выражение CASE можно использовать в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как select_list, IN, WHERE, ORDER BY и HAVING.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
input_expression
Выражение, полученное при использовании простого формата функции CASE. input_expression — это любое допустимое выражение.
WHEN when_expression
Простое выражение, с которым сравнивается input_expression при использовании простого формата CASE. when_expression — это любое допустимое выражение. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.
THEN result_expression
Выражение, возвращаемое, когда равенство input_expression и when_expression имеет значение TRUE или Boolean_expression имеет значение TRUE. result expression — это любое допустимое выражение.
ELSE else_result_expression
Это выражение, возвращаемое, если ни одна из операций сравнения не дает в результате TRUE. Если этот аргумент опущен и ни одна из операций сравнения не дает в результате TRUE, функция CASE возвращает NULL. else_result_expression — это любое допустимое выражение. Типы данных аргумента else_result_expression и каждого из выражений result_expression должны быть одинаковыми или неявно приводимыми друг к другу.
WHEN Boolean_expression
Логическое выражение, полученное при использовании поискового формата функции CASE. Boolean_expression — это любое допустимое логическое выражение.
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Типы возвращаемых данных
Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
Возвращаемые значения
Простое выражение CASE
Простое выражение CASE сравнивает первое выражение с выражением в каждом предложении WHEN. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN.
Допускается только проверка равенства.
В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.
Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE.
Если ни одна из операций input_expression = when_expression не дает значения TRUE, Компонент SQL Server Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.
Поисковое выражение CASE
Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.
Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.
Если ни одно выражение Boolean_expression не равно TRUE, Компонент Database Engine возвращает выражение else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.
Remarks
SQL Server допускает применение в выражениях CASE не более 10 уровней вложенности.
Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. Список методов управления потоком см. в статье Язык управления потоком (Transact-SQL).
Выражение CASE последовательно оценивает свои условия и останавливается, когда находит первое выполнимое условие. В некоторых ситуациях выражение оценивается до того, как выражение CASE получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегатные выражения в аргументах WHEN выражения CASE вначале оцениваются, после чего передаются выражению CASE. Например в следующем запросе создается ошибка деления на ноль при вычислении значения агрегата MAX. Это происходит до оценки выражения CASE.
Следует создавать зависимости только от порядка оценки условий WHEN для скалярных выражений (в том числе нескоррелированных вложенных запросов, возвращающих скалярные значения), а не для агрегатных выражений.
Примеры
A. Использование инструкции SELECT с простым выражением CASE
При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными.
Б. Использование инструкции SELECT с поисковым выражением CASE
При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта.
В. Использование выражения CASE в предложении ORDER BY
Г. Использование выражения CASE в инструкции UPDATE
В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска.
Д. Использование выражения CASE в инструкции SET
Е. Использование выражения CASE в предложении HAVING
Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)
Ж. Использование инструкции SELECT с выражением CASE
При использовании в инструкции SELECT выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В приведенном ниже примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. Если значение отсутствует, выводится текст «Not for sale».
З. Использование выражения CASE в инструкции UPDATE
В следующем примере выражение CASE используется в инструкции UPDATE, чтобы определить значение, установленное в столбце VacationHours для сотрудников, у которых столбец SalariedFlag имеет значение 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов.
SQL-Ex blog
Новости сайта «Упражнения SQL», статьи и переводы
SQL CASE: знать и избегать малоизвестных неприятностей
Неприятности с CASE? Действительно?
Нет, пока вы не столкнетесь с 3 неприятными проблемами, которые могут вызвать ошибки во время выполнения и ухудшить производительность.
Если вы пролистаете подзаголовки, чтобы увидеть эти проблемы, я не буду вас винить. Читатели, к которым я также отношусь, нетерпеливы.
Я уверен, что вы уже знакомы с основами SQL CASE, поэтому я не буду мучит вас длинным введением. Давайте углубимся в понимание того, что происходит под капотом.
1. SQL CASE не всегда оценивается последовательно
Выражения в SQL CASE по большей части оцениваются последовательно или слева направо. Хотя совсем другое дело, когда они используются с агрегатными функциями. Давайте рассмотрим пример:
Вышеприведенный код выглядит обычно. Если я спрошу вас, какой результат будет получен, вы, вероятно, ответите 1. Визуальная проверка скажет нам это, поскольку переменная @value установлена в 0. Если @value равна 0, то результат равен 1.
Но не в этом случае. Вот действительный результат, полученный в SQL Server Management Studio:
Когда условное выражение использует агрегатные функции типа MAX() в SQL CASE, они оцениваются в первую очередь. Таким образом, MAX(1/@value) вызывает ошибку деления на нуль, поскольку @value равна 0.
Ситуация становится еще более неприятной, когда скрыта. Я объясню это позже.
2. Простое выражение SQL CASE оценивается многократно
Хороший вопрос. Действительно, здесь вообще нет никаких проблем, если вы используете литералы или простые выражения. Но если вы используете подзапросы в качестве условного выражения, вы сильно удивитесь.
Прежде, чем проверять пример ниже, лучше восстановить отсюда копию базы данных. Мы будем использовать её в последующих примерах.
Теперь рассмотрим следующий очень простой пример:
Очень простой, правда? Он возвращает 1 строку с одним столбцом данных. STATISTICS IO показывает минимальное число логических чтений.
Рис.1. Логические чтения таблицы SportsCars до использования запроса в качестве подзапроса в SQL CASE
Замечание для непосвященных. Чем больше логических чтений, тем медленнее запрос. О логических чтениях можно почитать здесь.
План выполнения тоже показывает простой процесс:
Рис.2. План выполнения для запроса к SportsCar до его использования как подзапроса в SQL CASE
Давайте теперь поместим этот запрос в выражение CASE:
Анализ
Скрестите пальцы, поскольку сейчас логические чтения увеличатся в 4 раза.
Рис.3. Логические чтения после использования подзапроса в SQL CASE
Удивительно! По сравнению всего с двумя логическими чтениями на рис.1 мы получили в 4 раза больше. Таким образом, запрос стал в 4 раза медленнее. Как это могло произойти? Мы видим подзапрос только в одном месте.
Но это не конец истории. Посмотрите план выполнения:
Рис.4. План выполнения после использования простого запроса в качестве выражения подзапроса в SQL CASE
Мы видим 4 экземпляра операторов Top и Index Scan на рис.4. Если каждый Top и Index Scan потребляет 2 логических чтения, это объясняет, почему число логических чтений стало 8 на рис.3. И, поскольку каждый Top и Index Scan имеют 25% стоимости, это подтверждает сказанное.
Но это еще не все. Свойства оператора Compute Scalar показывают, как обрабатывается весь оператор.
Рис.5. Свойства Compute Scalar показывают 4 выражения CASE WHEN
Мы видим 3 выражения CASE WHEN в свойстве Defined Values оператора Compute Scalar. Это выглядит так, как будто простое выражение CASE стало поисковым выражением CASE типа:
Хорошее исправление? Давайте посмотрим логические чтения в STATISTICS IO:
Рис.6. Логические чтения после извлечения подзапроса из выражения CASE
Мы видим меньше логических чтений в модифицированном запросе. Извлечение подзапроса и присвоение результата переменной получается значительно лучше. Что насчет плана выполнения? Посмотрите ниже:
Рис.7. План выполнения после извлечения подзапроса из выражения CASE
Оператор Top и Index Scan появляются однажды, а не 4 раза. Замечательно!
На заметку: Не используйте подзапрос в качестве условия в операторе CASE. Если необходимо получить значение, поместите сначала результат подзапроса в переменную. Затем используйте эту переменную в выражении CASE.
Эти три встроенные функции тайно преобразуются в SQL CASE
Эта функция принимает условие и возвращает 1 из 2 аргументов в зависимости от результатов условия. И эта функция также имеется в T-SQL.
Но это просто обертка более длинного выражения CASE. Откуда нам это известно? Давайте проверим пример.
Результатом этого запроса является ‘No’. Однако проверьте план выполнения, а также свойства Compute Scalar.
Рис.8. IIF оказывается CASE WHEN в плане выполнения
Поскольку IIF является CASE WHEN, как вы думаете, что произойдет, если выполнить что-то подобное этому?
Будет получена ошибка деления на нуль, если @noOfPayments равен нулю. То же самое происходило в первом случае, рассмотренном ранее.
Вы можете спросить, что вызывает эту ошибку, поскольку результатом запроса является TRUE, и должно получиться 83333.33. Опять вернитесь к случаю 1.
Таким образом, если вы столкнулись с такой ошибкой при использовании IIF, виноват SQL CASE.
COALESCE
Давайте посмотрим план выполнения и свойство Defined Values оператора Compute Scalar.
Рис.9. COALESCE преобразуется в SQL CASE в плане выполнения
Разумеется SQL CASE. Нигде не упоминается COALESCE в окне Defined Values. Это доказывает тайный секрет этой функции.
Но это не все. Сколько раз вы увидели [Vehicles].[dbo].[Styles].[Style] в окне Defined Values? ДВАЖДЫ! Это согласуется с официальной документацией Microsoft. Представьте, что один из аргументов в COALESCE является подзапросом. Тогда получаем удвоение логических чтений и замедление выполнения.
CHOOSE
Наконец, CHOOSE. Она подобна функции CHOOSE в MS Access. Она возвращает одно значение из списка значений на основе позиции индекса. Она также действует как индекс массива.
Давайте посмотрим, сможем ли мы получить трансформацию в SQL CASE в примере. Проверьте нижеприведенный код:
Это наш пример с CHOOSE. Теперь давайте посмотрим план выполнения и свойство Defined Values в операторе Compute Scalar:
Рис.10. Как видно в плане выполнения CHOOSE преобразуется в SQL CASE
Вы видите ключевое слово CHOOSE в окне Defined Values на рис.10? Как насчет CASE WHEN?
Подобно предыдущим примерам, эта функция CHOOSE есть просто оболочка для более длинного выражения CASE. И поскольку запрос имеет 2 пункта для CHOOSE, ключевые слова CASE WHEN появляются дважды. Смотрите в окне Defined Values красные прямоугольники.
Однако CASE WHEN появлялось более двух раз. Это происходит из-за выражения CASE во внутреннем запросе CTE. Если посмотреть внимательно, эта часть внутреннего запроса также появляется дважды.
На заметку
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть третья
Предыдущие части
О чем будет рассказано в этой части
Выражение CASE – условный оператор языка SQL
Данный оператор позволяет осуществить проверку условий и возвратить в зависимости от выполнения того или иного условия тот или иной результат.
Оператор CASE имеет 2 формы:
| Первая форма: | Вторая форма: |
|---|---|
| CASE WHEN условие_1 THEN возвращаемое_значение_1 … WHEN условие_N THEN возвращаемое_значение_N [ELSE возвращаемое_значение] END | CASE проверяемое_значение WHEN сравниваемое_значение_1 THEN возвращаемое_значение_1 … WHEN сравниваемое_значение_N THEN возвращаемое_значение_N [ELSE возвращаемое_значение] END |
В качестве значений здесь могут выступать и выражения.
Разберем на примере первую форму CASE:
| ID | Name | Salary | SalaryTypeWithELSE | SalaryTypeWithoutELSE | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1000 | Иванов И.И. | 5000 | ЗП >= 3000 | ЗП >= 3000 | ||||||||||||||||||||||||||
| 1001 | Петров П.П. | 1500 | ЗП 3000».
Т.е. здесь в первую очередь происходит группировка и вычисляются данные по всем отделам: А уже к этим данным применяется условие указанно в блоке HAVING: В HAVING-условии так же можно строить сложные условия используя операторы AND, OR и NOT:
Как можно здесь заметить агрегатная функция (см. «COUNT(*)») может быть указана только в блоке HAVING. Соответственно мы можем отобразить только номер отдела, подпадающего под HAVING-условие: Пример использования HAVING-условия по полю включенного в GROUP BY: Это только пример, т.к. в данном случае проверку логичнее было бы сделать через WHERE-условие: Т.е. сначала отфильтровать сотрудников по отделу 3, и только потом сделать расчет.
Думаю, на этом рассказ о HAVING-условиях можно окончить. Подведем итогиСведем данные полученные во второй и третьей части и рассмотрим конкретное месторасположение каждой изученной нами конструкции и укажем порядок их выполнения:
Конечно же, вы так же можете применить к сгруппированным данным предложения DISTINCT и TOP, изученные во второй части. Эти предложения в данном случае применятся к окончательному результату:
Как получились данные результаты проанализируйте самостоятельно. ЗаключениеОсновная цель которую я ставил в данной части – раскрыть для вас суть агрегатных функций и группировок. Если базовая конструкция позволяла нам получить необходимые детальные данные, то применение агрегатных функций и группировок к этим детальным данным, дало нам возможность получить по ним сводные данные. Так что, как видите здесь все важно, т.к. одно опирается на другое – без знания базовой конструкции мы не сможем, например, правильно отобрать данные, по которым нам нужно просчитать итоги. Здесь я намеренно стараюсь показывать только основы, чтобы сосредоточить внимание начинающих на самых главных конструкциях и не перегружать их лишней информацией. Твердое понимание основных конструкций (о которых я еще продолжу рассказ в последующих частях) даст вам возможность решить практически любую задачу по выборке данных из РБД. Основные конструкции оператора SELECT применимы в таком же виде практически во всех СУБД (отличия в основном состоят в деталях, например, в реализации функций – для работы со строками, временем, и т.д.). Если вы делаете первые шаги в SQL, то сосредоточьтесь в первую очередь, именно на изучении базовых конструкций, т.к. владея базой, все остальное вам понять будет гораздо легче, и к тому же самостоятельно. Вам в первую очередь, как бы нужно объемно понять возможности языка SQL, т.е. какого рода операции он вообще позволяет совершить над данными. Донести до начинающих информацию в объемном виде – это еще одна из причин, почему я буду показывать только самые главные (железные) конструкции. Удачи вам в изучении и понимании языка SQL. |
