如何在Django或PostgreSQL中用distinct和group by实现指定数据聚合

原始数据表：production #

需求 #

按process_id汇总数据，规则是：同一process_id下，先对code去重（每个code仅保留一条记录的qty），再将去重后的qty求和，最终得到如下结果：

目标结果 #

尝试的Django代码（存在逻辑问题） #

Production.objects.values('process').distinct('code').annotate(total_qty=Sum('quantity'))

正确实现方法 #

Django 实现 #

根据需求，我们需要先对process_id和code的组合去重（保留每组的一条记录），再按process_id汇总qty。以下两种方式均可实现：

方式1：利用PostgreSQL的distinct特性（适用于PostgreSQL数据库）

from django.db.models import Sum

# 先按process_id和code去重，每个组合保留一条记录
unique_records = Production.objects.order_by('process_id', 'code').distinct('process_id', 'code')
# 再按process_id汇总qty
result = unique_records.values('process_id').annotate(total_qty=Sum('qty'))

注：这里的distinct('process_id', 'code')仅在PostgreSQL中支持，会返回每个(process_id, code)组合的第一条记录，排序由order_by指定。如果需要保留每组中qty最大的记录，可调整order_by为order_by('process_id', 'code', '-qty')。

方式2：通用子查询方式（兼容所有数据库）

from django.db.models import Sum, Subquery, OuterRef

# 子查询获取每个(process_id, code)组的最新记录ID
subquery = Production.objects.filter(
    process_id=OuterRef('process_id'),
    code=OuterRef('code')
).order_by('-id').values('id')[:1]

# 过滤出目标记录后按process_id汇总
result = Production.objects.filter(id__in=Subquery(subquery)).values('process_id').annotate(total_qty=Sum('qty'))

PostgreSQL SQL 实现 #

直接编写SQL语句的话，可利用PostgreSQL的DISTINCT ON语法：

SELECT process_id, SUM(qty) AS qty
FROM (
    -- 每个(process_id, code)组合保留一条记录，按qty降序取最大的那条
    SELECT DISTINCT ON (process_id, code) process_id, qty
    FROM production
    ORDER BY process_id, code, qty DESC
) AS filtered_data
GROUP BY process_id;

如果不需要取最大qty，仅需任意一条记录，可调整内层ORDER BY为ORDER BY process_id, code, id。

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