python - Django ORM : window function with subsequent filtering

Question

回答this question，我found out不允许窗口函数与filter组合(从技术上讲，它们是允许的，但是filter子句会影响窗口)。有一个将窗口函数包装在内部查询中的提示，因此最终的SQL如下所示(据我了解):

SELECT * FROM (
    SELECT *, *window_function* FROM TABLE)
WHERE *filtering_conditions*

问题是:如何使用Django ORM编写此查询？

Answer 1

另一个解决方案是通用表表达式(CTE)，借助django-cte，您可以实现所需的功能:

cte = With(
    YouModel.objects.annotate(
        your_window_function=Window(...),
    )
)

qs = cte.queryset().with_cte(cte).filter(your_window_function='something')

大致翻译为:

WITH cte as (
    SELECT *, WINDOW(...) as your_window_function
    FROM yourmodel
) 
SELECT * 
FROM cte
WHERE cte.your_window_function = 'something'