[Solved]-Cumulative (running) sum with django orm and postgresql

by

5👍

✅

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

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 was not summing the results but the above does.

👤wrdeman

16👍

For reference, starting with Django 2.0 it is possible to use the Window function to achieve this result:

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

2👍

For posterity, I found this to be a good solution for me. I didn’t need the result to be a QuerySet, so I could afford to do this, since I was just 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)
👤Scott Skiles

1👍

You can try to do this with 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')
👤Dima Kudosh

0👍

Check this

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 table in database.

👤itzMEonTV

Leave a comment