I realize this is a well known 'limitation', that you cannot straightforwardly filter on a window functions annotation, but I am looking for ways around that. I have seen an answer to this question that suggests using a subquery, but I cannot work out what the syntax should be without getting a *** django.db.utils.ProgrammingError: more than one row returned by a subquery used as an expression error.
The data represents a timeseries with discrete states. These states change over time. I need to get a list of timestamps (or indices) where the 'state' changes from one to the next. Using a Window Function, I can get a queryset with the state change, but cannot then filter on that to get the index/timestamp of when the change occurs.
Very simplified (non-working) code is:
*models.py
class MyModel(models.Model):
parent = models.ForeignKey('MyParent')
timestamp = models.DateTimeField()
state = models.IntegerField()
*view
deltas = MyModel.objects.filter(parent=parent).annotate(
previous_state=Window(
expression=Lag('state', offset=1, default=0),
order_by=F('timestamp').asc()
),
delta=F('state')-F('previous_state')
).values('delta')
This gives a queryset of records with the diff value indicating a state change (e.g. where state goes from 0->1, diff = 1, where state goes from 3 -> 2, diff = -1, etc.), however I cannot filter on it to find where those state changes are. Ideally I could do deltas.filter(delta__gte=1) to find any increases in state, but of course this is not allowed.
Is there any other way to achieve this using Subqueries or similar? (db is PSQL)
