Opened 3 years ago
Closed 20 months ago
#20930 closed New feature (duplicate)
Arithmetic operations on aggregates
Reported by: | debanshuk | Owned by: | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | master |
Severity: | Normal | Keywords: | aggregate, annotate |
Cc: | debanshuk, jorgecarleitao@… | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | yes |
Needs tests: | yes | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
In many cases one may require to do simple arithmetic operations (+, -, *, /) on aggregated (with numbers or other aggregates).
Eg. For model Xyz:
class Xyz(models.Model): a = models.IntegerField() b = models.IntegerField()
If I want to find percent ratio of sum of all a and sum of all b, I can write:
Xyz.objects.aggregate(ratio_percent=Sum('a')/Sum('b')*100)
This type of functionality has been implemented in this commit https://github.com/debanshuk/django/commit/722f7a4a17870fe1ffb6c207153d1fca6388a401
More operations (like pow) can also be added.
Change History (14)
comment:1 Changed 3 years ago by debanshuk
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:2 Changed 3 years ago by debanshuk
- Keywords aggregate annotate added
comment:3 follow-up: ↓ 4 Changed 3 years ago by wim@…
Hi debanshuk,
Wouldn't it be possible to make the same calculation outside of the aggregate? That seems cleaner to me.
Wim
comment:4 in reply to: ↑ 3 Changed 3 years ago by debanshuk
Hi Wim,
In the example given in the description, it would. And I guess in case of aggregates it would always be possible to make the calculations out side aggregate.
But, in case of annotate it would not. Well, it would certainly be possible, but wouldn’t be cleaner, as one would have to do the calculations for each element in resultant query-set.
Eg. Let modified Xyz be:
class Xyz(models.Model): user = models.ForeignKey(User) a = models.IntegerField() b = models.IntegerField()
Now if I want to find percent ratio of sum of all a and sum of all b of a user, for each user. I may write:
Xyz.objects.values('user').annotate(ratio_percent=Sum('a')/Sum('b')*100)
Currently, to do this (without writing raw SQL), one would have to do something like:
result = Xyz.objects.values('user').annotate(total_a=Sum('a'), total_b=Sum('b')) final_result = [{'user': r['user'], 'ratio_percent': r['total_a']/r['total_b']*100} for r in result]
Also, If someone want to aggregate over annotation results, like in the example if I want to find maximum of the percentages, I may do:
Xyz.objects.values('user').annotate(ratio_percent=Sum('a')/Sum('b')*100).aggregate(Max('ratio_percent'))
Currently, I would have to do:
result = Xyz.objects.values('user').annotate(total_a=Sum('a'), total_b=Sum('b')) final_result = max(r['total_a']/r['total_b']*100 for r in result)
In the second one, max calculation in done in python. In the first one it is done in SQL itself.
Debanshu Kundu.
comment:5 Changed 3 years ago by timo
Duplicate of #14030?
comment:6 Changed 3 years ago by debanshuk
Well...not exactly duplicate, as #14030 is for something like this:
Xyz.objects.aggregate(Sum(F('a')/F('b')*100))
but I guess #14030 can be extended to implement functionality of this ticket. In case of annotate something like this can be done:
Xyz.objects.values('user').annotate(total_a=Sum('a'), total_b=Sum('b')).values('user', ratio_percent=F('total_a')/F('total_b')*100)
But, in case of aggregate this wont work as aggregate returns dict (not QuerySet). So, I guess some changes are required for aggregate. Also, both functionalities (operations on aggregates and passing F objects to aggregates) can co-exist (as I think).
comment:7 Changed 3 years ago by apollo13
- Needs documentation set
- Needs tests set
- Patch needs improvement set
- Triage Stage changed from Unreviewed to Accepted
comment:8 Changed 3 years ago by debanshuk
- Owner changed from nobody to debanshuk
- Status changed from new to assigned
comment:9 Changed 3 years ago by debanshuk
Added pull request https://github.com/django/django/pull/1510. Fixed code and added tests and docs.
comment:10 Changed 3 years ago by debanshuk
Did amendment in the commit. Updated some conditions in test-cases to make them more reliable.
comment:11 Changed 3 years ago by debanshuk
- Owner debanshuk deleted
- Status changed from assigned to new
comment:12 Changed 23 months ago by jorgecarleitao
- Cc jorgecarleitao@… added
comment:13 Changed 20 months ago by jarshwah
FYI - the implementation of #14030 satisfies this ticket.
comment:14 Changed 20 months ago by timgraham
- Resolution set to duplicate
- Status changed from new to closed
Also added pull request https://github.com/django/django/pull/1484