Postgres - Using window function in grouped rows

Question

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

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?

forpas · Accepted Answer · 2020-11-27 19:15:46Z

1

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.

answered Nov 27, 2020 at 19:15

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eddymio · Accepted Answer · 2020-11-27 19:27:14Z

You should follow the last comment on your post. And for more analysis, you may process the following query :

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 (PARTITION BY foreign_id) as window_rows_count, count(*) as grouped_rows_count
from cte
group by foreign_id ;

You'll see this time that you are getting 1 row for each foreign id. Checkout the documentation on postgres at this url : https://www.postgresql.org/docs/13/tutorial-window.html

The window function is applied to the whole set obtained by the former query.

Collectives™ on Stack Overflow

Postgres - Using window function in grouped rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related