在Django中按日期分组

Question

我试图实现以下SQL查询的结果

SELECT 
UNIX_TIMESTAMP(DATE((FROM_UNIXTIME(`timestamp`)))) AS `x`, 
COUNT(`timestamp`) as y 
FROM somedb.events
WHERE user_id=3 AND 
`timestamp` > 1612117800 AND 
`timestamp` < 1614450600 AND
`kind`='food'
GROUP BY `x`
ORDER BY `x` desc;

使用Django ORM。预期的输出:

[
{
"x": 1613759400,
"y": 2
},
{
"x": 1612463400,
"y": 1
}
]

这是我到目前为止尝试的:

queryset = events.objects.filter(
user=request.user,
timestamp__range=dates
)

第一种方法:

result = queryset.annotate(
trunc_date_timestamp=Func(
Value(
TruncDate(
Func(
F('timestamp'),
function='FROM_UNIXTIME',
)
)
),
function='UNIX_TIMESTAMP',
output_field=models.IntegerField()
)
).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

输出如下:

[
{
"x": 0,
"y": 3
}
]

第二种方法:

result = queryset.annotate(
trunc_date_timestamp=Func(
Func(
F('timestamp'),
function='FROM_UNIXTIME',
output_field=models.DateField()
),
function='UNIX_TIMESTAMP'
)
).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

产生输出:

[
{
"x": 1613831760,
"y": 1
},
{
"x": 1613810160,
"y": 1
},
{
"x": 1612520520,
"y": 1
}
]

Answer 1

最后我让它工作了。我在两种方法上都犯了小错误。

第一种方法:

result = ueryset.annotate(
trunc_date_timestamp=Func(
TruncDate(
Func(
F('timestamp'),
function='FROM_UNIXTIME',
output_field=models.DateField() # <<--- This is must
)
),
function='UNIX_TIMESTAMP',
)
).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

移除Value()API，将output_field=models.DateTimeField()添加到Func()内部API。output_field将确保返回的字段类型的内在Func()API必须models.DateField()TruncDate将应用。

第二种方法:

queryset = queryset.annotate(
trunc_date_timestamp=Func(
Cast(
Func(
F('timestamp'),
function='FROM_UNIXTIME',
),
output_field=models.DateField()
),
function='UNIX_TIMESTAMP'
)
).values(x=F('trunc_date_timestamp')).annotate(y=models.Count('x')).order_by('-x')

一开始我以为

Func(
F('timestamp'),
function='FROM_UNIXTIME',
output_field=models.DateField()
)

这将返回一个models.DateField()类型的字段，但我不知道为什么它没有和失败的工作!!所以我使用Cast()方法，并将返回的表达式转换为models.DateField()，以使其工作。

虽然这个解决方案有效，但我强烈认为第二种方法的原始代码也应该有效，因为如果我正确理解Func()表达式，那么

Func(
F('timestamp'),
function='FROM_UNIXTIME',
output_field=models.DateField()
) 

和

Cast(
Func(
F('timestamp'),
function='FROM_UNIXTIME'
),
output_field=models.DateField()
)

应该产生相同的结果。