When sql: Учебник по языку SQL (DDL, DML) на примере диалекта MS SQL Server. Часть третья / Хабр

Выражение CASE в SQL: объяснение на примерах

Перевод статьи «SQL Case Statement Tutorial – With When-Then Clause Example Queries».

Выражение СASE — это, по сути, SQL-версия условной логики. Это выражение может использоваться примерно так же, как if-предложения в языках программирования вроде JavaScript, хотя его структура немного отличается.

Данные для примера

Представьте, что вы преподаете литературу в школе. ваши ученики должны написать сочинение.

Вы создали следующую таблицу, чтобы отслеживать, кто из учеников уже сдал сочинение (там же проставляются оценки). Если ученик еще не сдал сочинение, в графе оценок значится NULL.

Как написать выражение CASE в SQL

Допустим, вы хотели бы выводить ученикам сообщения о том, сдали они сочинение или нет. Чтобы получить статус каждого ученика, вы можете просто выбрать столбец submitted_essay, но тогда в сообщении будет только TRUE или FALSE, а это не очень читабельно.

Вместо этого вы можете использовать выражение CASE и вывоить разные сообщения, основываясь на статусе в submitted_essay.

Базовая структура выражения CASE:

CASE WHEN... THEN... END

Использование CASE WHEN, THEN и END является обязательным, а ELSE и AS — опцональным. Выражение СASE должно идти внутри инструкции SELECT.

SELECT name,
	CASE WHEN submitted_essay IS TRUE THEN 'сочинение сдано!'
	ELSE 'сдай сочинение!' END  AS status
FROM students;

В приведенном выше примере мы выбрали имена учеников, а затем вывели разные сообщения в столбце status, основываясь на значении submitted_essay. Результирующая таблица выглядит так:

Усложняем пример

Идем дальше. Допустим, вы хотели бы включить в сообщение побольше информации. Если ученик уже сдал сочинение, вы хотели бы добавить в сообщение его оценку, а если нет — напомнить, что сочинение еще не сдано. Здесь нам пригодятся предложения WHEN/THEN.

SELECT name, essay_grade,
CASE WHEN essay_grade >= 80 THEN 'молодец'
	 WHEN essay_grade < 80 THEN 'можешь лучше'
	 ELSE 'сдай сочинение!' END  AS teacher_comment
FROM students;

В этом примере кода мы вывели имена учеников, их оценки, а также комментарии, соответствующие оценкам.

После первого предложения WHEN/THEN вы можете добавить сколько угодно других WHEN/THEN, а также предложение ELSE, покрывающее все неучтенные случаи. Это аналог логики if... else if... else в JavaScript (или if. .. elif... else в Python и т. д.).

Обратите внимание, что в этом случае предложение ELSE призвано захватить все сочинения с оценками NULL (т. е. еще не сданные сочинения), но в других ситуациях для проверки, является ли значение null, вы могли бы использовать IS NULL.

Не забывайте ставить END в конце вашего CASE-выражения!

В таблице представлены результаты этого запроса:

Заключение

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

СЛУЧАЙ SQL | Промежуточный SQL

Начиная здесь? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

• Оператор SQL CASE
• Добавление нескольких условий в оператор CASE
• Краткий обзор основ CASE
• Использование CASE с агрегатными функциями
• Использование CASE внутри агрегатных функций
• Практические задачи

В течение следующих нескольких уроков вы будете работать с данными о футболистах колледжей. Эти данные были собраны с ESPN 15 января 2014 г. из списков, перечисленных на этой странице, с помощью парсера Python, доступного здесь. В этом конкретном уроке вы будете придерживаться информации о ростере. Эта таблица говорит сама за себя — одна строка на игрока со столбцами, описывающими атрибуты этого игрока.

 SELECT * FROM benn.college_football_players
 

Оператор CASE — это способ SQL для обработки логики if/then. За оператором CASE следует как минимум одна пара операторов WHEN и THEN — эквивалент SQL оператора IF/THEN в Excel. Из-за этой пары у вас может возникнуть соблазн назвать это SQL CASE WHEN , но CASE является принятым термином.

Каждый оператор CASE должен заканчиваться оператором END . Оператор ELSE является необязательным и предоставляет способ захвата значений, не указанных в КОГДА / ТОГДА утверждений.

 SELECT player_name,
       год,
       СЛУЧАЙ, КОГДА год = 'SR' THEN 'да'
            ELSE NULL END AS is_a_senior
  ОТ benn.college_football_players
 

Проще говоря, вот что происходит:

