django orm aggregate()和annotate() 以及 CASE WHEN的使用 【终于理解了】

阅读目录

• 一、django orm aggregate()和annotate()
• 二、values()与values_list()
• 三、orm中利用annotate进行group by 详解
• 四、django-ORM之聚合函数和CASE WHEN的使用

回到顶部

一、django orm aggregate()和annotate()

aggregate 和 annotate 用于查询查询结果集的，区别在于aggregate 是全部结果集的查询，annotate则是分组查询的。

一般会用到功能函数Avg、Max、Min、Count、Sum。

1.aggregate(*args,**kwargs) 聚合函数

通过对QuerySet进行计算，返回一个聚合值的字典。aggregate()中每一个参数都指定一个包含在字典中的返回值。即在查询集上生成聚合。
聚合函数 aggregate() 是 QuerySet 的一个终止子句， 生成的一个汇总值，相当于 count()。。返回结果类型为字典Dict。

示例：

from django.db.models import Avg,Sum,Max,Min
#求书籍的平均价
ret=models.Book.objects.all().aggregate(Avg('price'))
#{'price__avg': 145.23076923076923} 字典

2.annotate(*args,**kwargs) 分组函数

annotate()为每一个QuerySet在指定属性上生成汇总值，相当于GROUP BY。返回结果类型QuerySet。

注意：

annotate 里面放聚合函数。

• values 或者 values_list 放在 annotate 前面：values 或者 values_list 是声明以什么字段分组，annotate 执行分组。
• values 或者 values_list 放在annotate后面： 如果annotate的前面没有values() ，则默认表示直接以当前表的pk执行分组，values 或者 values_list 表示查询哪些字段， 并且要将 annotate 里的聚合函数起别名，在 values 或者 values_list 里写其别名。

from django.db.models import Avg,Sum,Max,Min
#查看每一位作者出过的书中最贵的一本（按作者名分组 values() 然后annotate 分别取每人出过的书价格最高的）
ret=models.Book.objects.values('author__name').annotate(Max('price'))
queryset 列表
  # < QuerySet[
  # {'author__name': '吴承恩', 'price__max': Decimal('234.000')},
  # {'author__name': '吕不韦','price__max': Decimal('234.000')},
  # {'author__name': '姜子牙', 'price__max': Decimal('123.000')},
  # {'author__name': '亚微',price__max': Decimal('123.000')},
  # {'author__name': '伯夷 ', 'price__max': Decimal('2010.000')},
  # {'author__name': '叔齐','price__max': Decimal('200.000')},
  # {'author__name': '陈涛', 'price__max': Decimal('234.000')},
  # {'author__name': '高路川', price__max': Decimal('234.000')}
  # ] >

回到顶部

二、values()与values_list()

model示例：

from django.db import models
 
class Blog(models.Model):
    name = models.CharField(max_length=100)
    tagline = models.TextField()
 
    def __str__(self):              # __unicode__ on Python 2
        return self.name
 
class Author(models.Model):
    name = models.CharField(max_length=50)
    email = models.EmailField()
 
    def __str__(self):              # __unicode__ on Python 2
        return self.name
 
class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(max_length=255)
    body_text = models.TextField()
    pub_date = models.DateField()
    mod_date = models.DateField()
    authors = models.ManyToManyField(Author)
    n_comments = models.IntegerField()
    n_pingbacks = models.IntegerField()
    rating = models.IntegerField()
 
    def __str__(self):              # __unicode__ on Python 2
        return self.headline
1234567891011121314151617181920212223242526272829

1.values()

values(*fields)

• （1） 返回一个ValuesQuerySet —— QuerySet 的一个子类，迭代时返回字典而不是模型实例对象。每个字典表示一个对象，键对应于模型对象的属性名称。

