Oracle Analytical Functions
От | Willem Buitendyk |
---|---|
Тема | Oracle Analytical Functions |
Дата | |
Msg-id | 47A0E8E4.9010003@pcfish.ca обсуждение исходный текст |
Ответы |
Re: Oracle Analytical Functions
Re: Oracle Analytical Functions Re: Oracle Analytical Functions Re: Oracle Analytical Functions Re: Oracle Analytical Functions |
Список | 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
В списке pgsql-general по дате отправления: