Django中QuerySet的annotate方法 可以实现分组（group by)后再聚合，返回一个queryset。
但是在使用时得到的结果总是不对，看了对应的sql语句也不正确。后来终于在一篇博客上找到了答案，现在将使用annotate遇到问题的过程记录下。
使用annotate实现对suspects字段分组统计
先看下表中的数据
image.png
>>> from kindaichi.models import Case
>>> from django.db.models import Count
第一次尝试
>>> q = Case.objects.all().annotate(Count("suspects"))
>>> print(q.query)
SELECT `kindaichi_case`.`id`, `kindaichi_case`.`created`, `kindaichi
_case`.`title`, `kindaichi_case`.`location`, `kindaichi_case`.`suspe
cts`, `kindaichi_case`.`episodes`, `kindaichi_case`.`owner_id`, COUN
T(`kindaichi_case`.`suspects`) AS `suspects__count` FROM `kindaichi_
case` GROUP BY `kindaichi_case`.`id` ORDER BY `kindaichi_case`.`crea
ted` ASC
image.png
可以看到默认按照id进行分组，并且按照默认的creaed排序,得到的k值都是1，和我希望的group by suspects不符。
第二次尝试
>>> a = Case.objects.values_list("suspects").annotate(Count("suspects"))
SELECT `kindaichi_case`.`suspects`,
COUNT(`kindaichi_case`.`suspects`) AS `episodes__count` FROM
`kindaichi_case` GROUP BY `kindaichi_case`.`suspects`,
`kindaichi_case`.`created` ORDER BY `kindaichi_case`.`created` ASC
image.png
发现在group by里还是多了个created，在一篇博客下看到是因为模型META类中的默认排序被添加到被添加到查询中，然后将其添加到group by子句中，因为SQL需要这样。查看了自己的modle，确实是这样。
image.png
解决方法是删除默认排序或者添加空order_by重置顺序，我选择第二种。
第三次尝试
>>> a = Case.objects.values_list("episodes").annotate(Count("episodes")).order_by()
SELECT `kindaichi_case`.`suspects`,
COUNT(`kindaichi_case`.`suspects`) AS `episodes__count` FROM
`kindaichi_case` GROUP BY `kindaichi_case`.`suspects` ORDER BY NULL
image.png
终于实现了预期的效果。
总结需要注意的情况：
- 不加values或者values_list会默认按id分组
>>> a = Case.objects.all().annotate(Count("suspects")).order_by()
SELECT `kindaichi_case`.`id`, `kindaichi_case`.`created`, `kindaichi_case`.`title`, `kindaichi_case`.`location`,
`kindaichi_case`.`suspects`, `kindaichi_case`.`episodes`,`kindaichi_case`.`owner_id`, COUNT(`kindaichi_case`.`suspects`) AS `k`
FROM `kindaichi_case` GROUP BY `kindaichi_case.`id` ORDER BY NULL
image.png
- values_list中添加多个字段会按照多个字段and分组，从下面查询结果
suspects为8的记录分成了四组也可以看出这点。
>>> a = Case.objects.values_list("suspects","episodes").annotate(Count("suspects")).order_by("created")
SELECT `kindaichi_case`.`suspects`, `kindaichi_case`.`episodes`,
COUNT(`kindaichi_case`.`suspects`) AS `suspects__count`
FROM `kindaichi_case`
GROUP BY
`kindaichi_case`.`suspects`,`kindaichi_case`.`episodes`,`kindaichi_case`.`created`
ORDER BY `kindaichi_case`.`created` ASC
image.png
并且如果用order_by("created")而不是order_by()，created又会被添加到group_by中，这里不限于created这个在META中默认排序的字段，只要在order_by中添加，就会在group_by中
比如下面用不在META类中的location字段
>>> a = Case.objects.values_list("suspects","episodes").annotate(Count("suspects")).order_by("location")
SELECT `kindaichi_case`.`suspects`, `kindaichi_case`.`episodes`, COUNT(`kindaichi_case`.`suspects`) AS `suspects__count` FROM `kindaichi_case` G
ROUP BY `kindaichi_case`.`suspects`, `kindaichi_case`.`episodes`, `kindaichi_case`.`location` ORDER BY `kindaichi_case`.`location` ASC
image.png
group_by依然多出了一个字段location。
从几次结果看order_by最好要么为空，要么是values中的字段。
