Файлы к уроку:
В этом уроке начнем знакомиться с оконными функциями, которые позволяют посчитать ранг. Будем работать с датасетом kinopoisk.
Сначала посчитаем ранг по всей таблице по убыванию рейтинга (поле rating_balls). В таком случае окно — это вся таблица.
-- Просто ранг по всей таблице
select
year
,movie
,country
,rating_balls
,dense_rank() over(order by rating_balls desc)
from kinopoisk k
Теперь посчитаем ранг для каждого года. В таком случае окно — это год. Ранг считается отдельно для каждого года.
-- Ранг для каждого года
select
year
,movie
,country
,rating_balls
,dense_rank() over(partition by year order by rating_balls desc) as rnk
from kinopoisk k
order by year desc, rnk
Сделаем теперь примерно то же самое, но для каждой страны. Окно — это страна. Для каждой страны будет считаться свой ранг.
-- Ранг для каждой страны
select
year
,movie
,country
,rating_balls
,dense_rank() over(partition by country order by rating_balls desc) as rnk
from kinopoisk k
order by country, rnk
Найдем тройку фильмов с самым высоким rating_balls для каждой страны. Здесь нужно воспользоваться CTE или подзапросом, чтобы оставить только 3 строки.
-- Тройка лучших для каждой страны
with cte as (
select
year
,movie
,country
,rating_balls
,dense_rank() over(partition by country order by rating_balls desc) as rnk
from kinopoisk k
order by country, rnk)
select *
from cte
where rnk between 1 and 3
Разделим все фильмы на 3 равные группы по рейтингу. Для этого есть функция ntile. Она делит ряд на n равных частей.
--Группы по рейтингу
select
year
,movie
,country
,rating_balls
,ntile(3) over (order by rating_balls desc) as grp
from kinopoisk k
Сделаем то же самое, но разделим на группы по году выпуска. Здесь хорошо видно, что один год может попасть в разные группы. Например, 1988 год входит как в первую группу, так и во вторую. Функция делит данные на равные группы. Бывает, что равные группы не получаются. В таких случаях равные значения окажутся в разных группах.
Если количество строк не делится без остатка на n, то в одной из групп окажется больше строк, чем в остальных. Например, в нашим случае 250 строк не получится поделить на 3 равные группы. Тогда в одной группе будет 84 строки, а в остальных по 83.
-- Группы по году
select
year
,movie
,country
,rating_balls
,ntile(3) over (order by year) as grp
from kinopoisk k
Сравниванием функции ранкинга
Существует несколько функций ранкинга:
- dense_rank
- rank
- row_number
Посмотрим значение ранга для тройки лидеров. Начнем с функции dense_rank. У первых двух фильмов одинаковый ранг со значением 1. Следом идет фильм с рангом 2.
select
year
,movie
,country
,round(rating_balls, 1) as rating_balls
,dense_rank() over(order by round(rating_balls, 1) desc)
from kinopoisk k
Сделаем то же самое с помощью функции rank. Третью строчку занимает фильм с рангом 3, а ранга 2 вообще нет в таблице.
-- rank
select
year
,movie
,country
,round(rating_balls, 1) as rating_balls
,rank() over(order by round(rating_balls, 1) desc)
from kinopoisk k
Теперь воспользуемся функцией row_number. Несмотря на то, что у первых двух фильмов оценки равны, ранги разные. Функция не присваивает равный ранг.
-- row_number
select
year
,movie
,country
,round(rating_balls, 1) as rating_balls
,row_number() over(order by round(rating_balls, 1) desc)
from kinopoisk k
