SQL -按ID和从变量起始点开始的时间间隔分组

Question

我有一个事件日志表，记录在每一行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

我需要按ID和+时间间隔分组，假设从ID的第一个事件开始20分钟。比如:

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

我不确定如何为dt间隔设置组子句。有什么想法吗?

Answer 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