Django 聚合（译）

原文地址：https://docs.djangoproject.com/en/2.1/topics/db/aggregation/

参考：https://docs.djangoproject.com/zh-hans/2.1/topics/db/aggregation/

翻译完成日期：2019年3月12日

by 桂浮云(laurelclouds@163.com)

转载请保留以上，谢谢

---

　　Django数据库抽象API指南描述了使用Django请求创建、检索、更新、删除某类对象的方法。然而，有时要获取的值需要对一组对象进行加总或聚合后才能得到。本指南描述了如何使用 Django queries 来生成和返回聚合值的方法。

　　贯穿本指南，我们将引用以下模型，用来记录多个网上书店的库存。

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()

class Publisher(models.Model):
    name = models.CharField(max_length=300)

class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()

class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)

---

 

速查表

基于以上模型，让我们看下常见的聚合查询：

# Book的总数
>>> Book.objects.count()
2452

# 符合publisher=BaloneyPress条件的Book总数
>>> Book.objects.filter(publisher__name='BaloneyPress').count()
73

# 所有Book价格的平均值
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

# 所有Book中价格的最大值
>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('81.20')}

# 所有Book中最高价与平均值的差值
>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
...     price_diff=Max('price', output_field=FloatField()) - Avg('price'))
{'price_diff': 46.85}

# 以下请求都包含了通过Book<->Publisher外键实现对反向关系的查询

# 每个出版社出版书籍的数量，统计到num_books属性上
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
<QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
>>> pubs[0].num_books
73

# 针对书籍评价等级，统计每个出版商评级等级大于5和小于等于5的书籍数量是多少
>>> from django.db.models import Q
>>> above_5 = Count('book', filter=Q(book__rating__gt=5))
>>> below_5 = Count('book', filter=Q(book__rating__lte=5))
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
>>> pubs[0].above_5
23
>>> pubs[0].below_5
12

# 出版书籍数量最多的五个出版商
>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323

　　Django 提供了为两种情况生成聚合的方法。第一种情况是基于一个完整的 QuerySet生成汇总值。比如，你想要计算所有在售书的平均价格，Django 的查询语法提供了一种用来描述所有图书集合的方法：

>>> Book.objects.all()

 

 　　在这个所有图书集合QuerySet的基础上，需要求得汇总值，就需要在QuerySet其后加上aggregate() 子句。

>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

 

　　（由于是所有图书），以上all()是多余的，因此可以如下简化：

>>> Book.objects.aggregate(Avg('price'))
{'price__avg': 34.35}

 

　　aggregate()从句的参数用来描述要计算的聚合值——本例中，也即Book模型的price字段平均值。聚合函数列表可见QuerySet参考。

