逆引きDjangoORM: グループ内の最新行をN件取得するSQL

要件

状態が更新されるとレコードが追加されるテーブルがある

7日分の、各日の最終状態を取得したい

Django ORMを使う

status テーブルのデータイメージ

table:status

id status created_at

100 未実施 2022-02-07 10:00:00

101 実行中 2022-02-07 10:00:00

102 成功 2022-02-07 10:00:00

103 未実施 2022-02-08 10:00:00

104 実行中 2022-02-08 10:30:00

105 失敗 2022-02-08 11:30:00

106 実行中 2022-02-08 13:30:00

107 成功 2022-02-08 15:00:00

108 未実施 2022-02-09 10:00:00

109 実行中 2022-02-09 10:00:00

このデータ例では id=102, 107, 109 を取得したい

SQL検討

sql - SQLで特定のグループに所属するユーザーの最新の投稿1件を取得したい - スタック・オーバーフローを参考にしました。

4つのアプローチ

1. INNER JOIN で自己結合

2. EXISTS

3. LEFT OUTER JOIN で自己結合

4. WINDOW関数

1. INNER JOIN で自己結合

グループ内の最新日時をサブクエリで取得し、 INNER JOIN で絞り込む

code:sql

SELECT *

FROM status

INNER JOIN (

SELECT MAX(created_at) -- グループ内の最新

FROM status

GROUP BY created_at::date -- 同じ日でグループ化

) AS s2 ON s2.created_at = status.created_at

2. EXISTS

「最新の行」を「自身より新しい行が存在しない行」と考える

code:sql

SELECT *

FROM status

WHERE NOT EXISTS (

SELECT *

FROM status AS s2

WHERE

s2.created_at::date = status.created_at::date -- 同じ日

AND

s2.created_at > status.created_at -- 自身より新しい行がない

)

EXISTS句が相関サブクエリになっている

3. LEFT OUTER JOINで自己結合

相関サブクエリを避けるため、 NOT EXISTS をLEFT OUTER JOINに置き換える

code:sql

SELECT *

FROM status

LEFT OUTER JOIN status AS s2 ON

s2.created_at::date = status.created_at::date -- 同じ日

AND

s2.created_at > status.created_at -- 自身より新しい行をJOIN

WHERE s2.created_at IS NULL -- 新しい行がない（つまり最新）

4. SQL: Window関数

code:sql

SELECT DISTINCT

FIRST_VALUE(id) OVER (PARTITION BY created_at::date ORDER BY created_at DESC) AS id,

FIRST_VALUE(status) OVER (PARTITION BY created_at::date ORDER BY created_at DESC) AS status,

FIRST_VALUE(created_at) OVER (PARTITION BY created_at::date ORDER BY created_at DESC) AS craeted_at

FROM status

-- PARTITION BY created_at::date で、日ごと

-- ORDER BY created_at DESC の FIRST_VALUEで、最新

-- 同じ日のデータ数行分が同じ値になるため、DISTINCTで1つにする

ムダが多い気もしないでもない

Django ORM

Django ORMで実装しやすいのはどれ？

1. INNER JOIN で自己結合 -> FKのないJOINが難しい

2. EXISTS -> 可能

3. LEFT OUTER JOIN で自己結合 -> FKの無いJOINが難しい

4. WINDOW関数

FKのないJOIN

参考: Django + SQLAlchemy: シンプルWay

extrasを使えばできるかも？ただし読みづらいし、extrasは廃止予定

ForeignObjectをモデルに定義すればJOINできるが、ドキュメント化されていない。モデルに定義したくない

FilteredRelationはannotateに使えるが、今回の絞り込みに使う方法が分からない

参考: Self join with django ORM - Stack Overflow

code:python

table=SomeModel._meta.db_table

join_column_1=SomeModel._meta.get_field('field1').column

join_column_2=SomeModel._meta.get_field('field2').column

join_queryset=SomeModel.objects.filter()

# Force evaluation of query

querystr=join_queryset.query.__str__()

