Lag sql: LAG LEAD | SQL | SQL-tutorial.ru

Содержание

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

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

Описание

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

Синтаксис

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

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

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

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

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

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

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

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

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

Применение

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

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

Пример

Функция LAG может быть использована в 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,
LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders;

select product_id,

order_date,

LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date

from orders;

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

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

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

Если бы мы использовали offset = 2 вместо 1, то запрос вернул бы ORDER_DATE на 2 позиции ранее. Если бы мы использовали offset = 3, то запрос вернул бы ORDER_DATE на 3 позиции ранне …. и так далее.

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

SELECT product_id,
order_date,
LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
FROM orders
WHERE product_id = 2000;

SELECT product_id,

order_date,

LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date

FROM orders

WHERE product_id = 2000;

Получим результат:

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

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

Использование partition

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

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

SELECT product_id,
order_date,
LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date
FROM orders;

SELECT product_id,

order_date,

LAG (order_date,1) OVER (PARTITION BY product_id ORDER BY order_date) AS prev_order_date

FROM orders;

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

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

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

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

SQL Server функция LAG — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

function-sql-server-lag
В этом учебном пособии вы узнаете, как использовать функцию LAG в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

В SQL Server (Transact-SQL) функция LAG является аналитической функцией, которая позволяет запрашивать более одной строки в таблице одновременно без необходимости присоединяться к таблице. Она возвращает значения из предыдущей строки в таблице. Чтобы вернуть значение из следующей строки, попробуйте использовать функцию LEAD.

Синтаксис

Синтаксис функции LAG в SQL Server (Transact-SQL):

LAG ( expression [, offset [, default] ] )
OVER ( [ query_partition_clause ] order_by_clause )

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

expression — выражение, которое может содержать другие встроенные функции, но не могут содержать аналитических функций.
offset — необязательный. Это физическое смещение от текущей строки в таблице. Если этот параметр опущен, значением по умолчанию является 1.
default — необязательный. Это значение возвращается, если смещение выходит за границы таблицы. Если этот параметр опущен, значение по умолчанию равно null.
query_partition_clause — необязательный. Он используется для разделения результатов на группы на основе одного или нескольких выражений.
order_by_clause — необязательный. Он используется для упорядочивания данных в каждом разделе.

Применение

Функция LAG может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012

Пример

Давайте посмотрим на пример. Если бы у нас была таблица employees, которая содержала следующие данные:

employee_number	last_name	first_name	salary	dept_id
12009	Матвеева	Галина	54000	45
34974	Шапошников	Семён	80000	45
34987	Верещагин	Павел	42000	45
45001	Сухов	Фёдор	57500	30
75623	Смирнов	Андрей	65000	30

И мы выполнили следующую оператор SQL:

SELECT dept_id, last_name, salary,
LAG (salary,1) OVER (ORDER BY salary) AS lower_salary
FROM employees;

SELECT dept_id, last_name, salary,

LAG (salary,1) OVER (ORDER BY salary) AS lower_salary

FROM employees;

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

dept_id	last_name	salary	lower_salary
45	Верещагин	42000	NULL
45	Матвеева	54000	42000
30	Сухов	57500	54000
30	Смирнов	65000	57500
45	Шапошников	80000	65000

В этом примере функция LAG будет сортировать в порядке возрастания все значения salary в таблице employees, а затем вернет salary, которая на 1 позицию ниже в результирующем наборе, поскольку мы использовали offset 1.

Использование partitions

Теперь давайте рассмотрим более сложный пример, в котором мы в запросе используем условие partition, чтобы вернуть lower_salary для каждого сотрудника в своем отделе.
Выполните следующий оператор SQL:

