在 django 中使用窗口函数
问题
通过 django ORM 实现如下写法的 SQL 语句:
select *, row_number() over (partition by c1 order by c2 desc) as rn from my_table
实现
from django.db.models import F, Window
from django.db.models.functions import RowNumber
MyModel.objects.annotate(rn=Window(expression=RowNumber(), partition_by=[F('c1')], order_by=F('c2').desc()))
扩展
如果需要对 rn 列筛选，比如：
with my_rownumber_table as (
select t.*, row_number() over (partition by c1 order by c2 desc) as rn from my_table t
)
select * from my_rownumber_table where rn = 1;
可以借助三方库 django-cte，语法如下：
from django.db.models import F, Window
from django.db.models.functions import RowNumber
from django_cte import With
cte = With(
MyModel.objects.annotate(rn=Window(expression=RowNumber(), partition_by=[F('c1')], order_by=F('c2').desc()))
)
cte.queryset().with_cte(cte).filter(rn=1)