　　aggregate()函数就像QuerySet的数据输出终端，当函数调用后， aggregate()将返回一个由键值对（name-value pairs)组成的dictionary对象。键(name）作为聚合值(value)的标识符；值（value）即聚合计算结果。键名(name)一般是自动生成的，由聚合函数和字段名两部分组成。如果需要为某个聚合值(value)自定义键名（name），在aggregate()函数调用时可用指定参数名的方式实现：

>>> Book.objects.aggregate(average_price=Avg('price'))
{'average_price': 34.35}

 

　　如果同时聚合的不止一个，可以在调用aggregate()从句时添加另外一个参数。所以，如果我们想同时得到所有书籍的最大值和最小值，可使用以下查询：

>>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

 

---

 

　　为QuerySet中每条记录生成聚合

　　第二种生成聚合的情况，就是为QuerySet中每个对象生成一个单独的注解。例如，如需获取一个书单，你可能想知道每本书(Book)的作者(Author)数量是多少。书Book与作者Author的关系是多对多的关系；我们需要对QuerySet中每一本书的这种与作者Author的关系进行统计。

　　这时我们就会用到annotate()从句，来生成这种基于每个对象的注解。（译者注：恰如annotate的中文意思，annotate()从句就像为每个对象加注释一样，）实现对QuerySet中每个对象生成汇总注释信息。

　　annotate()语法与aggregate()语法相同。传递给annotate()的参数用于描述要计算的聚合信息。如，为书(Book)添加作者(Author)数量的注解：

# 构建一个annotate聚合的QuerySet
>>> from django.db.models import Count
>>> q = Book.objects.annotate(Count('authors'))
# 查看queryset中第一个对象
>>> q[0]
<Book: The Definitive Guide to Django>
>>> q[0].authors__count
2
# 查看queryset中第二个对象
>>> q[1]
<Book: Practical Django Projects>
>>> q[1].authors__count
1

　　与aggregate()类似，annotatie聚合的对应名（name）是由聚合函数和字段名自动组合而成的，需要变更的话，可在调用annotate()时指定别名：

>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1

 

　　但与aggregate()不同，annotate()并非是（QuerySet的）终结者（译者注，与aggregate()对照而言，aggregate()对QuerySet的处理结果为dictionary，而annotate()不是）。annotate()函数的输出结果就是一个QuerySet；也意味着，annotate()的结果仍可以被任何其他QuerySet操作改变，包括filter()、order_by()等，甚至于额外的annotate()调用。

 

组合多个聚合

用annotate()组合多个聚合将产生错误结果，这是因为（annotate的底层实现）用的是（SQL的）连接(JOIN）而不是子查询。

>>> book = Book.objects.first()
>>> book.authors.count()
2
>>> book.store_set.count()
3
>>> q = Book.objects.annotate(Count('authors'), Count('store'))
>>> q[0].authors__count
6
>>> q[0].store__count
6

 

大多数聚合都无法避免此类问题，但有个例外，Count聚合由于可使用distinct参数，可以实现组合实现多个聚合：

>>> q = Book.objects.annotate(Count('authors', distinct=True), Count('store', distinct=True))
>>> q[0].authors__count
2
>>> q[0].store__count
3

 

为了弄明白请求做了什么，可以通过QuerySet的query属性检验下。

 

---

 连接和聚合

　　迄今为止，我们实现了对模型字段的聚合查询。然而，有时我们必须面对在查询模型中对相关模型进行聚合的情况。

　　当聚合函数对指定字段进行聚合求值时，Django允许我们使用类似过滤器(fliters）引用相关字段那样的双下划线表示法（解决此类问题）。Django将自行处理需要求值或聚合的任意数据表连接。

　　例如，要获取每个书店中售卖书籍的价格范围，可以采用以下annotate聚合代码：

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min('books__price'), max_price=Max('books__price'))

 

以上代码就是告诉Django检索Store模型，连接（通过多对多关系）Book模型，并对Book模型的price自动进行聚合运算，以产生最小值和最大值。

规则同样适用于aggregate()。如果我们想得到任意一本在售书籍在任意书店中售卖的最低和最高价，就可以这样求聚合值：

>>> Store.objects.aggregate(min_price=Min('books__price'), max_price=Max('books__price'))

 

跟踪反向关系

与查询跨越关系（Lookup span relationships）类似，模型字段上的聚合(aggregations）或摘要（annotations），或者与查询模型相关的模型，都可以包括横跨（两个模型的）“反向”关系。同样，采用了关联模型名称小写和双下划线（分割）的书写方式。

例如，我们可以要求所有发行商都标注出各自的书籍存货量（注意：我们是如何使用book（非Book）来说明Publisher->Book这种反向外键关系的）：

>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count('book'))

 

（结果QuerySet中每个Publisher将多出一个名为book_ccount的额外属性）

也可以获取每个发行商管理的旧书中最旧的图书是什么：

>>> Publisher.objects.aggregate(oldest_pubdate=Min('book__pubdate'))

 

(结果dictionary中包含了oldest_pubdate关键字，如果不指定oldest_pubdate这个别名，也可以采用更长的book__pubdate__min）

这种便捷方法当然不会只适用于外键，多对多关系也可以这么做。例如，我们要求对每个作者标注出所有书籍中该作者创作或合著的页数（注意下book用于说明Author->Book反向多对多跳连的）：

>>> Author.objects.annotate(total_pages=Sum('book__pages'))

 

（结果QuerySet中的每个Author豆浆包含额外的total_pages属性。如果不指定别名，也可采用较长的book__pages__sum）

　　又或者获取归档作者中所有创作的书籍的平均评价值：

>>> Author.objects.aggregate(average_rating=Avg('book__rating'))

 

　　（结果dictoinary中将包含average_rating的键名，如不指定，将采用book__rating__avg的名字）

---

聚合及其他QuerySet语句

filter()和exclude()

聚合也可以运用到过滤器上。任何应用到普通模型字段的filter()（或exclude()）对聚合而言都将获得对象限定的效果。

使用annotate()语句时，过滤器将对进行标注（annotate)运算的对象进行限定。例如，如果需要在书名以Django开头的书籍列表上进行标注：

>>> from django.db.models import Avg, Count
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count('authors'))

 

使用aggregate()语句时，过滤器实现对聚合计算的对象进行限定。例如，获取以Django打头的所有书名的平均价格：

>>> Book.objects.filter(name__startswith="Django").aggregate(Avg('price'))

 

基于annotate标注的过滤

标注后的QuerySet也可以被过滤。annotate结果中的别名也可以被filter()和exclude()使用，方式与任何其他模型字段无异。

例如，获取多于一个作者的图书列表，可以发送以下请求：

>>> Book.objects.annotate(num_authors=Count('authors')).filter(num_authors__gt=1)

 

请求首先获得annotate标注记录集，然后基于该记录集应用过滤。

如果需要用两个过滤器进行两次annotate标注，可以使用任意聚合函数中加带filter参数的方式实现。例如，获取附带标注高评价的书籍个数的作者(author)的列表。

>>> highly_rated = Count('books', filter=Q(books__rating__gte=7))
>>> Author.objects.annotate(num_books=Count('books'), highly_rated_books=highly_rated)

 

记录集中每个Author将包含num_books和highly_rated_books属性。

选用filter参数，还是QuerySet.filter()
在单独的annotate标注或aggregate聚合中，要避免使用filter参数的形式。对于排除某些行而言，使用QuerySet.filter()更有效率。
聚合函数所用的filter参数仅在基于同一关系但是条件不同的两个及以上的聚合中才有用。

 

Django2.0以来的改变
聚合中增加了filter参数。

---

annotate()及filter()语句的次序

当开发的复杂请求中需要同时使用annotate()和filter()语句时，千万千万千万当心，语句的次序将被应用到QuerySet中。

当使用annotate()语句应用到数据请求时，annotate标注以请求时的位置为原点，基于请求的状态进行计算。以下示范了filter()和annotate()的位置是不可替换的：

给定数据：

Publisher A 有两本书，评价为4和5；

Publisher B 有两本书，评价为1和4；

Publisher C有一本书，评价为1

下面是Count聚合的示例代码：

>>> a, b = Publisher.objects.annotate(num_books=Count('book', distinct=True)).filter(book__rating__gt=3.0)
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 2)

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
>>> a, a.num_books
(<Publisher: A>, 2)
>>> b, b.num_books
(<Publisher: B>, 1)

 

