如何用Django ORM实现分组后字段平均值与Top5值平均值计算？

问题场景 #

现有Django模型Cars关联Redshift数据库，需单次查询获取每个manufacturer的：

1. 该品牌所有车型的价格平均值
2. 该品牌价格Top5车型的价格平均值

模型定义：

class Cars(models.Model):
    manufacturer = models.CharField()
    model = models.CharField()
    price = models.FloatField()

方案一：基于窗口函数+条件聚合（对应SQL方式一） #

利用Django的Window函数为每条记录添加分组排名，再通过条件聚合计算Top5均值，与整体均值在同一分组查询中完成：

from django.db.models import Avg, Case, When, Window, RowNumber, F, FloatField

# 为每条记录添加按manufacturer分组、价格降序的排名
ranked_cars = Cars.objects.annotate(
    rank=Window(
        expression=RowNumber(),
        partition_by=F('manufacturer'),
        order_by=F('price').desc()
    )
)

# 分组计算两个平均值
queryset = ranked_cars.values('manufacturer').annotate(
    average_price=Avg('price'),
    average_top_5_price=Avg(
        Case(
            When(rank__lte=5, then=F('price')),
            output_field=FloatField()
        )
    )
).order_by('manufacturer')

说明：Case语句会过滤掉排名>5的价格（返回Null），Avg函数会自动忽略Null值，与SQL中AVG(CASE WHEN rank <=5 THEN price END)逻辑完全一致。

方案二：双生子查询关联（对应SQL方式二） #

分别通过两个子查询计算整体均值和Top5均值，再通过manufacturer关联结果：

from django.db.models import Avg, Window, Rank, F, Subquery, OuterRef

# 子查询1：计算每个品牌的整体均价
avg_price_subquery = Cars.objects.filter(
    manufacturer=OuterRef('manufacturer')
).values('manufacturer').annotate(
    avg_price=Avg('price')
).values('avg_price')

# 子查询2：先为记录添加排名，筛选Top5后计算均价
top5_avg_subquery = Cars.objects.filter(
    manufacturer=OuterRef('manufacturer')
).annotate(
    rank=Window(
        expression=Rank(),
        partition_by=F('manufacturer'),
        order_by=F('price').desc()
    )
).filter(rank__lte=5).values('manufacturer').annotate(
    top5_price=Avg('price')
).values('top5_price')

# 关联两个子查询结果
queryset = Cars.objects.values('manufacturer').annotate(
    average_price=Subquery(avg_price_subquery[:1]),
    average_top_5_price=Subquery(top5_avg_subquery[:1])
).distinct().order_by('manufacturer')

说明：如果需要严格取Top5（即使存在相同价格也不超5条），可将Rank()替换为RowNumber()，与SQL逻辑对齐。

注意事项 #

• 确保使用Django 2.0及以上版本，该版本开始支持Window窗口函数
• Redshift原生支持窗口函数，无需额外配置
• 实际业务表字段较多时，上述查询仅涉及manufacturer和price字段，不会加载其他冗余字段，性能不受影响

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