Django query expression for calculated fields that require conditions and casting
from django.db.models import Case, F, Sum, WhenStats.objects.values('product').annotate( tot_impressions=Sum('impressions'), tot_clicks=Sum('clicks')).annotate( ctr=Case(When(tot_impressions=0, then=None), # or other value, e.g. then=0 # 1.0*... is to get float in SQL default=1.0*F('tot_clicks')/F('tot_impressions'), output_field=models.FloatField())).order_by('ctr')
The exact math on how you scale this is up to you, but adding in a python constant inside a nested ExpressionWrapper should suffice:
ctr = models.ExpressionWrapper( models.F('clicks')/models.ExpressionWrapper( models.F('impressions') + 1, output_field = models.FloatField() ), output_field = models.FloatField())
You can use the django-pg-utils package for the divide query expression that handles division by zero and returns float values
from pg_utils import divideStats.objects.annotate(ctr=divide(F('clicks'), F('impressions')))