1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • dense_rank MAX over preceding 3 days?

    From Alec Taylor@21:1/5 to All on Sun Aug 13 04:48:04 2017
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lennart Jonsson@21:1/5 to Alec Taylor on Mon Aug 14 14:28:36 2017
    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)

    The groups I monitor on usenet have zero to none activity. I suggest you
    post your question on http://stackoverflow.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Robert Klemme@21:1/5 to Lennart Jonsson on Mon Aug 14 20:20:30 2017
    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/

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Lennart Jonsson@21:1/5 to Robert Klemme on Wed Aug 16 00:01:04 2017
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)