Django在日期范围内的聚合

Question

我在这里潜水学习了一段时间。现在我遇到了一个问题，似乎没有简单的解决办法。为了学习Django，我正在构建一个应用程序，主要是用来跟踪已预订的物品。

我想做的是，展示在选定年份的每个月里，一个物品被预订了多少天。

我有以下模型：

Asset(Model)

BookedAsset(Model):
 asset = models.ForeignKey(Asset)
 startdate = models.DateField()
 enddate = models.DateField()

所以有以下的记录：

asset 1, 2010-02-11, 2010-02-13
asset 2, 2010-03-12, 2010-03-14
asset 1, 2010-04-30, 2010-05-01

我希望得到以下的结果：

asset 1    asset 2
-------    -------
Jan = 0    Jan = 0
Feb = 2    Feb = 0
Mar = 0    Mar = 2
Apr = 1    Apr = 0
May = 1    May = 0
Jun = 0    Jun = 0
Jul = 0    Jul = 0
Aug = 0    Aug = 0
Sep = 0    Sep = 0
Oct = 0    Oct = 0
Nov = 0    Nov = 0
Dec = 0    Dec = 0

我知道我需要先获取一个日期范围内的天数（并且要注意这些天是否跨越当前月份到下一个月份），然后对这些天数进行汇总。我就是不知道怎么在Django中优雅地做到这一点。

任何帮助（或者指引我正确方向的提示）都非常感谢。

Answer 1

我不会选择上面提到的任何一个答案（虽然我喜欢写一些自定义的SQL，但我已经有至少5年没这么做了），而且你的模型简单到可以在关系型数据库中正常工作。

你需要找的答案是django中的annotate()函数，它属于聚合功能： http://docs.djangoproject.com/en/dev/topics/db/aggregation/ 你只需要知道如何从日期中提取月份，这在查询集的文档中有解释： http://docs.djangoproject.com/en/dev/ref/models/querysets/#month

这里有一个粗略的例子，展示如何为一个资产获取这个信息：

BookedAsset.objects.filter(asset=asset).annotate(month_count=Count('startdate__month')).order_by('startdate__month')

（显然这个例子是错的，但我懒得重建你的结构来给你一个完全正确的语句，你可以自己动手试试，并阅读文档）

你甚至可以通过使用文档中的连接语法，一次性为所有资产获取这些信息。

Answer 2

我想不出你现在的模型结构怎么能做到这一点。

这个需求比较复杂，不管你怎么解决，可能都需要写很多自定义的SQL语句。不过，作为一个开始，你可能需要考虑改变一下你的结构，创建一个叫做BookedAssetDay的表，这个表可以单独表示每一天的预订情况。

class BookedAsset(models.Model):
    asset = models.ForeignKey(Asset)
    day = models.DateField()

然后查询的样子大概是这样的：

BookedAsset.objects.extra(
    select={'month': 'MONTH(day)'}
).values('asset', 'month').annotate(Count('bookedasset__month'))

Answer 3

最后我选择了一个自定义查询来解决我的问题：

cursor = connection.cursor()
cursor.execute("""select to_char(allmonths.yeardate::date, 'YYYY/MM/DD') as monthdate,
                    COUNT("day") as bookings
                    from (
                    select distinct date_trunc('month', (date %s - offs)) as yeardate 
                    from generate_series(0,365,28) as offs
                    ) as allmonths
                    left join bookings_bookedassetday 
                    on EXTRACT(MONTH from "day")=EXTRACT(MONTH from yeardate) 
                    and asset_id=%s and EXTRACT(YEAR from "day") = %s
                    group by allmonths.yeardate
                    order by allmonths.yeardate asc""", [year+'-12-31', id, year])

query = cursor.fetchall()

也许这不是最符合Django风格的方法，但我觉得这样做比用纯Django来搞定要简单得多 :|

如果有人有更好的办法，我非常乐意听听 :)