Re: SQL Subqueries on each result row

On Sep 23, 5:43 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.
>
> Has anybody an idea?
>
> Thanks in advance!
>
> Anthony

How about the following?

BEGIN ;

CREATE TABLE z ( the_date   date not null,value      integer not null
) ;

INSERT INTO z VALUES('2009-09-19',1) ;
INSERT INTO z VALUES('2009-09-20',2) ;
INSERT INTO z VALUES('2009-09-21',6) ;
INSERT INTO z VALUES('2009-09-22',9) ;
INSERT INTO z VALUES('2009-09-23',1) ;

SELECT z.the_date, SUM(z2.value) FROM z      LEFT JOIN z z2              ON z2.the_date IN (             z.the_date
      ,z.the_date-'1 day'::interval            ,z.the_date-'2 day'::interval            )GROUP BY 1ORDER BY 1 
;

ROLLBACK ;

output:
 the_date  | sum
------------+-----2009-09-19 |   12009-09-20 |   32009-09-21 |   92009-09-22 |  172009-09-23 |  16
(5 rows)