如何在Django ORM中使用annotate和aggregate执行此GROUP BY查询

Question

我还不太明白怎么把 GROUP BY 和 HAVING 转换成 Django 的 QuerySet.annotate 和 QuerySet.aggregate。我正在尝试把这个 SQL 查询转换成 ORM 的写法。

SELECT EXTRACT(year FROM pub_date) as year, EXTRACT(month from pub_date) as month, COUNT(*) as article_count FROM articles_article GROUP BY year,month;

这个查询的输出是：

[(2008.0, 10.0, 1L), # year, month, number of articles
(2009.0, 2.0, 1L),
(2009.0, 7.0, 1L),
(2008.0, 5.0, 3L),
(2008.0, 9.0, 1L),
(2008.0, 7.0, 1L),
(2009.0, 5.0, 1L),
(2008.0, 8.0, 1L),
(2009.0, 12.0, 2L),
(2009.0, 3.0, 1L),
(2007.0, 12.0, 1L),
(2008.0, 6.0, 1L),
(2009.0, 4.0, 2L),
(2008.0, 3.0, 1L)]

我的 Django 模型是：

class Article(models.Model):
    title = models.CharField(max_length=150, verbose_name=_("title"))
    # ... more 
    pub_date = models.DateTimeField(verbose_name=_('publishing date'))

这个项目需要在几种不同的数据库系统上运行，所以我尽量不使用纯 SQL。

Answer 1

你可以使用日期来提取数据：http://docs.djangoproject.com/en/dev/ref/models/querysets/#dates-field-kind-order-asc

Answer 2

我觉得如果想在一个查询中完成这个操作，你可能需要把月份和年份分开存储为不同的字段...

Article.objects.values('pub_date').annotate(article_count=Count('title'))

这样的话就可以通过 group by 来按出版日期进行分组。但我想不出有什么办法可以在这里直接使用 extract 函数。

如果你的模型是这样的：

class Article(models.Model):
    title = models.CharField(max_length=150, verbose_name=_("title"))
    # ... more 
    pub_date = models.DateTimeField(verbose_name=_('publishing date'))
    pub_year = models.IntegerField()
    pub_month = models.IntegerField()

那么你可以这样做：

Article.objects.values('pub_year', 'pub_month').annotate(article_count=Count('title'))

如果你打算这样做，我建议你让 pub_year 和 pub_month 自动填充，可以通过重写文章的 save() 方法，从 pub_date 中提取这些值。

---

编辑：

一种方法是使用 extra；不过这样做的话，你的数据库就不能独立了...

models.Issue.objects.extra(select={'year': "EXTRACT(year FROM pub_date)", 'month': "EXTRACT(month from pub_date)"}).values('year', 'month').annotate(Count('title'))

虽然这样做是可行的，我觉得（没测试过），但如果你将来更换数据库服务器，就需要修改 extra 字段。例如，在 SQL Server 中，你需要用 year(pub_date) 来代替 extract(year from pub_date)...

如果你能设计一个自定义的模型管理器，并明确标记需要这样的数据库引擎相关的修改，那可能就没那么糟糕了。