# Add promote=True and nullable=True for left outer join

rh_alias=join_queryset.query.join((table,table,join_column_1,join_column_2))

# Add the second conditional and columns

join_queryset=join_queryset.extra(select=dict(rhs_col1='%s.%s' % (rhs,join_column_2)),

where='%s.%s = %s.%s' % (table,join_column_2,rh_alias,join_column_1))

EXISTS

実装できた

code:python

from django.db.models import Exists, OuterRef

from django.db.models.functions import TruncDate

sub = Status.objects.annotate(

created_date=TruncDate('created_at')

).filter(

created_date=OuterRef('created_date'),

created_at__gt=OuterRef('created_at')

)

q = Status.objects.annotate(

created_date=TruncDate('created_at')

).filter(

~Exists(sub)

).values('id', 'created_date', 'status')

q = q.order_by('-created_at'):7

code:sql

SELECT

"status"."id",

"status"."status",

("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date AS "created_date"

FROM "status"

WHERE NOT EXISTS(

SELECT (1) AS "a"

FROM "status" U0

WHERE (

U0."created_at" > "status"."created_at"

AND

(U0."created_at" AT TIME ZONE 'Asia/Tokyo')::date = ("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date

) LIMIT 1

) ORDER BY "status"."created_at" DESC LIMIT 7

code:explain

Limit (cost=24.34..24.36 rows=7 width=568)

-> Sort (cost=24.34..24.46 rows=47 width=568)

Sort Key: status.created_at DESC

-> Hash Anti Join (cost=11.57..23.45 rows=47 width=568)

Hash Cond: ((timezone('Asia/Tokyo'::text, status.created_at))::date = (timezone('Asia/Tokyo'::text, u0.created_at))::date)

Join Filter: (u0.created_at > status.created_at)

-> Seq Scan on status (cost=0.00..10.70 rows=70 width=564)

-> Hash (cost=10.70..10.70 rows=70 width=8)

-> Seq Scan on status u0 (cost=0.00..10.70 rows=70 width=8)

公式 https://docs.djangoproject.com/ja/3.2/ref/models/expressions/#exists-subqueries

code:python

>> from django.db.models import Exists, OuterRef

>> from datetime import timedelta

>> from django.utils import timezone

>> one_day_ago = timezone.now() - timedelta(days=1)

>> recent_comments = Comment.objects.filter(

... post=OuterRef('pk'),

... created_at__gte=one_day_ago,

... )

>> Post.objects.annotate(recent_comment=Exists(recent_comments))

You can query using NOT EXISTS with ~Exists().

参考 Django3系のORMでSQLのEXISTS句を使う - メモ的な思考的な

code:python

Color.objects.filter(

Exists(Apple.objects.filter(color=OuterRef('pk')))

)

Window関数

実装できた

code:python

from django.db.models import F

from django.db.models.functions import TruncDate

window = {

'partition_by': TruncDate('created_at'),

'order_by': F('created_at').desc(),

}

q = Status.objects.annotate(

id_ = Window(expression=FirstValue(F('id')), **window),

date = TruncDate('created_at'),

status_ = Window(expression=FirstValue(F('status')), **window),

).values('id_', 'date', 'status_').distinct()

q = q.order_by(TruncDate('created_at').desc()):7

code:sql

SELECT DISTINCT

FIRST_VALUE("status"."id") OVER (PARTITION BY ("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date ORDER BY "status"."created_at" DESC) AS "id_",

("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date AS "date",

FIRST_VALUE("status"."event_name") OVER (PARTITION BY ("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date ORDER BY "status"."created_at" DESC) AS "status_",

FROM "status"

ORDER BY ("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date DESC LIMIT 7

code:explain

Limit (cost=17.44..17.53 rows=7 width=84)

-> Unique (cost=17.44..18.32 rows=70 width=84)

-> Sort (cost=17.44..17.62 rows=70 width=84)

Sort Key: ((timezone('Asia/Tokyo'::text, created_at))::date) DESC, (first_value(id) OVER (?)), (first_value(status) OVER (?)))

-> WindowAgg (cost=13.20..15.30 rows=70 width=84)

-> Sort (cost=13.20..13.37 rows=70 width=568)

Sort Key: ((timezone('Asia/Tokyo'::text, created_at))::date), created_at DESC

-> Seq Scan on status (cost=0.00..11.05 rows=70 width=568)

参考 DjangoORMでウィンドウ関数を使おう - mizzsugar’s blog

公式 https://docs.djangoproject.com/ja/3.2/ref/models/database-functions/#window-functions

日時から日付を取得

TruncDate を使う

公式 https://docs.djangoproject.com/ja/3.2/ref/models/database-functions/#django.db.models.functions.TruncDate

Subqueryを使ってINNER JOINにより絞り込みっぽいもの

実装できた

code:python

from django.db.models import OuterRef, Subquery, Max

from django.db.models.functions import TruncDate

sub = Status.objects.annotate(

created_date=TruncDate('created_at')

).filter(

created_date=OuterRef('created_date'),

).order_by().values('created_date') # モデルのデフォルトソート条件を削除

newest_status = sub.annotate(newest=Max('created_at')).values('newest')

q = Status.objects.annotate(

created_date=TruncDate('created_at')

).filter(

created_at=Subquery(newest_status)

).values('id', 'created_date', 'status')

q = q.order_by('-created_at'):7

code:sql

SELECT

"status"."id",

"status"."status",

("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date AS "created_date"

FROM "status"

WHERE "status"."created_at" = (

SELECT MAX(U0."created_at") AS "newest"

FROM "status" U0

WHERE (U0."created_at" AT TIME ZONE 'Asia/Tokyo')::date = ("status"."created_at" AT TIME ZONE 'Asia/Tokyo')::date

GROUP BY (U0."created_at" AT TIME ZONE 'Asia/Tokyo')::date

)

ORDER BY "status"."created_at" DESC LIMIT 7

code:sql

Limit (cost=822.89..822.89 rows=1 width=568)

-> Sort (cost=822.89..822.89 rows=1 width=568)

Sort Key: status.created_at DESC

-> Seq Scan on status (cost=0.00..822.88 rows=1 width=568)

Filter: (created_at = (SubPlan 1))

SubPlan 1

-> GroupAggregate (cost=0.00..11.60 rows=1 width=12)

Group Key: (timezone('Asia/Tokyo'::text, u0.created_at))::date

-> Seq Scan on status u0 (cost=0.00..11.58 rows=1 width=12)

Filter: ((timezone('Asia/Tokyo'::text, created_at))::date = (timezone('Asia/Tokyo'::text, status.created_at))::date)

shimizukawa.icon WHERE句のサブクエリ、コスト高っ！

公式: https://docs.djangoproject.com/ja/3.2/ref/models/expressions/#using-aggregates-within-a-subquery-expression

The initial filter(...) limits the subquery to the relevant parameters. order_by() removes the default ordering (if any) on the Comment model. values('post') aggregates comments by Post. Finally, annotate(...) performs the aggregation. The order in which these queryset methods are applied is important. In this case, since the subquery must be limited to a single column, values('total') is required. This is the only way to perform an aggregation within a Subquery, as using aggregate() attempts to evaluate the queryset (and if there is an OuterRef, this will not be possible to resolve).

DeepL翻訳:

最初の filter(...) は、サブクエリを関連するパラメータに制限します。 order_by() は、Comment モデルのデフォルトの順序付けを (もしあれば) 削除します。 values('post') は、Post によってコメントを集約します。最後に、annotate(...) は集約を実行します。これらの queryset メソッドが適用される順序は重要です。この場合、サブクエリはひとつのカラムに限定しなければならないので、 values('total') が必要となります。 aggregate() を使用すると、クエリセットを評価しようとするため (そして OuterRef がある場合、これは解決できません)、これがサブクエリ内で集約を実行する唯一の方法です。

タグ

SQL

Django

逆引きDjangoORM