[Django] #32277: Aggregate of aggregate not allowed in window functions

[Django]

#32277: Aggregate of aggregate not allowed in window functions
-------------------------------------+-------------------------------------
Reporter: Héctor | Owner: nobody
Pablos |
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords: window, aggregates
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
When trying do something like

{{{#!python
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:

{{{#!python
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?

--
Ticket URL: <https://code.djangoproject.com/ticket/32277>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#32277: Support nested aggregates in window expressions.
-------------------------------------+-------------------------------------
Reporter: Héctor Pablos | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo

Keywords: window, aggregates | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* cc: Mads Jensen (added)
* status: new => closed
* type: Bug => New feature
* resolution: => needsinfo

Comment:

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

--
Ticket URL: <https://code.djangoproject.com/ticket/32277#comment:1>

[Django]

#32277: Support nested aggregates in window expressions.
-------------------------------------+-------------------------------------
Reporter: Héctor Pablos | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: needsinfo

Keywords: window, aggregates | Triage Stage:

| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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!):
{{{#!python
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',
)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/32277#comment:2>

[Django]

#32277: Support nested aggregates in window expressions.
-------------------------------------+-------------------------------------
Reporter: Héctor Pablos | Owner: nobody

Type: New feature | Status: new
Component: Database layer | Version: master

(models, ORM) |
Severity: Normal | Resolution:

Keywords: window, aggregates | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: closed => new
* version: 3.0 => master
* resolution: needsinfo =>
* stage: Unreviewed => Accepted

Comment:

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.

--
Ticket URL: <https://code.djangoproject.com/ticket/32277#comment:3>

[Django]

#32277: Support nested aggregates in window expressions.
-------------------------------------+-------------------------------------

Reporter: Héctor Pablos | Owner: Saeed
| Hasani Borzadaran
Type: New feature | Status: assigned
Component: Database layer | Version: dev

(models, ORM) |
Severity: Normal | Resolution:

Keywords: window, aggregates | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Saeed Hasani Borzadaran):

* owner: nobody => Saeed Hasani Borzadaran
* status: new => assigned

--
Ticket URL: <https://code.djangoproject.com/ticket/32277#comment:4>

[Django]

#32277: Support nested aggregates in window expressions.
-------------------------------------+-------------------------------------

Reporter: Héctor Pablos | Owner: (none)

Type: New feature | Status: new

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: window, aggregates | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Saeed Hasani Borzadaran):

* owner: Saeed Hasani Borzadaran => (none)
* status: assigned => new

--
Ticket URL: <https://code.djangoproject.com/ticket/32277#comment:5>

[Django]

#32277: Support nested aggregates in window expressions.
-------------------------------------+-------------------------------------

Reporter: Héctor Pablos | Owner: nobody

Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: window, aggregates | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by John Speno):

* cc: John Speno (added)

--
Ticket URL: <https://code.djangoproject.com/ticket/32277#comment:4>