Django annotate如何实现按日分组的字符串拼接聚合查询

你原有查询逻辑的核心问题有两个：

• 把browser字段放入了values()方法中，Django ORM 会将values()内声明的所有字段作为分组依据，最终会按「日期+浏览器」维度分组，无法实现仅按日期聚合的需求
• 缺少对应数据库的字符串聚合函数，无法完成同组内多字段值的拼接操作

可直接运行的实现代码 #

PostgreSQL 环境（Django 内置支持） #

PostgreSQL 可以直接使用Django 内置的StringAgg聚合函数实现字符串拼接，代码如下：

from django.db.models import Count
from django.contrib.postgres.aggregates import StringAgg
from datetime import datetime

queryset = Click.objects.filter(
    url=url_instance,
    # 补全年份过滤，避免不同年份同月份的数据被混入统计
    created_at__year=datetime.now().year,
    created_at__month=datetime.now().month
).values('created_at__day').annotate(
    # 用主键计数效率更高，不会受字段空值影响
    clicks=Count('id'),
    # distinct=True 自动去重同组内重复值，delimiter 指定拼接分隔符
    browsers=StringAgg('browser', delimiter=', ', distinct=True),
    platforms=StringAgg('platform', delimiter=', ', distinct=True)
)

执行后返回的结果结构和预期完全一致。

MySQL 环境 #

MySQL 没有内置在Django核心包中的字符串聚合函数，可以通过自定义Func实现，不需要额外安装第三方依赖：

from django.db.models import Count, Func
from datetime import datetime

# 自定义MySQL GROUP_CONCAT聚合
class GroupConcat(Func):
    function = 'GROUP_CONCAT'
    template = '%(function)s(DISTINCT %(expressions)s SEPARATOR "%(separator)s")'
    def __init__(self, expression, separator=', ', **extra):
        super().__init__(expression, separator=separator, **extra)

queryset = Click.objects.filter(
    url=url_instance,
    created_at__year=datetime.now().year,
    created_at__month=datetime.now().month
).values('created_at__day').annotate(
    clicks=Count('id'),
    browsers=GroupConcat('browser', separator=', '),
    platforms=GroupConcat('platform', separator=', ')
)

注意事项 #

• 必须补充created_at__year过滤条件：仅过滤月份会把历史上所有同月份的数据全部统计进来，不符合当月统计的业务需求
• 聚合时添加去重逻辑：避免同组内重复的浏览器、平台值被多次拼接，出现类似Chrome, Chrome, Firefox的冗余结果
• 优先在数据库层完成聚合计算：相比查询出原始数据后在Python内存中做分组拼接，数据库层聚合的性能高几个数量级

内容的提问来源于stack exchange，提问作者André Zucchetti Dalla Costa