SELECT dept_id, last_name, salary,
LAG (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS lower_salary
FROM employees;

SELECT dept_id, last_name, salary,

LAG (salary,1) OVER (PARTITION BY dept_id ORDER BY salary) AS lower_salary

FROM employees;

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

dept_id	last_name	salary	lower_salary
30	Сухов	57500	NULL
30	Смирнов	65000	57500
45	Верещагин	42000	NULL
45	Матвеева	54000	42000
45	Шапошников	80000	54000

В этом примере функция LAG будет разбивать результаты на dept_id, а затем сортировать по salary, как указано PARTITION BY dept_id ORDER BY. Это означает, что функция LAG будет оценивать только значение зарплаты, если dept_id соответствует dept_id текущей записи. Когда встречается новый dept_id, функция LAG перезапускает свои вычисления и использует соответствующий раздел dept_id.
Как вы можете видеть, первая запись в результирующем наборе имеет значение NULL для lower_salary, потому что это первая запись для раздела, где dept_id — 30 (отсортировано по salary), поэтому нет более низкого значения зарплаты. Это также относится к третьей записи, где dept_id — 45.

Функция LAG в MySQL | ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)

Описание: в этой статье вы узнаете, как использовать функцию LAG() в MySQL для доступа к данным предыдущей строки из текущей строки в том же наборе результатов.

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

Следующий код иллюстрирует синтаксис функции LAG():

LAG(<expression>[,offset[, default_value]]) OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)

Выражение

Функция LAG() возвращает значение из expression из строки, которая предшествует текущую строку по offset количеству строк в пределах его раздела или результирующий набор.

Смещение

offset – это число строк назад от текущей строки, из которой нужно получить значение. offset должен быть равен нулю или должно быть положительным целым числом. Если offset равен нулю, то функция LAG() оценивает expression текущей строки. Если вы не укажете offset, то функция LAG() использует один по умолчанию.

Значение по умолчанию

Если предшествующей строки нет, функция LAG() возвращает default_value. Например, если смещение равно 2, возвращаемым значением для первой строки является default_value. Если вы опустите default_value, функция LAG() вернется NULL по умолчанию.

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

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

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

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

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

Пример функции LAG() в MySQL

Мы будем использовать таблицы orders, orderDetails и productLines из образца базы данных для демонстрации.

Следующий оператор возвращает значение заказа для каждой линейки продуктов в конкретном году и в предыдущем году:

WITH productline_sales AS (
SELECT productline,
YEAR(orderDate) order_year,
ROUND(SUM(quantityOrdered * priceEach),0) order_value
FROM orders
INNER JOIN orderdetails USING (orderNumber)
INNER JOIN products USING (productCode)
GROUP BY productline, order_year
)
SELECT
productline,
order_year,
order_value,
LAG(order_value, 1) OVER (
PARTITION BY productLine
ORDER BY order_year
) prev_year_order_value
FROM
productline_sales;

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

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

Обратите внимание, что мы использовали функцию ROUND() для округления значений порядка до нуля.

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

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

SQL — LAG и LEAD

пример

Функция LAG предоставляет данные о строках перед текущей строкой в том же наборе результатов. Например, в SELECT вы можете сравнивать значения в текущей строке со значениями в предыдущей строке.

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

Параметр по умолчанию указывает значение, которое должно быть возвращено, когда выражение со смещением имеет значение NULL . Если вы не укажете значение, возвращается значение NULL .

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

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

Вы указываете значение, которое должно быть возвращено, когда выражение со смещением имеет значение NULL используя параметр по умолчанию. Если вы не укажете эти параметры, используется значение по умолчанию для одной строки и возвращается значение NULL .

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

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

BusinessEntityID	SalesYTD	Значение свинца	Значение запаса
274	559697.5639	3763178.1787	0,0000
275	3763178.1787	4251368.5497	559697.5639
276	4251368.5497	3189418.3662	3763178.1787
277	3189418.3662	1453719.4653	4251368.5497
278	1453719.4653	2315185.6110	3189418.3662
279	2315185.6110	1352577.1325	1453719.4653

Обзор функции задержки SQL и примеры

В статье Обзор и примеры функции SQL Server Lead мы исследовали функцию Lead для выполнения вычислительных операций с данными. В этой статье дается обзор функции SQL Lag и ее сравнение с функцией SQL Lead.

