[Django] #20930: Arithmetic operations on aggregates
Django
----------------------------------------------+--------------------
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/20930>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by debanshuk):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
Also added pull request https://github.com/django/django/pull/1484
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:1>
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Changes (by debanshuk):
* keywords: => aggregate, annotate
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:2>
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: aggregate, annotate | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by wim@…):
Hi debanshuk,
Wouldn't it be possible to make the same calculation outside of the
aggregate? That seems cleaner to me.
Wim
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:3>
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: aggregate, annotate | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (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 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 ratio_percentage, 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.'''
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:4>
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: aggregate, annotate | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (by timo):
Duplicate of #14030?
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:5>
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage:
Keywords: aggregate, annotate | Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Comment (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(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`.
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:6>
Django
Reporter: debanshuk | Owner: nobody
Type: New feature | Status: new
(models, ORM) | Resolution:
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Changes (by apollo13):
* needs_better_patch: 0 => 1
* needs_docs: 0 => 1
* needs_tests: 0 => 1
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:7>
Django
Reporter: debanshuk | Owner: debanshuk
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: new => assigned
* owner: nobody => debanshuk
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:8>
Django
Reporter: debanshuk | Owner: debanshuk
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by debanshuk):
Added pull request https://github.com/django/django/pull/1510. Fixed code
and added tests and docs.
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:9>
Django
Reporter: debanshuk | Owner: debanshuk
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by debanshuk):
Did amendment in the commit. Updated some conditions in test-cases to make
them more reliable.
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:10>
Django
Reporter: debanshuk | Owner:
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* owner: debanshuk =>
* status: assigned => new
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:11>
Django
Reporter: debanshuk | Owner:
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* cc: jorgecarleitao@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:12>
Django
Reporter: debanshuk | Owner:
Type: New feature | Status: new
(models, ORM) | Resolution:
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
Comment (by jarshwah):
FYI - the implementation of #14030 satisfies this ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:13>
Django
Reporter: debanshuk | Owner:
Type: New feature | Status: closed
Component: Database layer | Version: master
Severity: Normal | Triage Stage: Accepted
Keywords: aggregate, annotate | Needs documentation: 1
Has patch: 1 | Patch needs improvement: 1
Needs tests: 1 | UI/UX: 0
Easy pickings: 0 |
-------------------------------------+-------------------------------------
* status: new => closed
* resolution: => duplicate
--
Ticket URL: <https://code.djangoproject.com/ticket/20930#comment:14>