Django ORM实现带窗口函数的子查询问题求助

核心问题在于：Django ORM的Window函数默认会基于过滤后的数据集计算窗口聚合值，但你需要的是和原生SQL一致的逻辑——先基于全表数据计算每个日期日序（doy）的平均foo值，再将该值关联到对应记录上，即使后续过滤数据，平均值也不受过滤影响。

要实现等价逻辑，需先计算全表每个doy的平均foo，再关联到主查询记录中，而非直接用窗口函数计算。以下是两种可行方式：

方法一：子查询（Subquery）+ 外部引用（OuterRef） #

完全基于Django ORM实现，无需手写原生SQL：

from django.db.models import Subquery, Avg, OuterRef
from django.db.models.functions import Extract, Round

# 子查询：计算全表中每个doy对应的平均foo值
doy_avg_subquery = MyModel.objects.filter(
    date__doy=OuterRef('date__doy')
).values('date__doy').annotate(
    avg_foo_val=Avg('foo')
).values('avg_foo_val')

# 主查询：为每条记录关联对应doy的全表平均foo，并取整
queryset = MyModel.objects.annotate(
    avg_foo=Round(Subquery(doy_avg_subquery))
)

# 过滤数据时直接添加filter，avg_foo仍基于全表计算
filtered_queryset = queryset.filter(date__range=['2023-01-01', '2023-12-31'])

方法二：RawSQL（ORM子查询复杂时可选） #

若子查询写法不符合习惯，可直接用RawSQL实现原生窗口逻辑：

from django.db.models import Func, F
from django.db.models.functions import Round

queryset = MyModel.objects.annotate(
    avg_foo=Round(
        Func(
            F('foo'),
            function='AVG',
            template='%(function)s(%(expressions)s) OVER (PARTITION BY DATE_PART(\'doy\', date))'
        )
    )
)

注意：需确保数据库兼容DATE_PART函数（如PostgreSQL），MySQL等数据库需替换为DAYOFYEAR函数。

你之前的Window函数写法：

MyModel.objects.annotate(
    avg_foo=Round(
        Window(
            expression=Avg('foo'),
            partition_by=Extract('date', 'doy')
        )
    )
)

添加filter时，Django会先执行过滤，再在过滤后的数据集上做窗口聚合，得到的是过滤后数据中同doy的平均foo值，和原生SQL先算全表平均再处理的逻辑完全不同，因此结果不一致。

内容的提问来源于stack exchange，提问作者Evgenii Khlystov