Обсуждение: Advance SQL subquery

Поиск
Список
Период
Сортировка

Advance SQL subquery

От
AnthonyV
Дата:
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?

Thanks in advance!

Anthony

Re: Advance SQL subquery

От
Merlin Moncure
Дата:
On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV <avequeau@gmail.com> 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.

select date, (select count(*) from foo where date between f.date - 1
and f.date + 1) from foo f;

Re: Advance SQL subquery

От
AnthonyV
Дата:
On 23 sep, 17:32, mmonc...@gmail.com (Merlin Moncure) wrote:
> On Wed, Sep 23, 2009 at 8:13 AM, AnthonyV <avequ...@gmail.com> 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.
>
> select date, (select count(*) from foo where date between f.date - 1
> and f.date + 1) from foo f;
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

It works.
Thank you!

Re: Advance SQL subquery

От
David Fetter
Дата:
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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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