Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #792

Re: dense_rank MAX over preceding 3 days?

Show all headers | View raw

On 08/14/2017 08:20 PM, Robert Klemme wrote:
[...]
>> MAX(x) OVER (PARTITION by ...
>>               ORDER BY ...
>>               RANGE BETWEEN CURRENT ROW AND 3 PRECEDING)
> 
> If I am not mistaken this requires that data is rolled up to days
> already.  Could be achieved with a WITH clause though.
> 

You can do this in the window:

     MAX(x) OVER (PARTITION by ...
                  ORDER BY date(...)
                  RANGE BETWEEN CURRENT ROW AND 3 PRECEDING)

Some DBMS does only support range over numbers, a trick is to map the 
date to a number with for example a JULIAN_DAY function:

     MAX(x) OVER (PARTITION by ...
                  ORDER BY JULIAN_DAY(...)
                  RANGE BETWEEN CURRENT ROW AND 3 PRECEDING)

> The nice thing is this works easier with the three work days as you
> would just have to filter out weekends.  That would still miss public
> holidays which would require a bit more.
>

I agree, a calendar table is often very usefull

>> The groups I monitor on usenet have zero to none activity. I suggest you 
>> post your question on http://stackoverflow.com
> 
> Some old fashioned guys are still around. :-)
> 

:-)


/Lennart

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

dense_rank MAX over preceding 3 days? Alec Taylor <alec.taylor6@gmail.com> - 2017-08-13 04:48 -0700
  Re: dense_rank MAX over preceding 3 days? Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2017-08-14 14:28 +0200
    Re: dense_rank MAX over preceding 3 days? Robert Klemme <shortcutter@googlemail.com> - 2017-08-14 20:20 +0200
      Re: dense_rank MAX over preceding 3 days? Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2017-08-16 00:01 +0200

csiph-web