1. Оператор CASE проверяет каждую строку, чтобы убедиться в истинности условного оператора — year = 'SR' .
2. Для любой заданной строки, если это условное утверждение истинно, слово «да» печатается в столбце, который мы назвали is_a_senior .
3. В любой строке, для которой условное выражение ложно, ничего не происходит в этой строке, оставляя нулевое значение в столбце is_a_senior .
4. В то же время, когда все это происходит, SQL извлекает и отображает все значения в столбцах player_name и
   year .

Приведенный выше запрос довольно легко увидеть, что происходит, потому что мы включили оператор CASE вместе с год сама колонка. Вы можете проверить каждую строку, чтобы увидеть, соответствует ли год условию год = «SR» , а затем просмотреть результат в столбце, сгенерированном с помощью инструкции CASE .

Но что, если вам не нужны нулевые значения в столбце is_a_senior ? Следующий запрос заменяет эти нули на «нет»:

 SELECT player_name,
       год,
       СЛУЧАЙ, КОГДА год = 'SR' THEN 'да'
            ELSE 'no' END AS is_a_senior
  ОТ benn. college_football_players
 

Практическая задача

Напишите запрос, включающий столбец с пометкой «да», если игрок из Калифорнии, и сначала отсортируйте результаты с этими игроками.

Вы также можете определить количество результатов в операторе CASE , включив любое количество операторов WHEN / THEN :

 SELECT player_name,
       масса,
       СЛУЧАЙ, КОГДА вес > 250, ТОГДА «более 250»
            КОГДА вес > 200, ТОГДА "201-250"
            КОГДА вес > 175, ТОГДА '176-200'
            ELSE '175 или меньше' END AS Weight_group
  ОТ benn.college_football_players
 

В приведенном выше примере операторы WHEN / THEN будут оцениваться в том порядке, в котором они написаны. Таким образом, если значение в столбце weight данной строки равно 300, это даст результат «более 250». Вот что произойдет, если значение в столбце weight равно 180, SQL сделает следующее:

1. Проверьте, больше ли weight 250.
   180 не больше 250, поэтому переходите к следующему WHEN / ЗАТЕМ
2. Проверьте, не превышает ли вес 200. 180 не больше 200, поэтому переходите к следующему КОГДА / ТО
3. Проверьте, не больше ли weight 175. 180 больше 175, поэтому запишите «175-200» в столбце weight_group .

Несмотря на то, что все вышеперечисленное работает, на самом деле лучше всего создавать операторы, которые не перекрываются. ПРИ весе > 250 и КОГДА вес > 200 перекрываются для каждого значения больше 250, что немного сбивает с толку. Лучше написать это так:

 SELECT player_name,
       масса,
       СЛУЧАЙ, КОГДА вес > 250, ТОГДА «более 250»
            КОГДА вес > 200 И вес <= 250, ТО '201-250'
            КОГДА вес > 175 И вес <= 200, ТОГДА '176-200'
            ELSE '175 или меньше' END AS Weight_group
  ОТ benn.college_football_players
 

Практическая задача

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

Вы также можете объединить несколько условных операторов с И и ИЛИ так же, как в предложении WHERE :

 SELECT player_name,
       СЛУЧАЙ, КОГДА год = 'FR' И позиция = 'WR', ТО 'frosh_wr'
            ELSE NULL END AS sample_case_statement
  ОТ benn.college_football_players
 

Краткий обзор основ CASE:

1. Оператор CASE всегда находится в предложении SELECT
.
2. CASE должен включать следующие компоненты: WHEN , THEN и END . ELSE является дополнительным компонентом.
3. Вы можете сделать любой условный оператор, используя любой условный оператор (например, WHERE ) между WHEN и THEN . Это включает в себя объединение нескольких условных операторов с использованием И и ИЛИ .
4. Вы можете включить несколько операторов WHEN , а также оператор ELSE для работы с любыми неадресованными условиями.

Практическая задача

Напишите запрос, который выбирает все столбцы из benn.college_football_players и добавляет дополнительный столбец, отображающий имя игрока, если этот игрок является младшим или старшим.

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

Несколько более сложная и существенно более полезная функциональность CASE достигается за счет объединения его с агрегатными функциями. Например, предположим, что вы хотите подсчитывать только те строки, которые соответствуют определенному условию. Поскольку COUNT игнорирует нули, вы можете использовать оператор CASE для оценки условия и получения нулевых или ненулевых значений в зависимости от результата:

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn. college_football_players
 СГРУППИРОВАТЬ ПО РЕГИСТРУ, КОГДА год = 'FR', ТОГДА 'FR'
               ELSE 'Не FR' END
 

