PostgreSQL Window Functions

Introduction to PostgreSQL Window Functions

PostgreSQL window functions are used to compare contrast data, and it is a key to analytic and various use cases in PostgreSQL. The 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 the 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 the above function, one by one, are as follows. We have using the employee table to describe an example of the PostgreSQL window function. Below is the employee table data description are as follows.

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

1. 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.

Below is the syntax of the 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 a 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()

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

Below is the syntax of the 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()

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

Below is the syntax of the 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 the row number of a column.

Below is the syntax of the 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()

A dense rank function is very useful and important in PostgreSQL to define the rank of the column.

Below is the syntax of the 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 the 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()

The 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()

The 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()

The 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 are used to compare contrast data, and it is a key to analytic and various use cases in PostgreSQL. The 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