Re: [SQL] Recursive temporal query
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_direc
Re: [SQL] Recursive temporal query
With source data... On 19 October 2012 16:40, Liam Caffrey 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 i
[SQL] Recursive temporal query
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_direc
