Re: SQL Subqueries on each result row

On Wed, Sep 23, 2009 at 6:33 PM, Jim <shakahshakah(at)gmail(dot)com> wrote:

> 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)
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)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