Re: Difference in columns - Mailing list pgsql-sql

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.