django - 使用 Django 每小时对行进行分组

Question

我一直在尝试使用 DateTimeField 将表的结果分组为 Hourly 格式。

SQL:

SELECT strftime('%H', created_on), count(*)
FROM users_test
GROUP BY strftime('%H', created_on);

此查询工作正常，但相应的 Django 查询不工作。

我尝试过的 Django 查询:

Test.objects.extra({'hour': 'strftime("%%H", created_on)'}).values('hour').annotate(count=Count('id'))
# SELECT (strftime("%H", created_on)) AS "hour", COUNT("users_test"."id") AS "count" FROM "users_test" GROUP BY (strftime("%H", created_on)), "users_test"."created_on" ORDER BY "users_test"."created_on" DESC

它通过“users_test”、“created_on”添加了额外的组，我猜这会给出不正确的结果。

如果有人能向我解释这一点并提供解决方案，那就太好了。

环境:

• python 3
• Django 1.8.1

提前致谢

引用文献(可能重复)(但没有帮助):

• Grouping Django model entries by day using its datetime field
• Django - Group By with Date part alone
• Django aggregate on .extra values

Answer 1

要修复它，请将 order_by() 附加到查询链。这将覆盖模型元默认排序。像这样:

Test
.objects
.extra({'hour': 'strftime("%%H", created_on)'})
.order_by()                                        #<------ here
.values('hour')
.annotate(count=Count('id'))

在我的环境中(还有 Postgres):

>>> print ( Material
         .objects
         .extra({'hour': 'strftime("%%H", data_creacio)'})
         .order_by()
         .values('hour')
         .annotate(count=Count('id'))
         .query )

  SELECT (strftime("%H", data_creacio)) AS "hour", 
         COUNT("material_material"."id") AS "count" 
    FROM "material_material" 
GROUP BY (strftime("%H", data_creacio))

了解更多信息 order_by django docs :

If you don’t want any ordering to be applied to a query, not even the default ordering, call order_by() with no parameters.

旁注:使用 extra() 可能会引入 SQL injection vulnerability到你的代码。谨慎使用它并转义用户可以引入的任何参数。 Compare with docs :

Warning

You should be very careful whenever you use extra(). Every time you use it, you should escape any parameters that the user can control by using params in order to protect against SQL injection attacks . Please read more about SQL injection protection.