Django ORM查询：工单与最新遏制类评论的平均时间差计算

三个Django模型定义如下：

class Ticket(models.Model):
    date = models.DateTimeField(default=datetime.datetime.now, blank=True)
    subject = models.CharField(max_length=256)
    description = models.TextField()

class Comments(models.Model):
    date = models.DateTimeField(default=datetime.datetime.now, blank=True)
    comment = models.TextField()
    action = models.ForeignKey(Label, on_delete=models.CASCADE, limit_choices_to={'group__name': 'action'}, related_name='action_label')
    ticket = models.ForeignKey(Ticket, on_delete=models.CASCADE)

class Label(models.Model):
    name = models.CharField(max_length=50)
    group = models.ForeignKey(LabelGroup, on_delete=models.CASCADE)

需求 #

计算指定日期范围(start_date, end_date)内工单的创建时间Ticket.date，与对应Label.name为containment的最新Comment.date的平均时间跨度，最终在DRF视图返回如下格式JSON：

{
  "avg_time_to_contain": 37,
  "avg_time_to_recover": 157
}

当前查询语句 #

queryset = Comments.objects.filter(ticket__date__gte=start_date, ticket__date__lte=end_date).filter(action__name__icontains="containment").distinct(ticket).aggregate(avg_containment=Avg(F(date)- F(ticket__date)))

错误信息 #

NameError at /api/timetocontain/
名称 'ticket' 未定义
请求方法: GET 请求URL: http://127.0.0.1/api/timetocontain/
Django版本: 4.1.3 异常类型: NameError 异常值:
name 'ticket' is not defined

查询思路伪代码 #

• 获取关联工单日期在指定范围内的评论
• 筛选action关联的Label.name包含containment的评论
• 每个工单仅保留最新的符合条件评论，计算与工单创建时间的差值
• 对所有时间差值取平均值

问题排查与解决方案 #

1. 语法错误修复

报错的直接原因是distinct(ticket)中的ticket是未定义变量，Django的distinct()方法指定字段时需传入字符串，应改为distinct('ticket')。但这只是解决语法问题，无法满足“保留最新评论”的核心需求。

2. 正确实现逻辑（窗口函数方案）

使用窗口函数为每个工单的符合条件评论按日期排序，筛选出最新的那条后计算平均时间差：

from django.db.models import F, Avg, Window, Max
from django.db.models.functions import Rank

# 给每个工单的符合条件评论按日期倒序排名
ranked_comments = Comments.objects.filter(
    ticket__date__gte=start_date,
    ticket__date__lte=end_date,
    action__name__icontains='containment'
).annotate(
    latest_rank=Window(
        expression=Rank(),
        partition_by=F('ticket'),
        order_by=F('date').desc()
    )
)

# 筛选每个工单的最新评论，计算时间差平均值
contain_result = ranked_comments.filter(latest_rank=1).aggregate(
    avg_time_to_contain=Avg(F('date') - F('ticket__date'))
)

# 时间差转换为秒（按需转分钟/小时）
avg_contain_seconds = contain_result['avg_time_to_contain'].total_seconds() if contain_result['avg_time_to_contain'] else 0

# 补充recovery逻辑（替换action名称即可）
ranked_recover_comments = Comments.objects.filter(
    ticket__date__gte=start_date,
    ticket__date__lte=end_date,
    action__name__icontains='recover'  # 替换为recovery对应的标签名
).annotate(
    latest_rank=Window(
        expression=Rank(),
        partition_by=F('ticket'),
        order_by=F('date').desc()
    )
)
recover_result = ranked_recover_comments.filter(latest_rank=1).aggregate(
    avg_time_to_recover=Avg(F('date') - F('ticket__date'))
)
avg_recover_seconds = recover_result['avg_time_to_recover'].total_seconds() if recover_result['avg_time_to_recover'] else 0

# 构建返回结果
response_data = {
    'avg_time_to_contain': int(avg_contain_seconds),
    'avg_time_to_recover': int(avg_recover_seconds)
}

3. 备选方案（子查询方式）

通过子查询获取每个工单的最新评论日期，再关联计算平均时间差：

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

# 获取每个工单的最新containment评论日期
latest_contain_date = Comments.objects.filter(
    ticket=OuterRef('pk'),
    action__name__icontains='containment'
).order_by('-date').values('date')[:1]

# 计算containment平均时间差
contain_result = Ticket.objects.filter(
    date__gte=start_date,
    date__lte=end_date,
    comments__action__name__icontains='containment'
).annotate(
    latest_contain=Subquery(latest_contain_date)
).aggregate(
    avg_time_to_contain=Avg(F('latest_contain') - F('date'))
)

# 同理实现recovery逻辑
latest_recover_date = Comments.objects.filter(
    ticket=OuterRef('pk'),
    action__name__icontains='recover'
).order_by('-date').values('date')[:1]
recover_result = Ticket.objects.filter(
    date__gte=start_date,
    date__lte=end_date,
    comments__action__name__icontains='recover'
).annotate(
    latest_recover=Subquery(latest_recover_date)
).aggregate(
    avg_time_to_recover=Avg(F('latest_recover') - F('date'))
)

# 转换时间单位并返回
response_data = {
    'avg_time_to_contain': int(contain_result['avg_time_to_contain'].total_seconds()) if contain_result['avg_time_to_contain'] else 0,
    'avg_time_to_recover': int(recover_result['avg_time_to_recover'].total_seconds()) if recover_result['avg_time_to_recover'] else 0
}

内容的提问来源于stack exchange，提问作者Bring Coffee Bring Beer