В этом уроке вы узнаете, как использовать функцию LAG() для доступа к строке с определенным физическим смещением, которая находится перед текущей строкой.
Обзор функции SQL Server LAG()
LAG() SQL Server — это оконная функция, которая обеспечивает доступ к строке с указанным физическим смещением, которая находится перед текущей строкой.
Другими словами, используя функцию LAG(), из текущей строки можно получить доступ к данным предыдущей строки или строки перед предыдущей строкой и т. д.
Функция LAG() может быть очень полезна для сравнения значения текущей строки со значением предыдущей строки.
Ниже показан синтаксис функции LAG():
LAG(return_value ,offset [,default])
OVER(
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
В этом синтаксисе:
возвращаемое_значение
Возвращаемое значение предыдущей строки на основе указанного смещения. Возвращаемое значение должно оцениваться как одно значение и не может быть другой оконной функцией.
компенсировать
Количество строк от текущей строки, из которых следует получить доступ к данным. offset может быть выражением, подзапросом или столбцом, результатом которого является положительное целое число.
Значение смещения по умолчанию равно 1, если оно не указано явно.
по умолчанию
default — это возвращаемое значение, если offset выходит за рамки раздела. По умолчанию NULL, если не указано иное.
РАЗДЕЛЕНИЕ ПО пункту
Предложение PARTITION BY распределяет строки результирующего набора по разделам, к которым применяется функция LAG().
Если опустить предложение PARTITION BY, функция будет обрабатывать весь результирующий набор как один раздел.
Предложение ORDER BY
Предложение ORDER BY определяет логический порядок строк в каждом разделе, к которому применяется функция LAG().
Примеры функций SQL Server LAG()
Для демонстрации мы повторно используем представление sales.vw_netsales_brands, созданное в руководстве по функции LEAD().
Следующий запрос показывает данные из представления sales.vw_netsales_brands:
SELECT * FROM sales.vw_netsales_brands ORDER BY year, month, brand_name, net_sales;
Вот что получилось:
A) Пример использования функции SQL Server LAG() для набора результатов
В этом примере функция LAG() используется для возврата чистых продаж за текущий месяц и предыдущий месяц 2018 года:
WITH cte_netsales_2018 AS( SELECT month, SUM(net_sales) net_sales FROM sales.vw_netsales_brands WHERE year = 2018 GROUP BY month ) SELECT month, net_sales, LAG(net_sales,1) OVER( ORDER BY month ) previous_month_sales FROM cte_netsales_2018;
Вот что получилось:
В этом примере:
- Во-первых, CTE возвращает чистые продажи, сгруппированные по месяцам.
- Затем внешний запрос использует функцию LAG() для возврата продаж за предыдущий месяц.
B) Пример использования функции SQL Server LAG() для разделов
В следующем операторе используется функция LAG() для сравнения продаж текущего месяца с предыдущим месяцем каждого бренда в 2018 году:
SELECT month, brand_name, net_sales, LAG(net_sales,1) OVER( PARTITION BY brand_name ORDER BY month ) next_month_sales FROM sales.vw_netsales_brands WHERE year = 2018;
На этой картинке показан результат:
В этом примере:
- Предложение PARTITION BY разделило строки на разделы по названию бренда.
- Для каждого раздела(или наименования бренда) предложение ORDER BY сортирует строки по месяцу.
- Для каждой строки в каждом разделе функция LAG() возвращает чистый объем продаж предыдущей строки.
Чтобы сравнить продажи текущего месяца с чистыми продажами по брендам за предыдущий месяц 2018 года, используйте следующий запрос:
WITH cte_sales AS( SELECT month, brand_name, net_sales, LAG(net_sales,1) OVER( PARTITION BY brand_name ORDER BY month ) previous_sales FROM sales.vw_netsales_brands WHERE year = 2018 ) SELECT month, brand_name, net_sales, previous_sales, FORMAT( (net_sales - previous_sales) / previous_sales, 'P' ) vs_previous_month FROM cte_sales;
Вывод следующий:
В этом руководстве вы узнали, как использовать функцию SQL Server LAG() для доступа к строке с определенным физическим смещением, которая следует за текущей строкой.
