Zenn
🎉

Djangoのモデルで集計とgroupby

2024/08/22に公開
django
tech

Djangoのモデルで集計とgroupby

すぐに忘れてしまうので、モデルを利用したクエリ処理をを備忘録としてまとめておく

集計

公式サイトでの開設はこちら
https://docs.djangoproject.com/ja/5.1/topics/db/aggregation/

ここでは、単純な集計とGROUPBYまで含めて集計として説明されます

aggregateとannotate

集計関連ではaggregateとannotateの2種類の方式があります。簡単に言うと、aggregateはディクショナリ形式でデータが返ってきて、単一のテーブルでの集計に利用します。annotateはGROUPBYが行われており、結合を伴い参照先のテーブルの集計に利用します

集計用の関数のインポート

from django.db.models import Count, Sum, Avg, Min, Max

aggregate

QuerySetに対して、計算された集計値 (平均、合計など) をディクショナリで返します

全件数

この場合、countと同じですが、とりあえず、aggregateでも実現可能です

Book.objects.aggregate(Count("id"))
{'id__count': 12}

SELECT COUNT(`book`.`id`) AS `id__count` FROM `book`

filterしてCount

priceが1500以上の件数を取得します

Book.objects.filter(price__gte=1500).aggregate(Count("id"))
{'id__count': 4}

SELECT
    COUNT(`book`.`id`) AS `id__count`
FROM
    `book`
WHERE
    `book`.`price` >= 1500

filterしてSum

priceが1500以上のpriceの合計を取得します

Book.objects.filter(price__gte=1500).aggregate(Sum("price"))
{'price__sum': 6300}

SELECT
    SUM(`book`.`price`) AS `price__sum`
FROM
    `book`
WHERE
    `book`.`price` >= 1500

filterしてAvg

priceが1200以上で、priceの平均を取得します

Book.objects.filter(price__gte=1200).aggregate(Avg("price"))
{'price__avg': 1388.8889}

SELECT
    AVG(`book`.`price`) AS `price__avg`
FROM
    `book`
WHERE
    `book`.`price` >= 1200

filterしてMax

2021年12月31日以前で、priceの最大値を取得します

Book.objects.filter(pub_date__lte="2021-12-31").aggregate(Max("price"))
{'price__max': 1800}

SELECT
    MAX(`book`.`price`) AS `price__max`
FROM
    `book`
WHERE
    `book`.`pub_date` <= '2021-12-31'

filterしてMin

2021年12月31日以前で、priceの最小値を取得します

Book.objects.filter(pub_date__lte="2021-12-31").aggregate(Min("price"))
{'price__min': 1000}

SELECT
    MIN(`book`.`price`) AS `price__min`
FROM
    `book`
WHERE
    `book`.`pub_date` <= '2021-12-31'

annotate

annotateとは「注釈をつける」ということ、言葉の解釈が難しいが、QuerySet の各オブジェクト（つまり、モデルインスタンス）に「注釈」をつけます。ここでいう注釈とは、リレーション先のオブジェクトに対して集計した結果（合計、件数、平均など）をつけることと解釈します

annotate単体でも利用できますが、valuesと併用することでGROUPBYを実行します。GROUPBYを行う場合、values().annotate()の形式になります

関連先のモデルを指定しないで、単体のモデルでも利用できそうですが、あまり意味のない結果を取得しているので、基本的には関係先のモデルと結合して利用することが想定される（想定）

1対多で多側の件数（1対多）

1対多で多側の件数を取得する

1側モデル名.objects.annotate(Count("多側モデル名"))

多側のモデル名は小文字で指定する

