Re: Advance SQL subquery

On Wed, Sep 23, 2009 at 05:13:34AM -0700, AnthonyV wrote:
> Hello,
>
> I have a table like :
>
> date | value
> -------------------------------
> 2009-09-19 | 1
> 2009-09-20 | 2
> 2009-09-21 | 6
> 2009-09-22 | 9
> 2009-09-23 | 1
>
> I'd like a request which gives me the sum of each last n days.
> For example, if I want the sum of each 3 days, I want this result:
>
> date | sum_value
> -------------------------------
> 2009-09-19 | 1 (sum from 2009-09-17 to 2009-09-19)
> 2009-09-20 | 3 (sum from 2009-09-18 to 2009-09-20)
> 2009-09-21 | 9 (sum from 2009-09-19 to 2009-09-21)
> 2009-09-22 | 17 (sum from 2009-09-20 to 2009-09-22)
> 2009-09-23 | 16 (sum from 2009-09-21 to 2009-09-23)
>
> I try to make a subquery which is apply on each row of a query, but it
> does work.
>
> Has anybody an idea?

We've implemented part of the SQL standard windowing functions, but
not the part (ROWS BETWEEN M PRECEDING AND N FOLLOWING) that would
make this most convenient. What you can do instead is something like
this:

SELECT
"date",
(
value +
COALESCE(lag(value,1) OVER w, 0) +
COALESCE(lag(value,2) OVER w, 0)
) AS sum
FROM
your_log
WINDOW w AS (ORDER BY "date")
ORDER BY "date";

When we add (ROWS BETWEEN M PRECEDING AND N FOLLOWING) to the window,
you'll be able to use sum() and parameterize it like this:

SELECT
"date",
SUM (value) OVER w
FROM
your_log
WINDOW w AS (
ORDER BY "date"
ROWS BETWEEN
2 PRECEDING AND
CURRENT ROW
)
ORDER BY "date";

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate