Django的聚合查询

最近几天，碰到了这样一个需求，数据模型大概是这样的：

    服务器：(Host)

    id，(id)

    ip地址，(ip)

    创建时间，(create_time)

    ......

其中要求是，通过Django的内部查询得到每天服务器创建的个数，写成SQL语句大概是这样：

select create_time,count(create_time) from Host group by create_time

+-------------+--------------------+
| create_time | count(create_time) |
+-------------+--------------------+
| 2015-02-05  |                  1 |
| 2015-03-20  |                  2 |
| 2015-03-21  |                  1 |
| 2015-03-22  |                  2 |
| 2015-03-23  |                  1 |
| 2015-03-24  |                  1 |
| 2015-03-25  |                  3 |
| 2015-03-26  |                  2 |
+-------------+--------------------+

关键在于关键字group by和聚合函数count,但是如何通过Django的内置函数获取到这个结果集呢？

很巧的是，我在StackOverFlow上看到了类似的一条问题：

http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django

这位作者也碰到这种情况：

100 down vote favorite 44

I query a model, Members.objects.all() and it returns say Eric, Salesman, X-Shop Freddie, Manager, X2-Shop Teddy, Salesman, X2-Shop Sean, Manager, X2-Shop What i want is, to know the best Django way to firea group_by query to my db, as like, Members.objects.all().group_by('designation') Which doesn't work of course.I know we can do some tricks on "django/db/models/query.py", but i am just curious to know how to do it without patching. Thanks

 

其中一位老兄回答到：

If you mean to do aggregation and are using Django 1.1 (currently in alpha 1), you can use the newaggregation features of the ORM:

from django.db.models import Count
Members.objects.values('designation').annotate(dcount=Count('designation'))

This results in a query similar to

SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation

and the output would be of the form

[{'designation': 'Salesman', 'dcount': 2}, 
 {'designation': 'Manager', 'dcount': 

这段英语对话比较简单，我就不多解释了

受此启发，我也尝试着写出了类似查询语句

 result =  self.result_list.values('create_time').annotate(count=Count('create_time'))
其中self.result_list就是服务器Host的模型实体集

执行之后，输出result，对了！这正式我想要的结果。

[{'count': 1, 'create_time': datetime.date(2015, 2, 5)}, {'count': 2, 'create_time': datetime.date(2015, 3, 20)}, {'count': 1, 'create_time': datetime.date(2015, 3, 21)}, {'count': 2, 'create_time': datetime.date(2015, 3, 22)}, {'count': 1, 'create_time': datetime.date(2015, 3, 23)}, {'count': 1, 'create_time': datetime.date(2015, 3, 24)}, {'count': 3, 'create_time': datetime.date(2015, 3, 25)}, {'count': 2, 'create_time': datetime.date(2015, 3, 26)}]