From: | "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> |
---|---|
To: | "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | "Mag Gam" <magawake(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Difference in columns |
Date: | 2008-05-11 18:36:12 |
Message-ID: | 65937bea0805111136l77dd2f0el8c3fc6ae49f7383c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sun, May 11, 2008 at 11:47 PM, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
wrote:
> Mag Gam wrote:
>
> >
> > ts | size| Diff
> > -------------------+-----+------
> > 2002-03-16 | 11 | 0
> >
> > 2002-03-17 | 15 | 4
> > 2002-03-18 | 18 | 3
> > 2002-03-19 | 12 | -6
> >
> >
> > I need the first column to be 0, since it will be 11-11. The second
> > colum is 15-11. The third column is 18-15. The fourth column is 12-18.
> >
> > Any thoughts about this?
>
> Without making any comments on the advisability of the structure you're
> trying to use, here are a few ideas.
>
> The easy way is to use PL/PgSQL and FOR EACH .. SELECT . It's probably
> going to be rather fast too as it can use a single sequential scan.
>
> Otherwise (all examples use the following code):
>
> CREATE TABLE x (ts timestamp, size int);
> INSERT INTO x (ts, size) VALUES
> ('2002-03-16',11),
> ('2002-03-17',15),
> ('2002-03-18',18),
> ('2002-03-19',12);
>
> If you can assume that there is always exactly 1 day between entries
> then it's easy enough with a self join.
>
> If you cannot assume that, you can use a subquery with limit and order
> by to obtain the next record:
>
> SELECT
> a.ts,
> (SELECT b.size FROM x b WHERE b.ts > a.ts ORDER BY b.ts ASC LIMIT 1)
> - a.size AS difference
> FROM x a;
>
> ... but that'll be really slow for any significant number of entries.
not really... if you have an index on the TS column.
Best regards,
--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-05-11 18:58:02 | Re: Difference in columns |
Previous Message | Craig Ringer | 2008-05-11 18:17:45 | Re: Difference in columns |