how to write a effective SQL query to calculate based on dfferent rows

Check list of all posts

It is sometimes required to compare data from different rows when building ETL to create data mart.  Two SQL analytic functions are provided to dramatically reduce work effort.

Lag: Provides access to more than one row of a table at the same time without a self-join. The lag function provides access to a row at a physical offset before that position. If you do not specify the offset, the default is 1. The system returns the value in the optional default column, if the offset is beyond the scope of the window. If you do not specify the default, the value is null.

Lead: Provides access to more than one row of a table at the same time without a self-join. The lead function provides access to a row at a given physical offset beyond that position. If you do not specify the offset, the default is 1. The system returns the value in the optional default column, if the offset is beyond the scope of the window. If you do not specify the default, the value is null.

Two samples to calculate revenue are provided below:

Sample data is based on calendar year:

YEAR, MONTH,YTD, result

2017,01,$10,$10 (10-0)
2017,02,$20,$10  (20-10)
2017,03,$30,$10  (30-20)
2017,04,$40,$10  (40-30)

SELECT

      T.YEAR

      ,T.MONTH

      ,T.X

      ,T.YTD

      ,NVL(T.YTD,0)

- NVL(

LAG(T.YTD,1) OVER (partition by X, YEAR order by MONTH)

, 0) ) as result

FROM

      (

            SELECT

                  ,YEAR

                  ,MONTH

                  ,X

                  ,SUM(YTD)    AS YTD

            FROM   <TABLENAME>

            GROUP  BY YEAR,MONTH

      ) T  

 

Sample data is based on fiscal year:

YEAR, MONTH,FYTD, result

2016,11,$10,$10 (10-0)
2016,12,$20,$10 (20-10)
2017,01,$30,$10 (30-20)
2017,02,$40,$10  (40-30)
2017,03,$50,$10  (50-40)
2017,04,$60,$10  (60-50)

SELECT

      T.YEAR

      ,T.MONTH

      ,T.X

      ,T.YTD

      ,NVL(T.YTD,0)

- NVL(

LAG(T.YTD,1) OVER (partition by X, DT.FISCAL_YEAR

order by DT.FISCAL_MONTH)

, 0) ) as result

FROM

      (

            SELECT

                  ,YEAR

                  ,MONTH

                  ,X

                  ,SUM(YTD)    AS YTD

            FROM   <TABLENAME>

            GROUP  BY YEAR,MONTH

      ) T

LEFT JOIN (SELECT DISTINCT CALENDAR_YEAR , CALENDAR_MONTH, FISCAL_YEAR, FISCAL_MONTH FROM MPL_BI.MPL_DATE_DIM) DT ON (T.YEAR =DT.CALENDAR_YEAR AND T.MONTH =DT.CALENDAR_MONTH)