Can I calculate the geometric mean with django annotation?

Question

Is there a way to use it like

Foo.objects.annotate(geometric_mean=GeometricMean('bars__value'))

or

Foo.objects.annotate(geometric_mean=Power(Prod('bars__value'), Count('bars'))

Answer 1

You can do something quite similar: you can calculate the average of the natural logarithms:

from django.db.models import Avg
from django.db.models.functions import Ln

Foo.objects.annotate(geometric_mean=Avg(Ln('bars__value')))

then the geometric mean is the exponent of this. If you really need the geometric mean itself, then you can use Exp [Django-doc] on that result:

from django.db.models import Avg
from django.db.models.functions import Exp, Ln

Foo.objects.annotate(geometric_mean=Exp(Avg(Ln('bars__value'))))

but you do not need this if you for example only want to order by two geometric means, since the exponent leaves the order relation intact: e^a≤e^b implies a≤b and vice versa.

This works because ln(x₁×x₂×…×x_n) is equivalent to ln(x₁)+ln(x₂)+…+ln(x_n), and ln(x^y) is equivalent to y×ln(x).