Definition of PostgreSQL LAG()
PostgreSQL lag () is a function in which the row will come before the current rows as an output or a specified offset of a query. In general PostgreSQL lag function will states that for current row value, the lag function will access the data from previous rows, always it will access the data from previous rows to display the output of a query. A lag function is very important and useful in PostgreSQL to compare value or data for current rows and previous rows. It is used to compare the values of the current and previous rows.
Syntax of PostgreSQL LAG()
Below are the syntaxes as follows.
1. Using Partition by Clause
Syntax:
LAG (expression (Expression is column name of table) [, offset (Which specifies the rows number that comes before current row) [, default_value (Default value of lag function)]]) OVER (
[PARTITION BY (It will divides rows into partition) partition_expression (Partition column name), ... ]
ORDER BY (Order by clause is used to sort the data) sort_expression [ASC | DESC], ... (Sort column row by ascending or descending order))
2. Default Value of Partition by Clause
Syntax:
LAG (expression (Expression is column name of table) [, offset (Which specifies the rows number that comes before current row) [, default_value (Default value of lag function)]]) OVER (
ORDER BY (Order by clause is used to sort the data) sort_expression [ASC | DESC], ... (Sort column row by ascending or descending order))
Parameter
Below is the parameter description of the above syntax are as follows.
- Expression: Expression nothing but a column name used in a lag function to display the data of the specified column. This is a column or a Subquery. It will return a single value in the lag function.
- Lag(): PostgreSQL lag () function is a function that row will come before the current rows as an output or a specified offset of a query.
- Offset: Offset is integer number in lag function which specifies the number that comes before the current row. The default value of offset in lag function is 1. If we will not specify the offset value it will take 1 by default.
- Default value: This is a default value of a lag function, PostgreSQL lag function will return the default value if the offset will goes above to the scope value of partition.
- Partition by: Partition by is a clause of PostgreSQL which is used in a lag function. Partition by a clause in PostgreSQL lag function will divide rows into a partition on which the lag function was applied. If we didn’t specify a partition by clause it will consider the whole single partition of a table.
- Partition expression: Partition expression nothing but a column name which we have used in the partition by clause for dividing table values into the partition.
- Order by: This is the PostgreSQL clause used with lag function to specify the data in ascending and descending order. If we specify ASC then data will be fetched in ascending order, if we specify DESC then data will be fetched in descending order.
- ASC: Ascending is used with an order by clause in lag function to fetch data in ascending order.
- DESC: Descending is used with an order by clause in lag function to fetch data in descending order.
- Sort expression: This is the column name that we have used in order by clause to fetch the data by using ascending or descending order.
How PostgreSQL LAG () Function Work?
Below is the working of the lag function is as follows.
- The lag function is work by its names that lag behind the current rows, it will fetch the data from previous rows.
- PostgreSQL lag () function is a function that row will come before the current rows as an output or a specified offset of a query.
- In general term PostgreSQL lag function will state that for the current row PostgreSQL lag function will access the data from previous rows, always it will access the data from previous rows to display the output of a query.
- We have used partition by clause in lag function, Partition by the clause in PostgreSQL lag function will divided rows into a partition on which the lag function was applied. If we didn’t specify a partition by clause it will consider the whole partition of a table. If we specified partition then data will be fetched as per order.
- The lag function is very important and useful in PostgreSQL to compare value or data for current rows and previous rows. It is used to compare the values of the current and previous rows.
- The lag function is used with an order by clause to display or fetch the data by using ascending or descending order.
- The lag function is used to the comparison between the current row and previous rows of a table. If we need to do a comparison of two tables same time we have used lag function in PostgreSQL.
- The lag function is more important for comparison of current and previous rows.
Examples to Implement LAG() Function
- Below is the example to implement a lag function in PostgreSQL are as follows.
- We have using the employee table to describe the example of a lag function in PostgreSQL, below image shows the data of the employee table.
testing=# select * from Employee;
1. Lag Function Default Partition by Clause
The below example shows the lag function default partition by clause in PostgreSQL are as follows.
testing=# SELECT *,LAG (emp_salary,1) OVER (ORDER BY emp_salary ASC) AS previous_salary FROM Employee;
testing=# SELECT *,LAG (emp_salary,1) OVER (ORDER BY emp_salary DESC) AS previous_salary FROM Employee;
2. Lag Function with Partition by Clause
The below example shows Lag function with partition by clause in PostgreSQL is as follows.
testing=# SELECT *,LAG (emp_salary,1) OVER (PARTITION BY emp_id ORDER BY emp_salary ASC) AS previous_salary FROM employee;
testing=# SELECT *,LAG (emp_salary,1) OVER (PARTITION BY emp_id ORDER BY emp_salary DESC) AS previous_salary FROM employee;
Recommended Articles
This is a guide to PostgreSQL LAG(). Here we discuss the definition and how it works along with different examples and its code implementation. you may also have a look at the following articles to learn more –
