アグリゲーション¶
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, 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 provides two ways to generate aggregates. The first way is to generate
summary values over an entire QuerySet
. For example, say you wanted to
calculate the average price of all books available for sale. Django's query
syntax provides a means for describing the set of all books:
>>> Book.objects.all()
必要なのは、この QuerySet
に含まれるオブジェクトに対して合計値を計算する方法です。QuerySet
に aggregate()
句を加えることで計算されます:
>>> from django.db.models import Avg
>>> Book.objects.all().aggregate(Avg("price"))
{'price__avg': 34.35}
The all()
is redundant in this example, so this could be simplified to:
>>> Book.objects.aggregate(Avg("price"))
{'price__avg': 34.35}
aggregate()
句への引数は計算したい集約値を表します - この例では、 Book
モデルの price
フィールドの平均になります。 利用可能な集約関数の一覧は QuerySet リファレンス にあります。
aggregate()
is a terminal clause for a QuerySet
that, when invoked,
returns a dictionary of name-value pairs. The name is an identifier for the
aggregate value; the value is the computed aggregate. The name is
automatically 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 so
by providing that name when you specify the aggregate clause:
>>> Book.objects.aggregate(average_price=Avg("price"))
{'average_price': 34.35}
If you want to generate more than one aggregate, you add another argument to
the aggregate()
clause. So, if we also wanted to know the maximum and
minimum price of all books, we would issue the query:
>>> 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
内の各オブジェクトに対して個別の集計を生成することもできます。たとえば、書籍の一覧を取得しようとする場合には、それぞれの書籍に寄稿している著者が何名いるのかを知りたいこともあるでしょう。 各 Book は Author に対して多対多のリレーションを持っています; この QuerySet
内で、各書籍の関係性を集計できます。
オブジェクトごとの集計は annotate()
句を使うことで生成することができ ます。 annotate()
が指定されると、 QuerySet
の各オブジェクトは 指定された値で注釈付け (annotate) されます。
The syntax for these annotations is identical to that used for the
aggregate()
clause. Each argument to annotate()
describes
an aggregate that is to be calculated. For example, to annotate books with the
number of authors:
# 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
As with aggregate()
, the name for the annotation is automatically derived
from the name of the aggregate function and the name of the field being
aggregated. You can override this default name by providing an alias when you
specify the annotation:
>>> 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()
を用いて複数の集計 (アグリゲーション) を統合することは、 誤った結果を生み出します。サブクエリの代わりに結合(JOIN)が使われるからです:
>>> 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
プロパティを調べることを検討してみてください。
結合と集計方法¶
これまでのところ、クエリ問い合わせされたモデルに属したフィールドに対する集計だけを見てきました。しかし、集計したい値が、クエリ問い合わせをしているモデルに関係しているモデルに属している場合もあります。
集計関数の中で、集計するフィールドを特定するとき、Django はフィルター内で関係するフィールドを参照するためにも使われる double underscore notation を使えるようにしています。Django は関連する値を取得し集計するために必要なテーブル結合を処理します。
For example, to find the price range of books offered in each store, you could use the annotation:
>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min("books__price"), max_price=Max("books__price"))
これは、Store
モデルを取得し、(many-to-many リレーションシップを通じて) Book
モデルと結合し、そして書籍モデルの price フィールドの最大値と最小値を計算するように、Django に通知します。
The same rules apply to the aggregate()
clause. If you wanted to
know the lowest and highest price of any book that is available for sale
in any of the stores, you could use the aggregate:
>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))
Join chains can be as deep as you require. For example, to extract the age of the youngest author of any book available for sale, you could issue the query:
>>> Store.objects.aggregate(youngest_age=Min("books__authors__age"))
反対向きのリレーション¶
リレーションを横断するルックアップ と似たように、モデルのフィールドやモデルのリレーションに関する集計は"後ろ向きの"リレーションを含むことができます。ここでも小文字にしたモデル名と2つのアンダースコアが用いられます。
For example, we can ask for all publishers, annotated with their respective
total book stock counters (note how we use 'book'
to specify the
Publisher
-> Book
reverse foreign key hop):
>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count("book"))
(QuerySet
に含まれる全ての Publisher
には book__count
という名前の属性が追加されます。)
We can also ask for the oldest book of any of those managed by every publisher:
>>> Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate"))
(結果は 'oldest_pubdate'
というキーで参照できるようになります。もしこのように別名を指定しなければ、キーの名前は 'book__pubdate__min'
のように長くなります。)
This doesn't apply just to foreign keys. It also works with many-to-many
relations. For example, we can ask for every author, annotated with the total
number of pages considering all the books the author has (co-)authored (note how we
use 'book'
to specify the Author
-> Book
reverse many-to-many hop):
>>> Author.objects.annotate(total_pages=Sum("book__pages"))
(QuerySet
に含まれる Author
は total_pages
属性を持ちます。別名が指定されなければ、 book__pages__sum
のようになります。)
Or ask for the average rating of all the books written by author(s) we have on file:
>>> Author.objects.aggregate(average_rating=Avg("book__rating"))
(結果は average_rating
属性を持ちます。別名が指定されなければ、 book__rating__avg
のように長くなります。)
集計とその他の QuerySet
句¶
filter()
と exclude()
¶
集計はフィルタと一緒に使うこともできます。通常のモデルフィールドに適用される全ての filter()
(または exclude()
) は集計に利用できるオブジェクトを構築します。
When used with an annotate()
clause, a filter has the effect of
constraining the objects for which an annotation is calculated. For example,
you can generate an annotated list of all books that have a title starting
with "Django" using the query:
>>> from django.db.models import Avg, Count
>>> Book.objects.filter(name__startswith="Django").annotate(num_authors=Count("authors"))
When used with an aggregate()
clause, a filter has the effect of
constraining the objects over which the aggregate is calculated.
For example, you can generate the average price of all books with a
title that starts with "Django" using the query:
>>> Book.objects.filter(name__startswith="Django").aggregate(Avg("price"))
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("book", filter=Q(book__rating__gte=7))
>>> Author.objects.annotate(num_books=Count("book"), highly_rated_books=highly_rated)
Each Author
in the result set will have the num_books
and
highly_rated_books
attributes. See also Conditional aggregation.
Choosing between filter
and QuerySet.filter()
Avoid using the filter
argument with a single annotation or
aggregation. It's more efficient to use QuerySet.filter()
to exclude
rows. The aggregation filter
argument is only useful when using two or
more aggregations over the same relations with different conditionals.
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.
order_by()
¶
Annotations can be used as a basis for ordering. When you
define an order_by()
clause, the aggregates you provide can reference
any alias defined as part of an annotate()
clause in the query.
For example, to order a QuerySet
of books by the number of authors
that have contributed to the book, you could use the following query:
>>> Book.objects.annotate(num_authors=Count("authors")).order_by("num_authors")
values()
¶
Ordinarily, annotations are generated on a per-object basis - an annotated
QuerySet
will return one result for each object in the original
QuerySet
. However, when a values()
clause is used to constrain the
columns that are returned in the result set, the method for evaluating
annotations is slightly different. Instead of returning an annotated result
for each result in the original QuerySet
, the original results are
grouped according to the unique combinations of the fields specified in the
values()
clause. An annotation is then provided for each unique group;
the annotation is computed over all members of the group.
For example, consider an author query that attempts to find out the average rating of books written by each author:
>>> Author.objects.annotate(average_rating=Avg('book__rating'))
This will return one result for each author in the database, annotated with their average book rating.
However, the result will be slightly different if you use a values()
clause:
>>> Author.objects.values("name").annotate(average_rating=Avg("book__rating"))
In this example, the authors will be grouped by name, so you will only get an annotated result for each unique author name. This means if you have two authors with the same name, their results will be merged into a single result in the output of the query; the average will be computed as the average over the books written by both authors.
Order of annotate()
and values()
clauses¶
As with the filter()
clause, the order in which annotate()
and
values()
clauses are applied to a query is significant. If the
values()
clause precedes the annotate()
, the annotation will be
computed using the grouping described by the values()
clause.
However, if the annotate()
clause precedes the values()
clause,
the annotations will be generated over the entire query set. In this case,
the values()
clause only constrains the fields that are generated on
output.
For example, if we reverse the order of the values()
and annotate()
clause from our previous example:
>>> Author.objects.annotate(average_rating=Avg("book__rating")).values(
... "name", "average_rating"
... )
This will now yield one unique result for each author; however, only
the author's name and the average_rating
annotation will be returned
in the output data.
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 order_by()
¶
Fields that are mentioned in the order_by()
part of a queryset 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()
If you want to count how many times each distinct data
value appears in an
ordered queryset, you might try this:
items = Item.objects.order_by("name")
# Warning: not quite correct!
items.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 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:
items.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.
注釈
You might reasonably ask why Django doesn't remove the extraneous columns
for you. The main reason is consistency with distinct()
and other
places: Django never removes ordering constraints that you have
specified (and we can't change those other methods' behavior, as that
would violate our API の安定性 policy).
Aggregating annotations¶
You can also generate an aggregate on the result of an annotation. When you
define an aggregate()
clause, the aggregates you provide can reference
any alias defined as part of an annotate()
clause in the query.
For example, if you wanted to calculate the average number of authors per book you first annotate the set of books with the author count, then aggregate that author count, referencing the annotation field:
>>> from django.db.models import Avg, Count
>>> Book.objects.annotate(num_authors=Count("authors")).aggregate(Avg("num_authors"))
{'num_authors__avg': 1.66}
Aggregating on empty querysets or groups¶
When an aggregation is applied to an empty queryset or grouping, the result
defaults to its default parameter, typically
None
. This behavior occurs because aggregate functions return NULL
when
the executed query returns no rows.
You can specify a return value by providing the default argument for most aggregations. However, since
Count
does not support the default argument, it will always return 0
for empty querysets
or groups.
For example, assuming that no book contains web in its name, calculating the
total price for this book set would return None
since there are no matching
rows to compute the Sum
aggregation on:
>>> from django.db.models import Sum
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
{"price__sum": None}
However, the default argument can be set when
calling Sum
to return a different default value if
no books can be found:
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
{"price__sum": Decimal("0")}
Under the hood, the default argument is implemented
by wrapping the aggregate function with
Coalesce
.