Теперь вы можете подумать: «Почему бы мне просто не использовать предложение WHERE для фильтрации строк, которые я не хочу учитывать?» Вы можете сделать это — это будет выглядеть так:

 SELECT COUNT(1) AS fr_count
  ОТ benn.college_football_players
 ГДЕ год = 'FR'
 

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

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 СГРУППИРОВАТЬ ПО 1
 

Приведенный выше запрос — отличное место для использования чисел вместо столбцов в предложении GROUP BY , потому что повторение оператора CASE в предложении GROUP BY сделало бы запрос неприемлемо длинным. Кроме того, вы можете использовать псевдоним столбца в GROUP BY предложение, подобное этому:

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 ГРУППА ПО year_group
 

Обратите внимание: если вы решите повторить весь оператор CASE , вам следует удалить имя столбца AS year_group при копировании/вставке в GROUP BY пункт:

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 СГРУППИРОВАТЬ ПО РЕГИСТРУ, КОГДА год = 'FR', ТОГДА 'FR'
               КОГДА год = «ТАК», ТОГДА «ТАК»
               КОГДА год = 'JR', ТОГДА 'JR'
               КОГДА год = 'SR', ТОГДА 'SR'
               ELSE 'Нет данных за год' END
 

Объединение операторов CASE с агрегациями поначалу может показаться сложным. Часто полезно сначала написать запрос, содержащий оператор CASE , а затем выполнить его самостоятельно. Используя предыдущий пример, вы можете сначала написать:

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            *
  ОТ benn.college_football_players
 

Приведенный выше запрос покажет все столбцы таблицы benn.college_football_players , а также столбец, показывающий результаты оператора CASE . Оттуда вы можете заменить * агрегацией и добавить предложение GROUP BY . Попробуйте этот процесс, если вы боретесь с любой из следующих практических проблем.

Практическая задача

Напишите запрос, который подсчитывает количество игроков весом более 300 фунтов для каждого из следующих регионов: Западное побережье (Калифорния, Орегон, Вашингтон), Техас и другие (все остальные).

Практическая задача

Напишите запрос, вычисляющий общий вес всех игроков низшего класса (FR/SO) в Калифорнии, а также общий вес всех игроков высшего класса (JR/SR) в Калифорнии.

Использование CASE в агрегатных функциях

В предыдущих примерах данные отображались вертикально, но в некоторых случаях вам может понадобиться отобразить данные горизонтально. Это известно как «сводка» (как сводная таблица в Excel). Возьмем следующий запрос:

 ВЫБЕРИТЕ РЕГИСТР, КОГДА год = 'FR', ТОГДА 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 СГРУППИРОВАТЬ ПО 1
 

И переориентировать его по горизонтали:

 SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
       COUNT (CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
       COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) КАК jr_count,
       COUNT (СЛУЧАЙ, КОГДА год = 'SR' THEN 1 ELSE NULL END) КАК sr_count
  ОТ benn. college_football_players
 

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

Оттачивайте свои навыки SQL

Практические задачи

Напишите запрос, отображающий количество игроков в каждом штате, с игроками FR, SO, JR и SR в отдельных столбцах и в другом столбце для общего количества игроков. Расположите результаты так, чтобы штаты с наибольшим количеством игроков были первыми.

Практическая задача

Напишите запрос, показывающий количество игроков в школах с именами, начинающимися с букв от A до M, и количество игроков в школах с именами, начинающимися с N-Z.

SQL CASE | Промежуточный SQL

Начиная здесь? Этот урок является частью полного учебника по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

• Оператор SQL CASE
• Добавление нескольких условий в оператор CASE
• Краткий обзор основ CASE
• Использование CASE с агрегатными функциями
• Использование CASE внутри агрегатных функций
• Практические задачи

В течение следующих нескольких уроков вы будете работать с данными о футболистах колледжей. Эти данные были собраны с ESPN 15 января 2014 г. из списков, перечисленных на этой странице, с помощью парсера Python, доступного здесь. В этом конкретном уроке вы будете придерживаться информации о ростере. Эта таблица говорит сама за себя — одна строка на игрока со столбцами, описывающими атрибуты этого игрока. Запустите этот запрос, чтобы проверить необработанные данные:

 ВЫБЕРИТЕ * ИЗ benn.college_football_players
 

Оператор CASE — это способ SQL для обработки логики if/then. За оператором CASE следует как минимум одна пара операторов WHEN и THEN — эквивалент SQL оператора IF/THEN в Excel. Из-за этой пары у вас может возникнуть соблазн назвать это SQL CASE WHEN , но CASE является принятым термином.

