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
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
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. :-)
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 296 |
Nodes: | 16 (2 / 14) |
Uptime: | 87:14:57 |
Calls: | 6,658 |
Files: | 12,203 |
Messages: | 5,333,879 |