DjangoのQuerySetでグループごとに最大・最小のデータのみ抽出する方法を紹介します。
この記事はDjango Advent Calendar 2021 3日目の記事です。
グループごとの最大・最小のデータとは何でしょうか？
たとえば以下のような場合に必要となります。
- ブログ記事ごとに最新のコメントのみ取得
- ユーザーごとに金額が最大の購入履歴のみ取得
- ページごとにリビジョン番号が最大の差分データのみ取得
実際に仕事をしているとたまに欲しくなりますよね。
今回は Parent モデルというグループごとに、 Child モデルの number が最大になる Child の一覧を取得します。
class Parent(models.Model):
pass
class Child(models.Model):
parent = models.ForeignKey(Parent)
number = models.IntegerField()
（Meta や __str__ は表記上省略）
QuerySetでグループごとに最大・最小のみ取得する
最大のみ取得する処理は以下のようになります（解説は後述します）。
children = Child.objects.all()
sub_qs = children.filter(
parent=models.OuterRef("parent"),
number__gt=models.OuterRef("number"),
)
qs = children.filter(~models.Exists(sub_qs))
実際に動作を確認してみましょう。
p1、p2というグループごとに最大の値を持つ c1_4 と c2_2 のデータを作りました。
>>> p1 = Parent.objects.create()
>>> p2 = Parent.objects.create()
>>> c1_1 = Child.objects.create(parent=p1, number=1)
>>> c1_2 = Child.objects.create(parent=p1, number=2)
>>> c1_3 = Child.objects.create(parent=p1, number=3)
>>> c1_4 = Child.objects.create(parent=p1, number=4)
>>> c2_1 = Child.objects.create(parent=p2, number=1)
>>> c2_2 = Child.objects.create(parent=p2, number=2)
>>> children.filter(~models.Exists(sub_qs))
<QuerySet [<Child: 1-4>, <Child: 2-2>]>
Child: 1-4（c1_4）とChild: 2-2（c2_2）の取得を確認できました。
SQLは以下のようになりました（読みやすいよう改行を追加しています）。
SELECT "child"."id", "child"."parent_id", "child"."number"
FROM "child"
WHERE NOT EXISTS(
SELECT (1) AS "a"
FROM "child" U0
WHERE (
U0."number" > "child"."number" AND
U0."parent_id" = "child"."parent_id"
) LIMIT 1
)
この処理では「グループごとに最大・最小の値が複数ある場合に両方とも取得される」点に注意してください。
解説
今回のSQLでは「よりnumberが高い値のある行を除外する」という方法で、グループごとの最大値を取得しています。直感的にはGROUP BYで最大値を計算するサブクエリーを書きたいところですが、実行速度が速い方法を紹介しました。
Djangoではこのサブクエリーを書くために、 OuterRef と Exists を使っています。OuterRef を使えば外側のクエリー中のフィールドを参照できますので、サブクエリーを簡単に書けます。
今回の場合は OuterRef("parent") でグループにする Parent を指定し、 OuterRef("number") で除外対象の number を指定しています。
実際の環境で高速に動作するかどうかは、データベースの実行計画を確認してください。
データベースやデータの内容によって変わってくると思いますので、仕事の環境で使う場合は実行計画を見ましょう。
GROUP BYを使った書き方も紹介しておきます。
他の書き方
参考として、GROUP BYとサブクエリーを使った方法も紹介します。
children = Child.objects.all()
sub_qs = children\
.filter(parent=models.OuterRef("parent")) \
.values("parent") \
.annotate(max_number=models.Max("number")) \
.values("max_number")
qs = children.filter(number=models.Subquery(sub_qs))
SQLは以下のようになります。
SELECT "child"."id", "child"."parent_id", "child"."number"
FROM "child"
WHERE "child"."number" = (
SELECT MAX(U0."number") AS "max_number"
FROM "child" U0
WHERE U0."parent_id" = "child"."parent_id"
GROUP BY U0."parent_id"
)
この処理はサブクエリー内で number の最大値を求めたうえで、その値を条件として Child の一覧を取得しています。こちらのほうが NOT EXISTS を使った書き方よりも理解しやすいですが、処理としては遅くなると思います（実際のデータや環境でお試しください）。
おわりに
Djangoで OuterRef や Subquery 、 Exists を使ってグループごとに最大・最小の値のみを取得しました。
昔は「Djangoで凝ったSQLを書けない」という印象もありましたが、今のDjangoではクエリーの表現が多彩ですので、今回のようなサブクエリーも直感的に書けます。
ぜひ、業務の中でも活用していただけると嬉しいです
（実行計画の確認はしてください）。
ちょっと聞いてください
このブログ記事は私の会社で開発しているShodo（https://shodo.ink/）を使って執筆されました。
Shodoは書いた文章をAIがリアルタイムにチェックしてくれるWebサービスです。
ただのAI校正ツールではありません。
エンジニアの皆さまにも嬉しい特徴がたくさんあります。
と、お勧めできます。
さらにGitHubのようなワークフローで記事を書けるのも特徴です：
- 記事を書く日程やステータスを管理したり
- 複数人で記事のレビューコメントやレビュー依頼をしたり
- 共有リンクからたくさんの人にコメントをもらったり
そんな便利なWebサービスです。
今ならなんと！技術アドベントカレンダーの期間中、Shodoを無料で使えるキャンペーンを実施しています。
これから技術ブログを書く皆さん、ぜひShodoで技術記事を書いて知識やノウハウを共有しませんか？
クーポンコードはこちらから。
ぜひお試しください。
blog.shodo.ink
執筆：Kiyohara Hiroki (@hirokiky)
（Shodoで執筆されました）