Каждый оператор CASE должен заканчиваться цифрой 9.0029 КОНЕЦ заявление. Оператор ELSE является необязательным и предоставляет способ захвата значений, не указанных в операторах WHEN / THEN . CASE проще всего понять в контексте примера:

 SELECT player_name,
       год,
       СЛУЧАЙ, КОГДА год = 'SR' THEN 'да'
            ELSE NULL END AS is_a_senior
  ОТ benn.college_football_players
 

Проще говоря, вот что происходит:

1. Оператор CASE проверяет каждую строку на предмет истинности условного оператора — year = 'SR' .
2. Для любой заданной строки, если это условное выражение истинно, слово «да» печатается в столбце, который мы назвали is_a_senior .
3. В любой строке, для которой условное выражение ложно, ничего не происходит в этой строке, оставляя нулевое значение в столбце is_a_senior .
4. В то же время, когда все это происходит, SQL извлекает и отображает все значения в player_name и год столбцов.

Приведенный выше запрос довольно легко увидеть, что происходит, потому что мы включили оператор CASE вместе с самим столбцом year . Вы можете проверить каждую строку, чтобы увидеть, соответствует ли год условию год = «SR» , а затем просмотреть результат в столбце, сгенерированном с помощью инструкции CASE .

Но что, если вам не нужны нулевые значения в is_a_senior 9столбец 0030? Следующий запрос заменяет эти нули на «нет»:

 SELECT player_name,
       год,
       СЛУЧАЙ, КОГДА год = 'SR' THEN 'да'
            ELSE 'no' END AS is_a_senior
  ОТ benn.college_football_players
 

Практическая задача

Напишите запрос, включающий столбец с пометкой «да», если игрок из Калифорнии, и сначала отсортируйте результаты с этими игроками.

 SELECT player_name,
       масса,
       СЛУЧАЙ, КОГДА вес > 250, ТОГДА «более 250»
            КОГДА вес > 200, ТОГДА "201-250"
            КОГДА вес > 175, ТОГДА '176-200'
            ELSE '175 или меньше' END AS Weight_group
  ОТ benn.college_football_players
 

 SELECT player_name,
       масса,
       СЛУЧАЙ, КОГДА вес > 250, ТОГДА «более 250»
            КОГДА вес > 200 И вес <= 250, ТО '201-250'
            КОГДА вес > 175 И вес <= 200, ТОГДА '176-200'
            ELSE '175 или меньше' END AS Weight_group
  ОТ benn.college_football_players
 

 ВЫБЕРИТЕ player_name,
       СЛУЧАЙ, КОГДА год = 'FR' И позиция = 'WR', ТО 'frosh_wr'
            ELSE NULL END AS sample_case_statement
  ОТ benn.college_football_players
 

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            ELSE 'Not FR' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn. college_football_players
 СГРУППИРОВАТЬ ПО РЕГИСТРУ, КОГДА год = 'FR', ТОГДА 'FR'
               ELSE 'Не FR' END
 

 ВЫБРАТЬ СЧЕТЧИК(1) КАК fr_count
  ОТ benn.college_football_players
 ГДЕ год = 'FR'
 

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 СГРУППИРОВАТЬ ПО 1
 

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 ГРУППА ПО year_group
 

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 СГРУППИРОВАТЬ ПО РЕГИСТРУ, КОГДА год = 'FR', ТОГДА 'FR'
               КОГДА год = «ТАК», ТОГДА «ТАК»
               КОГДА год = 'JR', ТОГДА 'JR'
               КОГДА год = 'SR', ТОГДА 'SR'
               ELSE 'Нет данных за год' END
 

 SELECT CASE WHEN year = 'FR' THEN 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            *
  ОТ benn.college_football_players
 

 ВЫБЕРИТЕ РЕГИСТР, КОГДА год = 'FR', ТОГДА 'FR'
            КОГДА год = «ТАК», ТОГДА «ТАК»
            КОГДА год = 'JR', ТОГДА 'JR'
            КОГДА год = 'SR', ТОГДА 'SR'
            ELSE 'Нет данных за год' END AS year_group,
            COUNT(1) КАК считать
  ОТ benn.college_football_players
 СГРУППИРОВАТЬ ПО 1
 

 SELECT COUNT(CASE WHEN year = 'FR' THEN 1 ELSE NULL END) AS fr_count,
 COUNT (CASE WHEN year = 'SO' THEN 1 ELSE NULL END) AS so_count,
 COUNT(CASE WHEN year = 'JR' THEN 1 ELSE NULL END) КАК jr_count,
 COUNT (СЛУЧАЙ, КОГДА год = 'SR' THEN 1 ELSE NULL END) КАК sr_count
 ОТ benn.