Функции LEAD и LAG
Часть раздела Основы путешествия по SQL на Coddy — урок 61 из 72.
Функции LEAD и LAG позволяют нам обращаться к значению текущей строки на n шагов назад или на n шагов вперед.
Например, если мы хотим вычислить соотношение выручки компании для текущей строки и одного месяца назад, мы можем извлечь значение из предыдущего месяца:
| id | revenue | month |
| 1 | 532 | 5 |
| 2 | 492 | 6 |
| 3 | 393 | 7 |
| 4 | 723 | 8 |
SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY idЭто создаст следующую таблицу:
| id | revenue | prev_month_revenue |
| 1 | 532 | |
| 2 | 492 | 532 |
| 3 | 393 | 492 |
| 4 | 723 | 393 |
Таким образом, мы можем вычислить отношение prev_month_revenue/revenue.
Если мы вместо этого использовали функцию LEAD, она бы взяла выручку следующего месяца для каждой строки:
SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id| id | revenue | next_month_revenue |
| 1 | 532 | 492 |
| 2 | 492 | 393 |
| 3 | 393 | 723 |
| 4 | 723 |
Задание
ЛегкоДоступные таблицы и столбцы:
<strong>air_conditioners</strong>:<strong>id</strong>,<strong>efficiency</strong>,<strong>strength</strong>,<strong>month</strong>
Сравните эффективность каждого кондиционера воздуха с предыдущим.
Напишите запрос, который показывает эффективность каждого кондиционера воздуха вместе с эффективностью предыдущего установленного кондиционера (на основе id). Также вычислите разницу в эффективности между текущим и предыдущим кондиционером. Отсортируйте результат по <strong>id</strong> и <strong>efficiency</strong> в порядке возрастания.
Ожидаемые столбцы вывода:
idefficiencyprevious_efficiency(используя LAG)efficiency_difference(текущая эффективность - предыдущая эффективность)
Наконец, после этого запроса отфильтруйте строки, где previous_efficiency пустая или efficiency_difference пустая, следующим образом:
SELECT * FROM (
-- Your query here
)
WHERE previous_efficiency != 0Шпаргалка
Функции LAG и LEAD позволяют получать доступ к значениям из предыдущих или следующих строк:
Функция LAG — получает значение из предыдущей строки:
LAG(column_name, n) OVER (ORDER BY column)Функция LEAD — получает значение из следующей строки:
LEAD(column_name, n) OVER (ORDER BY column)Пример использования LAG для получения выручки предыдущего месяца:
SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue
FROM table1 ORDER BY idПример использования LEAD для получения выручки следующего месяца:
SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY idВ первой строке значение LAG будет NULL, а в последней строке значение LEAD будет NULL.
Попробуйте сами
Проверьте себяВ этом уроке есть небольшой тест. Начните урок, чтобы ответить на вопросы и сохранить прогресс.
Все уроки раздела Основы
4Больше ключевых слов
Ключевое слово INКлючевое слово BETWEENКлючевое слово LIKEКлючевое слово ASПовторение - Модели мобильных телефонов
2Условия
Основы условийКлючевое слово ANDКлючевое слово ORКлючевое слово NOTКомбинирование нескольких условийСкобкиБулевы значения5Арифметические операции
Математические операторыМатематические столбцыОперация moduloФункция ROUND()8Статистика
Встроенные агрегатные функции. Часть 1Встроенные агрегатные функции. Часть 2Группировка. Часть 1Группировка. Часть 2Подзапросы. Часть 1Подзапросы. Часть 2Повторение - Магазин общей прибылиПовторение - Магазин скутеровПовторение - Кофейня11Оконные функции, часть 1
Функция ROW_NUMBERКритерий ORDER BYКритерий PARTITION BYPARTITION и ORDERФункции LEAD и LAGПовторение - LEAD и LAGПовторение - КартинкиПовторение - Блоки3Специальный формат возврата
Значения NULLСортировка результатов, часть 1Сортировка результатов, часть 2Повторение - Фирма по кибербезопасностиОграничение количества записейПовторение - Автозавод6Вводные вызовы
Повторение - Парламентские выборыПовторение - Арест преступника полициейПовторение - Ёмкость для напитков в бареПовторение - Создание новых столбцов