SQL Базовый №14. Оконные функции: Смещение

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

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

• YouTube
• VK

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

В этом уроке мы поработаем с датасетом usdcad. Это история курсов валют.

Функции LAG() и LEAD()

Функция LAG(столбец, смещение) позволяет сместиться на указанное количество строк назад. Функция LEAD(столбец, смещение) позволяет сместиться на выбранное количество строк вперед.

Получим котировку close предыдущего дня и вычислим процентный прирост.

-- Цена закрытия предыдущего дня и процентное изменение
-- Используем функцию lag
select
  *
  ,lag(p_close, 1) over() as p_close_prev
  ,round( (p_close - lag(p_close, 1) over()) /
     lag(p_close, 1) over() * 100, 2) as pct_change
from usdcad

Функции FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()

Функции работают с фреймом. FIRST_VALUE(столбец) получает первое значение фрейма, LAST_VALUE(столбец) — последнее.

Фрейм — это диапазон от начала секции до текущей строки. Первая строка у секции и фрейма совпадает. Чтобы получить последнее значение секции нужно расширить фрейм до размера секции с помощью команды rows between unbounded preceding and unbounded following. Подробнее об этом можно узнать из видео.

-- Из каждого close вычесть open первого дня месяца
-- Функция first_value
select
  *
  ,first_value(p_open)
     over(partition by extract(year from q_date), extract(month from q_date) order by q_date)
     as p_open_month
  ,p_close - 
     first_value(p_open)
       over(partition by extract(year from q_date), extract(month from q_date) order by q_date) as change_from_start_of_month
from usdcad u

-- Из каждого open вычесть close последнего дня месяца года
-- Функция last_value
select
  *
  ,last_value(p_close) 
     over(partition by extract(year from q_date), extract(month from q_date) order by q_date rows between unbounded preceding and unbounded following)
     as p_close_month
  ,p_open - 
     last_value(p_close) 
       over(partition by extract(year from q_date), extract(month from q_date) order by q_date rows between unbounded preceding and unbounded following) as change_from_start_of_month
from usdcad u

Функция NTH_VALUE(столбец, смещение)

Функция тоже работает с фреймом, но позволяет получить любое значение из фрейма. Значение фрейма задается командой ROWS BETWEEN. Подробнее об этом можно узнать из видео. Подробнее про ROWS BETWEEN я расскажу в следующем уроке.

-- Из каждого open вычесть close 5 дней назад
-- Функция nth_value - значение из n строки фрейма
select
  *
  ,nth_value(p_close, 1) over(order by q_date rows between 5 preceding and 0 following) as p_close_5d_ago
  ,p_open - nth_value(p_close, 1)
  			over(order by q_date
  				 rows between 5 preceding
  				 and 0 following) as difference
from usdcad u