SQL Базовый №13. Оконные функции: агрегаты

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

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

• YouTube
• VK

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

В этом уроке мы будем работать с датасетами kinopoisk и companies_500.

Вернем средний рейтинг для каждой страны. В результате в каждой строке увидим средний рейтинг страны.

-- Среднее для каждой страны
select
  country 
  ,rating_balls 
  ,round(avg(rating_balls) over(partition by country), 3) as country_avg_rating
from public.kinopoisk

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

-- Максимум для каждой страны
select
  movie
  ,year
  ,country
  ,rating_balls
  ,round(max(rating_balls) over(partition by country), 3) as country_avg_rating
  ,round(max(rating_balls) over(partition by country), 3) - rating_balls as change
from public.kinopoisk

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

-- Максимум для страны в десятилетии
select
  movie
  ,year
  ,year - year % 10 as decade
  ,country
  ,rating_balls
  ,round(max(rating_balls) over(partition by year - year % 10, country), 3) as country_avg_rating
from public.kinopoisk k

Если же в over не передавать параметров, то мы получим значение для всего датасета. Вычислим максимальный рейтинг для всего датасета.

-- Максимум для всего датасета
select
  movie
  ,year
  ,year - year % 10 as decade
  ,country
  ,rating_balls
  ,round(max(rating_balls) over(), 3) as country_avg_rating
from public.kinopoisk k

Вычислим долю каждой компании в каждом секторе. Нам понадобится сумма revenues для каждого сектора.

-- Доля компании в revenues в каждом секторе
select
  sector
  ,industry
  ,title
  ,revenues
  ,sum(revenues) over(partition by sector) as sum_sector
  ,round( cast(revenues as numeric(10, 2)) / sum(revenues) over(partition by sector), 2) as pct_sector
from companies.companies_500
order by
  sum_sector desc
  ,revenues desc

Посчитаем количество компаний в секторе.

-- Количество компаний в секторе
select
  sector
  ,industry
  ,title
  ,revenues
  ,count(title) over(partition by sector) as count_counpanies
from companies.companies_500
order by
  count_counpanies desc
  ,revenues desc

Порядок выполнения операций

1. FROM
2. WHERE
3. SELECT
4. Оконная функция
5. ORDER BY