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