Обзор функции SQL Lag

Мы используем функцию Lag () для доступа к данным предыдущих строк в соответствии с определенным значением смещения. Это оконная функция, доступная начиная с SQL Server 2012.Она работает аналогично функции Lead. В ведущей функции мы получаем доступ к последующим строкам, но в функции задержки мы обращаемся к предыдущим строкам. Это полезная функция для сравнения текущего значения строки со значением предыдущей строки.

Синтаксис функции задержки

LAG (скалярное_выражение [, смещение] [, по умолчанию])

OVER ([partition_by_clause] order_by_clause)

Он использует следующие аргументы.

Scalar_expression: в этом аргументе мы определяем имя столбца или выражение. Функция задержки выполняет вычисления в этом столбце. Это обязательный аргумент, без которого мы не сможем выполнить функцию задержки.
Смещение: в этом аргументе мы определяем целое число. Функция запаздывания использует этот аргумент для отказа от количества строк (смещения). Значение по умолчанию для этого аргумента — единица. Это необязательный аргумент
По умолчанию: Предположим, мы определяем смещение, значение, которое не лежит в границах данных.Например, мы указали значение смещения 3 для первой строки. Функция задержки не может отставать на три строки. Если указано, отображается значение по умолчанию. Если мы не укажем никакого значения для этого, функция задержки отображает NULL в выходных данных для значений вне диапазона
РАЗДЕЛЕНИЕ ПО: создает логическую границу данных. Предположим, у нас есть обширный набор данных, и нам требуются вычисления на меньшем наборе данных, мы можем определить для него разделы. Например, данные о продажах организации могут содержать данные за несколько лет.Мы можем создавать раздел ежеквартально и производить вычисления. Это также необязательный аргумент
ORDER BY: мы можем сортировать данные в порядке возрастания или убывания, используя предложение ORDER by. По умолчанию для сортировки данных используется возрастающий порядок.

Мы также будем использовать данные из предыдущей статьи для демонстрации функции SQL Server Lag:

DECLARE @Employee TABLE

(

EmpCode VARCHAR (10),

EmpName VARCHAR (10),

JoiningDate DATE

)

INSERT INTO @Employee VALUES ‘, Rajendra’ VALUES ‘, Rajendra’ -Sep-2018 ‘)

INSERT INTO @Employee VALUES (‘ 2 ‘,’ Manoj ‘,’ 1-Oct-2018 ‘)

INSERT INTO @Employee VALUES (‘ 3 ‘,’ Sonu ‘, ’10 -Mar -2018 ‘)

INSERT INTO @Employee VALUES (‘ 4 ‘,’ Kashish ‘, ’25 -Oct-2018’)

INSERT INTO @Employee VALUES (‘5’, ‘Tim’, ‘1-Dec-2018 ‘)

INSERT INTO @Employee VALUES (‘ 6 ‘,’ Akshita ‘,’ 1-Nov-2018 ‘)

SELECT * FROM @Employee;

У нас есть следующие данные в таблице Employee:

Пример 1: Функция задержки SQL без значения по умолчанию

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

Выполните следующий запрос (нам необходимо выполнить полный запрос вместе с определением переменной, ее значения):

SELECT *,

Lag (JoiningDate, 1) OVER (.

ORDER BY JoiningDate ASC) AS EndDate

FROM @Employee;

На выходе мы можем отметить следующее:

В первой строке отображается значение NULL для столбца EndDate, поскольку в нем нет предыдущих строк.
Вторая строка содержит значение предыдущей строки в столбце EndDate.Принимает значение из предыдущей строки из-за значения смещения 1

Пример 2: Функция SQL Lag со значением по умолчанию

В предыдущем примере мы получили значение NULL как значение по умолчанию. Давайте использовать дату окончания по умолчанию в функции задержки. В этом примере также используется значение смещения 1 в функции задержки:

