聚合¶
Django 数据库抽象 API 描述了使用 Django queries 来增删查改单个对象的方法。 然而,有时候你要获取的值需要根据一组对象聚合后才能得到。这个主题指南描述了如何使用 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)
速查表¶
下面是根据以上模型执行常见的聚合查询:
# Total number of books.
>>> Book.objects.count()
2452
# Total number of books with publisher=BaloneyPress
>>> Book.objects.filter(publisher__name="BaloneyPress").count()
73
# Average price across all books, provide default to be returned instead
# of None if no books exist.
>>> from django.db.models import Avg
>>> Book.objects.aggregate(Avg("price", default=0))
{'price__avg': 34.35}
# Max price across all books, provide default to be returned instead of
# None if no books exist.
>>> from django.db.models import Max
>>> Book.objects.aggregate(Max("price", default=0))
{'price__max': Decimal('81.20')}
# Difference between the highest priced book and the average price of all books.
>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
... price_diff=Max("price", output_field=FloatField()) - Avg("price")
... )
{'price_diff': 46.85}
# All the following queries involve traversing the Book<->Publisher
# foreign key relationship backwards.
# Each publisher, each with a count of books as a "num_books" attribute.
>>> 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
# Each publisher, with a separate count of books with a rating above and below 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
# The top 5 publishers, in order by number of books.
>>> pubs = Publisher.objects.annotate(num_books=Count("book")).order_by("-num_books")[:5]
>>> pubs[0].num_books
1323
在 QuerySet
上生成聚合¶
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 reference 中找到。
aggregate()
是一个 QuerySet
的终端子句,当调用时,它返回一个名值对的字典。名称是聚合值的标识符;值是计算得到的聚合值。名称是从字段名称和聚合函数自动生成的。如果您想手动指定聚合值的名称,可以在指定聚合子句时提供该名称:
>>> 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
的每一个对象生成独立汇总。比如,如果你想检索书籍列表,你可能想知道每一本书有多少作者。每一本书与作者有多对多的关系;我们想在 QuerySet
中为每一本书总结这个关系。
使用 annotate()
子句可以生成每一个对象的汇总。当指定 annotate()
子句,QuerySet
中的每一个对象将对指定值进行汇总。
这些汇总语法规则与 aggregate()
子句的规则相同。annotate()
的每一个参数描述了一个要计算的聚合。比如,注解(annotate)所有书的所有作者:
# Build an annotated queryset
>>> from django.db.models import Count
>>> q = Book.objects.annotate(Count("authors"))
# Interrogate the first object in the queryset
>>> q[0]
<Book: The Definitive Guide to Django>
>>> q[0].authors__count
2
# Interrogate the second object in the queryset
>>> q[1]
<Book: Practical Django Projects>
>>> q[1].authors__count
1
与 aggregate()
一样,注释的名称是从聚合函数的名称和被聚合字段的名称自动派生的。您可以通过在指定注释时提供别名来覆盖这个默认名称:
>>> q = Book.objects.annotate(num_authors=Count("authors"))
>>> q[0].num_authors
2
>>> q[1].num_authors
1
与 aggregate()
不同的是,annotate()
不 是终端子句。annotate()
子句的输出就是 QuerySet
;这个 QuerySet
被其他 QuerySet
操作进行修改,包括 filter()
, order_by()
,甚至可以对 annotate()
进行额外调用。
组合多个聚合¶
使用 annotate()
组合多个聚合将产生错误的结果( yield the wrong results ),因为它使用连接(joins)而不是子查询:
>>> 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
如有疑问,请检查 SQL 查询!
为了搞清楚你的查询发生了什么问题,你得在 QuerySet
中检查一下``query`` 属性。
连接(Joins)和聚合¶
到目前为止,我们已经处理了被查询模型字段的聚合。然而,有时候想聚合的值属于你正在查询模型的关联模型。
在聚合函数中指定聚合字段时,Django 允许你使用与过滤器引用相关字段时相同的 双下划线表示法。然后,Django 将处理任何所需的表连接以检索和聚合相关值。
例如,要找出每家商店提供的书籍价格范围,您可以使用以下注释:
>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min("books__price"), max_price=Max("books__price"))
这告诉 Django 去检索 Store
模型,连接(通过多对多关系) Book
模型,并且聚合书籍模型的价格字段来获取最大最小值。
相同的规则也适用于 aggregate()
子句。如果您想知道在任何商店都有销售的任何书的最低价和最高价,可以使用聚合操作:
>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))
连接链可以根据需要深入。例如,要提取任何可售书籍中最年轻作者的年龄,可以发出以下查询:
>>> Store.objects.aggregate(youngest_age=Min("books__authors__age"))
反向关系¶
类似于 跨关系查询 ,你正在查询的在模型和模型字段上的聚合和注解(annotations)可以包含反向关系。关系模型的小写名和双下划线也可以用在这里。
例如,我们可以请求所有出版商,附带它们各自的总书库存计数器(请注意如何使用 'book'
来指定 Publisher
-> Book
的反向外键跳跃):
>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count("book"))
(查询结果里的每一个 Publisher
会有多余的属性—— book__count
。)
我们还可以要求每个出版商管理的书籍中最老的一本书:
>>> Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate"))
(结果字典中会有一个叫 'oldest_pubdate'
的键。如果没有指定这样的别名,它将会是一个很长的名字 'book__pubdate__min'
。)
这不仅适用于外键,还适用于多对多关系。例如,我们可以要求每个作者,附带考虑作者(共同)合著的所有书籍的总页数的注释(请注意如何使用 'book'
来指定 Author
-> Book
的反向多对多跳跃):
>>> Author.objects.annotate(total_pages=Sum("book__pages"))
(结果集里的每一个 Author
会有一个额外的属性——total_pages
)如果没有指定这样的别名,它将会是一个很长的名字 book__pages__sum
)
或者要求计算我们记录的所有作者所著书籍的平均评分:
>>> Author.objects.aggregate(average_rating=Avg("book__rating"))
(结果字典会有一个叫 'average_rating'
的键。如果没有指定这样的别名,它将会是一个很长的名字 'book__rating__avg'
。)
聚合和其他 QuerySet
子句¶
filter()
和 exclude()
¶
聚合也可以参与过滤。任何应用于普通模型字段的 filter()
(或 exclude()
)会具有约束被认为是聚合的对象的效果。
当与 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"))
过滤注解¶
注解过的值也可以使用过滤器。注解的别名可以和任何其他模型字段一样使用 filter()
和 exclude()
子句。
例如,要生成一个具有多位作者的书籍列表,您可以发出以下查询:
>>> Book.objects.annotate(num_authors=Count("authors")).filter(num_authors__gt=1)
这个查询生成一个注解结果集,然后生成一个基于注解的过滤器。
如果您需要两个具有两个单独过滤器的注释,可以在任何聚合操作中使用 filter
参数。例如,要生成具有高评分书籍计数的作者列表:
>>> highly_rated = Count("book", filter=Q(book__rating__gte=7))
>>> Author.objects.annotate(num_books=Count("book"), highly_rated_books=highly_rated)
结果集中的每个 Author
都有 num_books
和 highly_rated_books
属性。参见 条件聚合。
在 filter
和 QuerySet.filter()
中做选择
避免在单个注解和聚合中使用 filter
语句。使用 QuerySet.filter()
来排除列会很高效。聚合 filter
语句只在使用具有不同条件的相同关系的两个或以上的聚合时有用。
annotate()
和 filter()
子句的顺序¶
当开发一个涉及 annotate()
和 filter()
子句的复杂查询时,要特别注意应用于 QuerySet
的子句的顺序。
当一个 annotate()
子句应用于查询,会根据查询状态来计算注解,直到请求的注解为止。这实际上意味着 filter()
和 annotate()
不是可交换的操作。
比如:
出版者A有两本评分4和5的书。
出版者B有两本评分1和4的书。
出版者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的书,因此排除了C。
在第一个查询里,注解优先于过滤器,因此过滤器没有影响注解。distinct=True
用来避免 a query bug。
第二个查询每个发布者评分3以上的书籍数量。过滤器优先于注解,因此过滤器约束计算注解时考虑的对象。
以下是另一个使用 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以上的书籍的出版者的书籍平均分。第二个查询只请求评分3以上的作者书籍的平均评分。
很难凭直觉了解 ORM 如何将复杂的查询集转化为 SQL 查询,因此当有疑问时,请使用 str(queryset.query)
检查 SQL,并写大量的测试。
order_by()
¶
注解可以当做基本排序来使用。当你定义了一个 order_by()
子句,你提供的聚合可以引用任何定义为查询中 annotate()
子句的一部分的别名。
例如,要按参与书籍创作的作者数量对书籍的 QuerySet
进行排序,您可以使用以下查询:
>>> 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"))
在这个例子中,作者会按名字分组,所以你只能得到不重名的作者分组的注解值。这意味着如果你有两个作者同名,那么他们原本各自的查询结果将被合并到同一个结果中;两个作者的所有评分都将被计算为一个平均分。
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()
子句之前,任何注解将自动添加在结果集中。然而,如果 values()
子句应用在 annotate()
子句之后,则需要显式包含聚合列。
Interaction with order_by()
¶
在查询集的 order_by()
部分提到的字段会在选择输出数据时使用,即使它们在 values()
调用中没有被明确指定。这些额外的字段用于将类似的结果分组在一起,它们可以使否则相同的结果行看起来是分开的。这在计算数量时特别明显。
举个例子,假设你有这样的模型:
from django.db import models
class Item(models.Model):
name = models.CharField(max_length=10)
data = models.IntegerField()
如果您想计算在有序查询集中每个不同的 data
值出现的次数,您可以尝试这样做:
items = Item.objects.order_by("name")
# Warning: not quite correct!
items.values("data").annotate(Count("id"))
...这将根据它们共同的 data
值将 Item
对象分组,然后计算每个组中 id
值的数量。但实际上,它不会完全起作用。按照 name
进行排序也会影响分组,所以这个查询将根据不同的 (data, name)
对进行分组,这不是您想要的。相反,您应该构建这个查询集:
items.values("data").annotate(Count("id")).order_by()
清除任何查询中的排序。你也可以通过 data
排序,没有任何有害影响,因为它已经在查询中发挥了作用。
这个行为与 distinct()
的查询文档指出的行为相同,一般规则是一样的:通常情况下,你不希望额外的列在结果中发挥作用,因此要清除排序,或者至少确保它只限于您在 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}
在空查询集或组上进行聚合操作时,需要格外小心,因为这可能会导致未定义的行为或错误。在执行聚合操作之前,通常应确保查询集或组中包含足够的数据以执行所需的聚合计算。如果查询集或组为空,可以使用条件语句来避免聚合错误或不必要的操作。¶
当对空的查询集或分组应用聚合操作时,结果通常默认为其 default 参数,通常是 None
。这种行为发生是因为当执行的查询不返回任何行时,聚合函数会返回 NULL
。
您可以为大多数聚合操作提供 default 参数来指定返回值。但是,由于 Count
不支持 default 参数,它在空的查询集或分组上始终返回 0
。
例如,假设没有一本书的名称中包含 web,那么计算这本书集合的总价格会返回 None
,因为没有匹配的行来进行 Sum
聚合计算:
>>> from django.db.models import Sum
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
{"price__sum": None}
然而,可以在调用 Sum
时设置 default 参数,以返回不同的默认值,如果找不到书籍:
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
{"price__sum": Decimal("0")}