#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.