Home » SQL & PL/SQL » SQL & PL/SQL » Lag function
Lag function [message #297627] Fri, 01 February 2008 16:28 Go to next message
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 Go to previous message
Kevin Meade
Messages: 2101
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

Previous Topic: Existing Partitions with maxvalue need new partitions
Next Topic: Time taken for INSERT into table
Goto Forum:
  


Current Time: Sat Dec 03 20:11:06 CST 2016

Total time taken to generate the page: 0.09932 seconds