Re: Oracle Analytical Functions

Thanks Reece,

I got this to work for me. The only problem was with the ORDER BY
clause which did not seem to work properly. I took it out and instead
used a sorted view for the data table.

Cheers,

Willem

Reece Hart wrote:
> 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
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster