Cumulative (running) sum with django orm and postgresql

Question

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

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

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

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

I don't get it. What is cumsum? And there's only one record with id=1 — Mihai Zamfir
– Mihai Zamfir, Commented Apr 20, 2017 at 11:23
cumsum == cumulative sum, obviously this is for more than one record - edited to make clearer so the size of the set of data is greater than one. — wrdeman
– wrdeman, Commented Apr 20, 2017 at 11:32
I don't think you cand do it with the ORM...use python instead — Mihai Zamfir
– Mihai Zamfir, Commented Apr 20, 2017 at 11:41
The phrase you are looking for is the running total (or running sum). Which is a specialized case of moving aggregates. Which are a kind of window functions. — pozs
– pozs, Commented Apr 20, 2017 at 13:09
I think cumulative sum and running total are the same thing mathworld.wolfram.com/CumulativeSum.html . But yes it is a window function I'm after. — wrdeman
– wrdeman, Commented Apr 20, 2017 at 13:13

Wtower · Accepted Answer · 2022-02-14 21:03:50Z

18

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

edited Feb 14, 2022 at 21:03

Wtower

20.1k12 gold badges110 silver badges86 bronze badges

answered Mar 30, 2018 at 6:38

Campi

2,3471 gold badge19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Campi Over a year ago

Interesting to know also, the window functions do not work with SqLite3.

Community · Accepted Answer · 2017-05-23 12:17:44Z

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.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Apr 20, 2017 at 12:52

wrdeman

82010 silver badges23 bronze badges

Comments

Scott Skiles · Accepted Answer · 2018-09-07 18:07:35Z

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)

answered Sep 7, 2018 at 18:07

Scott Skiles

3,8867 gold badges45 silver badges73 bronze badges

Comments

Dima Kudosh · Accepted Answer · 2017-04-20 11:56:14Z

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

answered Apr 20, 2017 at 11:56

Dima Kudosh

7,4245 gold badges40 silver badges53 bronze badges

1 Comment

wrdeman Over a year ago

Thanks, really appreciate your answer. It didn't quite work for me and I've posted my amendment.

itzMEonTV · Accepted Answer · 2017-04-20 11:41:20Z

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.

answered Apr 20, 2017 at 11:41

itzMEonTV

20.4k4 gold badges44 silver badges53 bronze badges

Collectives™ on Stack Overflow

Cumulative (running) sum with django orm and postgresql

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related