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_DATEPRODUCT_IDQTY
25/09/2007100020
26/09/2007200015
27/09/200710008
28/09/2007200012
29/09/200720002
30/09/200710004

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

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_DATEPRODUCT_IDQTY
100025/09/2007
200026/09/200725/09/2007
100027/09/200726/09/2007
200028/09/200727/09/2007
200029/09/200728/09/2007
100030/09/200729/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_DATEPRODUCT_IDQTY
200026/09/2007
200028/09/200726/09/2007
200029/09/200728/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_IDORDER_DATEPREV_ORDER_DATE
10002007/09/25NULL
10002007/09/272007/09/25
10002007/09/302007/09/27
20002007/09/26NULL
20002007/09/282007/09/26
20002007/09/292007/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_numberlast_namefirst_namesalarydept_id
12009МатвееваГалина5400045
34974ШапошниковСемён8000045
34987ВерещагинПавел4200045
45001СуховФёдор5750030
75623СмирновАндрей6500030

И мы выполнили следующую оператор 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_idlast_namesalarylower_salary
45Верещагин42000NULL
45Матвеева5400042000
30Сухов5750054000
30Смирнов6500057500
45Шапошников8000065000

В этом примере функция 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_idlast_namesalarylower_salary
30Сухов57500NULL
30Смирнов6500057500
45Верещагин42000NULL
45Матвеева54000
42000
45Шапошников8000054000

В этом примере функция 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 ‘)

GO

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

)

GO

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

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

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

INSERT dbo.tblSeries (Series, Winner, StartDate, EndDate, Mentor)

ЗНАЧЕНИЯ (2, Н’Шейн Уорд ‘, CAST (0x112C0B00 как дата),

CAST (0x882C0B00 AS Date), N’Louis Walsh ‘)

INSERT dbo.tblSeries (Series, Winner, StartDate, EndDate, Mentor)

ЗНАЧЕНИЯ (4, Н’Леон Джексон ‘, CAST (0xE92E0B00 AS Date),

CAST (0x602F0B00 AS Date), N’Dannii Minogue ‘)

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

ЗНАЧЕНИЯ (6, Н’Джо МакЭлдерри ‘, CAST (0xC8310B00 как дата),

CAST (0x39320B00 AS Date), N’Cheryl Cole ‘)

INSERT dbo.tblSeries (Series, Winner, StartDate, EndDate, Mentor)

ЗНАЧЕНИЯ (7, N’Matt Cardle ‘, CAST (0x34330B00 AS Date),

CAST (0xA5330B00 AS Date), N’Dannii Minogue ‘)

INSERT dbo.tblSeries (Series, Winner, StartDate, EndDate, Mentor)

ЗНАЧЕНИЯ (9, N’James Arthur ‘, CAST (0x0C360B00 AS Date),

CAST (0x7D360B00 AS Date), N’Nicole Scherzinger ‘)

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

