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 the 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 essential 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 is nothing but a column name used in a lag function to display the specified column data. 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 an integer number in lag function which specifies the number that comes before the current row. The default value of offset in the lag function is 1. If we do not specify the offset value, it will take 1 by default.
- Default value: This is a default value of a lag function; the PostgreSQL lag function will return the default value if the offset will goes above the scope value of the 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 would 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 the 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 the lag function to fetch data in ascending order.
- DESC: Descending is used with an order by clause in the 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.
- PostgreSQL lag function will state that for the current row, PostgreSQL lag function will access the data from previous rows; it will always 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 divide rows into a partition on which the lag function was applied. If we didn’t specify a partition by clause, it would consider the whole partition of a table. If we specified a partition, then data will be fetched as per order.
- The lag function is essential and useful in PostgreSQL to compare current rows and previous rows’ value or data. 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 compare two tables simultaneously, we have used the lag function in PostgreSQL.
- The lag function is more important for the comparison of current and previous rows.
Examples to Implement LAG() Function
- Below is the example of implementing a lag function in PostgreSQL are as follows.
- We have used the employee table to describe the example of a lag function in PostgreSQL; the below image shows the employee table’s data.
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 the 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 –
