[Django] #29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[Django] #29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField

Django
#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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField

Django
#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 [hidden email].
To post to this group, send email to [hidden email].
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.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField

Django
In reply to this post by Django
#29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField
-------------------------------------+-------------------------------------
     Reporter:  Gabriel Oliveira     |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  2.0
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  invalid
     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
-------------------------------------+-------------------------------------
Changes (by Carlton Gibson):

 * status:  new => closed
 * resolution:   => invalid


Comment:

 Hi Gabriel. Sorry to be a pain but, as this stands it is a usage question
 that needs to be directed to the [https://groups.google.com/forum/#!forum
 /django-users Django Users' mailing list].

 If you can narrow it down to a specific issue with Django then we can
 review that here.

 Thanks!

--
Ticket URL: <https://code.djangoproject.com/ticket/29561#comment:2>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.43d8b4ce50cba058d5d7689dd5b2ce35%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: [Django] #29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField

Django
In reply to this post by Django
#29561: Window Function Lag/Lead supported on Filter (Not equal) for FloatField
-------------------------------------+-------------------------------------
     Reporter:  Gabriel Oliveira     |                    Owner:  nobody
         Type:  Uncategorized        |                   Status:  closed
    Component:  Database layer       |                  Version:  2.0
  (models, ORM)                      |
     Severity:  Normal               |               Resolution:  invalid
     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
-------------------------------------+-------------------------------------

Comment (by Gabriel Oliveira):

 No problem, man. I'll ask it there. It was just because I thought not
 being able to use window_function result in filters for FloatFields would
 point to something regarding a feature request.

 I'll let you all know if they narrow down to some specific issue.

--
Ticket URL: <https://code.djangoproject.com/ticket/29561#comment:3>
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 [hidden email].
To post to this group, send email to [hidden email].
To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/069.026ef4a415be18c93cbc9ab51c51e644%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.