In Django, how to multiply-aggregate a field?

Question

I want to aggregate a field using multiplication, but apparently Django doesn't have a Product function among its aggregation function.

Example of what I want to do

# models.py
class MyModel(models.Model):
  ratio = models.DecimalField(...)

# views.py
mys = MyModel.objects.filter(...).aggregate(cum_ratio=Product(ratio))

How to achieve this?

I feel like since Django didn't include a Product function like they did with Sum suggests that it's trivial but I can't put my finger on it.

There isn't a PRODUCT aggregate like there is a SUM aggregate. See also: https://stackoverflow.com/questions/3912204/why-is-there-no-product-aggregate-function-in-sql You might be able to use the exp(sum(ln(x))) identity, depending on your accuracy requirements. — Nick ODell, Aug 03 '23 at 00:19

Csaba Toth · Accepted Answer · 2023-08-03T00:37:49.710

The Django aggregate functions usually could be translated into corresponding SQL phrases, so the result would be an SQL query. SQL has AVG, or SUM, but doesn't have PRODUCT (Why is there no PRODUCT aggregate function in SQL?). One reason for this is probably the overflow danger. If you start to multiply even smaller numbers together you can pretty quickly arrive to very large values.

So if the computation will happen in Python land anyway (as opposed to SQL land under the hood of your SQL engine) then you can do something like:

import numpy as np

ratios = MyModel.objects.filter(ratio__lte=10).values_list("ratio", flat=True)
mys = np.prod(ratios)

Someone may try to implement a Product Django aggregate with a SELECT exp(SUM(LOG(price))) FROM products; type mathematical trick, but concluding to a product using exponential and logarithmic functions sounds like to me blatant waste of computational resources. I like to keep things in SQL level if possible (so the engine can optimize also), but maybe this is an example where it'd make sense to bring it to Python?

In Django, how to multiply-aggregate a field?

Example of what I want to do

1 Answers1