From: | Reece Hart <reece(at)harts(dot)net> |
---|---|
To: | Willem Buitendyk <willem(at)pcfish(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Oracle Analytical Functions |
Date: | 2008-01-30 22:29:12 |
Message-ID: | 1201732152.6589.122.camel@snafu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
create table data (
client_id integer,
datetime timestamp not null
);
create index data_client_id on data(client_id);
copy data from STDIN DELIMITER ',';
122,2007-05-01 12:00:00
122,2007-05-01 12:01:00
455,2007-05-01 12:02:00
455,2007-05-01 12:03:00
455,2007-05-01 12:08:00
299,2007-05-01 12:10:00
299,2007-05-01 12:34:00
\.
CREATE OR REPLACE FUNCTION visits (
OUT client_id INTEGER,
OUT datetime_1 TIMESTAMP,
OUT datetime_2 TIMESTAMP,
OUT dur INTERVAL )
RETURNS SETOF RECORD
LANGUAGE plpgsql
AS $_$
DECLARE
rp data%ROWTYPE; -- previous data table record
r data%ROWTYPE; -- data table record, more recent than
rp
BEGIN
rp = (NULL,NULL);
FOR r IN SELECT * FROM data ORDER BY client_id,datetime LOOP
IF rp.client_id = r.client_id THEN
client_id = r.client_id;
datetime_1 = r.datetime;
datetime_2 = rp.datetime;
dur = r.datetime-rp.datetime;
RETURN NEXT;
END IF;
rp = r;
END LOOP;
RETURN;
END;
$_$;
rkh(at)rkh=> select * from visits() order by client_id,datetime_1;
client_id | datetime_1 | datetime_2 | dur
-----------+---------------------+---------------------+----------
122 | 2007-05-01 12:01:00 | 2007-05-01 12:00:00 | 00:01:00
299 | 2007-05-01 12:34:00 | 2007-05-01 12:10:00 | 00:24:00
455 | 2007-05-01 12:03:00 | 2007-05-01 12:02:00 | 00:01:00
455 | 2007-05-01 12:08:00 | 2007-05-01 12:03:00 | 00:05:00
(4 rows)
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Attachment | Content-Type | Size |
---|---|---|
visits.sql | text/x-sql | 1.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Verite | 2008-01-30 22:32:47 | Re: expression index on date_trunc |
Previous Message | Joshua D. Drake | 2008-01-30 22:22:04 | Re: PostgreSQL Certification |