Django分组查询annotate可能遇到的坑
Django ORM的分组查询一般是values(或values_list)和annotate联用,values里面声明以什么字段分组,annotate 执行分组,这其中可能会有个坑
model大概长这样
class AdsEvent(models.Model):
id = models.CharField(primary_key=True, max_length=32)
dstip = models.GenericIPAddressField()
attack_type = models.CharField(max_length=20, blank=True, null=True)
start_time = models.DateTimeField(blank=True, null=True)
end_time = models.DateTimeField(blank=True, null=True)
total_in_bits = models.BigIntegerField(blank=True, null=True)
total_in_packets = models.BigIntegerField(blank=True, null=True)
total_drop_bits = models.BigIntegerField(blank=True, null=True)
total_drop_packets = models.BigIntegerField(blank=True, null=True)
attack_port = models.BigIntegerField(blank=True, null=True)
objects = ScrubbingEnvironmentADSEventManager()
class Meta:
managed = False
db_table = 't_ads_event'
ordering = ('-end_time', '-start_time')
功能需求是要返回每种攻击类型的攻击次数,那我就根据攻击类型分组,统计个数
ret = queryset.values("attack_type").annotate(value=Count('id'))
嗯,看似没问题
但是返回的结果根本没聚合
why???
可以把ORM转成sql打印出来看看
print(queryset.values("attack_type").annotate(value=Count('id')).query)
对应的sql:
SELECT "t_ads_event"."attack_type", COUNT("t_ads_event"."id") AS "value"
FROM "t_ads_event"
GROUP BY "t_ads_event"."attack_type", "t_ads_event"."end_time", "t_ads_event"."start_time"
ORDER BY "t_ads_event"."end_time" DESC, "t_ads_event"."start_time" DESC
等等,为啥分组条件里会有开始时间和结束时间???
看下model的meta类,原来ordering被指定成默认时间降序
class Meta:
managed = False
db_table = 't_ads_event'
ordering = ('-end_time', '-start_time')
解决方法:查询时把排序参数改成用来分组的参数
ret = queryset.order_by('attack_type').values("attack_type").annotate(value=Count('id'))
对应的sql:
SELECT "t_ads_event"."attack_type", COUNT("t_ads_event"."id") AS "value" FROM "t_ads_event"
GROUP BY "t_ads_event"."attack_type"
ORDER BY "t_ads_event"."attack_type" ASC
ok了
但还存在个问题,如果我的需求是要按照攻击次数从大到小的顺序返回咋办?
那就加个排序喽
ret = queryset.order_by('attack_type').values("attack_type").annotate(value=Count('id')).order_by('-value')
或者这样
ret = queryset.values("attack_type").annotate(value=Count('id')).order_by('-value')
对应sql都是:
SELECT "t_ads_event"."attack_type", COUNT("t_ads_event"."id") AS "value"
FROM "t_ads_event"
GROUP BY "t_ads_event"."attack_type"
ORDER BY "value" DESC
返回结果:
解决!
结论:如果model的meta类中的ordering有被指定,那在annotate分组查询时要多注意