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

2018-07-12 Thread 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: 
Django 
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.026ef4a415be18c93cbc9ab51c51e644%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

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

2018-07-12 Thread 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: 
Django 
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.43d8b4ce50cba058d5d7689dd5b2ce35%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.

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

2018-07-11 Thread 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