如何用SQL获取每位员工各任务的开始与结束时间

输入数据 #

假设数据表名为task_records，原始数据如下：

emp_id  task    timestamp
100     A       15/04/2023 02:01
100     A       15/04/2023 02:06
100     A       15/04/2023 02:17
100     B       15/04/2023 02:24
100     B       15/04/2023 02:34
100     A       16/04/2023 10:34
100     A       16/04/2023 10:36
100     A       16/04/2023 10:39
101     A       16/04/2023 20:34
101     A       16/04/2023 20:36

需求分析 #

需要将同一员工连续执行的同一任务合并，取该时段的最早（开始）和最晚（结束）时间；若同一员工的同一任务中断后再次执行，需作为不同的任务时段单独统计（比如员工100的任务A在15日和16日的两次执行）。

解决方案SQL代码 #

WITH task_groups AS (
    SELECT 
        emp_id,
        task,
        timestamp,
        -- 标记连续任务组：当上一条记录的任务与当前不同时，生成新分组
        SUM(CASE WHEN prev_task = task THEN 0 ELSE 1 END) OVER (PARTITION BY emp_id ORDER BY timestamp) AS group_id
    FROM (
        SELECT 
            emp_id,
            task,
            timestamp,
            -- 获取上一条记录的任务
            LAG(task) OVER (PARTITION BY emp_id ORDER BY timestamp) AS prev_task
        FROM task_records
    ) t
)
SELECT 
    emp_id AS id,
    task,
    MIN(timestamp) AS Start,
    MAX(timestamp) AS End
FROM task_groups
GROUP BY emp_id, task, group_id
ORDER BY emp_id, Start;

代码逻辑说明 #

1. 内层子查询用LAG()窗口函数，按员工分组、时间排序，获取每条记录的上一条任务名称；
2. 外层CTE通过SUM()窗口函数生成连续任务的分组ID：当上一条任务和当前不同时，分组ID加1，以此区分同一员工的不同任务时段；
3. 最后按员工ID、任务、分组ID聚合，取每个分组的最早和最晚时间，得到各任务时段的起止时间。

执行结果 #

id  task    Start               End
100 A       15/04/2023 02:01    15/04/2023 02:17
100 B       15/04/2023 02:24    15/04/2023 02:34
100 A       16/04/2023 10:34    16/04/2023 10:39
101 A       16/04/2023 20:34    16/04/2023 20:36

内容的提问来源于stack exchange，提问作者Rajesh G