聚合¶ T0>

The topic guide on Django’s database-abstraction API described the way that you can use Django queries that create, retrieve, update and delete individual objects. 但是,有时您需要检索通过汇总或聚合对象集合派生的值。 本主题指南介绍了使用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)
    num_awards = models.IntegerField()

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)
    registered_users = models.PositiveIntegerField()

备忘单

匆忙? 假设上面的模型是这样的:

# 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的对象的汇总值的方法。 这是通过在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}

The argument to the aggregate() clause describes the aggregate value that we want to compute - in this case, the average of the price field on the Book model. 可用的集合函数列表可以在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()的每个参数描述了要计算的聚合。 例如,要用作者的数量来注释书籍:

# 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()不是终端子句。 The output of the annotate() clause is a QuerySet; this QuerySet can be modified using any other QuerySet operation, including filter(), order_by(), or even additional calls to annotate().

合并多个聚合

将多个聚合与annotate()结合使用会使产生错误的结果,因为使用连接而不是子查询:

>>> 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将允许您使用在引用过滤器中的相关字段时使用的相同的double underscore notation 然后,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'))

关系向后

以类似于Lookups that span relationships的方式,与正在查询的模型或模型的字段相关的聚合和注释可以包括遍历“反向”关系。 这里也使用相关模型的小写名称和双下划线。

例如,我们可以要求所有的出版商,用它们各自的总帐簿股票计数器来注释(注意我们如何使用'book'来指定Publisher - > Book反向外键跳转):

>>> from django.db.models import Count, Min, Sum, Avg
>>> 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 Count, Avg
>>> 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('books', filter=Q(books__rating__gte=7))
>>> Author.objects.annotate(num_books=Count('books'), highly_rated_books=highly_rated)

结果集中的每个Author将具有num_bookshighly_rated_books属性。

filterQuerySet.filter()之间进行选择

避免对单个注释或聚合使用filter参数。 使用QuerySet.filter()排除行更有效。 聚合filter参数仅在使用两个或更多聚合对具有不同条件的相同关系进行时才有用。

在Django 2.0中更改:

filter参数已添加到聚合中。

annotate()filter()子句的顺序

在开发涉及annotate()filter()子句的复杂查询时,请特别注意将子句应用于QuerySet

annotate()子句应用于查询时,会根据查询的状态计算注释,直到请求注释的点。 The practical implication of this is that filter() and annotate() are not commutative operations.

鉴于:

  • 发行人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是避免query bug所必需的。

第二个查询计算每个发布商评级超过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的评分。

It’s difficult to intuit how the ORM will translate complex querysets into SQL queries so when in doubt, inspect the SQL with str(queryset.query) and write plenty of tests.

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() 的交互

在选择输出数据时使用查询集的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值出现的次数,您可以试试这个:

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

...将按照它们的公共data值对Item对象进行分组,然后计算每个组中的id值的数量。 除了它不会工作。 The default ordering by name will also play a part in the grouping, so this query will group by distinct (data, name) pairs, which isn’t what you want. 相反,你应该构造这个查询集:

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

清除查询中的任何顺序 您还可以通过data进行排序而不会产生任何有害影响,因为这已经在查询中扮演了角色。

这种行为与distinct()的queryset文档中提到的行为相同,一般规则是相同的:通常情况下,您不希望额外的列在结果中扮演角色,因此请清除排序,或者至少确保它仅限于您在values()调用中选择的那些字段。

注意

你可能会问,为什么Django不会为你删除多余的列。 主要原因是与distinct()和其他地方的一致性:Django never删除您指定的排序约束(并且我们无法将其他方法的行为更改为这会违反我们的API stability

聚合注释

您也可以根据注释的结果生成聚合。 在定义aggregate()子句时,您提供的聚合可以引用在查询中定义为annotate()子句一部分的任何别名。

例如,如果要计算每本书的平均作者数,则首先使用作者数对作品集进行注释,然后聚合作者数量,并引用注记字段:

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