Expresión de consulta de Django para campos calculados que requieren condiciones y conversión.

Estoy tratando de ejecutar una consulta de agregación que es aproximadamente igual a:

select sum(impressions) as impressions, sum(clicks) as clicks, sum(clicks)/sum(impressions) as ctr from stats group by product order by ctr; 

La base de datos utilizada es PostgreSQL.

Hice esta expresión de consulta (Django 1.9):

 Stats.objects.values('product').annotate( impressions = models.Sum('impressions'), clicks = models.Sum('clicks'), ctr = models.ExpressionWrapper( models.F('clicks')/models.F('impressions')), output_field = models.FloatField() ) ).order_by('ctr') 

Hay 2 problemas con esto:

  • ctr es 0.0 porque divide los enteros en el nivel de la base de datos
  • arroja division by zero si las impresiones son 0

¿Cuál es la solución adecuada?

Usa expresiones condicionales :

 from django.db.models import Case, F, Sum, When Stats.objects.values('product').annotate( tot_impressions=Sum('impressions'), tot_clicks=Sum('clicks') ).annotate( ctr=Case(When(tot_impressions=0, then=None), # or other value, eg 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') 

El cálculo exacto sobre cómo escalar esto depende de usted, pero agregar una constante de python dentro de un ExpressionWrapper nested debería ser suficiente:

 ctr = models.ExpressionWrapper( models.F('clicks')/models.ExpressionWrapper( models.F('impressions') + 1, output_field = models.FloatField() ), output_field = models.FloatField() ) 

Puede usar el paquete django-pg-utils para la expresión de consulta de divide que maneja la división por cero y devuelve valores flotantes

 from pg_utils import divide Stats.objects.annotate(ctr=divide(F('clicks'), F('impressions')))