django orm和postgresql的累积(运行)总和 [英] Cumulative (running) sum with django orm and postgresql

问题描述

是否可以使用Django的orm计算累积(运行)总和?考虑以下模型:

Is it possible to calculate the cumulative (running) sum using django's orm? Consider the following model:

class AModel(models.Model):
    a_number = models.IntegerField()

带有一组数据，其中a_number = 1.这样，我在数据库中就有数量(> 1)个AModel实例，全部带有a_number=1.我希望能够返回以下内容:

with a set of data where a_number = 1. Such that I have a number ( >1 ) of AModel instances in the database all with a_number=1. I'd like to be able to return the following:

AModel.objects.annotate(cumsum=??).values('id', 'cumsum').order_by('id')
>>> ({id: 1, cumsum: 1}, {id: 2, cumsum: 2}, ... {id: N, cumsum: N})

理想情况下，我希望能够限制/过滤累计金额.因此，在上述情况下，我想将结果限制为cumsum <= 2

Ideally I'd like to be able to limit/filter the cumulative sum. So in the above case I'd like to limit the result to cumsum <= 2

我相信在postgresql中，可以使用窗口函数来实现累加和.如何将其转换为ORM?

I believe that in postgresql one can achieve a cumulative sum using window functions. How is this translated to the ORM?

推荐答案

根据Dima Kudosh的回答，并基于 https://stackoverflow. com/a/5700744/2240489 我必须执行以下操作: 我在sql中删除了对PARTITION BY的引用，并替换为ORDER BY结果.

From Dima Kudosh's answer and based on https://stackoverflow.com/a/5700744/2240489 I had to do the following: I removed the reference to PARTITION BY in the sql and replaced with ORDER BY resulting in.

AModel.objects.annotate(
    cumsum=Func(
        Sum('a_number'), 
        template='%(expressions)s OVER (ORDER BY %(order_by)s)', 
        order_by="id"
    ) 
).values('id', 'cumsum').order_by('id', 'cumsum')

这将给出以下sql:

SELECT "amodel"."id",
SUM("amodel"."a_number") 
OVER (ORDER BY id) AS "cumsum" 
FROM "amodel" 
GROUP BY "amodel"."id" 
ORDER BY "amodel"."id" ASC, "cumsum" ASC

Dima Kudosh的答案不是对结果求和，但是上面的结果.

Dima Kudosh's answer was not summing the results but the above does.

这篇关于django orm和postgresql的累积(运行)总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！