Re: Oracle Analytical Functions

[Reece Hart <reece@xxxxxxxxx>]

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@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

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;
$_$;

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster