#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 <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.538c1e20da3f5afa9b00816ab32ec665%40djangoproject.com.
