在mysql中查询一系列连续的事件

tjvv9vkg 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(402)

我有一个带有项目和时间戳的事件表。我想查询所有系列的连续项目。如果一个项目连续发生多次，则该项目应多次列出。我也想得到开始和结束时间和每个系列的持续时间。
例子：

| project   | created_at              |
|-----------|-------------------------|
| project a | 2020-05-29 10:00:00.000 |
| project a | 2020-05-29 10:00:01.167 |
| project a | 2020-05-29 10:00:03.954 |
| project a | 2020-05-29 10:00:10.055 |
| project b | 2020-05-29 10:05:00.000 |
| project b | 2020-05-29 10:06:01.049 |
| project b | 2020-05-29 10:06:30.197 |
| project a | 2020-05-29 10:07:05.167 |
| project a | 2020-05-29 10:07:18.680 |

我希望收到以下输出：

| project   | start                   | end                     | duration     |
|-----------|-------------------------|-------------------------|--------------|
| project a | 2020-05-29 10:00:00.000 | 2020-05-29 10:00:10.055 | 00:00:10.055 |
| project b | 2020-05-29 10:05:00.000 | 2020-05-29 10:06:30.197 | 00:01:30:197 |
| project a | 2020-05-29 10:07:05.167 | 2020-05-29 10:07:18.680 | 00:00:13.513 |

到目前为止，我有以下疑问：

SELECT 
project,
created_at AS "Start", 
Max(created_at) AS "End", 
TIMEDIFF(MAX(created_at), created_at) AS "Duration"
FROM results GROUP BY project;

这将提供以下输出：

| project   | start                   | end                     | duration     |
|-----------|-------------------------|-------------------------|--------------|
| project a | 2020-05-29 10:00:00.000 | 2020-05-29 10:07:18.680 | 00:07:18.680 |
| project b | 2020-05-29 10:05:00.000 | 2020-05-29 10:06:30.197 | 00:01:30:197 |

问题是，我只得到两个输出，通过小组。这反过来又会弄乱要输出的开始和结束日期以及持续时间。
有没有办法让我得到想要的结果？

sql mysql group-by Database

来源：https://stackoverflow.com/questions/62085570/query-a-series-of-consecutive-events-in-mysql

1条答案

按热度按时间

30byixjq1#

这是一个缺口和孤岛问题的例子。行号的差异应该满足您的要求：

SELECT project, MIN(created_at) as start_dt, max(created_at) as end_dt
       TIMEDIFF(MAX(created_at), created_at) AS Duration
FROM (SELECT r.*,
             ROW_NUMBER() OVER (PARTITION BY project ORDER BY created_at) as seqnum_p,
             ROW_NUMBER() OVER (ORDER BY created_at) as seqnum
      FROM results r
     ) r
GROUP BY project, (seqnum - seqnum_p)
ORDER BY MIN(created_at);

赞(0）回复(0）举报 2021-08-13

我来回答

在mysql中查询一系列连续的事件

1条答案

相关问题

热门标签

最新问答