Djangoのモデルで集計とgroupby
Djangoのモデルで集計とgroupby
すぐに忘れてしまうので、モデルを利用したクエリ処理をを備忘録としてまとめておく
集計
公式サイトでの開設はこちら
ここでは、単純な集計と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