Some of the functions which are introduced are
- Lag
- Lead
- First_Value
- Last_Value
Lag: Lag enables to find the data from previous row without using the selfjoin
An example of lag function using the AdventureWorksDW2012 database ,i have created a CTE with the aggregate at sales territory productcategory and CaleanderYear and i'm using the following query to demonstrate the use of lag as shown below
Lag function accepts two arguements ,the first arguement is the offset, in this case i have used 1 for previous year and a default arguement to assign a default value for the row prior to the start of our window
Basically the above query uses lag function to find the previous year sales along with each calendar year Sales and it is partitioned by sales territory and ordered by calendar year ,
The result of the query looks like below
Lead is a similar function which avoids a self join to give a leading value for the period of interest
