Support nested aggregates in window expressions.

[Héctor Pablos]

When trying do something like

Sum(Count('field_name'))

Django raises a FieldError exception with the message Cannot compute Sum('count'): 'count' is an aggregate". That makes sense except when Sum(Count()) is the expression of a Window function, where nesting two aggregates is a valid SQL expression:

Window(expression=Sum(Count('field_name')), **omitted_window)

I may be missing some edge case here but would it be possible to check, in the django.db.aggregates.Aggregate Func resolve_expression method, whether the expression containing another aggregate is the main expression of a Window function and, in that case, allow the execution of the SQL? Or is there any way to circumvent this aggregation nesting limitation in window functions?

[Mariusz Felisiak]

... where nesting two aggregates is a valid SQL expression

I'm not sure about this, can you provide an example of useful window expression with nested aggregates. I've tried to remove this check for window expressions and it crashes on PostgreSQL:

django.db.utils.ProgrammingError: column "expressions_window_employee.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "expressions_window_employee"."id", "expressions_wind...

and Oracle:

django.db.utils.DatabaseError: ORA-00937: not a single-group group function

it works only on SQLite but the result is quite unexpected.

[Héctor Pablos]

Sure! Here's an example. Assuming the following models/tables:

users

| id | group | name |
  1     A      user1
  2     A      user2
  3     B      user3

comments

| id | user_id | comment |
  1     1      hi
  2     1      there
  3     2      foo
  4     3      bar

I am able to do an SQL query like the following in both PostgreSQL and Exasol (The DBs I currently have "at hand"):

SELECT u.group, u.name, count(1) as user_comments_count, sum(count(1)) OVER (PARTITION BY u.group) as group_comments_count
FROM users u
    INNER JOIN comments c ON c.user_id = u.id
GROUP BY u.group, u.name

With the following result:

| group | name | user_comments_count | group_comments_count |
   A     user1          2                    3
   A     user2          1                    3
   B     user3          1                    1

Therefore, I was expecting to be able to use the ORM like this (not tested, bear in mind I could be making mistakes here but I hope you get the idea, sorry!):

class User(models.Model):
   group = models.charField(max_length=2)
   name = models.charField(max_length=20, unique=True)


class Comments(models.Model):
   user = models.ForeignKey(User, on_delete=models.SET_NULL)
   coment = models.charField(max_length=500)


User.objects.annotate(
    user_comments_count=Count('comments__id'),
    group_comments_count=Window(expression=Sum(Count('comments__id')), partition_by=[F('group')]),
).values(
    'id',
    'group',
    'user_comments_count',
    'group_comments_count',
)

[Mariusz Felisiak]

Thanks for en example. It looks reasonable but an implementation can be tricky because all aggregations must be included in the GROUP BY clause. Tentatively accepted, however it may not be worth complexity. I would use a subquery for user_comments_count instead.