Re: [SQL] Difference in columns

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.

Another alternative if you can't assume each record is always exactly 1
day apart is to populate a temp table with the values and add a serial
column that guarantees a 1 offset between values, then do a self join. I
have no idea whether or not this might be faster, but thought I'd throw
it out there as an alternative:

CREATE TEMPORARY SEQUENCE x_seq;

SELECT nextval('x_seq') AS id, ts, size
INTO TEMPORARY TABLE x_temp
FROM x
ORDER BY ts ASC;

SELECT a.ts, a.size - b.size AS diff
FROM x_temp a, x_temp b
WHERE a.id = b.id + 1;

Note that this query doesn't give you the first record with zero
difference; it returns only true differences. Here's one possible way to
add your initial record:

SELECT a.ts, b.size - a.size AS diff
FROM x_temp a, x_temp b
WHERE b.id = a.id + 1
OR (b.id = (SELECT min(id) FROM x_temp) AND a.id = b.id);

--
Craig Ringer

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql