如何在Django查询集中使用Group By、Max列并计算文档版本时间差平均值

Django ORM处理分组聚合并获取对应行的其他字段，有三种常用方案：

方案1：子查询+OuterRef #

先通过子查询获取每个分组的Max值，再关联原表匹配对应记录。以按uid分组取最大version的记录为例：

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

# 子查询：获取每个uid的最大version
max_version_sub = Documents.objects.filter(
    uid=OuterRef('uid')
).values('uid').annotate(max_v=Max('version')).values('max_v')

# 匹配原表，拿到对应完整记录
target_records = Documents.objects.filter(
    version=Subquery(max_version_sub)
)

方案2：排序+去重 #

利用order_by和distinct特性，按分组字段排序后保留每组第一条（即Max值对应行）：

# 按uid分组，取每个uid的最大version记录
target_records = Documents.objects.order_by('uid', '-version').distinct('uid')

⚠️ 注意：该方法仅PostgreSQL支持按指定字段distinct，MySQL需调整SQL模式或改用其他方案。

方案3：原生SQL查询 #

当ORM方法受限，直接编写原生SQL更灵活：

from django.db import connection

with connection.cursor() as cursor:
    cursor.execute("""
        SELECT d.*
        FROM documents d
        INNER JOIN (
            SELECT uid, MAX(version) AS max_version
            FROM documents
            GROUP BY uid
        ) mv ON d.uid = mv.uid AND d.version = mv.max_version
    """)
    # 转换为模型实例
    target_records = Documents.objects.raw(cursor.query)

根据业务逻辑：用户认可文档则标记reviewed_dtm，否则提交新版本，因此每个uid的最大版本即为最终审核完成的版本。我们需要计算每个uid的「最大版本timestamp - 最小版本timestamp」的平均值。

实现代码 #

from django.db.models import Max, Min, F, ExpressionWrapper, DurationField, Avg

# 1. 按uid分组，计算每个uid的创建时间（最小timestamp）和审核完成时间（最大timestamp）
uid_time_diffs = Documents.objects.values('uid').annotate(
    create_time=Min('timestamp'),
    finish_time=Max('timestamp')
).annotate(
    # 计算时间差，输出为Duration类型
    time_spent=ExpressionWrapper(
        F('finish_time') - F('create_time'),
        output_field=DurationField()
    )
)

# 2. 计算所有时间差的平均值
average_spent = uid_time_diffs.aggregate(avg_time=Avg('time_spent'))['avg_time']

# 转换为易读格式（例如小时）
if average_spent:
    avg_hours = average_spent.total_seconds() / 3600
    print(f"平均审核耗时：{avg_hours:.2f}小时")
else:
    print("无符合条件的审核记录")

进阶：仅统计已审核的uid #

如果需要排除未完成审核的uid（即最大版本未标记reviewed_dtm），可调整查询：

# 先获取每个uid的最大版本记录ID
max_version_ids = Documents.objects.values('uid').annotate(
    max_v=Max('version')
).values_list('id', flat=True)

# 过滤已审核的最大版本记录，再计算时间差
reviewed_time_diffs = Documents.objects.filter(
    id__in=max_version_ids,
    reviewed_dtm__isnull=False
).values('uid').annotate(
    create_time=Min('timestamp'),
    finish_time=F('timestamp')
).annotate(
    time_spent=ExpressionWrapper(
        F('finish_time') - F('create_time'),
        output_field=DurationField()
    )
)

average_spent = reviewed_time_diffs.aggregate(avg_time=Avg('time_spent'))['avg_time']

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