Author.objects.annotate(Count("book"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    COUNT(`book`.`id`) AS `book__count`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY
    `author`.`id`

+----+-------+-----+-------------+
| id | name  | age | book__count |
+----+-------+-----+-------------+
|  1 | Bob   |  20 |           2 |
|  2 | Tom   |  21 |           1 |
|  3 | Nancy |  25 |           3 |
|  4 | Meg   |  20 |           1 |
|  5 | Amy   |  24 |           5 |
|  6 | Cindy |  20 |           0 |
+----+-------+-----+-------------+

本来、Authorモデルには「book__count」という属性を定義しませんが、集計式を指定したキーワード引数を用いることで、集計情報の名前を付与します

属性名の変更（1対多）

上記の例では、Authorに関連するBookの件数を集計しています。件数の属性名は「book__count」です。キーワード引数を利用することで属性名を変更します

Author.objects.annotate(c=Count("book"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    COUNT(`book`.`id`) AS `c`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY
    `author`.`id`

+----+-------+-----+---+
| id | name  | age | c |
+----+-------+-----+---+
|  1 | Bob   |  20 | 2 |
|  2 | Tom   |  21 | 1 |
|  3 | Nancy |  25 | 3 |
|  4 | Meg   |  20 | 1 |
|  5 | Amy   |  24 | 5 |
|  6 | Cindy |  20 | 0 |
+----+-------+-----+---+

1対多で多側の特定のカラムの合計（1対多）

1対多で多側の特定のカラムで集計します。今回は、priceの合計を取得する

1側モデル名.objects.annotate(取得カラム名=Sum("多側モデル名__フィールド名"))

Author.objects.annotate(c=Sum("book__price"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    SUM(`book`.`price`) AS `c`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY `author`.`id`

+----+-------+-----+------+
| id | name  | age | c    |
+----+-------+-----+------+
|  1 | Bob   |  20 | 2200 |
|  2 | Tom   |  21 | 1500 |
|  3 | Nancy |  25 | 4200 |
|  4 | Meg   |  20 | 1200 |
|  5 | Amy   |  24 | 6400 |
|  6 | Cindy |  20 | NULL |
+----+-------+-----+------+

1対多で多側の特定のカラムの平均（1対多）

1対多で多側の特定のカラムで集計します。priceの平均を取得する

1側モデル名.objects.annotate(取得カラム名=Avg("多側モデル名__フィールド名"))

Author.objects.annotate(c=Avg("book__price"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    AVG(`book`.`price`) AS `c`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY
    `author`.`id`

+----+-------+-----+-----------+
| id | name  | age | c         |
+----+-------+-----+-----------+
|  1 | Bob   |  20 | 1100.0000 |
|  2 | Tom   |  21 | 1500.0000 |
|  3 | Nancy |  25 | 1400.0000 |
|  4 | Meg   |  20 | 1200.0000 |
|  5 | Amy   |  24 | 1280.0000 |
|  6 | Cindy |  20 |      NULL |
+----+-------+-----+-----------+

複数の集計を取得する（1対多）

annotateの引数で複数の集計を指定します。Authorからみて関連するBookのpriceの件数、Bookのpriceの合計、Bookのpriceの平均を取得します

Author.objects.annotate(
    c=Count("book__price"),
    s=Sum("book__price"),
    a=Avg("book__price"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    COUNT(`book`.`price`) AS `c`,
    SUM(`book`.`price`) AS `s`,
    AVG(`book`.`price`) AS `a`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY
    `author`.`id`

+----+-------+-----+---+------+-----------+
| id | name  | age | c | s    | a         |
+----+-------+-----+---+------+-----------+
|  1 | Bob   |  20 | 2 | 2200 | 1100.0000 |
|  2 | Tom   |  21 | 1 | 1500 | 1500.0000 |
|  3 | Nancy |  25 | 3 | 4200 | 1400.0000 |
|  4 | Meg   |  20 | 1 | 1200 | 1200.0000 |
|  5 | Amy   |  24 | 5 | 6400 | 1280.0000 |
|  6 | Cindy |  20 | 0 | NULL |      NULL |
+----+-------+-----+---+------+-----------+

1対多で多側のDateフィールドの最大値（新しい日付）（1対多）

Authorと関連するBookのpub_dateの最大値、つまりもっと最新の日付を取得します。

Author.objects.annotate(new_pub=Max("book__pub_date"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    MAX(`book`.`pub_date`) AS `new_pub`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY
    `author`.`id`

+----+-------+-----+------------+
| id | name  | age | new_pub    |
+----+-------+-----+------------+
|  1 | Bob   |  20 | 2020-05-05 |
|  2 | Tom   |  21 | 2021-10-10 |
|  3 | Nancy |  25 | 2021-08-15 |
|  4 | Meg   |  20 | 2022-03-10 |
|  5 | Amy   |  24 | 2022-10-10 |
|  6 | Cindy |  20 | NULL       |
+----+-------+-----+------------+

Maxを使うと最大値、Date、Datetimeなら新しい日付を集計します
Minを使うと最小値、Date、Datetimeなら古い日付を集計します

Date型の集計カラムから年だけを抽出（1対多）

Authorと関連するBookのpub_dateの最大値、つまりもっと最新の日付を取得します。さらに最新のpub_dateから「年」のみを抽出します。つまり、Authorごとの最新刊の発行年を取得します

Author.objects.annotate(new_year=Max("book__pub_date__year"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    MAX(EXTRACT(YEAR FROM `book`.`pub_date`)) AS `new_year`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
GROUP BY
    `author`.`id`

+----+-------+-----+----------+
| id | name  | age | new_year |
+----+-------+-----+----------+
|  1 | Bob   |  20 |     2020 |
|  2 | Tom   |  21 |     2021 |
|  3 | Nancy |  25 |     2021 |
|  4 | Meg   |  20 |     2022 |
|  5 | Amy   |  24 |     2022 |
|  6 | Cindy |  20 |     NULL |
+----+-------+-----+----------+

filterしてannotateする（1対多）

1対多でAuthorのageが21以下で関連するbookの件数を取得します

Author.objects.filter(age__lte=21).annotate(c=Count("book"))

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    COUNT(`book`.`id`) AS `c`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
WHERE
    `author`.`age` <= 21
GROUP BY `author`.`id`

+----+-------+-----+---+
| id | name  | age | c |
+----+-------+-----+---+
|  1 | Bob   |  20 | 2 |
|  2 | Tom   |  21 | 1 |
|  4 | Meg   |  20 | 1 |
|  6 | Cindy |  20 | 0 |
+----+-------+-----+---+

annotate後のfilter

annotateで集計後、filterします。この場合、annotateの結果に対してfilterすることが可能になります。1対多でAuthorのageが21以下で関連するbookの件数を取得します。件数が2以上を取得します。SQLではHAVINGになります

Author.objects.filter(age__lte=21).annotate(c=Count("book")).filter(c__gte=2)

SELECT
    `author`.`id`,
    `author`.`name`,
    `author`.`age`,
    COUNT(`book`.`id`) AS `c`
FROM
    `author`
LEFT OUTER JOIN
    `book`
ON
    (`author`.`id` = `book`.`author_id`)
WHERE
    `author`.`age` <= 21
GROUP BY
    `author`.`id`
HAVING
    COUNT(`book`.`id`) >= 2

+----+------+-----+---+
| id | name | age | c |
+----+------+-----+---+
|  1 | Bob  |  20 | 2 |
+----+------+-----+---+

多側のみでGROUPBY（多対1だが、多のみ利用）

BookはAuthorに対して多側。多側のBookでAuthorごとの集計を行う。つまりBookでのAuthorごとのGROUPBYを行います。ここでは、Bookに関連するAuthorの件数を取得します

多側モデル名.objects.values("1側フィールド名").annotate(Count("1側フィールド名"))

多側モデル単体でのGROUPBYを行います。values().annotate()の場合、ディクショナリ形式のQuerySetが返ります

Book.objects.values("author").annotate(Count("author"))

SELECT
    `book`.`author_id`,
    COUNT(`book`.`author_id`) AS `author__count`
FROM
    `book`
GROUP BY
    `book`.`author_id`

<QuerySet [
    {'author': 1, 'author__count': 2},
    {'author': 2, 'author__count': 1},
    {'author': 3, 'author__count': 3},
    {'author': 4, 'author__count': 1},
    {'author': 5, 'author__count': 5}
]>

+-----------+---------------+
| author_id | author__count |
+-----------+---------------+
|         1 |             2 |
|         2 |             1 |
|         3 |             3 |
|         4 |             1 |
|         5 |             5 |
+-----------+---------------+

多側でGROUPBY（多対1だが、多のみ利用）

Bookの関連するAuthorでGROUPBY、priceの合計を集計する。つまりAuthorごとのpriceの合計値を取得します

Book.objects.values("author").annotate(total=Sum("price"))

SELECT
    `book`.`author_id`,
    SUM(`book`.`price`) AS `total`
FROM
    `book`
GROUP BY
    `book`.`author_id`

<QuerySet [
    {'author': 1, 'total': 2200},
    {'author': 2, 'total': 1500},
    {'author': 3, 'total': 4200},
    {'author': 4, 'total': 1200},
    {'author': 5, 'total': 6400}
]>

+-----------+-------+
| author_id | total |
+-----------+-------+
|         1 |  2200 |
|         2 |  1500 |
|         3 |  4200 |
|         4 |  1200 |
|         5 |  6400 |
+-----------+-------+

多側でGROUPBY（多対1だが、多のみ利用）

Bookの関連するAuthorでGROUPBY、Authorごとの件数、priceの合計、priceの最小値、priceの最大値を集計する。

Book.objects \
    .values("author") \
    .annotate(
        count=Count("author"),
        total=Sum("price"),
        min=Min("price"),
        max=Max("price"))

SELECT
    `book`.`author_id`,
    COUNT(`book`.`author_id`) AS `count`,
    SUM(`book`.`price`) AS `total`,
    MIN(`book`.`price`) AS `min`,
    MAX(`book`.`price`) AS `max`
FROM
    `book`
GROUP BY
    `book`.`author_id`

<QuerySet [
    {'author': 1, 'count': 2, 'total': 2200, 'min': 1000, 'max': 1200},
    {'author': 2, 'count': 1, 'total': 1500, 'min': 1500, 'max': 1500},
    {'author': 3, 'count': 3, 'total': 4200, 'min': 1000, 'max': 1800},
    {'author': 4, 'count': 1, 'total': 1200, 'min': 1200, 'max': 1200},
    {'author': 5, 'count': 5, 'total': 6400, 'min': 1000, 'max': 1500}
]>

+-----------+-------+-------+------+------+
| author_id | count | total | min  | max  |
+-----------+-------+-------+------+------+
|         1 |     2 |  2200 | 1000 | 1200 |
|         2 |     1 |  1500 | 1500 | 1500 |
|         3 |     3 |  4200 | 1000 | 1800 |
|         4 |     1 |  1200 | 1200 | 1200 |
|         5 |     5 |  6400 | 1000 | 1500 |
+-----------+-------+-------+------+------+

多側でGROUPBY（多対1で1側のカラムも利用）

Bookの関連するAuthorでGROUPBY、Authorごとの件数を集計、Author.nameを取得します。valuesでauthor、author__nameを指定する

多側モデル名.objects
.values("1側フィールド名", "1側フィールド名__フィールド名")
.annotate(Count("1側フィールド名"))

Bookで、AuthorごとのBookの件数とAuthorの名前を取得する

Book.objects.values("author","author__name").annotate(count=Count("author"))

SELECT
    `book`.`author_id`,
    `author`.`name`,
    COUNT(`book`.`author_id`) AS `count`
FROM
    `book`
INNER JOIN
    `author`
ON
    (`book`.`author_id` = `author`.`id`)
GROUP BY
    `book`.`author_id`,
    `author`.`name`

<QuerySet [
    {'author': 1, 'author__name': 'Bob', 'count': 2},
    {'author': 2, 'author__name': 'Tom', 'count': 1},
    {'author': 3, 'author__name': 'Nancy', 'count': 3},
    {'author': 4, 'author__name': 'Meg', 'count': 1},
    {'author': 5, 'author__name': 'Amy', 'count': 5}
]>

+-----------+-------+-------+
| author_id | name  | count |
+-----------+-------+-------+
|         1 | Bob   |     2 |
|         2 | Tom   |     1 |
|         3 | Nancy |     3 |
|         4 | Meg   |     1 |
|         5 | Amy   |     5 |
+-----------+-------+-------+

select_relatedの有無に関わらず、同じSQLになります。以下の2個は同じSQLを発行します

Book.objects.values("author","author__name").annotate(count=Count("author"))
Book.objects.select_related("author").values("author","author__name").annotate(count=Count("author"))

select_relatedはなくてもよさそうだが、付けたほうが意図が分かりやすいかも。どちらにせよ、valuesdで「1側フィールド名__1側のフィールド」を指定します

annotateの後にvalues

Bookの関連するAuthorでGROUPBY、Authorごとの件数、priceでの最小値、priceの最大値を集計します。再度、valuesを指定します。valuesでは、annotateで集計したカラムを含めて出力するカラムを制限します。valuesなので戻り値としてディクショナリ形式で取得します

Bookの関連するAuthorでGROUPBYして、Authorごと件数、priceの最小値、priceの最大値を集計します。出力するのは、author.name, authonr.age, count, min, maxです

Book.objects.values("author","author__name") \
    .annotate(count=Count("author"), min=Min("price"), max=Max("price")) \
    .values("author__name","author__age","count","min","max")

SELECT
    `author`.`name`,
    `author`.`age`,
    COUNT(`book`.`author_id`) AS `count`,
    MIN(`book`.`price`) AS `min`,
    MAX(`book`.`price`) AS `max`
FROM
    `book`
INNER JOIN
    `author`
ON
    (`book`.`author_id` = `author`.`id`)
GROUP BY
    `book`.`author_id`,
    `author`.`name`,
    `author`.`age`

<QuerySet [
{'author__name': 'Bob', 'author__age': 20, 'count': 2, 'min': 1000, 'max': 1200},
{'author__name': 'Tom', 'author__age': 21, 'count': 1, 'min': 1500, 'max': 1500},
{'author__name': 'Nancy', 'author__age': 25, 'count': 3, 'min': 1000, 'max': 1800},
{'author__name': 'Meg', 'author__age': 20, 'count': 1, 'min': 1200, 'max': 1200},
{'author__name': 'Amy', 'author__age': 24, 'count': 5, 'min': 1000, 'max': 1500}
]>

+-------+-----+-------+------+------+
| name  | age | count | min  | max  |
+-------+-----+-------+------+------+
| Bob   |  20 |     2 | 1000 | 1200 |
| Tom   |  21 |     1 | 1500 | 1500 |
| Nancy |  25 |     3 | 1000 | 1800 |
| Meg   |  20 |     1 | 1200 | 1200 |
| Amy   |  24 |     5 | 1000 | 1500 |
+-------+-----+-------+------+------+

Dateフィールドの年でGROUPBY

ここでは結合を行わない。Dateフィールド、Datetimeフィールドの「年」でGROUPBYします。つまり、年ごとの本の件数です

Book.objects.values("pub_date__year").annotate(count=Count("author"))

SELECT
    EXTRACT(YEAR FROM `book`.`pub_date`) AS `pub_date__year`,
    COUNT(`book`.`author_id`) AS `count`
FROM
    `book`
GROUP BY
    1

<QuerySet [
    {'pub_date__year': 2020, 'count': 4},
    {'pub_date__year': 2021, 'count': 2},
    {'pub_date__year': 2022, 'count': 6}
]>

+----------------+-------+
| pub_date__year | count |
+----------------+-------+
|           2020 |     4 |
|           2021 |     2 |
|           2022 |     6 |
+----------------+-------+

Discussion