From: | "Mag Gam" <magawake(at)gmail(dot)com> |
---|---|
To: | "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Difference in columns |
Date: | 2008-05-12 00:58:28 |
Message-ID: | 1cbd6f830805111758j7334e57an63281f5560b7b726@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Thanks all. I will try some of these suggestions.
On Sun, May 11, 2008 at 3:58 PM, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
wrote:
> Mag Gam wrote:
>
> > I am trying to find the difference between the size column. So the
> > desired output would be
> >
> > 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?
>
> Here's one way to do this with PL/PgSQL. It's probably not the most
> efficient, but it does work. For this code to be safe `size' must never
> be NULL and `ts' must be unique across all records in the input set.
>
> CREATE OR REPLACE FUNCTION x_diff(
> OUT ts TIMESTAMP,
> OUT size INTEGER,
> OUT diff INTEGER)
> RETURNS SETOF record AS $$
> DECLARE
> cur_x x;
> last_size INTEGER := null;
> BEGIN
> FOR cur_x IN SELECT * FROM x ORDER BY ts ASC LOOP
> ts := cur_x.ts;
> size := cur_x.size;
> IF last_size IS NULL THEN
> -- First record in set has diff `0' because the differences
> -- are defined against the previous, rather than next,
> -- record.
> diff := 0;
> ELSE
> diff := cur_x.size - last_size;
> END IF;
> last_size := cur_x.size;
> RETURN NEXT;
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE 'plpgsql' STRICT;
>
> If you need to constrain the range of values processed that's not too
> tricky - either feed the function a refcursor for a query result set to
> iterate over, or pass it parameters to constrain the query with a WHERE
> clause. The former is more flexible, the latter is easier to use.
>
> --
> Craig Ringer
>
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Scharrenberg | 2008-05-12 05:38:47 | merge timestamps to intervals |
Previous Message | Craig Ringer | 2008-05-11 19:58:56 | Re: Difference in columns |