#28296: Add support for aggregation through subqueries
-------------------------------------+-------------------------------------
Reporter: László Károlyi | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by S. Dole Melipone):
I would like to offer the following solution using the new SQL `Window`
class. This allows the aggregation of annotated values by calculating the
aggregate over partitions based on the outer query model (in the GROUP BY
clause), then annotating that data to every row in the subquery queryset.
The subquery can then use the aggregated data from the first row returned
and ignore the other rows.
I'd like some opinions and feedback on whether this is the optimal
solution for aggregating arbitrary annotated data in a subquery. If so I'd
be happy to write something up for the documentation. I couldn't find any
other way to do this in the ORM! This also works well for calling `Sum`,
`Avg` etc. rather than `Count` on annotated values on the subquery
queryset.
{{{
Performance.objects.annotate(
reserved_seats=Subquery(
SeatReservation.objects.filter(
performance=OuterRef(name='pk'),
status__in=TAKEN_TYPES,
).annotate(
reserved_seat_count=Window(
expression=Count('pk'),
partition_by=[F('performance')]
),
).values('reserved_seat_count')[:1],
output_field=FloatField()
)
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/28296#comment:6>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.61cfe5e38916eb452c078a465900a437%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
