| From: | Willem Buitendyk <willem(at)pcfish(dot)ca> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | Oracle Analytical Functions |
| Date: | 2008-01-30 21:15:16 |
| Message-ID: | 47A0E8E4.9010003@pcfish.ca |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
I'm trying to replicate the use of Oracle's 'lag' and 'over partition
by' analytical functions in my query. I have a table (all_client_times)
such as:
client_id, datetime
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
and I would like to create a new view that takes the first table and
calculates the time difference in minutes between each row so that the
result is something like:
client_id,datetime, previousTime, difftime
122,2007-05-01 12:01:00, 2007-05-01 12:00:00, 1
455,2007-05-01 12:03:00, 2007-05-01 12:02:00, 1
455,2007-05-01 12:08:00, 2007-05-01 12:03:00, 5
299,2007-05-01 12:34:00, 2007-05-01 12:10:00, 24
In Oracle I can achieve this with:
CREATE OR REPLACE VIEW client_time_diffs AS SELECT client_id,datetime,
LAG(datetime, 1) OVER (partition by client_id ORDER BY
client_id,datetime) AS previoustime from all_client_times;
Any idea how I could replicate this in SQL from PG. Would this be an
easy thing to do in Pl/pgSQL? If so could anyone give any directions as
to where to start?
Appreciate the help,
Willem
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dann Corbit | 2008-01-30 21:33:57 | Re: Oracle Analytical Functions |
| Previous Message | Tom Hart | 2008-01-30 21:10:07 | Mailing list archives/docs project |