# This list contains a Blog object.
>>> Blog.objects.filter(name__startswith='Beatles')
[<Blog: Beatles Blog>]
# This list contains a dictionary.
>>> Blog.objects.filter(name__startswith='Beatles').values()
[{'id':
1234567

• （2）values() 接收可选的位置参数*fields，它指定SELECT 应该限制哪些字段。如果指定字段，每个字典将只包含指定的字段的键/值。如果没有指定字段，每个字典将包含数据库表中所有字段的键和值。

>>> Blog.objects.values()
[{'id': 1, 'name': 'Beatles Blog', 'tagline': 'All the latest Beatles news.'}],
>>> Blog.objects.values('id', 'name')
[{'id': 1, 'name': 'Beatles Blog'}]
1234

• （3）如果你有一个字段foo 是一个ForeignKey，默认的values() 调用返回的字典将有一个叫做foo_id 的键，因为这是保存实际的值的那个隐藏的模型属性的名称（foo 属性引用关联的模型）。当你调用values() 并传递字段的名称，传递foo 或foo_id 都可以，得到的结果是相同的（字典的键会与你传递的字段名匹配）。

>>> Entry.objects.values()
[{'blog_id': 1, 'headline': 'First Entry', ...}, ...]
>>> Entry.objects.values('blog')
[{'blog': 1}, ...]
>>> Entry.objects.values('blog_id')
[{'blog_id': 1}, ...]
12345678

• （4）ValuesQuerySet 用于你知道你只需要字段的一小部分，而不需要用到模型实例对象的函数。只选择用到的字段当然更高效。
• （5）最后，要注意ValuesQuerySet 是QuerySet 的子类，它实现了大部分相同的方法。你可以对它调用filter()、order_by() 等等。这表示下面的两个调用完全相同：

Blog.objects.values().order_by('id')
Blog.objects.order_by('id').values()
12

2.values_list()

values_list(*fields, flat=False)

• （1）与values() 类似，只是在迭代时返回的是元组而不是字典。每个元组包含传递给values_list() 调用的字段的值 —— 所以第一个元素为第一个字段，以此类推。例如：

>>> Entry.objects.values_list('id', 'headline')
[(1, 'First entry'), ...]
12

• （2）如果只传递一个字段，你还可以传递flat 参数。如果为True，它表示返回的结果为单个值而不是元组。如果有多个字段，传递flat 将发生错误。
  一个例子会让它们的区别更加清晰：

>>> Entry.objects.values_list('id').order_by('id')
[(1,), (2,), (3,), ...]
>>> Entry.objects.values_list('id', flat=True).order_by('id')
[1, 2, 3, ...]
12345

• （3）如果你不传递任何值给values_list()，它将按照字段在模型中定义的顺序, 返回模型中的所有字段。
  注意，这个方法返回ValuesListQuerySet。这个类的行为类似列表。大部分时候它足够用了，但是如果你需要一个真实的Python 列表对象，可以对它调用list()、dict()，这将会对查询集求值。

shop_id_list = list(Subscribe.objects.filter(create_time__gte=datetime.datetime(2017, 2, 9)).values_list('shop_id', flat=True))
 
dept_dict = dict(Department.objects.all().values_list('code', 'id'))
123

总结：

• 1.values方法可以获取number字段的字典列表。
• 2.values_list可以获取number的元组列表。
• 3.values_list方法加个参数flat=True可以获取number的值列表。

回到顶部

三、orm中利用annotate进行group by 详解

用法

之前的orm的group by方法在django 1.8 中已经不能使用，需要利用annotate来实现

示例1

第一个values用来选中需要用来group by的字段（此处group by user_id），之后紧跟annotate来分组并聚合需要的字段（需要每个user_id对应的question_id的数量和catalog_id的最小值），之后再values来实际查询需要的字段（原user_id和聚合后的字段的别名）

第一个values用来指定用来group by的字段，里面必须是Count、Min等等聚合函数（例如用F("user_id")取别名是不行的），不需要最终查询的就不必聚合了

第二个values用来指定实际select的字段，只能指定annotate后的字段名（以此处为例：user_id是用来分组的字段，可以直接取，而其他字段必须聚合并使用聚合后的别名，qid和cid，假如原表还有个字段status，annotate中没有聚合此字段，所以最后value不能查询该字段）

q = PxbNCEUserQuest.objects.filter(user_id=335).values("user_id").annotate(qid=Min("question_id"), cid=Min("catalog_id")).values("user_id", "qid", "cid")
 
print q
 
print q.query
 
# 输出
[{'qid': 22, 'user_id': 335L, 'cid': 17}]
SELECT `pxb_nce_user_quest`.`user_id`, MIN(`pxb_nce_user_quest`.`question_id`) AS `qid`, MIN(`pxb_nce_user_quest`.`catalog_id`) AS `cid` FROM `pxb_nce_user_quest` WHERE `pxb_nce_user_quest`.`user_id` = 335 GROUP BY `pxb_nce_user_quest`.`user_id` ORDER BY NULL

示例2

与示例1一样，但是此处第一个annotate用来分组字段，第二个annotate用来单独别名其他字段

q = PxbNCEUserQuest.objects.filter(user_id=335).values("user_id").annotate(qid=Min("question_id"), cid=Min("catalog_id")).annotate(uid=F("user_id")).values("uid", "qid", "cid")
 
print q
print q.query
# 输出：
[{'qid': 22, 'uid': 335L, 'cid': 17}]
 
SELECT MIN(`pxb_nce_user_quest`.`question_id`) AS `qid`, MIN(`pxb_nce_user_quest`.`catalog_id`) AS `cid`, `pxb_nce_user_quest`.`user_id` AS `uid` FROM `pxb_nce_user_quest` WHERE `pxb_nce_user_quest`.`user_id` = 335 GROUP BY `pxb_nce_user_quest`.`user_id` ORDER BY NULL

举例:

SomeModel.objects.annotate(Count('somecol'))

GROUP BY: 所有字段

SomeModel.objects.values('name').annotate(Count('somecol'))

GROUP BY: name字段,聚合somecol

SomeModel.objects.annotate(Count('somecol')).values('name')

GROUP BY: 所有字段，查询name

SomeModel.objects.values('name', 'pk').annotate(Count('somecol')).values('pk')

GROUP BY: name, pk字段，查询pk字段

SomeModel.objects.values('name').annotate(Count('somecol')).values('pk')

GROUP BY: name, pk字段，查询pk字段

关联知识：

刚开始上面的查询方法可能比较难理，但是对比原生sql语句的group by方法就会发现类似原理

老版本mysql中

select a, b from t group by a会正常工作，b字段会自动取第一条，等于是隐式聚合了

新版本mysql中以上语句不能工作，因为默认启用严格模式sql_mode=ONLY_FULL_GROUP_BY，正确方法是：

select a,max(b) as b from t group by，即需要显示的聚合所有查询的字段

对比新版mysql语法会发现跟orm中查询方法很类似

回到顶部

四、django-ORM之聚合函数和CASE WHEN的使用

业务场景

有这样一个场景：两张表，文件表 和 文件内容表/query表，表结构如下，由于公司规定，没有办法使用外键，所以使用了逻辑关联（文件和query是一对多的关系）。

class File(models.Model):
    id = models.AutoField(primary_key=True, editable=False)
    file_name = models.CharField(max_length=255, db_index=True)
    create_time = models.DateTimeField(auto_now_add=True)
    status = models.IntegerField(default=1, db_index=True)  # 0：已经标完，页面不可见  1：未标完，页面可见
    hash = models.CharField(max_length=255)
    in_mysql = models.IntegerField(default=0, db_index=True)  # 0：默认是0，没有同步。后期同步后，变为1
    count = models.IntegerField()  # 文件中query的条数
 
    def __unicode__(self):
        return self.file_name
 
    class Meta:
        db_table = 'mark_file'
 
class Query(models.Model):
    id = models.AutoField(primary_key=True, editable=False)
    query = models.CharField(max_length=255)
    status = models.IntegerField(default=0)  # 0：未标注，1：白名单，2：黑名单，3：废弃，4：待扩充
    update_time = models.DateTimeField(auto_now=True)
    file_id = models.IntegerField(db_index=True)  # 根据此字段进行逻辑关联
    
    def __unicode__(self):
        return self.query
 
    class Meta:
        db_table = 'mark_query'
 
    def save(self, *args, **kwargs):
        self.update_time = datetime.datetime.now()
        super(Query, self).save(*args, **kwargs)

现在需要查询出id为 1、2、3、4、5、6的文件信息，并且计算出各个文件中对应的各个状态的query（status=0，status=3，status=4）的数量。以达到下面的效果。

file.png

简单实现，不考虑性能

如果是不考虑性能问题，可能会使用，先查出id为1、2、3、4、5、6的文件，再for循环一个个count，例如：

for file_obj in file_list:
    file_id = file_obj.id
    # 未标注
    no_recall_count = Query.objects.filter(Q(file_id=file_id) & Q(status=0)).count()
    # 已废弃
    no_use_count = Query.objects.filter(Q(file_id=file_id) & Q(status=3)).count()
    # 待扩充
    no_use_count = Query.objects.filter(Q(file_id=file_id) & Q(status=4)).count()

但是这样做是最笨，最不合理的做法。这才是6个文件，如果是100个文件，应该查询100✖3=300次数据库，才能得出最终结果。对于一个接口来说，数据库操作次数越少越好，查这么多次简直是一个天文数字。

其实可以这样做，一次查询就可以得出统计结果。(django1.10.8)

from django.db.models import Q, F, Count, When, Case
 
files_id = [1,2,3,4,5,6]
counts = []
#要用聚合函数Count，所以用annotate分组函数
files_count = Query.objects.filter(file_id__in=files_id).values('file_id').annotate(
            no_recall_count=Count(Case(When(status=0, then=0))), 
            no_use_count=Count(Case(When(status=3, then=0))),
            expand=Count(Case(When(status=4, then=0))))
 
for files_count_obj in files_count:  
    counts.append({
        "file_id": files_count_obj.get('file_id'),
        "no_recall_count": files_count_obj.get('no_recall_count'),
        "no_use_count": files_count_obj.get('no_use_count'),
        "expand_count": files_count_obj.get('expand'),
        })

sql语句就是

SELECT `mark_query`.`file_id`, 
COUNT(CASE WHEN `mark_query`.`status` = 0 THEN 0 ELSE NULL END) AS `no_recall_count`, 
COUNT(CASE WHEN `mark_query`.`status` = 3 THEN 0 ELSE NULL END) AS `no_use_count`, 
COUNT(CASE WHEN `mark_query`.`status` = 4 THEN 0 ELSE NULL END) AS `expand` 
FROM `mark_query` WHERE `mark_query`.`file_id` IN (1, 2, 3, 4, 5, 6) 
GROUP BY `mark_query`.`file_id`;

如果是django2.0以上，还可以这样（没有验证是否可行）
https://www.bbsmax.com/A/KE5QKvLPzL/

参考：https://blog.csdn.net/qq_38923792/article/details/102371339

参考：https://www.jianshu.com/p/6a5fb253e167