使用Django每小时分组一行 [英] Hourly grouping of rows using Django
问题描述
我一直在尝试使用 DateTimeField
将表格的结果分组为小时格式。
I have been trying to group the results of table into Hourly format using DateTimeField
.
SELECT strftime('%H', created_on), count(*)
FROM users_test
GROUP BY strftime('%H', created_on);
此查询工作正常,但相应的Django查询不会。
This query works fine, but the corresponding Django query does not.
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,其中猜测是给出不正确的结果。
如果有人可以解释我,并提供一个解决方案,这将是巨大的。
It would be great if anyone can explain me this and provide a solution as well.
- Python 3
- Django 1.8.1
感谢提前
- 使用其datetime字段按日分组Django模型条目
- Django - 分组与日期部分单独
- <一个href =https://stackoverflow.com/questions/13182293/django-aggregate-on-extra-values> .extra值上的Django聚合
- Grouping Django model entries by day using its datetime field
- Django - Group By with Date part alone
- Django aggregate on .extra values
推荐答案
要修复它,请将 order_by()
追加到查询链中。这将覆盖Meta默认排序。这样:
To fix it, append order_by()
to query chain. This will override model Meta default ordering. Like this:
Test
.objects
.extra({'hour': 'strftime("%%H", created_on)'})
.order_by() #<------ here
.values('hour')
.annotate(count=Count('id'))
在我的环境中(Postgres):
In my environment ( Postgres also ):
>>> 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 :
如果你不想要任何订单应用于查询,甚至没有默认的排序,调用order_by(),没有参数。
If you don’t want any ordering to be applied to a query, not even the default ordering, call order_by() with no parameters.
这篇关于使用Django每小时分组一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!