From: | Jim <shakahshakah(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL Subqueries on each result row |
Date: | 2009-09-23 16:33:39 |
Message-ID: | d4bb176c-8e81-49bd-8160-e3b2a8eb3bd9@d23g2000vbm.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sep 23, 5:43 am, AnthonyV <avequ(dot)(dot)(dot)(at)gmail(dot)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 1
ORDER BY 1
;
ROLLBACK ;
output:
the_date | sum
------------+-----
2009-09-19 | 1
2009-09-20 | 3
2009-09-21 | 9
2009-09-22 | 17
2009-09-23 | 16
(5 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Kai Carter | 2009-09-23 23:04:15 | SQL moving window averages/statistics |
Previous Message | AnthonyV | 2009-09-23 09:43:37 | SQL Subqueries on each result row |