两种查询都会返回至少有一本书的评价大于3.0的出版商列表，但是C出版商被排除了。

第一种查询，先标注后过滤，因此过滤器不会作用于标注（生成的属性）上的。distinct=True对于避免查询bug是必要的。

第二种查询，获取每个出版商(Publisher)的书籍评价超过3.0的书籍个数。过滤在前，标注在后，因此，过滤器限定了可用于annotate标注的对象。

另一个Avg聚合的例子：

>>> a, b = Publisher.objects.annotate(avg_rating=Avg('book__rating')).filter(book__rating__gt=3.0)
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 2.5)  # (1+4)/2

>>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(avg_rating=Avg('book__rating'))
>>> a, a.avg_rating
(<Publisher: A>, 4.5)  # (5+4)/2
>>> b, b.avg_rating
(<Publisher: B>, 4.0)  # 4/1 (book with rating 1 excluded)

第一种查询，首先对所有出版商的书籍评价求平均值，然后确保出版商应该至少有一本书的评价超过3.0。第二种查询，仅对一个出版商的书籍中评价超过3.0的书籍评价进行评价。

很难凭直觉知道ORM（对象关系映射）如何把复杂的QuerySets转换成SQL查询，因此有疑虑的时候，最好通过str(queryset.query)进行检查并多做测试。

---

 

order_by()

annotate注解可以继续进行排序操作。定义order_by()语句时，在聚合查询中可以引用annotate()中定义的别名部分。

例如，以书籍的创作者/合著人的数量进行排序：

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')

 

