在django ORM中使用postgresql窗口函数的清洁方式？ [英] Clean way to use postgresql window functions in django ORM?

问题描述

我想在需要的某些查询中使用postgresql窗口函数，如 rank（）和 dense_rank 在Django做我在原始SQL中工作，但我不知道如何在ORM中执行此操作。

I'd like to use postgresql window functions like rank() and dense_rank in some queries in need to do in Django. I have it working in raw SQL but I'm not sure how to do this in the ORM.

简化为：

SELECT
    id,
    user_id,
    score,
    RANK() OVER(ORDER BY score DESC) AS rank
FROM
    game_score
WHERE
    ...

<你在ORM中怎么做？

How would you do this in the ORM?

在某些时候，我可能需要添加分区：|

At some point I might need to add partitioning too :|

（我们正在使用Django 1.9 Python 3已经依赖于django.contrib.postgres功能）

(we're using Django 1.9 on Python 3 and already depend on django.contrib.postgres features)

推荐答案

由于提供 Func 表达式Django 1.8（ https：// docs .djangoproject.com / en / 1.10 / ref / models / expressions /＃func-expressions ），它允许这样做更干净，更可重复使用：

Since offers Func expression Django 1.8 (https://docs.djangoproject.com/en/1.10/ref/models/expressions/#func-expressions), which allows to do this much cleaner and more reusable way:

class Rank(Func):
    function = 'RANK'
    template = '%(function)s() OVER (ORDER BY %(expressions)s DESC)'

GameScore.objects.annotate(rank=Rank('score'))

我把它封装到独立的应用程序 django-rank-query ：
https://pypi.python.org/pypi/dja ngo-rank-query

I have encapsulated this to independent app django-rank-query: https://pypi.python.org/pypi/django-rank-query

还有本地Django实现的窗口函数的方式：
code.djangoproject.com/ticket/26608

There is also native Django implementation of window functions on the way: code.djangoproject.com/ticket/26608

这篇关于在django ORM中使用postgresql窗口函数的清洁方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！