アグリゲーション (集計)¶
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 は集計を生成するために2つの方法を提供します。最初の方法は、 クエリセット 全体のサマリ値を生成する方法です。たとえば、販売されているすべての本の平均価格を計算したいとします。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}
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 内の各オブジェクトに対して個別の集計を生成することもできます。たとえば、書籍の一覧を取得しようとする場合には、それぞれの書籍に寄稿している著者が何名いるのかを知りたいこともあるでしょう。 各 Book は Author に対して多対多の関係を持っています。この関係を QuerySet 内の各書籍について集計できます。
オブジェクトごとの集計は annotate() 句を使うことで生成できます。 annotate() が指定されると、 QuerySet の各オブジェクトは 指定された値で注釈付け (annotate) されます。
これらのアノテーションの構文は 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() を用いて複数の集計 (アグリゲーション) を統合することは、 誤った結果を生み出します 。サブクエリの代わりに結合(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 ではフィルタ内でリレーション先のフィールドを参照するために使われるのと同じ 2重アンダースコア表記 が使えます。Django はリレーション先の値を取得し集計するために必要なテーブル結合を処理します。
たとえば、各店舗で扱っている本の価格帯を調べるには、アノテーションを使います:
>>> 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 に通知します。
同じルールが aggregate() 句にも適用されます。もし、どこかのストアで販売されている本の最低価格と最高価格を知りたければ、集計が使えます。
>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))
連結は必要ならいくつでもできます。たとえば、販売されている本の中で最も若い著者の年齢を抽出するには、次のようなクエリを発行します:
>>> Store.objects.aggregate(youngest_age=Min("books__authors__age"))
逆方向のリレーションシップ¶
リレーションシップを横断するルックアップ と同じように、モデルのフィールドやモデルのリレーションシップに関する集計には "逆方向" のリレーションシップが使用できます。ここでも、小文字にしたモデル名と2つのアンダースコアを使います。
たとえば、すべての出版社にそれぞれの総書籍数カウンターのアノテーションを付けることができます（ Publisher -> Book の逆引き外部キーホップを指定するために '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' のように長くなります。)
これは外部キーだけに適用されるわけではありません。多対多のリレーションシップにも使えます。たとえば、すべての著者について、その著者が(共著で)執筆したすべての本を考慮した総ページ数のアノテーションを求めることができます (Author -> Book の逆多対多のホップを指定するために '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)
このクエリはアノテーション付きの結果セットを生成し、そのアノテーションに基づいてフィルタを生成します。
もし2つのアノテーションを別々のフィルタで付ける必要がある場合は、 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_books と highly_rated_books 属性を持ちます。条件付きの集計 も参照してください。
filter 引数と QuerySet.filter() のどちらを使うか？
単一のアノテーションや集計で filter 引数を使用することは避けてください。 QuerySet.filter() を使用して行をフィルタする方が効率的です。集計の filter 引数は、同じリレーションシップに対して異なる条件で2つ以上の集計を行う場合にだけ有効です。
annotate() 句と filter() 句の順番¶
annotate() と filter() の両方の句を含む複雑なクエリを開発する場合は、 QuerySet に適用する順番に特に注意してください。
annotate() 句がクエリに適用されると、アノテーションはアノテーションが要求された時点までのクエリの状態に対して計算されます。これは、filter() と annotate() は可換な演算ではないことを意味します。
以下のことを仮定します。
出版社Aの本は2冊あり、評価は4と5である。
出版社Bの本は2冊あり、評価は1と4である。
出版社Cの本は1冊あり、評価は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を超える本を少なくとも1冊持っている出版社のリストを返すので、出版社Cは除外されます。
最初のクエリでは、アノテーションをフィルタよりも先に付けるため、フィルタはアノテーションに影響を与えません。 distinct=True は クエリのバグ を避けるために必要です。
2つ目のクエリは、出版社ごとにレーティングが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を超える評価を1冊でも持つ出版社の全書籍の平均評価を求めるものです。2つ目のクエリは、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 の各オブジェクトに対して1つの結果を返します。しかし、 values() 句を使って結果セットで返されるカラムを制限している場合は、アノテーションを付けるしくみが少し異なります。元の QuerySet の各結果に対してアノテーションを付ける代わりに、元の結果は values() 句で指定されたフィールドの一意な組み合わせに従ってグループ化されます。アノテーションはグループのすべてのメンバに対して計算されます。
たとえば、各著者が書いた本の平均評価を調べるための Author へのクエリを考えてみましょう:
>>> Author.objects.annotate(average_rating=Avg("book__rating"))
これは、データベース内の各著者について、その本の平均評価をアノテーションした結果を1つ返します。
しかし、 values() 句を使うと結果は少し違ってきます:
>>> Author.objects.values("name").annotate(average_rating=Avg("book__rating"))
この例では、著者は名前によってグループ化されるので、それぞれの 一意な 著者名に対してのみアノテーションを付けた結果が得られます。つまり、同じ名前の著者が2人いる場合、それらの結果はクエリの出力で1つの結果にマージされます。平均は両方の著者が書いた本の平均として計算されます。
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() 句より先にある場合、アノテーションは結果セットに自動的に追加されます。しかし、 values() 句を annotate() 句の後に適用する場合は、集計列を明示的に含める必要があります。
order_by() と一緒に使う¶
クエリセットの order_by() 部分で言及されたフィールドは、 values() 呼び出しで特に指定されていなくても、出力データの選択時に使用されます。これらの追加フィールドは "同じような" 結果をグループ化するため、同じ結果の行が別々の行に見えることがあります。これは特に、何かを数えるときに顕著に現れます。
たとえば、次のようなモデルがあるとします:
from django.db import models
class Item(models.Model):
name = models.CharField(max_length=10)
data = models.IntegerField()
それぞれの data 値が、並べ替えられたクエリセットに何回現れるかを数えたい場合は、次のようにします:
items = Item.objects.order_by("name")
# Warning: not quite correct!
items.values("data").annotate(Count("id"))
...これは Item オブジェクトを共通の data 値でグループ化し、各グループの id 値の数をカウントします。ただし、これはうまくいきません。 name によるソートもグループ化に影響するため、このクエリは (data, name) のペアをグループ化することになります。代わりに、次のようなクエリセットを作成する必要があります:
items.values("data").annotate(Count("id")).order_by()
...このようにクエリ内のソートをクリアします。たとえば、 data で並べ替えることもできますが、これはすでにクエリで役割を果たしているため、害はありません。
この動作は、 distinct() のクエリセットドキュメントで指摘されているのと同じで、一般的なルールも同じです。通常、結果に余分な列が影響を与えることは期待されないため、順序付けをクリアするか、少なくとも values() 呼び出しで選択するフィールドにのみ制限されていることを確認してください。
注釈
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 explicitly with order_by() (and we can't change those
other methods' behavior, as that would violate our
API の安定性 policy).
Default ordering not applied to GROUP BY
GROUP BY queries (for example, those using .values() and
.annotate()) don't use the model's default ordering.
Use order_by() explicitly when a given order is needed.
アノテーションを集計する¶
アノテーションの結果に対して集計を生成することもできます。aggregate() 句を定義するとき、提供する集計はクエリ内の annotate() 句の一部として定義された任意のエイリアスを参照できます。
たとえば、1冊あたりの著者数の平均を計算したい場合、まず著者数をアノテーション付きで本のセットに追加し、その後、アノテーションフィールドを参照して著者数を集計します。
>>> from django.db.models import Avg, Count
>>> Book.objects.annotate(num_authors=Count("authors")).aggregate(Avg("num_authors"))
{'num_authors__avg': 1.66}
空のクエリセットまたはグループでの集計¶
集計が空のクエリセットやグループに適用された場合、結果のデフォルトは通常「None」となる default パラメータになります。この動作は、実行されたクエリが行を返さない場合に集計関数が「NULL」を返すために発生します。
ほとんどの集計では、 default 引数を渡すことで戻り値を指定できます。しかし、 Count は default 引数をサポートしていないため、空のクエリセットやグループに対しては常に 0 を返します。
たとえば、書籍名に web を含む書籍がないと仮定して、この書籍セットの合計金額を計算すると、 Sum 集計を計算する一致する行がないため、 None を返します:
>>> from django.db.models import Sum
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price"))
{"price__sum": None}
しかし、 Sum を呼び出す際に default 引数を指定することで、本が見つからない場合に異なるデフォルト値を返すことができます:
>>> Book.objects.filter(name__contains="web").aggregate(Sum("price", default=0))
{"price__sum": Decimal("0")}
