Oracle Database - SQL - Analytic Function LAG/LEAD
Table of Contents
About
lag is an analytical function that can be used to get the value of a column in a previous row.
If you want to retrieve the value of the next row, use lead instead of lag.
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.
Articles Related
Syntax
{LAG | LEAD} ( value_expr [, offset] [, default] )
OVER ( [query_partition_clause] order_by_clause )
The LAG function provides access to a row at a given offset prior to the current position, and the LEAD function provides access to a row at a given offset after the current position.
The [query_partition_clause] give a reliable relative positions of rows.
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.
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 |