Re: SQL Subqueries on each result row
| От | Mark J Camilleri |
|---|---|
| Тема | Re: SQL Subqueries on each result row |
| Дата | |
| Msg-id | e3387ab60909240043q70c728yd0f29e3c7f0fe507@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: SQL Subqueries on each result row (Jim <shakahshakah@gmail.com>) |
| Список | pgsql-sql |
On Wed, Sep 23, 2009 at 6:33 PM, Jim <shakahshakah@gmail.com> wrote:
On Sep 23, 5:43 am, AnthonyV <avequ...@gmail.com> wrote:How about the following?
> 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
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)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Hi,
The only problem that crops to mind with the above solution is that it assumes that the dates will always be contiguous...well to be fair the original request did not mention otherwise either!.. in any case the solution below should work also with non-consecutive dates. I am afraid though that it might be a hogger with larger datasets - I just tried out the first idea that hit me, so use with caution.
CREATE TABLE index_dates AS
SELECT --The result of this is a relation with the dates, values and 'index numbers' - placed in a temp table (you may also create
a view instead so that it is updated all the time - your call)
z.the_date, z.value, z3.indx
FROM
z,
( SELECT --This select generates the 'index number' for each date, 1 being assigned to the smallest date
z2.the_date, count(z.the_date) AS indx
FROM
z,
z AS z2
WHERE
z.the_date <= z2.the_date
GROUP BY
z2.the_date ) AS z3
WHERE
z.the_date = z3.the_date;
SELECT --same query as suggested by Jim but run on the temp table and joined on the index numbers instead of dates
index_dates.the_date, sum(z2.value)
FROM
index_dates
LEFT JOIN index_dates z2
ON z2.indx IN (index_dates.indx, index_dates.indx-1, index_dates.indx-2)
GROUP BY index_dates.the_date
ORDER BY index_dates.the_date;
DROP TABLE index_dates;
RESULT:
DATE | SUM
-----------------------
2009-09-19 |1
2009-09-20 |3
2009-09-21 |9
2009-09-24 |17 {inserted instead of 22 Sep}
2009-10-25 |16 {inserted instead of 23 Sep}
Regs,
Mark
В списке pgsql-sql по дате отправления: