Функция SQL Server LEAD

В этом руководстве вы узнаете, как использовать функцию SQL Server LEAD() для доступа к строке с определенным физическим смещением, которая следует за текущей строкой.

Обзор функции SQL Server LEAD()

SQL Server LEAD() — это оконная функция, которая обеспечивает доступ к строке с указанным физическим смещением, следующей за текущей строкой.

Например, используя функцию LEAD(), из текущей строки можно получить доступ к данным следующей строки или строки после следующей строки и т. д.

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

Ниже показан синтаксис функции LEAD():

LEAD(return_value ,offset [,default]) 
OVER(
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

В этом синтаксисе:

возвращаемое_значение

Возвращаемое значение следующей строки на основе указанного смещения. Возвращаемое значение должно оцениваться как одно значение и не может быть другой функцией окна.

компенсировать

offset — это количество строк вперед от текущей строки, из которой осуществляется доступ к данным. Смещение может быть выражением, подзапросом или столбцом, который вычисляется как положительное целое число.

Значение смещения по умолчанию равно 1, если оно не указано явно.

по умолчанию

Функция возвращает значение по умолчанию, если смещение выходит за рамки раздела. Если не указано, по умолчанию используется значение NULL.

РАЗДЕЛЕНИЕ ПО пункту

Предложение PARTITION BY распределяет строки результирующего набора по разделам, к которым применяется функция LEAD().

Если предложение PARTITION BY не указано, функция обрабатывает весь результирующий набор как один раздел.

Предложение ORDER BY

Предложение ORDER BY определяет логический порядок строк в каждом разделе, к которому применяется функция LEAD().

Примеры функции SQL Server LEAD()

Давайте создадим новое представление с именем sales.vw_netsales_brands для демонстрации:

CREATE VIEW sales.vw_netsales_brands
AS
 SELECT 
 c.brand_name, 
 MONTH(o.order_date) month, 
 YEAR(o.order_date) year, 
 CONVERT(DEC(10, 0), SUM((i.list_price * i.quantity) *(1 - i.discount))) AS net_sales
 FROM sales.orders AS o
 INNER JOIN sales.order_items AS i ON i.order_id = o.order_id
 INNER JOIN production.products AS p ON p.product_id = i.product_id
 INNER JOIN production.brands AS c ON c.brand_id = p.brand_id
 GROUP BY c.brand_name, 
 MONTH(o.order_date), 
 YEAR(o.order_date);

Следующий запрос возвращает данные из представления sales.vw_netsales_brands:

SELECT 
 *
FROM 
 sales.vw_netsales_brands
ORDER BY 
 year, 
 month, 
 brand_name, 
 net_sales;

Вот что получилось:

A) Пример использования функции SQL Server LEAD() для набора результатов

Следующий оператор использует функцию LEAD() для возврата чистых продаж текущего месяца и следующего месяца 2017 года:

WITH cte_netsales_2017 AS(
 SELECT 
 month, 
 SUM(net_sales) net_sales
 FROM 
 sales.vw_netsales_brands
 WHERE 
 year = 2017
 GROUP BY 
 month
)
SELECT 
 month,
 net_sales,
 LEAD(net_sales,1) OVER(
 ORDER BY month
 ) next_month_sales
FROM 
 cte_netsales_2017;

Вот что получилось:

В этом примере:

• Во-первых, CTE возвращает чистые продажи, сгруппированные по месяцам.
• Затем внешний запрос использует функцию LEAD() для возврата продаж за следующий месяц для каждого месяца.

Сделав это, вы сможете легко сравнить продажи текущего месяца со следующим месяцем.

B) Пример использования функции SQL Server LEAD() для разделов

В следующем операторе функция LEAD() используется для сравнения продаж текущего месяца со следующим месяцем каждого бренда в 2018 году:

SELECT 
 month,
 brand_name,
 net_sales,
 LEAD(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 сортирует строки по месяцу.
• Для каждой строки в каждом разделе функция LEAD() возвращает чистый объем продаж следующей строки.

В этом руководстве вы узнали, как использовать функцию SQL Server LEAD() для доступа к строке с определенным физическим смещением, которая следует за текущей строкой.