SQL Оконные Функции: Смещение, Lead, Lag, First и Last Value на Практике

В начале видео автор приветствует зрителей и объявляет, что это четырнадцатый урок базового курса по SQL, посвящённый теме смещения с использованием оконных функций. Основное внимание уделяется функциям LAG, LEAD, FIRST_VALUE и LAST_VALUE, которые позволяют получать значения из других строк таблицы, а не только из текущей.

"Функции смещения позволяют нам получить значения которые находятся не в текущей строчке, а в другой строчке, например на предыдущей строчке, на одну строчку выше, на одну ниже, на пять строчек выше и так далее." 0:22

Ключевые моменты:

• Функции смещения позволяют обращаться к значениям в соседних строках.
• Можно получить первое или последнее значение в выбранной партиции.
• Для урока используется датасет usdcad с данными по валютной паре.

Автор напоминает, что все файлы и домашние задания доступны спонсорам канала.

---

Автор показывает, как использовать функцию LAG для получения значения из предыдущей строки. В качестве примера берётся столбец с ценой закрытия (Close).

"У этой функции два параметра: первый — это столбец, из которого мы хотим получить значение, в нашем случае это цена закрытия, дальше запятая и второй параметр — это сколько строк от текущей данное значение должно быть выше." 1:44

• Функция LAG принимает два параметра: столбец и смещение (число строк вверх).
• Внутри OVER обязательно указывается сортировка, чтобы гарантировать правильный порядок.
• Пример: LAG(Close, 1) OVER (ORDER BY Date) — значение цены закрытия предыдущего дня.
• Можно менять смещение, например, на 2 строки выше.

---

Далее автор демонстрирует функцию LEAD, которая работает аналогично LAG, но смещается вниз по таблице.

"Давайте проверим как работает функция Lead... мы видим здесь 1.5123, перемещаемся вниз на одну строчку, смотрим столбец CL и видим здесь тоже 1.5123." 3:39

• LEAD позволяет получить значение из следующей строки.
• Аналогично LAG, принимает параметры: столбец и смещение вниз.
• Пример: LEAD(Close, 1) OVER (ORDER BY Date) — значение цены закрытия следующего дня.

---

Автор решает практическую задачу — вычислить процентное изменение цены закрытия по сравнению с предыдущим днём.

"Нам нужно получить значение CL предыдущего дня и найти процентное изменение... берём текущий CL, из него вычитаем предыдущий, делим на предыдущее значение." 4:14

• Используется LAG для получения предыдущего значения.
• Формула процентного изменения: (Close - PrevClose) / PrevClose.
• Рекомендуется умножить результат на 100 и округлить до двух знаков для удобства восприятия.

---

Автор переходит к функциям FIRST_VALUE и LAST_VALUE, которые возвращают первое и последнее значение в рамках окна (фрейма).

"First value и Last value работают с фреймом, а не с секцией... начало фрейма и секции всегда совпадают, поэтому для First value результат будет один и тот же." 5:22

• FIRST_VALUE возвращает первое значение в окне.
• Для корректной работы обязательно указывать сортировку в OVER.
• Можно использовать PARTITION BY для группировки, например, по годам.
• Пример: получение первого значения открытия (Open) для каждого года.

---

Автор показывает, как разбить данные по годам и месяцам, чтобы получить первое значение открытия для каждого месяца.

"Добавляем Partition by, используем функцию extract для получения года и месяца из даты." 7:13

• Используется PARTITION BY EXTRACT(YEAR FROM Date), EXTRACT(MONTH FROM Date).
• Это позволяет получить первое значение для каждого месяца отдельно.
• Пример: вычисление разницы между текущим Close и первым Open дня месяца.

---

Автор объясняет, что функция LAST_VALUE по умолчанию возвращает последнее значение в текущем фрейме, который расширяется от начала до текущей строки, а не до конца секции.

"Фрейм в первой строчке равен первой строчке, во второй — двум, в третьей — трём и так далее... поэтому в первой ячейке мы не получаем последнее значение." 10:17

• Фрейм расширяется от начала до текущей строки.
• Чтобы получить последнее значение всего окна, нужно расширить фрейм с помощью ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
• Это позволяет охватить все строки партиции.

---

Автор показывает, как добавить в OVER конструкцию ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, чтобы LAST_VALUE возвращала последнее значение всей партиции.

"Добавляем rows between unbounded preceding and unbounded following, и теперь получаем одно значение во всём столбце." 12:07

• Эта команда расширяет окно на все строки партиции.
• После этого LAST_VALUE возвращает именно последнее значение.
• Пример: получение последнего значения Close для всего датасета или для каждого месяца.

---

Автор решает задачу: из каждого значения Open вычесть последнее значение Close для соответствующего месяца и года.

"Нужно из Open вычесть Close последнего дня месяца года, для этого добавляем Partition by с годом и месяцем." 13:49

• Используется PARTITION BY с годом и месяцем.
• Вычисляется разница между текущим Open и последним Close месяца.
• Результат рекомендуется округлить и умножить на 100 для удобства.

---

В конце видео автор подводит итоги:

"Сегодня мы изучали функции смещения: LAG и LEAD позволяют получить значения выше или ниже текущей строки, First_value и Last_value работают с фреймом и позволяют получить первое или последнее значение фрейма." 15:22

• Функции LAG и LEAD — для смещения вверх и вниз.
• FIRST_VALUE — легко получить первое значение секции.
• LAST_VALUE требует расширения окна через ROWS BETWEEN для получения последнего значения секции.
• В следующем уроке будет подробно рассмотрена команда ROWS BETWEEN и бегущие агрегаты, например, скользящее среднее и нарастающий итог.

Автор прощается и приглашает к новым встречам.

---

Этот урок даёт чёткое и практическое понимание работы с функциями смещения и оконными функциями в SQL, показывая, как получать значения из соседних строк и как работать с первым и последним значениями в рамках партиций.