Search code examples
postgresqlgroup-bywindow-functions

Postgres - Using window function in grouped rows

According to the Postgres Doc at https://www.postgresql.org/docs/9.4/queries-table-expressions.html#QUERIES-WINDOW it states

If the query contains any window functions (...), these functions are evaluated after any grouping, aggregation, and HAVING filtering is performed. That is, if the query uses any aggregates, GROUP BY, or HAVING, then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE.

I didn't get the concept of " then the rows seen by the window functions are the group rows instead of the original table rows from FROM/WHERE". Allow me to use an example to explain my doubt:

Using this ready to run example below

with cte as ( 
  select 1 as primary_id, 1 as foreign_id, 10 as begins
  union
  select 2 as primary_id, 1 as foreign_id, 20 as begins
  union
  select 3 as primary_id, 1 as foreign_id, 30 as begins
  union
  select 4 as primary_id, 2 as foreign_id, 40 as begins
)
select foreign_id, count(*) over () as window_rows_count, count(*) as grouped_rows_count
from cte
group by foreign_id

You may notice that the result is

enter image description here

So if "the rows seen by the window functions are the group rows".. then ¿why window_rows_count is returning a different value from grouped_rows_count?

Solution

  • If you remove the window function from the query:

    select foreign_id, count(*) as grouped_rows_count
from cte
group by foreign_id

    the result, as expected is this:

    > foreign_id | grouped_rows_count
> ---------: | -----------------:
>          1 |                  3
>          2 |                  1

    and on this result, which is 2 rows, if you also apply the window function count(*) over(), it will return 2, because it counts all the rows of the resultset since the over clause is empty, without any partition.