#29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField
-------------------------------------+-------------------------------------
Reporter: | Owner: nobody
gabriellima |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.0
layer (models, ORM) | Keywords: window functions
Severity: Normal | database
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 ?
--
Ticket URL: <https://code.djangoproject.com/ticket/29561>
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/054.5c03407b8839cd4cefc11b9f577d6b4a%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
