Django Window functions

Question

Question

How do I calculate the delta of a column between two consecutive rows in my database using the Django ORM?

Background

I have the following model:

class Foobar(models.Model):
    foo = models.IntegerField()

I want to calculate the delta between foo for each row in the database. I found Django's support for Window functions and the Lag() function. With those in hand, I tried something like this:

Foobar.objects.annotate(delta=Window(expression=F('foo') - Lag('foo')))

which gives the following error:

Traceback (most recent call last):
  File "/usr/lib/python3.8/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<input>", line 1, in <module>
  File "/home/user/.cache/pypoetry/virtualenvs/scraper-2Ye6bxs0-py3.8/lib/python3.8/site-packages/django/db/models/expressions.py", line 1275, in __init__
    raise ValueError(
ValueError: Expression 'CombinedExpression' isn't compatible with OVER clauses.

So to simplify, I just tried to annotate each row with the value from the previous row:

fs = Foobar.objects.annotate(prev=Window(expression=Lag('foo'))
for f in fs:
    print(model_to_dict(f))

The resulting dicts in my print() statement don't have 'prev', though. What am I missing? What is the correct way to use Lag() to calculate the delta between each row?

Answer 1

The resulting dicts in my print() statement don't have 'prev', though.

That is because you are using model_to_dict which uses the models declared fields to return a dictionary, of course it doesn't know anything about the data you annotated via your query. If you actually check the model the value does get annotated on it:

fs = Foobar.objects.annotate(prev=Window(expression=Lag('foo'))

for f in fs:
    print(f.prev)

Moving on for your calculation of the delta you can do it of course, just that F('foo') needs to be outside the Window expression:

fs = Foobar.objects.annotate(delta=F('foo') - Window(expression=Lag('foo')))

for f in fs:
    print(f.delta)

---

Note: You seem to be using model_to_dict for some reason, perhaps for serializing data to use for an API? This is not really a great way to serialize models, instead you should look into using Django REST framework.