基于布尔字段按日期区间分组的Django查询问题

数据表 #

期望结果 #

• Alice于2023/05/01至2023/05/15处于在线状态
• Alice于2023/05/31至2023/06/04处于离线状态
• Alice于2023/06/20至2023/06/21处于在线状态

原查询问题分析 #

你写的ORM查询直接按online和record_date分组，这会把每个日期的记录单独拆分，无法合并连续的同状态时间段。因为values('online', 'record_date')会让每个不同日期都成为独立分组，Min和Max在此场景下失去聚合意义。

解决方案 #

要实现连续同状态的时间段聚合，需先通过窗口函数标记连续状态的分组，再按分组聚合计算起止日期：

1. 用窗口函数标记连续状态分组 #

利用LAG窗口函数获取前一条记录的在线状态，当当前状态与前一条不同时标记新分组，再通过累加标记值生成唯一分组ID：

from django.db.models import F, Sum, Min, Max, Window
from django.db.models.functions import Lag, Coalesce
from django.db.models.window import WindowFrame

# 第一步：标记连续状态的分组
annotated = MyUserModel.objects.filter(record_date__year=2023).annotate(
    # 获取前一条记录的在线状态
    prev_online=Window(
        expression=Lag('online'),
        order_by=F('record_date').asc()
    ),
    # 当前状态与前一条不同时标记为1，否则0；第一条记录默认是新分组
    group_flag=Coalesce(
        F('online') != F('prev_online'),
        True
    ),
).annotate(
    # 累加group_flag得到连续状态的分组ID
    group_id=Window(
        expression=Sum('group_flag'),
        order_by=F('record_date').asc(),
        frame=WindowFrame(start=None, end='current_row')
    )
)

2. 按分组聚合起止日期 #

基于分组ID，结合用户和在线状态聚合，计算每个连续状态段的最小和最大日期：

result = annotated.values('user', 'online', 'group_id').annotate(
    min_date=Min('record_date'),
    max_date=Max('record_date')
).order_by('min_date')

3. 格式化输出结果 #

遍历查询结果生成目标格式：

for item in result:
    status = '在线' if item['online'] else '离线'
    print(f"- {item['user']}于{item['min_date'].strftime('%Y/%m/%d')}至{item['max_date'].strftime('%Y/%m/%d')}处于**{status}**状态")

注意事项 #

• 该方案需要Django 2.0+，且数据库需支持窗口函数（如PostgreSQL、MySQL 8.0+）；
• 若使用MySQL 5.x等不支持窗口函数的数据库，需通过子查询实现分组标记，写法会更繁琐，建议升级数据库版本。

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