Clever Geek Handbook

Cumulative (running) amount with django orm and postgresql

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

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

with a dataset where a_number = 1

. So I have a number (> 1) of instances AModel

in the database, all with a_number=1

. I would 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})

Ideally I would like to limit / filter the cumulative amount. So in the above case, I would like to limit the result tocumsum <= 2

I believe it is possible to get the cumulative sum in postgresql using window functions. How does this translate to ORM?

+6


source to share


5 answers


From Dima Kudosh's answer and based on fooobar.com/questions/123784 / ... I had to do the following: I removed the reference to PARTITION BY

in sql and replaced it with ORDER BY

.

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')

This gives the following 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's answer did not summarize the results, but the above does.

+2


source


For reference, since Django 2.0, a function can be used Window

to achieve this result:



AModel.objects.annotate(cumsum=Window(Sum('a_number'), order_by=F('id').asc()))\
              .values('id', 'cumsum').order_by('id', 'cumsum')
+3


source


For posterity, I found this a good solution for me. I don't need the result to be a QuerySet, so I could afford to do this as I was going to plot the data using D3.js:

import numpy as np
import datettime

today = datetime.datetime.date()

raw_data = MyModel.objects.filter('date'=today).values_list('a_number', flat=True)

cumsum = np.cumsum(raw_data)
+2


source


You can try to do this using the Func expression .

from django.db.models import Func, Sum

AModel.objects.annotate(cumsum=Func(Sum('a_number'), template='%(expressions)s OVER (PARTITION BY %(partition_by)s)', partition_by='id')).values('id', 'cumsum').order_by('id')
+1


source


check it

AModel.objects.order_by("id").extra(select={"cumsum":'SELECT SUM(m.a_number) FROM table_name m WHERE m.id <= table_name.id'}).values('id', 'cumsum')

where table_name

should be the name of the table in the database.

0


source






More articles:


All Articles