Window Functions

In the previous chapter, we talked about aggregates. In this chapter, we are going to further discuss another way to make aggregates: window functions. The official documentation (https://www.postgresql.org/docs/12/tutorial-window.html) describes window functions as follows:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row as non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

In this chapter, we will talk about window functions, what they are, and how we can use them to improve the...

Using basic statement window functions

As we saw in the previous chapter, aggregation functions behave in the following way:

The data is first sorted and then aggregated; the data is then flattened through aggregation. This is what happens when we execute the following statement:

forumdb=# select category,count(*) from posts group by category order by category;

Alternatively, we can decide to use window functions by executing the following statement:

forumdb=# select category, count(*) over (partition by category) from posts order by category;
 category | count 
----------+-------
       10 | 1
       11 | 3
       11 | 3
       11 | 3
       12 | 1
(5 rows)

Window functions create aggregates without flattening the data into a single row. However, they replicate it for all the rows to which the grouping functions refer. The behavior of PostgreSQL is depicted in the following diagram:

This is the reason that the distinct keyword has to be added to the preceding query if we want to obtain the...

Using advanced statement window functions

In this section, we will discuss advanced window functions in more detail, and we will explore some techniques that may be useful for carrying out more detailed data analysis.

Let's start with another way to write the same aggregate that we have described before:

forumdb=# select distinct category, count(*) over w1 
from posts 
WINDOW w1 as (partition by category RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
order by category;
 category | count 
----------+-------
       12 | 1
       10 | 1
       11 | 3
(3 rows)

What does RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean? They are the default conditions, known as the frame clause. This means that the data is partitioned, first by category, and then within the partition, the count is calculated by resetting the count every time the frame is changed.

The frame clause

In this section, we'll talk about the frame clause, which allows us to manage partitions in a different way. The frame...

Summary

In this chapter, we explored how to use window functions. We have seen that by using window functions we can create more complex aggregates compared to those made with the GROUP BY statement, which we saw in Chapter 5, Advanced Statements. We learned how to use the ROW_NUMBER (), FIRST_VALUE (), LAST_VALUE (), RANK DENSE_RANK(), LAG (), LEAD (), CUME_DIST (), and NTILE () functions. We have also seen the difference between creating aggregates with the ROWS BETWEEN and RANGE BETWEEN clauses. You can use what you have learned in this chapter in data mining operations to make your work much easier.

For more information on window functions, you can consult the official documentation: https://www.postgresql.org/docs/12/functions-window.html.

In the next chapter, we will talk about server-side programming, we will look at how to create functions to be used on the server side and, if necessary, where to use window functions.

References

• PostgreSQL 12 - window functions official documentation: https://www.postgresql.org/docs/12/functions-window.html