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