在 Django ORM 中使用 postgresql 窗口函数的干净方法? [英] Clean way to use postgresql window functions in django ORM?

问题描述

我想在 Django 中需要做的一些查询中使用 postgresql 窗口函数，如 rank() 和 dense_rank.我在原始 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 :|

(我们在 Python 3 上使用 Django 1.9 并且已经依赖于 django.contrib.postgres 功能)

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

推荐答案

从 Django 2.0 开始，它被内置到 ORM 中.请参阅窗口函数

Since Django 2.0 it is built-in into the ORM. See window-functions

# models.py
class GameScore(models.Model):
     user_id = models.IntegerField()
     score = models.IntegerField()

# window function usage
from django.db.models.expressions import Window
from django.db.models.functions import Rank

GameScore.objects.annotate(rank=Window(
    expression=Rank(),
    order_by=F('score').desc(),
    partition_by=[F('user_id')]))

# generated sql
SELECT "myapp_gamescore"."id",
   "myapp_gamescore"."user_id",
   "myapp_gamescore"."score",
   RANK() OVER (
     PARTITION BY "myapp_gamescore"."user_id"
     ORDER BY "myapp_gamescore"."score" DESC
   ) AS "rank"
FROM "myapp_gamescore"

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