#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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
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.
