PostgreSQL Window Functions

Introduction to PostgreSQL Window Functions

PostgreSQL window functions is used to compare contrast data and it is a key of analytic and various use cases in PostgreSQL. Window function in PostgreSQL is used to compare the values between current rows and related to the current rows from all values of a table. PostgreSQL window function is a part of SQL standard and is used in practical when finding the first time user login action of any performed action. We have used window function in order by clause to distinguish the data in ascending or descending order.

Window Function available in PostgreSQL

Below is the window function available are as follows:

• Row number()
• Rank()
• Dens rank()
• Percent rank()
• Cume dist()
• Ntile()
• Lag()
• Lead()
• First value()
• Last value()
• Nth value()

We discuss above function one by one are as follows. We have using employee table to describe example of PostgreSQL window function. Below is the employee table data description are as follows.

Example of employee table to describe example of employee table in PostgreSQL

1. lag()

PostgreSQL lag () function is a function that row will comes before the current rows as an output or a specified offset of a query.

Below is the syntax of lag () function in PostgreSQL are as follows.

Code:

LAG(expression[,[, default_value]]) OVER ([PARTITION BY  partition_expression... ] ORDER BYsort_expression [ASC | DESC], ...)

Below is the example of lag () function in PostgreSQL are as follows.

Code:

SELECT *,LAG (emp_salary,1) OVER (ORDER BY emp_salary ASC) AS previous_salary FROM Employee;

Output:

2. Lead()

Lead function is used to compare value if records between current row and value of record which following a current row.

Below is the syntax of lead () function in PostgreSQL are as follows.

Code:

LEAD (expression[,offset [, default_value]]) OVER ( [PARTITION BY partition_expression (Partition column name), ... ] ORDER BY sort_expression [ASC | DESC], ...)

Below is the example of lead () function in PostgreSQL are as follows.

Code:

SELECT *,LEAD (emp_salary,1) OVER (ORDER BY emp_salary ASC) AS previous_salary FROM Employee;

Output:

3. Rank()

Rank window function is used to define rank of defined column in PostgreSQL. Rank function is very useful and important in PostgreSQL to define rank.

Below is the syntax of rank () function in PostgreSQL are as follows.

Code:

SELECT column_name1, column_name2, column_nameN, rank() OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name;

Below is the example of rank () function in PostgreSQL are as follows.

Code:

SELECT emp_id, emp_salary, emp_address, rank() OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

4. Row_number()

Row_number function is very useful and important in PostgreSQL to define row number of column.

Below is the syntax of row number () function in PostgreSQL are as follows.

Code:

SELECT column_name1, column_name2, column_nameN, row_number () OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name;

Below is the example of row number () function in PostgreSQL are as follows.

Code:

SELECT emp_id, emp_salary, emp_address, row_number() OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

5. Dense rank()

Dense rank function is very useful and important in PostgreSQL to define rank of column.

Below is the syntax of Dense rank () function in PostgreSQL are as follows.

Code:

SELECT column_name1, column_name2, column_nameN, dense_rank () OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name;

Below is the example of Dense rank () function in PostgreSQL are as follows.

Code:

SELECT emp_id, emp_salary, emp_address, Dense_rank () OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

6. Ntile()

Ntile function is very useful and important in PostgreSQL.

Below is the syntax of ntile() function in PostgreSQL are as follows.

Code:

SELECT column_name1, column_name2, column_nameN, ntile (Argument) OVER (PARTITION BY column_name ORDER BY column_name DESC) FROM table_name;

Below is the example of ntile() function in PostgreSQL are as follows.

Code:

SELECT emp_id, emp_salary, emp_address, ntile(1) OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

7. Percent rant()

Percent rank function is very useful and important in PostgreSQL.

Below is the syntax of percent rank() function in PostgreSQL are as follows.

Code:

SELECT column_name1, column_name2, column_nameN, percent_rank () OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;

Code:

SELECT emp_id, emp_salary, emp_address, percent_rank() OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

8. Cume dist()

Cume dist function is very useful and important in PostgreSQL.

Code:

SELECT column_name1, column_name2, column_nameN, cume_dist () OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;

Code:

SELECT emp_id, emp_salary, emp_address, cume_dist() OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

9. First Value()

First value function is very useful and important in PostgreSQL.

Code:

SELECT column_name1, column_name2, column_nameN, first_value (value) OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;

Code:

SELECT emp_id, emp_salary, emp_address, first_value(emp_id) OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

10. Last value()

Last value function is very useful and important in PostgreSQL.

Code:

SELECT column_name1, column_name2, column_nameN, last_value (value) OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;

Code:

SELECT emp_id, emp_salary, emp_address, last_value(emp_id) OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

11. Nth value()

Last value function is very useful and important in PostgreSQL.

Code:

SELECT column_name1, column_name2, column_nameN, nth_value (column_name, value) OVER (PARTITION BY column_name ORDER BY column_name ASC) FROM table_name;

Code:

SELECT emp_id, emp_salary, emp_address, nth_value(emp_salary, 2) OVER (PARTITION BY emp_address ORDER BY emp_salary DESC) FROM employee;

Output:

Conclusion

PostgreSQL window functions is used to compare contrast data and it is a key of analytic and various use cases in PostgreSQL. Window function in PostgreSQL is used to compare the values between current rows and related to the current rows from all values of a table.

Recommended Articles

This is a guide to PostgreSQL Window Functions. Here we discuss an introduction to PostgreSQL Window Functions with respective examples for better understanding. You can also go through our other related articles to learn more –

1. What is PostgreSQL?
2. SQL Server vs PostgreSQL
3. Oracle vs PostgreSQL
4. Guide to PostgreSQL Queries
5. PostgreSQL REGEXP_MATCHES()
6. SQL REGEXP | List of Operators