---
title: "アグリゲーション"
version: 3.0
locale: ja
source: https://docs.djangoproject.com/ja/3.0/topics/db/aggregation/
canonical: https://djangodocs.dev/ja/3.0/topics/db/aggregation/
---
# アグリゲーション

[Djangoのデータベース抽象API](/ja/3.0/topics/db/queries/) のトピックガイドでは、個別のオブジェクトの作成、取得、削除を行うDjangoのクエリの使い方を説明しました。しかし、オブジェクトのコレクションを *集計 (アグリゲーション)* した値や、集計することによって派生された値を取得しなければならないことがあります。 このトピックガイドはで、Django のクエリを使って集計値を生成して返す方法を説明します。

このガイドでは、以下のモデルを使用します。これらのモデルは、一連のオンライン書店の在庫を追跡するために使用されます。

```python
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.
>>> 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 では集約を生成するために 2 つの方法が用意されています。1 つめは、 全 `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}
```

`aggregate()` 句への引数は計算したい集約値を表します - この例では、 `Book` モデルの `price` フィールドの平均になります。 利用可能な集約関数の一覧は [QuerySet リファレンス](/ja/3.0/ref/models/querysets/#aggregation-functions) にあります。

`aggregate()` は `QuerySet` の最後の句になります。それが呼び出されると、name-value のペアの辞書が返されます。name は集計値に対する識別子です; valueは 計算された集計値です。 name はフィールド名と集計関数より自動的に生成されます。 集約値の name を手動で指定したい場合は、集約句を指定する際に名前を指定します:

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

2つ以上の集計を生成したい場合は、`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()` と同様に、注釈 (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()` を用いて複数の集計 (アグリゲーション) を統合することは、 [誤った結果を生み出します](https://code.djangoproject.com/ticket/10060)。サブクエリの代わりに結合(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`](/ja/3.0/ref/models/querysets/#django.db.models.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](/ja/3.0/topics/db/queries/#field-lookups-intro) を使えるようにしています。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'))
```

### 反対向きのリレーション

:ref:lookups-that-span-relationships\`と似たように、モデルのフィールドやモデルのリレーションに関する集計は"後ろ向きの"リレーションを含むことができます。ここでも小文字にしたモデル名と2つのアンダースコアが用いられます。

たとえば全ての出版社に対して、その出版社に対してリレーションをもつ本の数を集計することができます。（`'book'` という名前で `Publisher` -\> `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'のように長くなります。)

多対多これらが使用できるのは外部キーだけではありません。多対多のリレーションでも利用できます。例えば全ての著者に対して、それぞれが書いた本のページ数の合計を集計することができます。 (```'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 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.

> **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](#combining-multiple-aggregations).

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 default ordering or `order_by()`

> **Deprecated since Django 2.2**
>
> バージョン 2.2 で非推奨: Starting in Django 3.1, the ordering from a model's `Meta.ordering` won't
> be used in `GROUP BY` queries, such as `.annotate().values()`. Since
> Django 2.2, these queries issue a deprecation warning indicating to add an
> explicit `order_by()` to the queryset to silence the warning.

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()`](/ja/3.0/ref/models/querysets/#django.db.models.query.QuerySet.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.

> **Note**
>
> 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 の安定性](/ja/3.0/misc/api-stability/) 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}
```