如何在Django中根据日期时间字段按年、月、日进行分组计数？

Question

我有一个表格，里面记录了文件的信息，还有一个日期时间的字段。我想做一个报告，统计每年有多少文件，按每年和每个月统计有多少文件，以及按每年、每个月和每天统计有多少文件。我只想要那些数量大于0的记录。最好是用Django的ORM来实现，如果不行的话，也希望能用一些在PostgreSQL和SQLite上都能运行的SQL语句。

这个数据库里的记录数量可能非常庞大，所以我尝试用代码来实现这个功能（而不是直接用SQL，或者通过ORM间接用SQL）时，效果不好。如果我能让它工作，我觉得也根本无法扩展。

非常感谢任何提示或解决方案。

Answer 1

我犯了个错误 - date() 这个函数只适用于 MySql：

你可以试试这个（适用于 SQLite）：

tbl = MyTable.objects.filter()                                       
tbl = tbl.extra(select={'count':'count(strftime('%Y-%m-%d', timestamp))', 'my_date':'strftime('%Y-%m-%d', timestamp))'}
tbl = tbl.values('count', 'my_date')
tbl.query.group_by = ['strftime('%Y-%m-%d', timestamp)']

如果你想要显示日期中的天和月，可以把 '%Y-%m-%d' 替换成其他日期格式的字符串。

这是针对 MySQL 的（以防有人需要这个）

tbl = MyTable.objects.filter()                                       
tbl = tbl.extra(select={'count':'count(date(timestamp))', 'my_date':'date(timestamp)'}
tbl = tbl.values('count', 'my_date')
tbl.query.group_by = ['date(timestamp)']

这个方法适用于年份。

Answer 2

我通常使用Oracle数据库，不过我快速搜索了一下，发现这个方法在Postgres数据库中也可以用。对于分钟的计算，你可以这样做：

select to_char(yourtimestamp,'yyyymmdd hh24:mi'), count(*)
from yourtable
group by to_char(yourtimestamp,'yyyymmdd hh24:mi') 
order by to_char(yourtimestamp,'yyyymmdd hh24:mi') DESC;

这样做可以一直计算到年份：

select to_char(yourtimestamp,'yyyy'), count(*)
from yourtable
group by to_char(yourtimestamp,'yyyy') 
order by to_char(yourtimestamp,'yyyy') DESC;

这样你只会得到有数据的年份。我觉得这正是你想要的结果。

补充一下：你需要在“yourtimestamp”上建立一个索引，否则如果数据行很多，性能会很差。