Oracle Database - SQL - Analytic Function LAG/LEAD

> Database > Oracle Database

1 - 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.

Advertising

3 - 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.

4 - Example: How to

4.1 - 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.

Advertising

4.2 - 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
Advertising
db/oracle/lag_lead.txt · Last modified: 2017/09/13 16:16 by gerardnico