SELECT *,

Lag (JoiningDate, 1, ‘1999-09-01’) OVER (

ORDER BY JoiningDate ASC) AS EndDate

FROM @Employee;

На выходе мы видим значение по умолчанию вместо NULL в первой строке:

Мы можем использовать совместимые типы данных в столбце значений по умолчанию.Если мы используем несовместимые типы данных, мы получаем следующее сообщение об ошибке:

Пример 3: Функция задержки SQL со значением OFFSET 2

Ранее мы использовали смещение по умолчанию 1 в функции Lag, и оно принимает значение из предыдущей строки. В этом примере мы используем значение смещения 2. В выходных данных вы можете видеть, что у нас есть значение по умолчанию для строк 1 и 2. В строке 3 оно принимает значение из строки 1:

SELECT *,

Lag (JoiningDate, 2, ‘1999-09-01’) OVER (

ORDER BY JoiningDate ASC) AS EndDate

FROM @Employee;

Пример 4: Функция задержки SQL с предложением PARTITION BY

Как обсуждалось ранее, мы используем предложение PARTITION BY для создания логического подмножества данных.Давайте воспользуемся функцией PARTITION в таблице ProductSales. Вы можете обратиться к функции SQL Server Lead, чтобы создать эту таблицу:

В следующем запросе мы используем функцию SQL Server Lag и просматриваем вывод:

SELECT [Год],

[Квартал],

Продажи,

LAG (Продажи, 1, 0) ВЫШЕ (

ORDER BY [Год],

[Quarter] ASC) AS [NextQuarterSales]

ОТ dbo.Продажи продукции;

На выходе функция задержки рассматривает все строки как единый набор данных и применяет функцию задержки:

В таблице ProductSales у нас есть данные за 2017, 2018 и 2019 годы. Мы хотим использовать функцию задержки на годовой основе. Мы используем предложение PARTITION BY в столбце Year и определяем логическое подмножество данных на годовой основе. Мы используем предложение Order by для столбцов года и квартала для сортировки данных сначала по годам, а затем по месяцам:

SELECT [Год],

[Квартал],

Продажи,

LAG (Sales, 1, 0) OVER (РАЗДЕЛЕНИЕ ПО [ГОДУ]

ORDER BY [Год],

[Quarter] ASC) AS [NextQuarterSales]

ОТ dbo.Продажи продукции;

На следующем скриншоте мы видим три раздела данных за 2017,2018 и 2019 год. Функция Lag индивидуально работает с каждым разделом и вычисляет необходимые данные:

Заключение

В этой статье мы узнали о функции SQL Lag и ее использовании для получения значения из предыдущих строк. Вот краткое описание функции задержки:

Функция Lag выбирает значение из предыдущих строк на основе заданного смещения
Смещение один — это значение смещения по умолчанию, и в этом лаге функция извлекает значение из предыдущей строки.
Предложение PARTITION BY определяет логическую границу данных на основе указанного условия
Функция задержки использует значение по умолчанию NULL для данных вне диапазона
Мы можем использовать функцию Lag с общим табличным выражением, хранимыми процедурами и функциями для вычислительных целей.

Раджендра имеет более 8 лет опыта в администрировании баз данных и увлечен оптимизацией производительности баз данных, мониторингом, технологиями обеспечения высокой доступности и аварийного восстановления, изучением новых вещей, новыми функциями.

Работая старшим консультантом по администрированию баз данных для крупных клиентов и получив сертификат MCSA SQL 2012, он любит делиться знаниями в различных блогах.
С ним можно связаться по адресу [email protected]

Посмотреть все сообщения Rajendra Gupta

Последние сообщения Rajendra Gupta (посмотреть все)

SQL Server: функция LAG

В этом руководстве Oracle объясняется, как использовать функцию LAG в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

В SQL Server (Transact-SQL) функция LAG — это аналитическая функция, которая позволяет запрашивать более одной строки в таблице за раз без необходимости присоединять таблицу к самой себе. Он возвращает значения из предыдущей строки таблицы. Чтобы вернуть значение из следующей строки, попробуйте использовать функцию LEAD.

