编写SQL查询计算单个任务耗时及会话累计耗时（基于时间戳）

需要编写SQL查询，基于给定的任务日志数据计算两个维度的耗时：

• 单个任务耗时：当前任务与同会话中上一个任务的时间差（Start任务耗时为0）
• 会话累计耗时：当前任务与同会话中Start任务的时间差（即从会话开始到当前任务的总耗时）

样本输入数据 #

Task    UID             sessionID   logTime
Start   170431926600021 1704319266  2024-01-03 22:01:06
Task-1  170431926700021 1704319266  2024-01-03 22:01:07
Start   170433797800098 1704337978  2024-01-04 03:12:58
Task-1  170433797900098 1704337978  2024-01-04 03:12:59
Start   170436928400009 1704369284  2024-01-04 11:54:44
Task-1  170436928500009 1704369284  2024-01-04 11:54:45
Task-2  170438477900098 1704337978  2024-01-04 16:13:00
Task-2  170448126700021 1704319266  2024-01-05 19:01:08
Task-2  170450248500009 1704369284  2024-01-06 00:54:46

期望输出格式 #

Task           UID             sessionID   logTime            Task elapsed time    total elapsed time
Start          170431926600021 1704319266  2024-01-03 22:01:06 0                    0
Task-1         170431926700021 1704319266  2024-01-03 22:01:07 1                    1
Start          170433797800098 1704337978  2024-01-04 03:12:58 0                    0
Task-1         170433797900098 1704337978  2024-01-04 03:12:59 1                    1
Start          170436928400009 1704369284  2024-01-04 11:54:44 0                    0
Task-1         170436928500009 1704369284  2024-01-04 11:54:45 1                    1
Task-2         170438477900098 1704337978  2024-01-04 16:13:00 10861                10862
Task-2         170448126700021 1704319266  2024-01-05 19:01:08 172799               172800
Task-2         170450248500009 1704369284  2024-01-06 00:54:46 133201               133202

（注：上述输出中已填充实际计算出的秒数）

SQL查询方案 #

以下是适用于大多数SQL数据库（如MySQL、PostgreSQL、SQL Server）的查询语句：

WITH session_start AS (
    -- 获取每个会话的起始时间
    SELECT 
        sessionID,
        logTime AS start_time
    FROM your_table_name
    WHERE Task = 'Start'
),
task_order AS (
    -- 为会话内任务排序，计算基础耗时
    SELECT 
        t.*,
        s.start_time,
        LAG(t.logTime) OVER (PARTITION BY t.sessionID ORDER BY t.logTime) AS prev_task_time,
        TIMESTAMPDIFF(SECOND, LAG(t.logTime) OVER (PARTITION BY t.sessionID ORDER BY t.logTime), t.logTime) AS task_elapsed,
        TIMESTAMPDIFF(SECOND, s.start_time, t.logTime) AS total_elapsed
    FROM your_table_name t
    JOIN session_start s ON t.sessionID = s.sessionID
)
SELECT 
    Task,
    UID,
    sessionID,
    logTime,
    CASE WHEN Task = 'Start' THEN 0 ELSE task_elapsed END AS `Task elapsed time`,
    CASE WHEN Task = 'Start' THEN 0 ELSE total_elapsed END AS `total elapsed time`
FROM task_order
ORDER BY sessionID, logTime;

逻辑说明 #

1. session_start CTE：提取每个会话的起始时间（Task为Start的记录）。
2. task_order CTE：
   • 关联会话起始时间到每条任务记录；
   • 使用LAG()窗口函数获取同会话中上一个任务的时间，计算单个任务耗时；
   • 直接计算当前任务与会话起始时间的差值，得到累计耗时。
3. 最终查询：将Start任务的两个耗时字段设为0，按会话和时间排序输出。

注意：不同数据库的时间差函数可能略有差异，例如PostgreSQL需使用EXTRACT(EPOCH FROM (logTime - prev_task_time))替代TIMESTAMPDIFF，可根据实际使用的数据库调整。

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