Using advanced statement window functions

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

Let’s start with a query that we saw at the start of this chapter:

forumdb=> select distinct category, count(*) over (partition by category)              
from posts order by category;
 category | count
----------+-------
        1 |     2
        3 |     1
(2 rows)

Here, below, there is another way to write the same aggregate that we 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
----------+-------
        1 |     2
        3 |     1
(2 rows)

What does RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW mean? They are the default conditions, known as the frame clause. This means that...