Функции 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 |
Задание
ЛегкоДоступные таблицы и столбцы:
air_conditioners:id,efficiency,strength,month
Сравните эффективность каждого кондиционера с предыдущим.
Напишите запрос, который показывает эффективность каждого кондиционера вместе с эффективностью предыдущего установленного кондиционера (на основе id). Также вычислите разницу в эффективности между текущим и предыдущим кондиционером. Отсортируйте результат по id и efficiency в порядке возрастания.
Ожидаемые столбцы вывода:
idefficiencyprevious_efficiency(с использованием LAG)efficiency_difference(текущая эффективность - предыдущая эффективность)
Наконец, оберните запрос и отфильтруйте первую строку, в которой previous_efficiency (и, следовательно, efficiency_difference) является пустым значением, то есть NULL:
SELECT * FROM (
-- Your query here
)
WHERE previous_efficiency IS NOT 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Вводные вызовы
Повторение - Парламентские выборыПовторение - Арест преступника полициейПовторение - Ёмкость для напитков в бареПовторение - Создание новых столбцовПотренируйтесь самостоятельно: Песочница SQL