有用户关于位置和时间的事件列表,需要保留用户的行为轨迹:即在同一位置连续时间段内发生的事件合并成一条,保留最早发生和最晚结束时间,若用户移动到别的位置,又移动回原位,不进行合并,如下图:
最终合并成:
请问不用游标,该如何操作,谢谢!
------解决方案--------------------
WITH t AS (
SELECT '甲' person, 'a' position, '17:00' begintime, '17:10' endtime FROM dual UNION ALL
SELECT '甲' person, 'a' position, '17:10' begintime, '17:30' endtime FROM dual UNION ALL
SELECT '甲' person, 'a' position, '17:30' begintime, '17:35' endtime FROM dual UNION ALL
SELECT '甲' person, 'b' position, '17:35' begintime, '17:40' endtime FROM dual UNION ALL
SELECT '甲' person, 'a' position, '17:40' begintime, '18:10' endtime FROM dual)
SELECT a.person,
a.position,
MIN(a.begintime) begintime,
MAX(a.endtime) endtime
FROM t a
START WITH NOT EXISTS (SELECT 1
FROM t b
WHERE b.person = a.person
AND b.position = a.position
AND b.endtime = a.begintime)
CONNECT BY PRIOR a.endtime = a.begintime
AND PRIOR a.person = a.person
AND PRIOR a.position = a.position
GROUP BY a.person, a.position, ROWNUM - LEVEL
ORDER BY 1, 3;