Groups > comp.databases.postgresql > #791

Re: dense_rank MAX over preceding 3 days?

Path	csiph.com!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From	Robert Klemme <shortcutter@googlemail.com>
Newsgroups	comp.databases.postgresql
Subject	Re: dense_rank MAX over preceding 3 days?
Date	Mon, 14 Aug 2017 20:20:30 +0200
Lines	38
Message-ID	<eve7vfFi4hlU1@mid.individual.net> (permalink)
References	<f8f99226-b285-40d7-a234-3a428a8dfdf0@googlegroups.com> <oms4pn$pj2$1@dont-email.me>
Mime-Version	1.0
Content-Type	text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding	7bit
X-Trace	individual.net z2w0xRe3vAvrI4+Tk1RqvgYlARY3HKsGnkoOFrTSvgLlY8TSI=
Cancel-Lock	sha1:v8AuqX9StHlVuQ+414nnGJPqptY=
User-Agent	Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1
In-Reply-To	<oms4pn$pj2$1@dont-email.me>
Content-Language	en-US
Xref	csiph.com comp.databases.postgresql:791

Show key headers only | View raw

On 14.08.2017 14:28, Lennart Jonsson wrote:
> On 08/13/2017 01:48 PM, Alec Taylor wrote:
>> How do I get the MAX (or another aggregate function) for of a column 
>> of each row, OVER its preceding 3 days worth of rows?
>>
>> SQL example with expected output and db schema: 
>> http://sqlfiddle.com/#!17/24686/3
>>
>> Thanks for all suggestions
>>
>> PS: Also interested with how I can cover 3 work days rather than 
>> calendar days
> 
> Sketch:
> 
> 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.

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.

> 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. :-)

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next 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