SQL Базовый №7. CASE

Файлы к уроку:

• Для спонсоров Boosty
• Для спонсоров VK

• YouTube
• VK

CASE — это условный оператор, который позволяет осуществлять проверку и возвращать результат, который зависит от того, какое условие выполнено.

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

В этом и последующий примерах используется датасет kinopoisk. Наш запрос должен среди прочих вернуть столбец region. Если в столбце страна находится значение «США» или «Канада», то вернуться должно значение «Северная Америка» и т. д.

-- Пример CASE
select
  rating,
  year,
  movie,
  country,
  case
  	when country in ('Россия', 'СССР', 'Польша')
  	 then 'Восточная Европа'
  	when country in ('США', 'Канада')
  	 then 'Северная Америка'
  	when country in ('Великобритания', 'Германия', 'Ирландия', 'Франция')
  	 then 'Западная Европа'
  	else 'Иное'
  end as region,
  rating_balls
from kinopoisk

Вернется следующий результат.

Без ELSE

Блок ELSE не является обязательным. Если не указывать ELSE, то во всех остальных случаях вернется NULL.

-- Без ELSE
-- Если не заполнить блок ELSE, то вернется NULL в остальных случаях
select
  rating,
  year,
  movie,
  country,
  case
  	when country in ('Россия', 'СССР', 'Польша')
  	 then 'Восточная Европа'
  	when country in ('США', 'Канада')
  	 then 'Северная Америка'
  	when country in ('Великобритания', 'Германия', 'Ирландия', 'Франция')
  	 then 'Западная Европа'
  end as region,
  rating_balls
from kinopoisk

В данном случае вместо «Иное» будет возвращаться значение NULL.

CASE внутри WHERE

Внутри WHERE тоже может находиться выражение с использованием CASE. Данный запрос вернет то же самое, что предыдущий, но без строк со значением NULL в столбце region.

-- Если нужно отфильтровать NULL
select
  rating,
  year,
  movie,
  country,
  case when country in ('Россия', 'СССР', 'Польша')
  	   then 'Восточная Европа'
  	   when country in ('США', 'Канада')
  	   then 'Северная Америка'
  	   when country in ('Великобритания', 'Германия', 'Ирландия', 'Франция')
  	   then 'Западная Европа'
       end as region,
  rating_balls
from kinopoisk
where case when country in ('Россия', 'СССР', 'Польша')
  	 	   then 'Восточная Европа'
  		   when country in ('США', 'Канада')
  	 	   then 'Северная Америка'
		   when country in ('Великобритания', 'Германия', 'Ирландия', 'Франция')
		   then 'Западная Европа'
  		   end is not null

Запрос на закрепление

Запрос должен вернуть среду прочих условный столбец rating_category. Если рейтинг фильма >= 9, то вернется значение «Сверхвысокий», если >= 7, то «Высокий» и т. д.

select
  rating,
  year,
  movie,
  country,
  rating_balls,
  case when rating_balls >= 9 then 'Сверхвысокий'
	   when rating_balls >= 7 then 'Высокий'
       when rating_balls >= 5 then 'Средний'
       else 'Низкий' end as rating_category
from kinopoisk

Группировка по условному полю с CASE

Для каждого региона найдем количество фильмов в рейтинге.

-- Группировка с использованием CASE
select
  case
  	when country in ('Россия', 'СССР', 'Польша')
  	 then 'Восточная Европа'
  	when country in ('США', 'Канада')
  	 then 'Северная Америка'
  	when country in ('Великобритания', 'Германия', 'Ирландия', 'Франция')
  	 then 'Западная Европа'
  	else 'Иное'
  end as region,
  count(*) as number_of_films
from kinopoisk
group by
  case
  	when country in ('Россия', 'СССР', 'Польша')
  	 then 'Восточная Европа'
  	when country in ('США', 'Канада')
  	 then 'Северная Америка'
  	when country in ('Великобритания', 'Германия', 'Ирландия', 'Франция')
  	 then 'Западная Европа'
  	else 'Иное'
  end
order by number_of_films desc

CASE вместе с агрегатными функциями

Запрос должен вернуть страну и 4 столбца с количеством фильмов для нескольких десятилетий.

-- CASE вместе с агрегатными функциями
select
  country,
  count(case when year >= 2010
  			 then year
  			 end) as decade_2010s,
  count(case when year >= 2000 and year < 2010
  			 then year
  			 end) as decade_2000s,
  count(case when year >= 1990 and year < 2000
  			 then year
  			 end) as decade_1990s,
  count(case when year >= 1980 and year < 1990
  			 then year
  			 end) as decade_1980s
from kinopoisk
group by country

У какой страны выше средний рейтинг в Кинопоиске?

-- У какой страны выше средний рейтинг в топе Кинопоиска
select
  round(avg(case when country = 'США'
  		   		 then rating_balls
  		   		 end), 2) as usa,
  round(avg(case when country = 'СССР'
  		   		 then rating_balls
  		   		 end), 2) as ussr
from kinopoisk
where country in ('США', 'СССР')

Функция AVG вместе с CASE

AVG вместе с CASE обычно применяется для нахождения доли от общего. Здесь мы найдем долю фильмов из США в каждом десятилетии.

-- Доля фильмов из США в каждом десятилетии
select
  year - year % 10 as decade,
  round(avg((case when country = 'США'
  	   			  then 1
  	   			  else 0
  	   			  end)), 2) as usa_percentage
from kinopoisk
group by
  year - year % 10
order by decade