LAG / LEAD

LAG / LEAD

All rows to be referenced by their position relative to the current row.

They are useful for comparing one row of a result set with another row of the same result set.

Example:
"Compute the total sales per month for the Mid-Atlantic region, including the percent change from the previous month"

Requires data from both the current and preceding rows in order to calculate the answer.



Example

SELECT month, SUM(tot_sales) monthly_sales,

  LAG(SUM(tot_sales), 1) OVER (ORDER BY month) prev_month_sales

FROM orders

WHERE year = 2001

  AND region_id = 6

GROUP BY month

ORDER BY month;



     MONTH MONTHLY_SALES PREV_MONTH_SALES

---------- ------------- ----------------

         1        610697

         2        428676           610697

         3        637031           428676

         4        541146           637031

         5        592935           541146

         6        501485           592935

         7        606914           501485

         8        460520           606914

         9        392898           460520

        10        510117           392898

        11        532889           510117

        12        492458           532889

As we might expect, the LAG value for month 1 is NULL, since there is no preceding month. This would also be the case for the LEAD value for month 12.
Take this into account when performing calculations that utilize the results of the LAG or LEAD functions.
The next query utilizes the output from the previous query to generate the percentage difference from month to month.
Note how the prev_month_sales column is wrapped in the NVL function so that month 1 won't generate a NULL value for the percentage change:

SELECT months.month month, months.monthly_sales monthly_sales,

  ROUND((months.monthly_sales -- NVL(months.prev_month_sales,

    months.monthly_sales)) /

    NVL(months.prev_month_sales, months.monthly_sales),

    3) * 100 percent_change

FROM

 (SELECT month, SUM(tot_sales) monthly_sales,

    LAG(SUM(tot_sales), 1) OVER (ORDER BY month) prev_month_sales

  FROM orders

  WHERE year = 2001

    AND region_id = 6

  GROUP BY month) months

ORDER BY month;



     MONTH MONTHLY_SALES PERCENT_CHANGE

---------- ------------- --------------

         1        610697              0

         2        428676          -29.8

         3        637031           48.6

         4        541146          -15.1

         5        592935            9.6

         6        501485          -15.4

         7        606914             21

         8        460520          -24.1

         9        392898          -14.7

        10        510117           29.8

        11        532889            4.5

        12        492458           -7.6