id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29561	Window Function Lag/Lead supported on Filter (Not equal) for FloatField	Gabriel Oliveira	nobody	"I'd like to be able to reproduce the following example in Django, but on the Temperature column
(https://fle.github.io/detect-value-changes-between-successive-lines-with-postgresql.html)


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:

{{{#!sql
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:

{{{#!python
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:

{{{#!python
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: 
{{{#!python
FieldError: Unsupported lookup 'ne' for FloatField or join on the field not permitted.
}}}

Another try:

{{{#!python
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: 
{{{#!python
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 ?"	Uncategorized	new	Database layer (models, ORM)	2.0	Normal		window functions database		Unreviewed	0	0	0	0	0	0