聚合 — Django 文档

来自菜鸟教程
Django/docs/2.2.x/topics/db/aggregation
跳转至:导航、​搜索

聚合

Django 的数据库抽象 API 的主题指南描述了使用 Django 查询创建、检索、更新和删除单个对象的方式。 但是,有时您需要检索通过汇总或 聚合 对象集合而派生的值。 本主题指南描述了使用 Django 查询生成和返回聚合值的方法。

在本指南中,我们将参考以下模型。 这些模型用于跟踪一系列在线书店的库存:

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.
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg('price'))
{'price__avg': 34.35}

# Max price across all books.
>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'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 的对象的汇总值的方法。 这是通过将 aggregate() 子句附加到 QuerySet 来完成的:

>>> 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 的终端子句,调用时返回名称-值对的字典。 名称是聚合值的标识符; 该值是计算的聚合。 该名称是根据字段名称和聚合函数自动生成的。 如果要手动指定聚合值的名称,可以通过在指定聚合子句时提供该名称来实现:

>>> 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() 的每个参数描述了一个要计算的聚合。 例如,要使用作者人数注释书籍:

# 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()将要 :ticket:`产生错误的结果 <10060>` 因为使用连接而不是子查询:

>>> 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 查询!

为了了解您的查询中发生了什么,请考虑检查 QuerySetquery 属性。


联接和聚合

到目前为止,我们已经处理了属于被查询模型的字段的聚合。 但是,有时您想要聚合的值将属于与您正在查询的模型相关的模型。

在聚合函数中指定要聚合的字段时,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 模型,并在 book 模型的 price 字段上聚合以产生最小值和最大值。

相同的规则适用于 aggregate() 子句。 如果您想知道在任何一家商店出售的任何书籍的最低和最高价格,您可以使用聚合:

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

联接链可以根据您的需要尽可能深。 例如,要提取任何可出售书籍的最年轻作者的年龄,您可以发出查询:

>>> Store.objects.aggregate(youngest_age=Min('books__authors__age'))

向后跟踪关系

以类似于 跨越关系 的查找的方式,模型或与您查询的模型相关的字段上的聚合和注释可以包括遍历“反向”关系。 这里也使用了相关模型的小写名称和双下划线。

例如,我们可以要求所有出版商,用他们各自的总图书库存计数器进行注释(注意我们如何使用 'book' 指定 Publisher -> Book 反向外键跃点):

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

(结果 QuerySet 中的每个 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'))

(结果 QuerySet 中的每个 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_bookshighly_rated_books 属性。

filterQuerySet.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.0 的书籍的出版商列表,因此出版商 C 被排除在外。

在第一个查询中,注解在过滤器之前,因此过滤器对注解没有影响。 distinct=True 需要避免 查询错误

第二个查询计算每个出版商的评分超过 3.0 的图书数量。 过滤器位于注释之前,因此过滤器会限制计算注释时考虑的对象。

这是 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 如何将复杂的查询集转换为 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() 子句之前,任何注释都将自动添加到结果集中。 但是,如果在 annotate() 子句之后应用 values() 子句,则需要显式包含聚合列。


与默认排序或 order_by() 交互

自 2.2 版起已弃用: 从 Django 3.1 开始,模型 Meta.ordering 的排序将不会用于 GROUP BY 查询,例如 .annotate().values()。 从 Django 2.2 开始,这些查询发出弃用警告,指示向查询集中添加显式 order_by() 以消除警告。


选择输出数据时会使用查询集的 order_by() 部分中提到的字段(或用于模型的默认排序),即使 [ X210X] 呼叫。 这些额外的字段用于将“相似”的结果分组在一起,它们可以使其他相同的结果行看起来是分开的。 这尤其体现在计数事物时。

举例来说,假设你有一个这样的模型:

from django.db import models

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

    class Meta:
        ordering = ["name"]

这里的重要部分是 name 字段的默认排序。 如果你想计算每个不同的 data 值出现的次数,你可以试试这个:

# Warning: not quite correct!
Item.objects.values("data").annotate(Count("id"))

...这将按 Item 对象的公共 data 值分组,然后计算每组中 id 值的数量。 除了它不会完全奏效。 name 的默认排序也将在分组中起作用,因此此查询将按不同的 (data, name) 对分组,这不是您想要的。 相反,您应该构造此查询集:

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

...清除查询中的任何排序。 您还可以按 data 排序而不会产生任何有害影响,因为它已经在查询中起作用了。

此行为与 distinct() 的查询集文档中所述的行为相同,一般规则相同:通常您不希望额外的列在结果中起作用,因此清除排序,或者至少确保它仅限于您在 values() 调用中选择的那些字段。

笔记

您可能会合理地问为什么 Django 不为您删除无关的列。 主要原因是与 distinct() 和其他地方的一致性:Django never 删除您指定的排序约束(我们不能改变那些其他方法的行为,因为这会违反我们的 [ X217X]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}