Django, division between two annotate result won't calculate correctly

Question

I'm trying to get a division between two annotate results in queryset. Impression is much larger than click, so I should get tenth decimal.

def get_queryset(self):
        return googleData.objects.filter(account=self.account_name).\
            values('date').\
            annotate(Sum('click'), Sum('impression'), Sum('converted_click'),
                     Sum('conversion_value'), Sum('cost'), Sum('conversion_value'), ctr_monthly= Sum('click')/Sum('impression')).\
            order_by('-date')

Trouble here:

ctr_monthly= Sum('click')/Sum('impression'))

In template i have:

<td>{{ googleData.ctr_monthly | floatformat:2}} </td>

And the result is 0.00. If I do ctr_final = click * impression, it generates correctly. Click & Impression are both integerfield.

I tried use the float(), gave me a syntax error.

The other question is: what's the best pratice to make a queryset like this? Is there any way that I can break it down to several short piece of code and make it more neat and readable ?

Thank you~

Radioman

Answer 1

With newer versions of Django, you can use the new Func object to cast the values to FloatFields or DecimalFields before the Sum.

from django.db.models.functions import Cast
from django.db.models import FloatField
ctr_monthly= Cast(Sum('click'), FloatField())/Cast(Sum('impression')), FloatField())

Even with an older version of Django, you might be able to just specify the output_field on the Sum before annotating ctr_monthly like so:

from django.db.models import F
def get_queryset(self):
    return googleData.objects.filter(
        account=self.account_name
    ).values('date').annotate(
        click_sum=Sum(
            'click',
            output_field=FloatField()
        ),
        impression_sum=Sum(
            'impression',
            output_field=FloatField()
        ),
        converted_click_sum=Sum('converted_click'),
        conversion_value_sum=Sum('conversion_value'),
        cost_sum=Sum('cost')
    ).annotate(
        ctr_monthly=F('click_sum') / F('impression_sum')
    ).order_by('-date')

Sociolinguistics

Answer 2

As far as I am aware, there isn't a way to do this using the ORM.

The Sum() function returns the same field type as put into it (i.e. an IntegerField() will always return an Integer). You could use a function like ExpressionWrapper to force the output to be a float, but that won't help in this case as it will be too late: the division of two integers will have been already returning another integer.

To solve your problem, remove the ctr_monthly section form your query, and create a simple template tag which converts the two numbers to floats and divide them.

Your template will then look like: <td>{{ monthly_ctr(googleData.click, googleData.impression) | floatformat:2}} </td>

Shannashannah

This works! Thank you. Is a a better practice to have everything in decimalField ? – Radioman

Answer 3

My case is a bit different but it will help you to solve your problem. In my case I want float value for percentage but I got integer in response. So I use Cast to get float value for numerator which solved my problem.

return category_qs.annotate(
            rankings_count=Count(
                f"user_{self.category}_rankings__user", distinct=True
            ),
            percentage=Case(
                When(
                    rankings_count__gt=0,
                    then=Cast(F("rankings_count") * 100, FloatField())
                    / users_count,
                ),
                default=Value(0.0),
                output_field=FloatField(),
            ),
        ).order_by("-percentage")

Headed