SQL Oracle-按ID、任务ID、最小和最大时间戳分组

Question

我有执行不同任务的用户的数据。我想按用户id和任务id对这些数据进行分组，以获得每个任务的开始和结束时间。当员工更改为另一项任务时，应该有一个新行，其中包含新的开始和结束时间。

简化数据集示例：

2021年6月16日04:17:00

userid	taskid	date_time_stamp(升序(
1	任务A
1	任务A	2021年6月16日04:19:00
1	任务A	2021年6月16日04:27:00
1	任务B	2021年6月16日04:31:00
1	任务B	2021年6月16日04:33:00
1	任务B	2021年6月16日04:36:00
1	任务A	2021年6月16日04:42:00
1	任务A	2021年6月16日04:44:00

从Oracle12，您可以使用MATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
PARTITION BY userid
ORDER BY date_time_stamp
MEASURES
FIRST(taskid) AS taskid,
FIRST(date_time_stamp) AS start_date,
LAST(date_time_stamp) AS end_date
ONE ROW PER MATCH
PATTERN ( same_task+ )
DEFINE same_task AS FIRST(taskid) = taskid
)

在此之前，您可以使用ROW_NUMBER分析函数和聚合：

SELECT userid,
taskid,
MIN(date_time_stamp) AS start_date,
MAX(date_time_stamp) AS end_date
FROM   (
SELECT t.*,
ROW_NUMBER() OVER ( PARTITION BY userid ORDER BY date_time_stamp )
- ROW_NUMBER() OVER ( PARTITION BY userid, taskid ORDER BY date_time_stamp )
AS grp
FROM   table_name t
)
GROUP BY userid, taskid, grp
ORDER BY userid, start_date

对于您的样本数据：

CREATE TABLE table_name ( userid, taskid, date_time_stamp ) AS
SELECT 1, 'task-A', DATE '2021-06-16' + INTERVAL '04:17:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-A', DATE '2021-06-16' + INTERVAL '04:19:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-A', DATE '2021-06-16' + INTERVAL '04:27:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-B', DATE '2021-06-16' + INTERVAL '04:31:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-B', DATE '2021-06-16' + INTERVAL '04:33:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-B', DATE '2021-06-16' + INTERVAL '04:36:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-A', DATE '2021-06-16' + INTERVAL '04:42:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 1, 'task-A', DATE '2021-06-16' + INTERVAL '04:44:00' HOUR TO SECOND FROM DUAL

两种输出：

2021:06-16 04:27:00

USERID	TASKID	START_DATE	END_DATE
1	任务A	2021-06-16 04:17:00
1	任务-B	2021-06-16 04:31:00	2021:06-16 04:306:00
1	任务A	2021-06-16 04:42:00	2021:06-16 044:00