在 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 Rank, 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 Rank, 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)

参考

1. python - Django ORM: window function with subsequent filtering - Stack Overflow
2. Common Table Expressions with Django | django-cte