Django2.0手册:聚合

Filtering on annotations¶

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

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

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

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

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

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

Each Author in the result set will have the num_books and
highly_rated_books attributes.

Order of annotate() and filter() clauses¶

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

When an annotate() clause is applied to a query, the annotation is computed
over the state of the query up to the point where the annotation is requested.
The practical implication of this is that filter() and annotate() are
not 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:

>>> 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)

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

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

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

Here’s another example with the Avg aggregate:

>>> 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)

The first query asks for the average rating of all a publisher’s books for
publisher’s that have at least one book with a rating exceeding 3.0. The second
query asks for the average of a publisher’s book’s ratings for only those
ratings exceeding 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.

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 注解会返回在输出结果中。

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

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

Interaction with default ordering or order_by()¶

Fields that are mentioned in the order_by() part of a queryset (or which
are used in the default ordering on a model) are used when selecting the
output data, even if they are not otherwise specified in the values()
call. These extra fields are used to group “like” results together and they
can 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:

from django.db import models

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

    class Meta:
        ordering = ["name"]

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

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

…which will group the Item objects by their common data values and
then count the number of id values in each group. Except that it won’t
quite work. 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. Instead, you should construct this queryset:

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

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

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