Opened 11 years ago

Closed 10 years ago

#20930 closed New feature (duplicate)

Arithmetic operations on aggregates

Reported by: Debanshu Kundu Owned by:
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: aggregate, annotate
Cc: Debanshu Kundu, 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 by Debanshu Kundu, 11 years ago

comment:2 by Debanshu Kundu, 11 years ago

Keywords: aggregate annotate added

comment:3 by wim@…, 11 years ago

Hi debanshuk,

Wouldn't it be possible to make the same calculation outside of the aggregate? That seems cleaner to me.

Wim

in reply to:  3 comment:4 by Debanshu Kundu, 11 years ago

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.

Last edited 11 years ago by Debanshu Kundu (previous) (diff)

comment:5 by Tim Graham, 11 years ago

Duplicate of #14030?

comment:6 by Debanshu Kundu, 11 years ago

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

Last edited 11 years ago by Debanshu Kundu (previous) (diff)

comment:7 by Florian Apolloner, 11 years ago

Needs documentation: set
Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedAccepted

comment:8 by Debanshu Kundu, 11 years ago

Owner: changed from nobody to Debanshu Kundu
Status: newassigned

comment:9 by Debanshu Kundu, 11 years ago

Added pull request https://github.com/django/django/pull/1510. Fixed code and added tests and docs.

comment:10 by Debanshu Kundu, 11 years ago

Did amendment in the commit in pull request. Updated some conditions in test-cases to make them more reliable.

Last edited 11 years ago by Debanshu Kundu (previous) (diff)

comment:11 by Debanshu Kundu, 11 years ago

Owner: Debanshu Kundu removed
Status: assignednew

comment:12 by jorgecarleitao, 10 years ago

Cc: jorgecarleitao@… added

comment:13 by Josh Smeaton, 10 years ago

FYI - the implementation of #14030 satisfies this ticket.

comment:14 by Tim Graham, 10 years ago

Resolution: duplicate
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top