Django 向 GROUP BY 子句添加一个额外的字段

Question

这是一个查询:

speed_by_computer = ChartPoint.objects.filter(
    date__gte=start,
    date__lt=end,
).values('computer_id') \
 .annotate(max_speed=Max('speed'))

这是此查询生成的 SQL 查询:

SELECT "computer_id", MAX("speed") AS "max_speed" 
FROM "api_chartpoint" 
WHERE (
    "date" >= 2018-05-17 08:00:00+00:00 AND 
    "date" < 2018-05-17 09:00:00+00:00
) 
GROUP BY "computer_id", "date" 
ORDER BY "date" DESC

请注意，GROUP BY 子句中有一个意外的date。它为什么在这里？这是我的预期:

SELECT "computer_id", MAX("speed") AS "max_speed" 
FROM "api_chartpoint" 
WHERE (
    "date" >= 2018-05-17 08:00:00+00:00 AND
    "date" < 2018-05-17 09:00:00+00:00
) 
GROUP BY "computer_id"

Answer 1

问题出在默认顺序上。它在 Meta 中指定:

class ChartPoint(models.Model)
    ...
    class Meta:
       ordering = ['-date']

删除排序，date 将不会添加到 GROUP BY:

speed_by_computer = ChartPoint.objects.filter(
    date__gte=start,
    date__lt=end,
).values('computer_id') \
 .annotate(hs_avg=Max('speed'))
 .order_by()