Синтаксис

Синтаксис функции LAG в SQL Server (Transact-SQL):

 LAG (выражение [, смещение [, по умолчанию]])
ВЫШЕ ([раздел_запроса] предложение_заказа)

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

выражение: Выражение, которое может содержать другие встроенные функции, но может содержать , но не аналитических функций.
смещение: Необязательно. Это физическое смещение от текущей строки в таблице.Если этот параметр опущен, по умолчанию используется 1.
по умолчанию: Необязательно. Это значение, которое возвращается, если смещение выходит за пределы таблицы. Если этот параметр опущен, значение по умолчанию — null.
раздел_запроса: Необязательно. Он используется для разделения результатов на группы на основе одного или нескольких выражений.
order_by_clause: Необязательно. Он используется для упорядочивания данных в каждом разделе.

Относится к

Функцию LAG можно использовать в следующих версиях SQL Server (Transact-SQL):

SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012

DDL / DML для примеров

Если вы хотите следовать этому руководству, получите DDL для создания таблиц и DML для заполнения данных. Тогда попробуйте примеры в своей базе данных!

Получить DDL / DML

Пример

Функцию LAG можно использовать в SQL Server (Transact-SQL).

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

Номер сотрудника	фамилия	имя	зарплата	dept_id
12009	Сазерленд	Барбара	54000	45
34974	Йейтс	Фред	80000	45
34987	Эриксон	Нил	42000	45
45001	Паркер	Салли	57500	30
75623	Ворота	Стив	65000	30

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

 ВЫБЕРИТЕ dept_id, last_name, salary,
LAG (зарплата, 1) ВЫШЕ (ЗАКАЗАТЬ ПО зарплате) AS lower_salary
ОТ сотрудников;

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

dept_id	фамилия	зарплата	lower_salary
45	Эриксон	42000	НЕТ
45	Сазерленд	54000	42000
30	Паркер	57500	54000
30	Ворота	65000	57500
45	Йейтс	80000	65000

В этом примере функция LAG отсортирует в порядке возрастания все значения salary в таблице employee , а затем вернет зарплату, которая на 1 позицию ниже в наборе результатов, поскольку мы использовали смещение , равное 1.

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

Использование разделов

Теперь давайте рассмотрим более сложный пример, в котором мы используем предложение разделения запроса, чтобы вернуть более низкую заработную плату для каждого сотрудника в своем отделе.

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

 ВЫБЕРИТЕ dept_id, last_name, salary,
LAG (зарплата, 1) ВЫШЕ (РАЗДЕЛЕНИЕ ПО dept_id ЗАПРАЧЕНО ПО зарплате) КАК lower_salary
ОТ сотрудников;

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

dept_id	фамилия	зарплата	lower_salary
30	Паркер	57500	НЕТ
30	Ворота	65000	57500
45	Эриксон	42000	НЕТ
45	Сазерленд	54000	42000
45	Йейтс	80000	54000

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

Как вы можете видеть, первая запись в наборе результатов имеет значение NULL для lower_salary , потому что это первая запись для раздела e , где dept_id — 30 (отсортировано по зарплате), поэтому нет нижняя зарплата значение.Это также верно для третьей записи, где dept_id — 45.

Oracle / PLSQL: функция LAG

В этом руководстве Oracle объясняется, как использовать функцию Oracle / PLSQL LAG с синтаксисом и примерами.

Описание

Функция Oracle / PLSQL LAG — это аналитическая функция, которая позволяет вам запрашивать более одной строки в таблице за один раз без необходимости присоединять таблицу к самой себе. Он возвращает значения из предыдущей строки таблицы. Чтобы вернуть значение из следующей строки, попробуйте использовать функцию LEAD.

Синтаксис

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

 LAG (выражение [, смещение [, по умолчанию]])
