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

Курс профессиональной Power BI аналитики	Подробнее
Курс SQL-анализа данных с нуля	Подробнее

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

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

SQL Базовый

Номер урока	Урок	Описание
1	SQL Базовый №1. Установка PostgreSQL, создание таблицы, импорт из CSV	Начинаем изучать SQL. Установим СУБД, создадим схему, таблицу и выполним импорт из CSV.
1.1	SQL Базовый №1.1. UI создание схемы, таблицы, импорт из CSV	Дополнение к уроку 1. Создадим схему, таблицу и выполним импорт данных из CSV с помощью пользовательского интерфейса.
2	SQL Базовый №2. Простые операции, SELECT	Научимся отбирать нужные нам столбцы и строки таблицы и делать сортировку.
3	SQL Базовый №3. Типы данных	Знакомство с основными типами данных в PostgreSQL: строковые, числовые, дата и время.
4	SQL Базовый №4. Импорт и экспорт данных	Научимся импортировать данные из CSV и экспортировать в CSV.
5	SQL Базовый №5. Группировка	Изучаем операцию группировки и основные агрегатные функции.
6	SQL Базовый №6. Математические операции	Сложение, вычитание, умножение, деление, остаток от деления, факториал, возведение в степень, корень.
7	SQL Базовый №7. CASE	CASE — это условный оператор, который позволяет осуществлять проверку и возвращать результат, который зависит от того, какое условие выполнено.
8	SQL Базовый №8. Дополнения к урокам 1-7	Значение NULL, запросы к таблицам из других схем, рекомендации по форматированию кода, порядок выполнения операций в SQL.
9	SQL Базовый №9. Повторение изученного	Повторим все, что изучили в уроках 1-8. Спонсоры также смогут получить домашнее задание.
10	SQL Базовый №10. Подзапросы	Подзапросы нужны, когда для решения задачи нужно сделать несколько действий. Подзапросы внутри WHERE, FROM, SELECT, HAVING.
11	SQL Базовый №11. CTE	CTE также используются для решения задач в несколько этапов.
12	SQL Базовый №12. Оконные функции. Ранг, ранжирование (ranking)	Как посчитать ранг для всей таблицы или для конкретной категории.
13	SQL Базовый №13. Оконные функции. Агрегаты (SUM, AVG, MIN, MAX, COUNT)	Получить агрегированную итоговую сумму для категории.
14	SQL Базовый №14. Смещение	Приемы из этого урока позволяют получить значение из другой строки таблицы.
15	SQL Базовый №15. Оконные функции. Скользящие агрегаты	Научимся вычислять скользящее среднее и нарастающий итог.
16	SQL Базовый №16. Операция JOIN	Какие бывают виды объединения по горизонтали. Чем они отличаются друг от друга. Практика и домашнее задание.
17	SQL Базовый №17. Практика	Повторим все изученное в предыдущих уроках. Создадим схему, несколько таблиц и выполним несколько запросов на закрепление изученного материала.
18	SQL Базовый №18. Объединение по вертикали (UNION, UNION ALL, INTERSECT, EXCEPT)	Научимся объединять таблицы по вертикали.
19	SQL Базовый №19. Устройство на работу 1	Тестовые задания при приеме на работу.
20	SQL Базовый №20. Сравнение с Excel, важные функции DBeaver	Важные функции DBeaver. Сравниваем SQL и Excel.
21	SQL Базовый №21. Устройство на работу 2	Решим еще один тест при устройстве на работу.
22	SQL Базовый №22. Краткий обзор основных DDL и DML команд	Познакомимся с командами, которые позволяют создавать таблицы, наполнять их данными, редактировать, удалять и т. д.
23	SQL Базовый №23. Устройство на работу 3	Еще одно тестовое задание по SQL при приеме на работу на позицию BI-аналитика.

предыдущая запись

SQL Базовый №6. Простые математические операции

следующая запись