A powerful data manipulation function - Lag Function - Steem Alliance Community.

bountyking5 (79)Steem Devin Steem Dev • 16 days ago (edited)

Hello Everyone,

portfolio (5).jpg

Good evening, how are you all, a hope you all guys will happy and good. By the grace of God, I am also good. In SQL, there are many super and useful function that are used for data manipulation and data analysis, a function that I will be discussed here are called Lag function. We will explain what it does and how we can use it and why it is so handy for getting insights from our data.

The Lag function is used in SQL that allows us to access data from the previous row within the result set. It is particularly useful when we need to compare the current row with the preceding row or perform calculations based on historical data.

LAG(column_expression [, offset [, default_value]]) OVER (ORDER BY order_expression)

column_expression refers to the set of data that we want to look at from the previous row in our table.
offset tells SQL how many rows back you want to look from the current row.
default_value is the value that a function will return if it cannot find a row at the specified offset. If we do not provide a default_value then it will return as NULL.
It is about how rows are sorted within a specific partition/group in data set.

let's discussed by practically.

I have created a table tbd_lag containing employee data with columns EMPCODE, EMPNAME, JOININGDATE, PREV_CUSTOMER now we will retrieve each employees previous joining date (if it exists), grouped by EMPCODE and ordered by JOININGDATE within each group.

select * from (
select a.* ,  
LAG(a.JOININGDATE) OVER (PARTITION BY a.EMPCODE ORDER BY a.JOININGDATE ) PREV_CUSTOMER 
from tbd_lag a ) a

First of all, i am going to use the select statement to run and show the whole data inside table.

Now I am using lag function on the base of JOININGDATE. the above query group the data on the base of empcode and get the previous joining date of employees that have more than one entry.