ВЫШЕ ([раздел_запроса] предложение_заказа)

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

выражение: Выражение, которое может содержать другие встроенные функции, но может содержать , но не аналитических функций.
смещение: Необязательно. Это физическое смещение от текущей строки в таблице. Если этот параметр опущен, по умолчанию используется 1.
по умолчанию: Необязательно. Это значение, которое возвращается, если смещение выходит за пределы таблицы. Если этот параметр опущен, значение по умолчанию — null.
раздел_запроса: Необязательно. Он используется для разделения результатов на группы на основе одного или нескольких выражений.
order_by_clause: Необязательно. Он используется для упорядочивания данных в каждом разделе.

Возвращает

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

Относится к

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

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

Пример

Функцию LAG можно использовать в Oracle / PLSQL.

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

ДАТА ЗАКАЗА	PRODUCT_ID	КОЛ-ВО
2007/09/25	1000	20
26.09.2007	2000	15
2007/09/27	1000	8
2007/09/28	2000	12
2007/09/29	2000	2
30.09.2007	1000	4

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

 ВЫБЕРИТЕ product_id, order_date,
LAG (order_date; 1) OVER (ORDER BY order_date) AS prev_order_date
ОТ заказов;

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

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
1000	25.09.2007	НЕТ
2000	26.09.2007	25.09.2007
1000	27.09.2007	26.09.2007
2000	28.09.2007	27.09.2007
2000	29.09.2007	28.09.2007
1000	30.09.2007	29.09.2007

В этом примере функция LAG отсортирует в порядке возрастания все значения order_date в таблице orders , а затем вернет предыдущее значение order_date , поскольку мы использовали смещение , равное 1.

Если бы вместо этого мы использовали смещение , равное 2, было бы возвращено order_date из 2 заказов ранее. Если бы мы использовали смещение , из 3, он вернул бы order_date из 3-х ранее … и так далее.

Использование разделов

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

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

 ВЫБЕРИТЕ product_id, order_date,
LAG (order_date; 1) OVER (PARTITION BY product_id ORDER BY order_date) КАК prev_order_date
ОТ заказов;

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

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
1000	25.09.2007	НЕТ
1000	27.09.2007	25.09.2007
1000	30.09.2007	27.09.2007
2000	26.09.2007	НЕТ
2000	28.09.2007	26.09.2007
2000	29.09.2007	28.09.2007

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

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

Функции LAG и LEAD в SQL

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

Если вы застряли на старых версиях SQL Server, не отчаивайтесь — я
включил выдумку для имитации функции LAG в конце этого
блог.

Загрузка данных примера

Предположим, что у вас есть таблица, содержащая первые 9 серий X Factor в
Великобритания:

Первые 9 победителей конкурса X Factor в Великобритании.

Вы хотите перечислить всех наставников, чьи протеже выиграли соревнование в
лет подряд, чтобы получить такой ответ:

Что должен показать ответ: 6 серия была единственной, где один и тот же наставник
«выиграли», как и в предыдущем году.

Беглый взгляд на приведенную выше таблицу показывает, что ответ таков:
Шерил Коул — единственный человек, достигший этого подвига,
гид Александра Берк , а затем Джо Макэлдерри
чтобы побеждать в последующие годы.Однако при этом упускается возможность
выучить SQL!

Чтобы примеры из этого блога работали, запустите следующий сценарий в
существующая база данных для создания и заполнения таблицы с именем tblSeries :

СОЗДАТЬ ТАБЛИЦУ tblSeries (

Серия

int NULL,

Победитель nvarchar (255) NULL,

Дата начала NULL,

Дата окончания NULL,

Наставник varchar (50) NULL

)

ВСТАВИТЬ dbo.tblSeries (Серия, Победитель, Дата начала, Дата окончания, Наставник)

VALUES (1, N’Steve Brookstein ‘, CAST (0xB32A0B00 AS Дата),

CAST (0x152B0B00 AS Date), N’Simon Cowell ‘)