---

 

values()

（本部分内容参考了官方中文文档注释）

通常，注解值会添加到每个对象上，即一个注解后的 QuerySet 将会为初始 QuerySet 的每个对象返回一个结果。然而，当使用 values() 语句来对结果集的列进行约束时，生成注解值的方法会稍有不同。不是在原始 QuerySet 中对每个对象添加注解并返回，而是根据定义在 values() 子句中的字段组合先对结果进行分组，再对每个单独的分组进行注解，这个注解值是根据分组中所有的对象计算得到的。

下面是一个关于作者的查询例子，查询每个作者所著书的平均评分：

>>> Author.objects.annotate(average_rating=Avg('book__rating'))

这段代码返回的是数据库中的所有作者的结果集，每个作者都标注了其所著书的平均评分。

但是如果你使用 values() ，结果会稍有不同：

>>> Author.objects.values('name').annotate(average_rating=Avg('book__rating'))

 

本例中，作者会按name分组，所以你只能得到不重名的作者分组的注解值。这意味着如果你有两个作者同名，那么他们原本各自的查询结果将被合并到同一个结果中；平均分其实是对两个作者的书籍进行取平均值运算。

annotate()和values()的顺序

（本部分内容参考了官方中文文档注释）

和使用 filter() 一样，作用于某个查询的 annotate() 和 values() 的顺序非常重要。如果 values() 语句在 annotate() 之前，就会根据 values() 子句产生的分组来进行注解。

然而如果 annotate() 子句在 values() 之前，就会根据整个查询集生成注解。这种情况下，values() 子句只能限制输出的字段。

举个例子，如果我们颠倒上个例子中 values() 和 annotate() 的顺序：

>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')

这段代码将为每个作者添加一个唯一注解，但只有作者姓名和 average_rating 注解会返回在输出结果中。

应该注意到，average_rating已明确包含在返回结果中，这对values()和annotate()的顺序而言是必须的。

如果values()在annotate()之前，任何注解将自动添加到结果集中。但是，如果在annotate()之后，就要受到聚合列的限制。

---

 

默认排序与order_by()的相互影响

order_by()部分用到的字段（或者模型中默认排序的字段），在查询输出数据时将被用到，即便这些列未在values()调用中指定也是如此。这些额外字段被用来对“喜欢的”结果进行分组，并且可以使其他列相同结果行看起来有所不同。特别是在计算某些事项时会有这种情况。

比如，我们这里假定有个模型：

from django.db import models

class Item(models.Model):
    name = models.CharField(max_length=10)
    data = models.IntegerField()

    class Meta:
        ordering = ["name"]

 

以上代码中关键的部分是指定了默认的排序字段name。当我们想要统计唯一的data字段的值出现了多少次之时，可做以下尝试：

# 警告: 并不十分正确！
Item.objects.values("data").annotate(Count("id"))

 

……将通过data字段的值对Item对象分组，然后计算每个组中id字段值的个数，除了它并不十分正确。字段name上的默认排序在分组中也会产生影响，因此该查询其实是根据唯一的(data, name)对进行的分组，只是并非你所要的。正确的做法是：

Item.objects.values("data").annotate(Count("id")).order_by()

 

……把查询中任意排序都清除干净。换句话说，你也可以在data字段上进行无害的排序，因为data字段在查询中就是扮演了这样的角色。

这种行为与distinct()的queryset文档提示的相同，基本规则也相同：通常我们不想额外的列对查询结果造成影响，所以要清除排序，或者要确定把字段限定在values()调用的字段之中。

注意

很自然，我们就会对django为什么不删除无关列产生疑问。主要原因是要与distinct()等保持一致：
Django从不删除您指定的排序约束（并且我们也无法更改其他方法的行为，因为会违反API稳定性策略）。

 

---

 

聚合注解

也可以在注解之上生成聚合。aggregate()定义时，聚合可以引用annotate()定义的别名。

例如，如果要计算每本书的平均作者数，首先使用作者计数对书籍集合进行注解，然后汇总该作者计数，这时引用了注解字段：

>>> from django.db.models import Avg, Count
>>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))
{'num_authors__avg': 1.66}

 

转载于:https://www.cnblogs.com/laurelclouds/p/10503396.html