This article is the next part of my previous article where we learned about Windows Ranking Function.
In this article, I am going to discuss the other Windows Functions - Lead and Lag. These functions also work on the Window, or Grouped Row DataSet, or Partition created by Windows Functions in the T-SQL. For a better understanding, I request you to please read the previous article, mentioned above and then read this one.
So, let’s get started. We will create a table and fill it with some random data.
Now, our test table is ready and we will fire a T-SQL to understand how the Windows Functions work. So, let’s run our T-SQL.
Now, let’s understand the LEAD and LAG functions.
LAG()
This function, when used with Over Clause, gives us the value of the PREVIOUS ROW. This function generally accepts three parameters -Col_Name on which you want to perform, number of previous rows than the current row, the default value to replace NULL. So the basic syntax of LAG is as LAG (Col_Name, number of previous rows, default value), however, you can use only the LAG(Col_Name) also.
If you check the results, the LAG function returns NULL for the first row as there is no row before SaleID 11. Then comes LAG_PART which uses Partition By clause and partitioning the table on the basis of State Name. If you look the column value of LAG_PART, it states that for the partition Ahemdabad, for ID 11 there is no previous value hence NULL, and for ID 12 in the same partition, it is showing the previous value of ID11. Now, comes the column LAG_PARAM in which we used the LAG function with the parameters.
LAG_PARAM([SALE_AMT],2,0.00)
Here, we want to evaluate LAG on the basis of SALE_AMT column and then we are telling the LAG function to fill the current row with the value of 2 rows above or previous to the current row, and also if there is NULL, then to replace it with 0.00. The values in column LAG_PARAM are understandable.
LEAD()
This function is very similar to the LAG with a minor difference that it gives the value of the NEXT ROW in the current row. It also accepts three parameters similar to LAG.
Syntax -
LEAD(Col_Name on which you want to perform, No Of Rows Next to the Current Row, Default value to replace NULL).
Again, you can use LEAD also with the evaluating column name only, such as - LEAD(COL_NAME).
I hope this will help you in understanding LAG() and LEAD() Windows Functions and how to use them. One thing to keep in mind is that these two functions do not support the functionality of Windows Frame which I will discuss in the next part when we will learn about First_Value() and Last_Value() functions.
Thank you for taking time to read this article. Any feedback will be welcomed. Happy Learning!