如何使用Django ORM实现基于Unix时间戳字段的按日期分组统计

错误原因排查 #

1. 第一种方案错误：你使用Value()包裹了TruncDate表达式，Value是Django用来定义常量值的方法，不能用来包装动态字段表达式，导致所有行的截断时间戳被解析为同一个常量值，最终全部被统计到x=0的分组中。
2. 第二种方案错误：你仅为FROM_UNIXTIME的结果指定了output_field=models.DateField()，但这个声明不会在SQL层面执行DATE()日期截断逻辑，实际返回的还是带时分秒的完整时间戳，所以每条数据的x值都不相同，每个分组的统计结果y都为1。
3. 另外你的基础过滤逻辑遗漏了原SQL中的kind='food'条件，也会导致统计结果不符合预期。

正确实现代码 #

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

# 先补全过滤条件
queryset = events.objects.filter(
    user=request.user,
    timestamp__range=dates,
    kind='food'
)

# 完全对齐原SQL逻辑实现分组统计
result = queryset.annotate(
    x=Func(
        # 第二层：将转好的datetime截断为日期
        Func(
            # 第一层：将整型时间戳转为datetime格式
            Func(F('timestamp'), function='FROM_UNIXTIME'),
            function='DATE'
        ),
        # 第三层：将截断后的日期转回Unix时间戳
        function='UNIX_TIMESTAMP',
        output_field=models.IntegerField()
    )
).values('x').annotate(
    y=Count('timestamp')
).order_by('-x')

该写法生成的SQL和你给出的目标SQL完全一致，执行后就能得到预期的输出结果。

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