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

发布于 2024-11-01 19:34:30 字数 823 浏览 0 评论 0原文

我有一个事件日志表，记录每行 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 间隔设置组子句。对此有什么想法吗？

原文

I've an event log table that records in each row ID and DT_EVENT, ie:

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

I need to group by ID and + a time interval of, let's say, 20mins starting from the first event for ID. Something like:

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

I'm not sure on how to set up the group clause for that dt interval. Any idea on that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无戏配角 2024-11-08 19:34:30

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

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

回复收藏 0 原文

~没有更多了~