Search Postgresql Archives

Re: window function ordering not working as expected

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Feb 17, 2015 at 4:18 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Lonni J Friedman <netllama@xxxxxxxxx> writes:
>> I'm interested in seeing:
>> * the date for the most recent result
>> * test name (identifier)
>> * most recent result (decimal value)
>> * the worst (lowest decimal value) test result from the past 21 days
>> * the date which corresponds with the worst test result from the past 21 days
>> * the 2nd worst (2nd lowest decimal value) test result
>> ...
>> The problem that I'm seeing is in the prv_score column. It should show
>> a value of 0.6, which corresponds with 2015-02-13, however instead its
>> returning 0.7. I thought by ordering by metrics->>'PT TWBR' I'd always
>> be sorting by the scores, and as a result, the lead(metrics->>'PT
>> TWBR', 1) would give me the next greatest value of the score. Thus my
>> confusion as to why ORDER BY metrics->>'PT TWBR' isn't working as
>> expected.
>
> lead() and lag() retrieve values from rows that are N away from the
> current row in the specified ordering.  That isn't what you want here
> AFAICS.
>
> I think the worst test result would be obtained with
>         nth_value(metrics->>'PT TWBR', 1)
> which is equivalent to what you used,
>         first_value(metrics->>'PT TWBR')
> while the 2nd worst result would be obtained with
>         nth_value(metrics->>'PT TWBR', 2)
>
> However, "worst" and "2nd worst" with this implementation would mean
> "worst and 2nd worst within the partition", which isn't the stated
> goal either, at least not with the partition definition you're using.
>
> What you really want for the "worst in last 21 days" is something like
>
>         min(metrics->>'PT TWBR') OVER (
>            PARTITION BY ... that same mess you used ...
>            ORDER BY tstamp
>            RANGE BETWEEN '21 days'::interval PRECEDING AND CURRENT ROW)
>
> However Postgres doesn't implement RANGE x PRECEDING yet.  You could
> get "worst in last 21 observations" easily:
>
>         min(metrics->>'PT TWBR') OVER (
>            PARTITION BY ... that mess ...
>            ORDER BY tstamp
>            ROWS BETWEEN 20 PRECEDING AND CURRENT ROW)
>
> and maybe that's close enough.
>
> I do not know an easy way to get "second worst" :-(.  You could build a
> user-defined aggregate to produce "second smallest value among the inputs"
> and then apply it in the same way as I used min() here.

Thanks Tom, much appreciate the fast reply.  I'll chew this over and
see if I have any other questions.


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux