SQL-按ID和从可变起始点开始的时间间隔进行分组
|
我有一个事件日志表，记录每个行ID和DT_EVENT，即：
ID DT_EVENT
-------------------------
1 14-MAR-11 00:00:01
2 14-MAR-11 00:02:00
3 14-MAR-11 00:05:01
1 14-MAR-11 00:08:01
3 14-MAR-11 00:22:00
1 14-MAR-11 15:00:01
1 14-MAR-11 15:15:01
EV_GROUP ID DT_FIRST_EVENT DT_LAST_EVENT N_EVENTS
-----------------------------------------------------------------------
1 1 14-MAR-11 00:00:01 14-MAR-11 00:08:01 2
2 2 14-MAR-11 00:02:00 14-MAR-11 00:02:00 1
3 3 14-MAR-11 00:05:01 14-MAR-11 00:22:00 2
4 1 14-MAR-11 15:00:01 14-MAR-11 15:15:01 2
没有找到相关结果
已邀请:
1 个回复
弦砂牧扁
SELECT id, diff, MIN(dt_event), MAX(dt_event) FROM ( SELECT t.*, TRUNC((dt_event - FIRST_VALUE(dt_event) OVER (PARTITION BY id ORDER BY dt_event)) * 86400 / 1200) AS diff FROM mytable t ) GROUP BY id, diff