Why can you nest aggregate functions when using a window function in PostgreSQL?

user_1587271 0
Viewed 705

I'm trying to understand window functions a bit better, and I'm stumped as to why I can't run a nested aggregate function normally, but I can when using a window function.

This is the dbfiddle I'm working off of: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=76d62fcf4066053db18783e70269438c

Before running the window function, basically everything else in my query is evaluated (JOIN and GROUP BY).

So I believe the data the window function is working off of is something like this (after grouping):

the window of data we're working off of

Or is it something like this?

or is the window of data like this

So why can I do this: SUM(COUNT(votes.option_id)) OVER(), but I can't do it without OVER()?

As far as I understand, OVER() makes the SUM(COUNT(votes.option_id)) run on this related data set, but it's still a nested aggregate function.

What am I missing?

Thank you very much!

postgresql
1 Answers

If you have something like SUM(COUNT(votes.option_id)) OVER() you can think of COUNT(votes.option_id) as a column generated in the GROUP BY clause.

According to the documentation:

The rows considered by a window function are those of the “virtual table” produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any.

This means that window functions operate at a level above the GROUP BY clause and any aggregates, and therefore aggregates are available to be used inside window functions. In your example the "virtual table" corresponds to the second picture.

The reason you cannot nest aggregate functions is that you cannot have multiple levels of GROUP BY on the same query. Similarly you cannot nest window functions. The documentation is clear on what type of expression are allowed inside aggregate and window functions. For aggregates functions we can use:

any value expression that does not itself contain an aggregate expression or a window function call

while for window functions we can use:

any value expression that does not itself contain window function calls

user_163429640
Related