有如下数据，需要获取每个EVENT持续的时间段
| ID | EVENT | CREATED_AT |
| 1 | EVENT1 | 2018-06-15 15:34:04 |
| 2 | EVENT1 | 2018-06-15 15:34:08 |
| 3 | EVENT1 | 2018-06-15 15:40:04 |
| 4 | EVENT2 | 2018-06-15 15:41:04 |
| 5 | EVENT2 | 2018-06-15 15:42:04 |
| 6 | EVENT2 | 2018-06-15 15:43:05 |
| 7 | EVENT1 | 2018-06-15 15:44:04 |
| 8 | EVENT1 | 2018-06-15 15:44:08 |
| 9 | EVENT3 | 2018-06-15 15:45:04 |
| 10 | EVENT3 | 2018-06-15 15:46:04 |
| 11 | EVENT3 | 2018-06-15 15:47:05 |
这种情况下可以通过窗口函数row_number() 实现,SQL大致如下:
SELECT EVENT, MIN(CREATED_AT) AS STARED_AT, MAX(CREATED_AT) AS ENDED_AT
FROM (
SELECT ID,
EVENT,
CREATED_AT,
ROW_NUMBER() OVER(PARTITION BY EVENT ORDER BY ID) ROW_NO
FROM T_EVENT) as T
GROUP BY EVENT, ID - ROW_NO
查询后的结果如下：
| EVENT | STARTED_AT | ENDED_AT |
| EVENT1 | 2018-06-15 15:34:04 | 2018-06-15 15:40:04 |
| EVENT1 | 2018-06-15 15:44:04 | 2018-06-15 15:44:08 |
| EVENT2 | 2018-06-15 15:41:04 | 2018-06-15 15:43:05 |
| EVENT3 | 2018-06-15 15:45:04 | 2018-06-15 15:47:05 |
微信扫一扫，打赏作者吧～使用row_number函数实现连续内容分组