Home » SQL & PL/SQL » SQL & PL/SQL » Lag function
Lag function [message #297627] |
Fri, 01 February 2008 16:28  |
tracy17
Messages: 8 Registered: March 2005
|
Junior Member |
|
|
I have data that remsembles the following:
Person---Location-----Datetime
1----------A----------1/1/2008 10:00
1----------A----------1/1/2008 12:00
1----------B----------1/1/2008 14:00
1----------A----------1/1/2008 16:00
1----------A----------1/5/2008 21:00
1----------B----------1/6/2008 14:00
1----------A----------1/7/2008 14:00
1----------A----------1/8/2008 14:00
Person---Location-----Datetime
2----------A----------1/1/2008 10:00
2----------A----------1/1/2008 12:00
2----------B----------1/1/2008 14:00
2----------B----------1/2/2008 16:00
2----------B----------1/2/2008 17:00
2----------B----------1/2/2008 18:00
2----------A----------1/4/2008 16:00
2----------A----------1/5/2008 21:00
2----------B----------1/7/2008 19:00
2----------A----------1/8/2008 19:00
I want to write a query that evaluates the length of time between a break in Location A timestamps. So, for ex., this:
1----------A----------1/1/2008 12:00
1----------B----------1/1/2008 14:00
1----------A----------1/1/2008 16:00
is a 4 hour break and this:
2----------A----------1/1/2008 12:00
2----------B----------1/1/2008 14:00
2----------B----------1/2/2008 16:00
2----------B----------1/2/2008 17:00
2----------B----------1/2/2008 18:00
2----------A----------1/4/2008 16:00
is a 76 hour break.
I've been experimenting with the lag function but it seems this function can carry values over a fixed lag interval (e.g.,carry by 1 row, by two rows). I need to carry a value of location A's timestamp over as many rows as needed to reach the next location A, so I can evaluate the time elapsed between them.
Can anyone suggest an approach to doing this query in SQL? I'd like to do it in SQL if I can, rather than PL/SQL.
Thanks in advance.
|
|
|
Re: Lag function [message #297639 is a reply to message #297627] |
Fri, 01 February 2008 20:27  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Normally I don't prefer posting solutions right off the bat, but I remember when I was first getting into analytics, and examples help a lot. So, let me point you to some websites your should be visiting and reading:
http://www.akadia.com/services/ora_analytic_functions.html
http://www.orafaq.com/node/1874
Once you read these pages, you should have a better mental picture of what is going on with analytics, and then you can fiddle with this possible solution.
--
-- make a table to hold your sample data
--
create table person_location_time
(
person_id number not null
,location_name varchar2(1) not null
,datetime date not null
)
/
--
-- set the default session date format to your funky date format
--
alter session set nls_date_format = 'dd/mm/rrrr hh24:mi'
/
--
-- load your sample data
--
insert into person_location_time values ('1','A',to_date('1/1/2008 10:00'));
insert into person_location_time values ('1','A',to_date('1/1/2008 12:00'));
insert into person_location_time values ('1','B',to_date('1/1/2008 14:00'));
insert into person_location_time values ('1','A',to_date('1/1/2008 16:00'));
insert into person_location_time values ('1','A',to_date('1/5/2008 21:00'));
insert into person_location_time values ('1','B',to_date('1/6/2008 14:00'));
insert into person_location_time values ('1','A',to_date('1/7/2008 14:00'));
insert into person_location_time values ('1','A',to_date('1/8/2008 14:00'));
COMMIT
/
--
-- demonstrate the affect of lag/leag
--
select person_location_time.*
,lag(datetime)
over (partition by person_id,location_name order by datetime) prior_datetime
,lead(datetime)
over (partition by person_id,location_name order by datetime) next_datetime
from person_location_time
/
--
-- to get your final solution
-- do two passes on the data, one to get lag/lead, and one to do math on the dates they give
--
select x.*
,round((datetime-prior_datetime)*24,2) lag_hours
from (
select person_location_time.*
,lag(datetime)
over (partition by person_id,location_name order by datetime) prior_datetime
,lead(datetime)
over (partition by person_id,location_name order by datetime) next_datetime
from person_location_time
) x
order by datetime
/
I have assumed you are not mixing persons across locations and thus your term "location" really means "person/location".
If you don't understand the OVER clause and its components PARTITION BY and ORDER BY, then you should be reading the links I provided.
If you use this solution without actually understanding it, I will be dissapointed in you (though I may not ever know it), because the whole point of providing code samples is for you to learn something. This in turn implies three things:
1) you ask questions when you need clarification
2) before you ask us a question you google first to try and find your own answer
3) when you have that AHA moment, you post back again to tell us what you figured out, feedback is important both ways
Good luck, Kevin
[Updated on: Sat, 02 February 2008 01:03] by Moderator Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Feb 08 07:49:58 CST 2025
|