Analytic Function LAG/LEAD

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.

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

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 :

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: