Re: Recursive temporal query

With source data...

On 19 October 2012 16:40, Liam Caffrey <liam(dot)caffrey(at)gmail(dot)com> wrote:

> Hi,
>
> I am trying to run a recursive cte query on temporal data.
> I attach the starting set (temp_station) and the solution set
> (temp_solution)
>
> A user visits many stations in random order.
> They can enter a station many times but they only exit once.
> After they enter a station X, they can enter other stations or exit other
> stations, i.e. a station entry/exit can fully contain other stations) but
> eventually they will exit station X (these are invalid visits to other
> stations within an entry/exit context for a single station.)
> They can validly visit a station multiple times but these visits must not
> overlap with each other.
>
> The objective is to list the earliest entry time and matching exit time
> for each valid visit to a station according to the rules above.
>
> My attempt below doesn't recurse and I can't understand what I need to do
> here. Can anyone shed some light on this?
>
> Regards
>
> Liam
>
> --drop table temp_station;
>
> create table temp_station
> (
> station_id int
> ,movement_direction varchar(5)
> ,event_tick char(3)
> ,event_time timestamp with time zone
> );
>
> delete from temp_station;
>
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (1, 'enter', 't01', '2012-10-18 10:23:31');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (1, 'enter', 't02', '2012-10-18 10:31:42');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (1, 'enter', 't03', '2012-10-18 10:41:41');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (1, 'exit', 't04', '2012-10-18 10:48:34');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't05', '2012-10-18 10:54:57');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'exit', 't06', '2012-10-18 11:01:35');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (3, 'enter', 't07', '2012-10-18 11:10:52');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (3, 'enter', 't08', '2012-10-18 11:20:50');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (4, 'enter', 't09', '2012-10-18 11:29:56');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (3, 'enter', 't10', '2012-10-18 11:37:53');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (7, 'exit', 't11', '2012-10-18 11:44:42');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (3, 'exit', 't12', '2012-10-18 11:53:14');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (4, 'enter', 't13', '2012-10-18 12:02:59');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (4, 'enter', 't14', '2012-10-18 12:12:11');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (4, 'exit', 't15', '2012-10-18 12:20:29');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't16', '2012-10-18 12:26:57');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't17', '2012-10-18 12:34:12');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (9, 'exit', 't18', '2012-10-18 12:40:10');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'exit', 't19', '2012-10-18 12:48:29');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (5, 'enter', 't20', '2012-10-18 12:54:37');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (5, 'exit', 't21', '2012-10-18 13:00:42');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (6, 'enter', 't22', '2012-10-18 13:06:09');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (6, 'enter', 't23', '2012-10-18 13:15:47');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (6, 'exit', 't24', '2012-10-18 13:23:34');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (6, 'enter', 't25', '2012-10-18 13:29:22');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (6, 'enter', 't26', '2012-10-18 13:37:27');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (5, 'exit', 't27', '2012-10-18 13:45:02');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (6, 'exit', 't28', '2012-10-18 13:53:44');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (7, 'enter', 't29', '2012-10-18 14:00:09');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (7, 'enter', 't30', '2012-10-18 14:08:45');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (8, 'enter', 't31', '2012-10-18 14:14:12');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (8, 'exit', 't32', '2012-10-18 14:20:20');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (7, 'exit', 't33', '2012-10-18 14:28:23');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't34', '2012-10-18 14:35:43');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (2, 'exit', 't35', '2012-10-18 14:44:34');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (8, 'enter', 't36', '2012-10-18 14:51:26');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (8, 'enter', 't37', '2012-10-18 14:59:08');
> insert into temp_station (station_id, movement_direction, event_tick,
> event_time) values (8, 'exit', 't38', '2012-10-18 15:06:10');
>
>
> --drop table temp_solution;
>
> create table temp_solution
> (
> station_id int
> ,movement_direction varchar(5)
> ,event_tick char(3)
> ,event_time timestamp with time zone
> );
>
> delete from temp_solution;
>
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (1, 'enter', 't01', '2012-10-18 10:23:31');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (1, 'exit', 't04', '2012-10-18 10:48:34');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't05', '2012-10-18 10:54:57');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (2, 'exit', 't06', '2012-10-18 11:01:35');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (3, 'enter', 't07', '2012-10-18 11:10:52');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (3, 'exit', 't12', '2012-10-18 11:53:14');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (4, 'enter', 't13', '2012-10-18 12:02:59');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (4, 'exit', 't15', '2012-10-18 12:20:29');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't16', '2012-10-18 12:26:57');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (2, 'exit', 't19', '2012-10-18 12:48:29');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (5, 'enter', 't20', '2012-10-18 12:54:37');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (5, 'exit', 't21', '2012-10-18 13:00:42');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (6, 'enter', 't22', '2012-10-18 13:06:09');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (6, 'exit', 't24', '2012-10-18 13:23:34');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (6, 'enter', 't25', '2012-10-18 13:29:22');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (6, 'exit', 't28', '2012-10-18 13:53:44');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (7, 'enter', 't29', '2012-10-18 14:00:09');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (7, 'exit', 't33', '2012-10-18 14:28:23');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (2, 'enter', 't34', '2012-10-18 14:35:43');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (2, 'exit', 't35', '2012-10-18 14:44:34');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (8, 'enter', 't36', '2012-10-18 14:51:26');
> insert into temp_solution (station_id, movement_direction, event_tick,
> event_time) values (8, 'exit', 't38', '2012-10-18 15:06:10');
>
>
> with recursive
> enter_event as
> (
> select row_number() over(order by event_time) as rownum, *
> from temp_station
> where movement_direction = 'enter'
> --and station_id in (7, 8)
> )
> ,exit_event as
> (
> select row_number() over(order by event_time) as rownum, *
> from temp_station
> where movement_direction = 'exit'
> --and station_id in (7, 8)
> )
> ,event_period as
> (
> select a.rownum, a.station_id, a.event_tick as start_tick, b.event_tick as
> end_tick, a.event_time as start_time, b.event_time as end_time
> from enter_event a
> join
> exit_event b
> on a.station_id = b.station_id
> -- entry always before exit
> and a.event_time < b.event_time
> )
> ,dtr (rownum, station_id, start_tick, end_tick, start_time, end_time) as
> (
> select rownum, station_id, start_tick, end_tick, start_time, end_time
> from event_period
> where start_tick = (select min(start_tick) from event_period)
> union all
> select a.rownum, a.station_id, a.start_tick, a.end_tick, a.start_time,
> b.end_time
> from event_period a
> ,dtr b
> where a.end_time < b.start_time
> and a.start_time < a.end_time
> and b.start_time < b.end_time
> )
> select * from dtr
>
>
>
>
>
>