聚合

    整篇指南我们将引用以下模型。这些模型用来记录多个网上书店的库存。

    下面是根据以上模型执行常见的聚合查询：

    1. # Total number of books.
    2. >>> Book.objects.count()
    3. 2452
    4.  
    5. # Total number of books with publisher=BaloneyPress
    6. >>> Book.objects.filter(publisher__name='BaloneyPress').count()
    7. 73
    8.  
    9. # Average price across all books.
    10. >>> from django.db.models import Avg
    11. >>> Book.objects.all().aggregate(Avg('price'))
    12. {'price__avg': 34.35}
    13.  
    14. # Max price across all books.
    15. >>> from django.db.models import Max
    16. >>> Book.objects.all().aggregate(Max('price'))
    17. {'price__max': Decimal('81.20')}
    18.  
    19. # Difference between the highest priced book and the average price of all books.
    20. >>> from django.db.models import FloatField
    21. >>> Book.objects.aggregate(
    22. ...     price_diff=Max('price', output_field=FloatField()) - Avg('price'))
    23. {'price_diff': 46.85}
    24.  
    25. # All the following queries involve traversing the Book<->Publisher
    26. # foreign key relationship backwards.
    27.  
    28. # Each publisher, each with a count of books as a "num_books" attribute.
    29. >>> from django.db.models import Count
    30. >>> pubs = Publisher.objects.annotate(num_books=Count('book'))
    31. >>> pubs
    32. <QuerySet [<Publisher: BaloneyPress>, <Publisher: SalamiPress>, ...]>
    33. >>> pubs[0].num_books
    34. 73
    35.  
    36. # Each publisher, with a separate count of books with a rating above and below 5
    37. >>> from django.db.models import Q
    38. >>> below_5 = Count('book', filter=Q(book__rating__lte=5))
    39. >>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
    40. >>> pubs[0].above_5
    41. 23
    42. >>> pubs[0].below_5
    43. 12
    44.  
    45. # The top 5 publishers, in order by number of books.
    46. >>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
    47. >>> pubs[0].num_books
    48. 1323

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

    1. >>> Book.objects.all()

    可以通过在 QuerySet 后添加 aggregate() 子句来计算 QuerySet 对象的汇总值。

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

    The all() is redundant in this example, so this could be simplified to:

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

    The argument to the aggregate() clause describes the aggregate value thatwe want to compute - in this case, the average of the price field on theBook model. A list of the aggregate functions that are available can befound in the QuerySet reference.

    aggregate() is a terminal clause for a QuerySet that, when invoked,returns a dictionary of name-value pairs. The name is an identifier for theaggregate value; the value is the computed aggregate. The name isautomatically generated from the name of the field and the aggregate function.If you want to manually specify a name for the aggregate value, you can do soby providing that name when you specify the aggregate clause:

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

    If you want to generate more than one aggregate, you just add anotherargument to the aggregate() clause. So, if we also wanted to knowthe maximum and minimum price of all books, we would issue the query:

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

    The second way to generate summary values is to generate an independentsummary for each object in a . For example, if you areretrieving a list of books, you may want to know how many authors contributedto each book. Each Book has a many-to-many relationship with the Author; wewant to summarize this relationship for each book in the QuerySet.

    Per-object summaries can be generated using theannotate() clause. When an annotate() clause isspecified, each object in the QuerySet will be annotated with thespecified values.

    The syntax for these annotations is identical to that used for the clause. Each argument to annotate() describesan aggregate that is to be calculated. For example, to annotate books with thenumber of authors:

    1. # Build an annotated queryset
    2. >>> from django.db.models import Count
    3. >>> q = Book.objects.annotate(Count('authors'))
    4. # Interrogate the first object in the queryset
    5. >>> q[0]
    6. <Book: The Definitive Guide to Django>
    7. >>> q[0].authors__count
    8. 2
    9. # Interrogate the second object in the queryset
    10. >>> q[1]
    11. <Book: Practical Django Projects>
    12. >>> q[1].authors__count
    13. 1

    As with aggregate(), the name for the annotation is automatically derivedfrom the name of the aggregate function and the name of the field beingaggregated. You can override this default name by providing an alias when youspecify the annotation:

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

    Unlike aggregate(), annotate() is not a terminal clause. The outputof the annotate() clause is a QuerySet; this QuerySet can bemodified using any other QuerySet operation, including filter(),order_by(), or even additional calls to annotate().

    Combining multiple aggregations with annotate() will yield the wrongresults because joins are usedinstead of subqueries:

    1. >>> book = Book.objects.first()
    2. >>> book.authors.count()
    3. 2
    4. >>> book.store_set.count()
    5. 3
    6. >>> q = Book.objects.annotate(Count('authors'), Count('store'))
    7. >>> q[0].authors__count
    8. 6
    9. >>> q[0].store__count
    10. 6

    For most aggregates, there is no way to avoid this problem, however, the aggregate has a distinct parameter thatmay help:

    If in doubt, inspect the SQL query!

    In order to understand what happens in your query, consider inspecting thequery property of your QuerySet.

    So far, we have dealt with aggregates over fields that belong to themodel being queried. However, sometimes the value you want to aggregatewill belong to a model that is related to the model you are querying.

    When specifying the field to be aggregated in an aggregate function, Djangowill allow you to use the same double underscore notation that is used when referring to related fields infilters. Django will then handle any table joins that are required to retrieveand aggregate the related value.

    For example, to find the price range of books offered in each store,you could use the annotation:

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

    This tells Django to retrieve the Store model, join (through themany-to-many relationship) with the Book model, and aggregate on theprice field of the book model to produce a minimum and maximum value.

    The same rules apply to the aggregate() clause. If you wanted toknow the lowest and highest price of any book that is available for salein any of the stores, you could use the aggregate:

      Join chains can be as deep as you require. For example, to extract theage of the youngest author of any book available for sale, you couldissue the query:

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

      Following relationships backwards

      In a way similar to Lookups that span relationships, aggregations andannotations on fields of models or models that are related to the one you arequerying can include traversing "reverse" relationships. The lowercase nameof related models and double-underscores are used here too.

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

      (Every Publisher in the resulting QuerySet will have an extra attributecalled .)

      We can also ask for the oldest book of any of those managed by every publisher:

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

      (The resulting dictionary will have a key called 'oldest_pubdate'. If nosuch alias were specified, it would be the rather long 'bookpubdatemin'.)

      This doesn't apply just to foreign keys. It also works with many-to-manyrelations. For example, we can ask for every author, annotated with the totalnumber of pages considering all the books the author has (co-)authored (note how weuse 'book' to specify the Author -> Book reverse many-to-many hop):

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

      (Every Author in the resulting QuerySet will have an extra attributecalled total_pages. If no such alias were specified, it would be the ratherlong bookpagessum.)

      Or ask for the average rating of all the books written by author(s) we have onfile:

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

      (The resulting dictionary will have a key called 'average_rating'. If nosuch alias were specified, it would be the rather long 'bookratingavg'.)

      Aggregates can also participate in filters. Any filter() (orexclude()) applied to normal model fields will have the effect ofconstraining the objects that are considered for aggregation.

      When used with an annotate() clause, a filter has the effect ofconstraining the objects for which an annotation is calculated. For example,you can generate an annotated list of all books that have a title startingwith "Django" using the query:

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

      When used with an aggregate() clause, a filter has the effect ofconstraining the objects over which the aggregate is calculated.For example, you can generate the average price of all books with atitle that starts with "Django" using the query:

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

      Filtering on annotations

      Annotated values can also be filtered. The alias for the annotation can beused in filter() and exclude() clauses in the same way as any othermodel field.

      For example, to generate a list of books that have more than one author,you can issue the query:

      This query generates an annotated result set, and then generates a filterbased upon that annotation.

      If you need two annotations with two separate filters you can use thefilter argument with any aggregate. For example, to generate a list ofauthors with a count of highly rated books:

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

      Each Author in the result set will have the num_books andhighly_rated_books attributes.

      Choosing between filter and QuerySet.filter()

      Avoid using the filter argument with a single annotation oraggregation. It's more efficient to use QuerySet.filter() to excluderows. The aggregation filter argument is only useful when using two ormore aggregations over the same relations with different conditionals.

      Order of annotate() and filter() clauses

      When developing a complex query that involves both annotate() andfilter() clauses, pay particular attention to the order in which theclauses are applied to the QuerySet.

      When an annotate() clause is applied to a query, the annotation is computedover the state of the query up to the point where the annotation is requested.The practical implication of this is that filter() and annotate() arenot commutative operations.

      Given:

      • Publisher A has two books with ratings 4 and 5.
      • Publisher B has two books with ratings 1 and 4.
      • Publisher C has one book with rating 1.
        Here's an example with the Count aggregate:
      1. >>> a, b = Publisher.objects.annotate(num_books=Count('book', distinct=True)).filter(book__rating__gt=3.0)
      2. >>> a, a.num_books
      3. (<Publisher: A>, 2)
      4. >>> b, b.num_books
      5. (<Publisher: B>, 2)
      6.  
      7. >>> a, b = Publisher.objects.filter(book__rating__gt=3.0).annotate(num_books=Count('book'))
      8. >>> a, a.num_books
      9. (<Publisher: A>, 2)
      10. >>> b, b.num_books
      11. (<Publisher: B>, 1)

      Both queries return a list of publishers that have at least one book with arating exceeding 3.0, hence publisher C is excluded.

      In the first query, the annotation precedes the filter, so the filter has noeffect on the annotation. distinct=True is required to avoid a .

      The second query counts the number of books that have a rating exceeding 3.0for each publisher. The filter precedes the annotation, so the filterconstrains the objects considered when calculating the annotation.

      Here's another example with the Avg aggregate:

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

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

      order_by()

      Annotations can be used as a basis for ordering. When youdefine an order_by() clause, the aggregates you provide can referenceany alias defined as part of an annotate() clause in the query.

      For example, to order a QuerySet of books by the number of authorsthat have contributed to the book, you could use the following query:

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

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

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

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

      这段代码返回的是数据库中的所有作者及其所著书的平均评分。

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

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

      在这个例子中，作者会按名字分组，所以你只能得到不重名的作者分组的注解值。这意味着如果你有两个作者同名，那么他们原本各自的查询结果将被合并到同一个结果中；两个作者的所有评分都将被计算为一个平均分。

      annotate() 和 values() 的顺序

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

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

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

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

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

      You should also note that average_rating has been explicitly includedin the list of values to be returned. This is required because of theordering of the values() and annotate() clause.

      If the values() clause precedes the annotate() clause, any annotationswill be automatically added to the result set. However, if the values()clause is applied after the annotate() clause, you need to explicitlyinclude the aggregate column.

      Interaction with default ordering or order_by()

      2.2 版后已移除: Starting in Django 3.1, the ordering from a model's Meta.ordering won'tbe used in GROUP BY queries, such as .annotate().values(). SinceDjango 2.2, these queries issue a deprecation warning indicating to add anexplicit order_by() to the queryset to silence the warning.

      Fields that are mentioned in the order_by() part of a queryset (or whichare used in the default ordering on a model) are used when selecting theoutput data, even if they are not otherwise specified in the values()call. These extra fields are used to group "like" results together and theycan make otherwise identical result rows appear to be separate. This shows up,particularly, when counting things.

      By way of example, suppose you have a model like this:

      1. from django.db import models
      2.  
      3. class Item(models.Model):
      4.     name = models.CharField(max_length=10)
      5.     data = models.IntegerField()
      6.  
      7.     class Meta:
      8.         ordering = ["name"]

      The important part here is the default ordering on the name field. If youwant to count how many times each distinct data value appears, you mighttry this:

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

      …which will group the Item objects by their common data values andthen count the number of id values in each group. Except that it won'tquite work. The default ordering by name will also play a part in thegrouping, so this query will group by distinct (data, name) pairs, whichisn't what you want. Instead, you should construct this queryset:

      …clearing any ordering in the query. You could also order by, say, datawithout any harmful effects, since that is already playing a role in thequery.

      This behavior is the same as that noted in the queryset documentation for and the general rule is thesame: normally you won't want extra columns playing a part in the result, soclear out the ordering, or at least make sure it's restricted only to thosefields you also select in a values() call.

      注解

      You might reasonably ask why Django doesn't remove the extraneous columnsfor you. The main reason is consistency with distinct() and otherplaces: Django never removes ordering constraints that you havespecified (and we can't change those other methods' behavior, as thatwould violate our API 的稳定性 policy).

      Aggregating annotations

      You can also generate an aggregate on the result of an annotation. When youdefine an clause, the aggregates you provide can referenceany alias defined as part of an annotate() clause in the query.

      1. >>> from django.db.models import Avg, Count
      2. >>> Book.objects.annotate(num_authors=Count('authors')).aggregate(Avg('num_authors'))