DjangoのQuerySetでグループごとに最大・最小のデータのみ取得する

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ではクエリーの表現が多彩ですので、今回のようなサブクエリーも直感的に書けます。

ぜひ、業務の中でも活用していただけると嬉しいです（実行計画の確認はしてください）。