I was just writing my first more complex window function with the ORM and to achieve my goal, I basically need the following SQL:
count(*) filter (…) over (partition by … order by … rows between unbounded preceding and 1 preceding)
For which the Django representation could be
ct=Window( expression=Count('position_id', filter=Q(type=Value('exit'))), partition_by=[F('position_id'), F('list_id')], order_by=F('datetime').asc(), frame=RowRange(start=None, end=-1), )
ValueError: end argument must be a positive integer, zero, or None, but got '-1'.
Apparently, for the
end argument, only
X following is supported by Django, not
The docs somehow confirm this:
The accepted values for the
None, an integer, or zero. A negative integer for
N preceding, while
UNBOUNDED PRECEDING. For both
end, zero will return
CURRENT ROW. Positive integers are accepted for
But I can’t see any reason why. I only tested on Postgres, but it seems to be allowed by SQL. Does anyone know about a database backend that does not support this? Or is this just an oversight? A quick look into Django’s git history indicates that this was in there in the initial window function patch by
@atombrella 4 years ago and hasn’t been touched since.
Happy for any feedback on whether this is a bug and we should be looking for a fix or an intended limitation.