#29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField
-------------------------------------+-------------------------------------
Reporter: Gabriel Oliveira | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer | Version: 2.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: window functions | Triage Stage:
database | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Gabriel Oliveira:
Old description:
> 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 ?
New description:
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 ?
Right now I'm doing:
{{{#!python
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])
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29561#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/069.d823c4ac512281df3258789bf549d37e%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
