与Django queryset批注中的上一个对象的区别 [英] Difference with previous object in django queryset annotation

问题描述

让我们假设我具有以下模型：

Let's assume that I have following model:

class TestModel(models.Model):
    some_integer = models.IntegerField()

我有3个该模型的实例：

and I have 3 instances of this model:

TestModel.objects.create(some_integer=100)
TestModel.objects.create(some_integer=50)
TestModel.objects.create(some_integer=20)

，我想以某种方式注释queryset以获得以下结果：

and I'd like to annotate queryset somehow to be able to get following results:

for obj in TestModel.objects.annotate(difference=...):
    print(obj.difference)
=> 50  # 100 - 50
=> 30  # 50 - 30
=> None  # we don't have anything created after this record

使用Django（2.0 ）查询集还是应该在Python中手动执行？我们可以安全地假定对象是按pk或某个日期排序的，因此可以保留排序。

Any chance of doing this with Django(2.0) querysets or should I do this 'manually' in Python? We can safely assume that objects are ordered by pk or some date so ordering is preserved.

推荐答案

基于此答案，如果您想使用原始SQL进行操作，则可以这样操作：

Based on this Answer, if you want to do it with raw SQL, you could do it this way:

SELECT *,
       LEAD("some_integer") OVER(ORDER BY "id") AS "next_val",
       "some_integer" - "next_val" AS "difference"
FROM "myapp_testmodel";

但是，从Django版本 2.0 使用窗口函数创建上述查询：

However, from Django version 2.0 you can use Window Functions to create the above query:

from django.db.models import Window, F
from django.db.models.functions import Lead

q = TestModel.objects.annotate(
    next_val=Window(
        expression=Lead('some_integer', offset=1, default=0),
        order_by=F('id').asc()
    ), 
    difference=F('some_integer')-F('next_val')
)

注意：我使用0作为默认值（将应用于最后一项），以简化答案。防止由于从Integer中减去Null值而引起的 FieldError 。您可以使用Django的案例查询。

Note: I have used 0 as the default value (which will be applied to the last item), to simplify the answer to prevent FieldError caused by subtracting Null value from Integer. you could handle returning None for last item using Django's Case query.

这篇关于与Django queryset批注中的上一个对象的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！