Django cumulative sum in model (sqlite)

Question

My model is:

class transaction(models.Model):
    transactiondate = models.DateField()
    category = models.ForeignKey(category, on_delete=models.PROTECT, default=0)
    details = models.CharField(max_length=200, null=True)
    amount = models.DecimalField(decimal_places=2, max_digits=10)
    accountid = models.ForeignKey(account, on_delete=models.PROTECT)

And I'm getting a cumulative total for each date using a raw SQL query:

def cumulativebalance():
        query = """SELECT id, transactiondate,
            SUM("amount") 
            OVER (ORDER BY transactiondate) AS "cumsum" 
            FROM "transactions_transaction"
            GROUP BY transactiondate 
            ORDER BY transactiondate ASC, "cumsum" ASC
            """
        return balance = transaction.objects.raw(query)

This does return the output I'm looking for. My question is whether there's a way in Django that I can incorporate this into my model instead of having it as raw SQL?

What I've tried

I did find a previous similar question and I've adapted the answer to my own model:

test = transaction.objects.annotate(
            cumsum=Func(
                Sum('amount'), 
                template='%(expressions)s OVER (ORDER BY %(order_by)s)', 
                order_by="transactiondate"
            ) 
        ).values('transactiondate', 'cumsum').order_by('transactiondate', 'cumsum')

But this gives me an error: OperationalError at /transactions/ near "OVER": syntax error. I don't understand what this is telling me.

The SQL which was generated is shown in the traceback. My best guess is that it should be PARTITION BY after OVER instead of ORDER BY, but I don't know how I could change that from my python code.

('SELECT "transactions_transaction"."transactiondate", '
 'CAST(CAST(SUM("transactions_transaction"."amount") AS NUMERIC) OVER (ORDER '
 'BY transactiondate) AS NUMERIC) AS "cumsum" FROM "transactions_transaction" '
 'GROUP BY "transactions_transaction"."id", '
 '"transactions_transaction"."transactiondate", '
 '"transactions_transaction"."category_id", '
 '"transactions_transaction"."details", "transactions_transaction"."amount", '
 '"transactions_transaction"."accountid_id" ORDER BY '
 '"transactions_transaction"."transactiondate" ASC, "cumsum" ASC')

Willem Van Onsem · Accepted Answer · 2022-01-14T16:10:44.530

Since django-2.0, it is possible to work with a Window expressions [Django-doc]:

from django.db.models import F, Sum, Window

transaction.objects.annotate(
    cumsum=Window(Sum('amount'), order_by=F('transactiondate').asc())
).order_by('transactiondate', 'cumsum')

The transaction objects that arise from this will have an extra attribute .cumsum.

Note: Models in Django are written in PascalCase, not snake_case, so you might want to rename the model from ~~transaction~~ to Transaction.

Django cumulative sum in model (sqlite)

What I've tried

1 Answers1