Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 83C72632F72 for ; Thu, 24 Sep 2009 04:44:12 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49590-09 for ; Thu, 24 Sep 2009 07:44:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f228.google.com (mail-ew0-f228.google.com [209.85.219.228]) by mail.postgresql.org (Postfix) with ESMTP id E4C17632AA8 for ; Thu, 24 Sep 2009 04:44:03 -0300 (ADT) Received: by ewy28 with SMTP id 28so1980619ewy.42 for ; Thu, 24 Sep 2009 00:44:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:from:date:x-google-sender-auth:message-id:subject:to :content-type; bh=R4goZaZADntZIQ/56iDC7cNDSf8yOo0otoNJgF1PKMU=; b=BMKt+XnhDR1hqo+OPZibMCAAMbPxhkhhrG10wiBBjVrFn6XOeS3ZptUzPDRvZqc70z YgVhU6ZPiR/UU/iiCqTekLUglaKpKCa7AvKZRc4Vnz96n6kYEhA58x66un8/Nf5FTfYp yVfbXlm68yUcoCPXfjXnVEOmKspQafnyGJlaQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:from:date :x-google-sender-auth:message-id:subject:to:content-type; b=nz2owcwIFa7VnwU+xVaij7STDyUNXXdfBC3kcTigYRfzaa/9QoRlLFoi5gWaLJ11TY hMVtJPpJB4pOyDoCD/yYZnxkRU9sfsFDsfFNPVLeLeKkZ91HyaSJl7oNJRX529bXE+O2 8M66YBAuFQA2s22AYxJkh7iq/rXn62Pux78Is= MIME-Version: 1.0 Received: by 10.216.86.83 with SMTP id v61mr687942wee.80.1253778242093; Thu, 24 Sep 2009 00:44:02 -0700 (PDT) In-Reply-To: References: From: Mark J Camilleri Date: Thu, 24 Sep 2009 09:43:41 +0200 X-Google-Sender-Auth: b1d8373e94f7f2c3 Message-ID: Subject: Re: SQL Subqueries on each result row To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d77d12bf44c704744dfe12 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200909/115 X-Sequence-Number: 33180 --0016e6d77d12bf44c704744dfe12 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Sep 23, 2009 at 6:33 PM, Jim wrote: > On Sep 23, 5:43 am, 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? > > > > 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@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 --0016e6d77d12bf44c704744dfe12 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Sep 23, 2009 at 6:33 PM, Jim <shakahshakah@g= mail.com> wrote:
On Sep 23, 5:43=A0am, AnthonyV <avequ...@gmail.com> wrote:
> Hello,
>
> I have a table like :
>
> =A0 =A0date =A0 =A0 =A0 =A0| =A0 =A0value
> -------------------------------
> 2009-09-19 | =A0 =A0 =A01
> 2009-09-20 | =A0 =A0 =A02
> 2009-09-21 | =A0 =A0 =A06
> 2009-09-22 | =A0 =A0 =A09
> 2009-09-23 | =A0 =A0 =A01
>
> 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:
>
> =A0 =A0date =A0 =A0 =A0 =A0| sum_value
> -------------------------------
> 2009-09-19 | =A0 =A0 =A01 =A0 (sum from 2009-09-17 to 2009-09-19)
> 2009-09-20 | =A0 =A0 =A03 =A0 (sum from 2009-09-18 to 2009-09-20)
> 2009-09-21 | =A0 =A0 =A09 =A0 (sum from 2009-09-19 to 2009-09-21)
> 2009-09-22 | =A0 =A0 =A017 =A0 (sum from 2009-09-20 to 2009-09-22)
> 2009-09-23 | =A0 =A0 =A016 =A0 (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 (
=A0the_date =A0 date not null
=A0,value =A0 =A0 =A0integer 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)
=A0FROM z
=A0 =A0 =A0 LEFT JOIN z z2
=A0 =A0 =A0 =A0 =A0 =A0 =A0 ON z2.the_date IN (
=A0 =A0 =A0 =A0 =A0 =A0 =A0z.the_date
=A0 =A0 =A0 =A0 =A0 =A0 ,z.the_date-'1 day'::interval
=A0 =A0 =A0 =A0 =A0 =A0 ,z.the_date-'2 day'::interval
=A0 =A0 =A0 =A0 =A0 =A0 )
=A0GROUP BY 1
=A0ORDER BY 1
;

ROLLBACK ;

output:

=A0the_date =A0| sum
------------+-----
=A02009-09-19 | =A0 1
=A02009-09-20 | =A0 3
=A02009-09-21 | =A0 9
=A02009-09-22 | =A017
=A02009-09-23 | =A016
(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 t= o mind with the above solution is that it assumes that the dates will alway= s be contiguous...well to be fair the original request did not mention othe= rwise either!.. in any case the solution below should work also with non-co= nsecutive dates.=A0 I am afraid though that it might be a hogger with large= r datasets - I just tried out the first idea that hit me, so use with cauti= on.


CREATE TABLE index_dates AS
=A0=A0=A0 SELECT=A0=A0=A0 --The resu= lt 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)
=A0=A0=A0 =A0=A0=A0 z.the_date, z.value, z3.indx
=A0=A0=A0 FROM
=A0= =A0=A0 =A0=A0=A0 z,
=A0=A0=A0 =A0=A0=A0 ( SELECT=A0=A0=A0 --This select = generates the 'index number' for each date, 1 being assigned to the= smallest date
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 z2.the_date, count(z.the_da= te) AS indx
=A0=A0=A0 =A0=A0=A0 FROM
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 z,
=A0=A0=A0 = =A0=A0=A0 =A0=A0=A0 z AS z2
=A0=A0=A0 =A0=A0=A0 WHERE
=A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 z.the_date <=3D z2.the_date
=A0=A0=A0 =A0=A0=A0 GRO= UP BY
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 z2.the_date ) AS z3
=A0=A0=A0 WHE= RE
=A0=A0=A0 =A0=A0=A0 z.the_date =3D z3.the_date;


SELECT=A0=A0=A0 --same query as suggested by Jim but run on the tem= p table and joined on the index numbers instead of dates
=A0=A0=A0 index= _dates.the_date, sum(z2.value)
FROM
=A0=A0=A0 index_dates
=A0=A0= =A0 LEFT JOIN index_dates z2
=A0=A0=A0 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;<= br>

DROP TABLE index_dates;


RESULT:

DATE=A0=A0=A0 = =A0=A0=A0 | SUM
-----------------------
2009-09-19=A0=A0=A0 |1
2009-09-20=A0=A0=A0 |3=
2009-09-21=A0=A0=A0 |9
2009-09-24=A0=A0=A0 |17=A0=A0=A0=A0=A0=A0=A0 = {inserted instead of 22 Sep}
2009-10-25=A0=A0=A0 |16=A0=A0=A0=A0=A0=A0= =A0 {inserted instead of 23 Sep}

Regs,
Mark
--0016e6d77d12bf44c704744dfe12--