Django按Owner分组后，如何获取最小report_date对应的Balance字段？

背景信息 #

模型定义 #

class Report(models.Model):
    owner = models.ForeignKey(
        to=Owner,
        on_delete=models.CASCADE,
        related_name='data',
    )
    balance = models.PositiveIntegerField()
    report_date = models.DateField()

现有数据 #

<QuerySet [
    {'owner': 1, 'balance': 100, 'report_date': datetime.date(2023, 3, 4)},
    {'owner': 1, 'balance': 50, 'report_date': datetime.date(2023, 3, 9)},
    {'owner': 2, 'balance': 1000, 'report_date': datetime.date(2023, 2, 2)},
    {'owner': 2, 'balance': 2000, 'report_date': datetime.date(2023, 2, 22)}
]>

当前聚合查询（仅获取owner和最小report_date） #

Report.objects.values('owner').annotate(min_report_date=Min('report_date')).values('owner', 'min_report_date')

查询结果：

<QuerySet [
    {'owner': 1, 'min_report_date': datetime.date(2023, 3, 4)},
    {'owner': 2, 'min_report_date': datetime.date(2023, 2, 2)}
]>

问题 #

需要在返回owner和min_report_date的同时，获取对应最小report_date的balance字段，期望结果：

<QuerySet [
    {'owner': 1, 'min_report_date': datetime.date(2023, 3, 4), 'balance': 100},
    {'owner': 2, 'min_report_date': datetime.date(2023, 2, 2), 'balance': 1000}
]>

直接在values中添加balance会丢失聚合效果，返回所有数据行：

Report.objects.values('owner').annotate(min_report_date=Min('report_date')).values('owner', 'min_report_date', 'balance')

结果：

<QuerySet [
    {'owner': 1, 'balance': 50, 'min_report_date': datetime.date(2023, 3, 9)},
    {'owner': 1, 'balance': 100, 'min_report_date': datetime.date(2023, 3, 4)},
    {'owner': 2, 'balance': 2000, 'min_report_date': datetime.date(2023, 2, 22)},
    {'owner': 2, 'balance': 1000, 'min_report_date': datetime.date(2023, 2, 2)}
]>

解决方案 #

方法1：使用Subquery和OuterRef #

通过子查询筛选每个owner对应最小report_date的balance：

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

subquery = Report.objects.filter(
    owner=OuterRef('owner')
).order_by('report_date').values('balance')[:1]

result = Report.objects.values('owner').annotate(
    min_report_date=Min('report_date'),
    balance=Subquery(subquery)
).values('owner', 'min_report_date', 'balance')

方法2：使用窗口函数（Django 2.0+） #

利用窗口函数给每个owner的记录按report_date排序，筛选第一条（最早记录）：

from django.db.models import Window, F
from django.db.models.functions import RowNumber

# 给每个owner的记录按report_date升序生成行号
ranked_reports = Report.objects.annotate(
    row_num=Window(
        expression=RowNumber(),
        partition_by=[F('owner')],
        order_by=F('report_date').asc()
    )
)

# 筛选行号为1的记录
result = ranked_reports.filter(row_num=1).values('owner', 'report_date', 'balance').annotate(min_report_date=F('report_date'))

方法3：关联筛选（Django 3.2+） #

先聚合最小日期，再筛选对应日期的记录：

from django.db.models import Min, F

result = Report.objects.values('owner').annotate(
    min_report_date=Min('report_date')
).filter(report_date=F('min_report_date')).values('owner', 'min_report_date', 'balance')

验证结果 #

上述方法均可得到期望的查询结果：

<QuerySet [
    {'owner': 1, 'min_report_date': datetime.date(2023, 3, 4), 'balance': 100},
    {'owner': 2, 'min_report_date': datetime.date(2023, 2, 2), 'balance': 1000}
]>

内容的提问来源于stack exchange，提问作者Ashkan Khademian