Weighted average from queryset - Django

Question

How get the weighted average from queryset as quickly as possible. Is it possible without the use of loops. Below is an example.

My models.py:

class Product(models.Model):
    price = models.DecimalField(max_digits=15, decimal_places=2)
    weighted = models.IntegerField()
    day = models.IntegrField()

In my database, I have the following values for day 1:

Object ID I: Price=100, weighted=12, day=1
Object ID II: Price=50, weighted=1, day=1
Object ID III: Price=75, weighted=3, day=1

how to calculate the weighted average for day one?

day_1_average_weighted = Product.objects.filter(day=1) ???how to get a weighted average - 71.88???

If you can use `numpy`, you could do `np.average([100, 50, 75], weights=[12, 1, 3])` — cmbfast, Jul 23 '21 at 22:04
@cmbfast: but this will not calculate the weighted sum at the database side, which is usually more efficient, since it limits the "bandwidth" between the database and the application. — Willem Van Onsem, Jul 23 '21 at 22:09

Willem Van Onsem · Accepted Answer · 2021-07-23T22:08:38.527

You can implement this with:

from django.db.models import F, Sum

Product.objects.filter(day=1).aggregate(
    weighted_sum=Sum(F('weighted') * F('price')) / Sum('weighted')
)

This will return a dictionary with one item: weighed_sum will contain the weighted sum.

For example:

>>> Product.objects.create(weighted=9, price=2, day=1)
<Product: Product object (1)>
>>> Product.objects.create(weighted=1, price=5, day=1)
<Product: Product object (2)>
>>> Product.objects.filter(day=1).aggregate(
...     weighted_sum=Sum(F('weighted') * F('price')) / Sum('weighted')
... )
{'weighted_sum': Decimal('2.300000')}

Weighted average from queryset - Django

1 Answers1

Linked