[SQL] Difference in columns
Hi All, I have a view that generates output similar to this. select * from foo.view; ts | size ---+- 2002-03-16| 11 2002-03-17| 16 2002-03-18| 18 2002-03-19| 12 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? TIA
Re: [SQL] Difference in columns
--- Mag Gam <[EMAIL PROTECTED]> wrote: > Hi All, > > I have a view that generates output similar to this. > > select * from foo.view; > >ts | size > ---+- > 2002-03-16| 11 > 2002-03-17| 16 > 2002-03-18| 18 > 2002-03-19| 12 > > 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? > select cur.ts, cur.size, cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1 day'::interval), cur.size ) as diff from view cur; alternately: select cur.ts, cur.size, case when cur.ts = '2002-03-16' then 0 else cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1 day'::interval), cur.size ) end as diff from view; Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Difference in columns
On Sun, May 11, 2008 at 11:07 PM, Mag Gam <[EMAIL PROTECTED]> wrote: > Hi All, > > I have a view that generates output similar to this. > > select * from foo.view; > >ts | size > ---+- > 2002-03-16| 11 > 2002-03-17| 16 > > 2002-03-18| 18 > 2002-03-19| 12 > > 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? Try this: select ts, size, t1.size - (select t2.size from foo.view as t2 where t2.ts < t1.ts order by ts desc limit 1) as diff from foo.view as t1 order by ts asc; HTH, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [SQL] Difference in columns
On Sun, May 11, 2008 at 01:37:52PM -0400, Mag Gam wrote: > Any thoughts about this? 1. will there be any gaps in between dates? if yes, what should be diff be then? 2. can't you calculate it in client application? 3. is usage of pl/pgsql acceptable (i think this will be the fastest way to do it in postgresql itself). depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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
Re: [SQL] Difference in columns
On Sun, May 11, 2008 at 11:47 PM, Craig Ringer <[EMAIL PROTECTED]>
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,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [SQL] Difference in columns
"Gurjeet Singh" <[EMAIL PROTECTED]> writes: > On Sun, May 11, 2008 at 11:47 PM, Craig Ringer <[EMAIL PROTECTED]> >> 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. The OP said this was a view, so it may well not have any easy way to provide such an index. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Difference in columns
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Difference in columns
Thanks all. I will try some of these suggestions. On Sun, May 11, 2008 at 3:58 PM, Craig Ringer <[EMAIL PROTECTED]> 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 >
[SQL] merge timestamps to intervals
Hi! I have a table where I repeatingly log the status of some service, which looks something like this: < timestamp, status > Now, everytime my service is up I save the timestamp and a status of "up", if it's down I save the timestamp with "down", eg: 10:13 up 10:14 up 10:15 up 10:16 down 10:17 up 10:18 up I'd like to merge this information to intervals where the service was up or down. < intervall, status > 10:13-10:15 up 10:16-10:16 down 10:17-1018 up I've no clue how to approach this problem. Any ideas/hints? Also suggestions on a feasible better schema are welcome. :-) Thanks Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] merge timestamps to intervals
Patrick Scharrenberg wrote: > I'd like to merge this information to intervals where the service was up > or down. > > < intervall, status > > 10:13-10:15 up > 10:16-10:16 down > 10:17-1018 up > > I've no clue how to approach this problem. About 12 hours ago there was a conversation in pgsql-sql with subject "Difference in columns" that included examples that can be trivially adapted to your problem. -- 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
