Skip to main content

Window functions

Window functions are a powerful feature of PostgreSQL that allow you to perform calculations across a set of table rows that are somehow related to the current row. This is akin to an advanced type of aggregation where you can look at surrounding rows to compute a value. In this article, we will learn about the usage and advantages of window functions in PostgreSQL.

What Are Window Functions?

Window functions are SQL functions where the input values are taken from a "window" of one or more rows in the result set of a SELECT statement. They are used to solve problems which require computation on a set of rows, for example calculating running totals, averages, or for finding the maximum value in each category etc.

Basic Structure of a Window Function

A window function call always contains an OVER clause directly following the window function’s name and argument(s). This is what syntactically distinguishes it from other function calls. An example would be:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

In the above example, avg(salary) OVER (PARTITION BY depname) is a window function call.

Key Elements of Window Functions

OVER Clause​

The OVER clause differentiates window functions from other functions. This clause includes PARTITION BY, ORDER BY, and ROWS or RANGE clause.

PARTITION BY​

The PARTITION BY clause divides rows into multiple groups or partitions to which the window function is applied.

For example, if you want to find the highest salary in each department from the employees table, you would use the PARTITION BY clause to divide employees into partitions by department.

SELECT depname, empno, salary, max(salary) OVER (PARTITION BY depname) FROM empsalary;

ORDER BY​

The ORDER BY clause is used to sort rows in each partition. For example, if you want to calculate the running total of salaries in each department, you would use the ORDER BY clause to sort the employees based on their salaries.

SELECT depname, empno, salary, sum(salary) OVER (PARTITION BY depname ORDER BY salary) FROM empsalary;

Types of Window Functions

There are two types of window functions in PostgreSQL:

  1. Aggregate Window Functions: These functions perform calculations across a set of rows and return a single output row. For example, avg(), sum(), max(), min(), etc.

  2. Ranking Window Functions: These functions assign a unique rank to each row within a partition of a result set. For example, row_number(), rank(), dense_rank(), percent_rank(), cume_dist(), etc.

Conclusion

Window functions in PostgreSQL offer a powerful way to perform complex calculations that involve related rows in a dataset. By understanding how to use these functions, you can greatly increase the efficiency and readability of your SQL queries.

Remember, practice is key when it comes to mastering window functions, so try to use these functions in your queries and see the magic they can do.

Happy querying!