LEAD ФУНКЦИЯ — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как использовать Oracle/PLSQL функцию LEAD с синтаксисом и примерами.

Описание

Oracle/PLSQL функция LEAD является аналитической функцией, что позволяет запрашивать более одной строки в таблице, в то же время, не имея для присоединения к себе таблицы. Возвращает значения из следующей строки в таблице. Для возврата значения из предыдущего ряда, попробуйте использовать функцию LAG.

Синтаксис

Синтаксис Oracle/PLSQL функции LEAD:

LEAD ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

Параметры или аргументы

expression выражение, которое может содержать другие встроенные функции, но не может содержать аналитические функции.

offset - необязательный. Это физические смещение от текущей строки в таблице. Если этот параметр не указан, то по умолчанию 1.

default - необязательный. Это значение, которое возвращается, если offset выходит за границы таблицы. Если этот параметр не указан, то по умолчанию Null.

query_partition_clause - необязательный. Он используется для разделения результатов на группы на основе одного или нескольких выражений.

order_by_clause - необязательный. Он используется для упорядочения данных в каждом разделе.

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

Применение

Функцию LEAD можно использовать в следующих версиях Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Пример

Функция LEAD может быть использована в Oracle/PLSQL.

Рассмотрим пример. Если у нас есть таблица orders, которая содержит следующие данные:

ORDER_DATE	PRODUCT_ID	QTY
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

И мы запустим следующий запрос:

SELECT product_id, 
       order_date,
       LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
  FROM orders;

SELECT product_id,

order_date,

LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date

FROM orders;

То получим следующий результат:

PRODUCT_ID	ORDER_DATE	NEXT_ORDER_DATE
1000	25/09/2007	26/09/2007
2000	26/09/2007	27/09/2007
1000	27/09/2007	28/09/2007
2000	28/09/2007	29/09/2007
2000	29/09/2007	30/09/2007
1000	30/09/2007

Так как мы использовали offset 1, запрос возвращает следующий ORDER_DATE.

Если бы мы использовали offset 2 вместо этого, он бы вернул ORDER_DATE 2 заказами позже. Если бы мы использовали offset 3, то это вернуло бы ORDER_DATE на 3 заказа позже .... и так далее.

Если мы захотим узнать только заказы для данного product_id, выполним следующий SQL запрос:

SELECT product_id, 
	   order_date,
	   LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
  FRON orders
 WHERE product_id = 2000;

SELECT product_id,

order_date,

LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date

FRON orders

WHERE product_id = 2000;

Запрос вернет следующий результат:

PRODUCT_ID	ORDER_DATE	NEXT_ORDER_DATE
2000	26/09/2007	28/09/2007
2000	28/09/2007	29/09/2007
2000	29/09/2007

В этом примере, вернется следующий ORDER_DATE для product_id = 2000 и будут проигнорированы все другие заказы.

Использование partitions
Теперь давайте посмотрим на более сложный пример, в котором мы используем параметр запроса partition для возврата следующей order_date для каждого product_id.

Введите следующий оператор SQL:

SELECT product_id, 
	   order_date,
       LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS next_order_date
  FROM orders;

SELECT product_id,

order_date,

LEAD (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS next_order_date

FROM orders;

ORDER_DATE	PRODUCT_ID	QTY
1000	2007/09/25	2007/09/27
1000	2007/09/27	2007/09/30
1000	2007/09/30	NULL
2000	2007/09/26	2007/09/28
2000	2007/09/28	2007/09/29
2000	2007/09/29	NULL

В этом примере функция LEAD разделит результаты по product_id, а затем отсортирует по order_date, как указано в PARTITION BY product_id ORDER BY order_date. Это означает, что функция LEAD будет оценивать значение order_date, только если product_id совпадает с product_id текущей записи. Когда встречается новый product_id, функция LEAD перезапускает свои вычисления и использует соответствующий раздел product_id.

Как вы можете видеть, третья запись в наборе результатов имеет значение NULL для next_order_date, потому что это последняя запись для раздела, где product_id равен 1000 (отсортировано по order_date). Это также верно для 6-й записи, где product_id равен 2000.