ЗНАЧЕНИЯ (3, Н’Леона Льюис, CAST (0x7D2D0B00 как дата),

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

INSERT dbo.tblSeries (Series, Winner, StartDate, EndDate, Mentor)

VALUES (5, N’Alexandra Burke ‘, CAST (0x55300B00 как дата),

CAST (0xCC300B00 AS Date), N’Cheryl Cole ‘)

INSERT dbo.tblSeries (Series, Winner, StartDate, EndDate, Mentor)

ЗНАЧЕНИЯ (8, N’Little Mix (Rhythmix) ‘, CAST (0xA0340B00 AS Date),

CAST (0x11350B00 AS Date), N’Tulisa Contostavlos ‘)

Как работают функции LAG и LEAD

Функции LAG и LEAD работают в одном способ: они смотрят назад или вперед n рядов.Вот синтаксис функция LAG , например:

LAG (столбец для отображения, количество периодов, что отображать, если без значения)

Мы хотим показать предыдущего победителя для каждой серии, поэтому мы будем использовать:

В этом коде аргументы функции LAG :

Аргумент Значение Что это значит Значение по умолчанию, если не указано
1 Наставник Рассматриваемая колонка Вы не можете пропустить этот аргумент.
2 1 На сколько строк вернуться 1
3 N / A Что показывать, когда нет предшествующих строк. Пусто

Однако мы также должны сказать, что на самом деле означает предшествующие строки . В нашем случае мы имеем в виду, что для каждой серии мы будем брать ту, которая произошла непосредственно перед ним в том порядке, в котором были созданы серии.Мы делаем это с предложением OVER , так что полное выражение будет:

LAG (Mentor, 1, ‘N / A’) ВЫШЕ (ЗАКАЗ серии)

То есть показывать наставника предыдущей серии для каждой текущей серии. Помещение этого в полный запрос даст:

ВЫБРАТЬ

серии,

Победитель,

Ментор,

LAG (Mentor, 1, ‘N / A’) OVER (ORDER BY Series) AS PreviousMentor

ИЗ

таблицы

ЗАКАЗАТЬ В

серии

Обратите внимание, что вам не нужно указывать LAG в том же столбце, что и вы ЗАКАЗЫВАЕТЕ (хотя это может сбивать с толку, если вы этого не сделаете).

Этот запрос при запуске даст следующие результаты:

Серии X Factor по порядку, с указанием наставника предыдущего года для каждого.

Обратите внимание, что предыдущий наставник для первой серии отображается как N / A , поскольку это то, что мы указали, должно быть значением по умолчанию.

Изящный способ показать окончательный ответ на наш вопрос — использовать простое общее табличное выражение:

С победителями AS (

ВЫБРАТЬ

серии,

Победитель,

Ментор,

LAG (Mentor, 1, ‘N / A’) OVER (ORDER BY Series) AS PreviousMentor

ИЗ

таблицы

)

ВЫБРАТЬ

серии,

Победитель,

Наставник

ИЗ

Победители

ГДЕ

Наставник = Предыдущий наставник

Разбиение на разделы

В предыдущем примере отставание или опережение выполняется по всему набору строк, возвращаемых запросом.Однако вы также можете разделить этот набор строк для создания таких эффектов:

Здесь мы показываем для каждого победителя предыдущий победитель наставника (в большинстве случаев его не будет, потому что это первый раз, когда конкретный наставник выиграл соревнование X Factor).

Для этого вы можете расширить синтаксис LEAD или LAG функции для включения РАЗДЕЛЕНИЕ ПО статья:

LAG (столбец, количество периодов, значение по умолчанию) БОЛЕЕ
РАЗДЕЛЕНИЕ ПО столбцу
ЗАКАЗАТЬ ПО столбцу)

Вот SQL, чтобы показать наши результаты выше:

ВЫБРАТЬ

серии,

Победитель,

Ментор,

LAG (Победитель, 1, ‘Первый победитель’)

НАВЕРХ (РАЗДЕЛЕНИЕ ПО НАСТАВНИКУ ПО ЗАКАЗУ Серии)

AS ‘Предыдущий победитель для этого наставника’

ИЗ

таблицы

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

Это дает очевидные преимущества для данных временных рядов, позволяя отображать (для пример) продаж за тот же период в предыдущем году.

Предыдущие версии SQL Server

Если вы читаете это с ревностью, потому что у вашей компании нет SQL Server 2012, не отчаивайтесь! Можно добиться того же эффекта, что и на первом пример выше с использованием функции ROW_NUMBER и самостоятельного соединения:

С серией с предыдущим AS (

ВЫБРАТЬ

серии,

Победитель,

Ментор,

ROW_NUMBER () OVER (ORDER BY Series) КАК RowNo

ИЗ

таблицы Серия

)

ВЫБРАТЬ

ThisRow.RowNo AS ‘This row no’,

PrevRow.RowNo AS ‘Номер предыдущей строки’,

ThisRow.Series,

Победитель этого ряда,

ThisRow.Mentor,

IsNull (PrevRow.Mentor, ‘N / A’) AS ‘Наставник предыдущей серии’

ИЗ

Серия

с предыдущим AS ThisRow

ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, серия с предыдущей AS Предыдущая строка

НА ThisRow.RowNo = (PrevRow.RowNo + 1)

Это намного сложнее! Приведенный выше запрос вернет следующее результатов:

Запрос показывает номер текущей и предыдущей строки для каждой строки.

Может быть, пора приобрести SQL Server 2012?

.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *