What are the LAG and LEAD SQL functions ?

Data System Architecture

About

lag and lead are window functions that provide access to a row at a given offset prior to/after the current row position.

  • lag retrieves the value of a column in a previous row.
  • lead retrieves the value of a column in a next row.

They provide access to a row at a given offset prior to/after the current row position.

Because the functions provide access to more than one row of a table at the same time without a self-join, they can enhance processing speed.

Syntax

The general syntax (oracle, sqlite, …) is.

lead(expr)                  over ( ..... )
lead(expr, offset)          over ( ..... )
lead(expr, offset, default) over ( ..... )
-- or/and
lag(expr)                   over ( ..... )
lag(expr, offset)           over ( ..... )
lag(expr, offset, default)  over ( ..... )

where:

  • value_expr is the sql expression that is applied on the row retrieved
  • offset is the offset of the row retrieved (non-negative integer, default to 1). Note that offset may be 0 in this case, the expression is evaluated against the current row.
  • default (default to NULL) is the default value returned if the row identified by offset does not exist. (ie when the current row is the last/first).

Example

How to retrieve the quantity sold from the previous month?

The example schema SH is used.

select CALENDAR_MONTH_DESC, 
       sum(quantity_sold), 
       lag(sum(quantity_sold),1) over (order by CALENDAR_MONTH_DESC) "Previous Month" 
from 
       SH.sales,
       SH.TIMES
where
       SH.times.TIME_ID = SH.sales.TIME_ID and
       SH.times.CALENDAR_YEAR = '2000'
group by CALENDAR_MONTH_DESC
order by CALENDAR_MONTH_DESC ASC

And you get :

CALENDAR_MONTH_DESC SUM(QUANTITY_SOLD) Previous Month
2000-01 44270
2000-02 41218 44270
2000-03 38906 41218
2000-04 34962 38906
2000-05 40092 34962
2000-06 35976 40092
2000-07 37068 35976
2000-08 40738 37068
2000-09 40094 40738
2000-10 43084 40094
2000-11 39418 43084
2000-12 29466 39418

The little problem of this time solution against an ago function for a period-to-period comparison is that you don't have the previous value for the month 2000-01 because the value is not in the returned data set.

How to retrieve the end date from the previous row in a SCD2 table?

Sometimes, you need to update or control the structure of an SCD2 table. For this need, you need to retrieve the date of the previous rows. The lag function gives here a beautiful solution.

SELECT
  BUSINESS_KEY AS BUSINESS_KEY
,
  START_DATE AS START_DATE
,
  END_DATE AS END_DATE
,
  LAG(END_DATE,1) over (partition BY BUSINESS_KEY order by END_DATE ASC) + 1/(24*60*
  60 ) AS THE_LAG
FROM
  TABLE
ORDER BY
  BUSINESS_KEY
,
  END_DATE;

Result:

BUSINESS_KEY START_DATE END_DATE THE_LAG
A101 01-01-1800 00:00:00 02-01-2011 23:59:59
A101 03-01-2011 00:00:00 31-12-9999 00:00:00 03-01-2011 00:00:00
A204 01-01-1800 00:00:00 02-01-2011 23:59:59
A204 03-01-2011 00:00:00 31-12-9999 00:00:00 03-01-2011 00:00:00
A308 01-01-1800 00:00:00 02-01-2011 23:59:59
A308 03-01-2011 00:00:00 31-12-9999 00:00:00 03-01-2011 00:00:00
A401 01-01-1800 00:00:00 02-01-2011 23:59:59
A401 03-01-2011 00:00:00 31-12-9999 00:00:00 03-01-2011 00:00:00
A404 01-01-1800 00:00:00 02-01-2011 23:59:59
A404 03-01-2011 00:00:00 31-12-9999 00:00:00 03-01-2011 00:00:00





Discover More
Obiee Period To Period Comparison Lag
Blog - Obiee 10G – Period to period comparison with the analytical function Lag/Lead

During this thread on the Obiee forum, Goran point out a solution for a period to period comparison...
Obiee Period To Period Comparison Lag
OBIEE - Period to period comparison (YAGO, MAGO) with the analytical function Lag/Lead

lag and lead are analytical functions that can be used to get the value of a column in a previous/next row. More ... densewith sparse datadense use the Obiee Ago Function or densify your data ...
Card Puncher Data Processing
Oracle Database - SQL - Analytic Functions

Analytic functions in the Oracle Database Context An analytic function takes place after that the original data set is retrieved. The clause AVG CORR COVAR_POP COVAR_SAMP...
Analytic Function Process Order
SQL Function - Window Aggregate (Analytics function)

Windowing functions (known also as analytics) allow to compute: cumulative, moving, and aggregates. They are distinguished from ordinary SQL functions by the presence of an OVER clause. With...



Share this page:
Follow us:
Task Runner