How to Filter Window Function (Lag/Lead) for FloatField

[Gabriel Lima de Oliveira]

Hi all. Carlton suggested I should ask this here, as stated at https://code.djangoproject.com/ticket/29561#ticket

Please, I'd like to know how to filter using new django 2.0 window functions. Something similar to this beautiful and simple example. 

Given a table

db=> SELECT * FROM weather ORDER BY day DESC;

day	temperature	rainy
2014-04-08	20.0	f
2014-04-07	20.0	f
2014-04-06	16.0	t
2014-04-05	16.0	t
2014-04-04	16.0	t
2014-04-03	22.0	f
2014-04-02	22.0	f
2014-04-01	22.0	t

I'd like to show dates only if the temperature changed:

day	temperature
2014-04-08	20.0
2014-04-06	16.0
2014-04-03	22.0

On pure PostgreSQL, this translates to:

SELECT
    w1.day, w1.temperature
FROM
    (SELECT
        w2.day,
        w2.temperature,
        lead(w2.temperature) OVER (ORDER BY w2.day DESC) as prev_temp
     FROM
        weather w2
     ORDER BY
        w2.day DESC) as w1
WHERE
    w1.temp IS DISTINCT FROM w1.prev_temp
ORDER BY
    w1.day DESC;

I could accomplish the inner query by using the new Window Functions:

Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc()))

Now my problem is use this annotation to filter only when temperature differs from prev_temp
(in order to accomplish something similar to the "temperature IS DISTINCT FROM prev_temp")

When I try to use the available filters, the following errors occurs:

Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())).order_by('-day').filter(temperature__ne=F('prev_temp'))

Gives the error: 

FieldError: Unsupported lookup 'ne' for FloatField or join on the field not permitted.

Another try:

Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())).order_by('-day').filter(~Q(temperature=F('prev_temp'))))

Gives the error: 

ProgrammingError: window functions are not allowed in WHERE

It's in fact a PostgreSQL error, because the generated SQL query tries to pass the LAG function inside the where clause.

How may I accomplish that, even if I have to use the extra fields, or even RawSQL class ?

Right now I'm doing:

with connection.cursor() as c:
    c.execute('SELECT q.day, q.temperature FROM (%s) AS q WHERE q.temperature IS DISTINCT FROM q.prev_temp' % str(Weather.objects.annotate(prev_temp=Window(expression=Lead('temperature'), order_by=F('day').desc())).order_by('-day').values('day','temperature', 'prev_temp').query))
    result = c.fetchall()

Important to note that I couldn't use the str(queryset.query) as parameter to the c.execute. I had to do a python replace.

I.e, had to use ('...%s...' % parameter) instead of c.execute ('...%s...', [parameter])

Thank you all in advance.