Django使用Annotate按日期分组统计去重活跃用户数问题求助

问题根因 #

你之前统计结果偏大的核心原因是：直接对user_id加distinct=True时，如果Django ORM生成的SQL语句存在隐式连接（比如关联了product外键表、或者存在其他注释字段导致结果集行数膨胀），去重计数的逻辑会被干扰，导致统计值高于实际值。另外你尝试同时按user_id和日期分组后没有做二次按日期聚合，只会得到「单日单用户」的明细行，不会得到单日总用户数，所以达不到统计效果。

优先推荐实现方案 #

from django.db.models import Count
from django.db.models.functions import TruncDate

daily_active_users = Visualization.objects.annotate(
    # 将访问时间截断为日期，时区遵循Django全局配置
    visit_date=TruncDate('start_time')
).values('visit_date')\
 .annotate(
    # 按用户ID去重计数，同一用户单日多次访问只算1次
    active_user_count=Count('user', distinct=True)
).order_by('visit_date')

稳妥兼容实现方案 #

如果上面的代码依然存在统计偏差，可以改用先去重再聚合的写法，彻底屏蔽隐式连接带来的行数膨胀问题：

from django.db.models import Count
from django.db.models.functions import TruncDate

daily_active_users = Visualization.objects.annotate(
    visit_date=TruncDate('start_time')
).values('visit_date', 'user_id')\
 .distinct() # 先去重得到所有「日期+用户」的唯一组合
 .values('visit_date')\ # 再按日期分组统计
 .annotate(
    active_user_count=Count('user_id')
).order_by('visit_date')

结果输出示例 #

遍历查询结果即可得到你要的格式：

for item in daily_active_users:
    print(f"{item['visit_date']}活跃用户数{item['active_user_count']}")

示例输出：

2021-09-07活跃用户数3
2021-09-06活跃用户数2

注意事项 #

• 如果你的业务数据带有时区，需要确保Django时区配置和业务数据的时区一致，避免日期截断出现偏移
• 数据量较大时，建议给start_time和user字段加联合索引，大幅提升查询性能

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