如何统计Django中每日成本超当日均值的采购订单数量？

问题分析 #

你原代码的错误在于：在同一个values('date')后的链式annotate中，第二个annotate里的Sum(Case(...))尝试直接引用第一个annotate生成的聚合字段avg_cost，但Django ORM不允许在聚合函数内部的条件判断中直接引用同层级的聚合结果——因为分组聚合是对整组数据计算的，行级的cost无法在聚合阶段直接和组级的avg_cost做比较。

可行解决方案 #

你的需求完全可以实现，以下是两种可靠的写法：

方法1：子查询+分组计数 #

先通过子查询获取每个日期的平均成本，再过滤出成本超标的记录并分组统计：

from django.db.models import Avg, Count, Subquery, OuterRef

# 子查询：计算每个日期的平均采购成本
daily_avg_subquery = Purchase.objects.filter(
    date=OuterRef('date')
).values('date').annotate(avg_cost=Avg('cost')).values('avg_cost')

# 主查询：过滤出成本超过当日平均的记录，按日期分组统计数量
result = Purchase.objects.filter(
    cost__gt=Subquery(daily_avg_subquery)
).values('date').annotate(purchases_above=Count('id'))

方法2：Window函数+过滤分组 #

利用Window函数为每条记录标记其所属日期的平均成本，再过滤统计：

from django.db.models import Avg, Count, Window, F

# 为每条记录添加当日平均成本字段
purchase_with_daily_avg = Purchase.objects.annotate(
    daily_avg=Window(
        expression=Avg('cost'),
        partition_by=['date'],  # 按日期分组计算平均
        frame=RowRange(start=None, end=None)  # 包含整组所有记录
    )
)

# 过滤出成本超标的记录，按日期分组计数
result = purchase_with_daily_avg.filter(
    cost__gt=F('daily_avg')
).values('date').annotate(purchases_above=Count('id'))

结果说明 #

两种方法最终都会返回一个QuerySet，每个元素是类似{'date': datetime.date(2024, 1, 1), 'purchases_above': 3}的字典，代表对应日期有3笔采购订单成本超过当日平均。

内容的提问来源于stack exchange，提问作者Djozeph