需要条件和转换的计算字段的Django查询表达式 [英] Django query expression for calculated fields that require conditions and casting

问题描述

我正在尝试运行一个大致相等于以下的聚合查询：

I am trying to run an aggregation query that is roughly equals to:

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

使用的数据库是PostgreSQL。

The database used is PostgreSQL.

我做了这个查询表达式（Django 1.9）：

I made this query expression (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')

有两个问题：

• ctr 是0.0，因为它在数据库级别分割整数


• 它会抛出除以零，如果展示次数为0

• ctr is 0.0 because it divides integers at the database level
• it throws division by zero if impressions are 0

什么是正确的解决方案？

What is the proper solution?

推荐答案

使用条件表达式：

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, 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')

这篇关于需要条件和转换的计算字段的Django查询表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！