基于开始和结束时间戳计算MySQL实时运行进程数

我有一个名为workflows的表，包含processID、started_at、ended_at字段。需要根据以下数据，生成按时间序列展示的、指定时间点的实时运行进程数——也就是统计每个时间点（用进程的started_at作为时间戳）满足started_at < 时间点 < ended_at的进程数量，若ended_at为0001-01-01T00:00:00Z则视为该进程仍在运行。

进程时间戳表 #

id      started_at              ended_at
------- --------------------    --------------------
1203914 2023-04-20T04:54:29Z    2023-04-20T20:43:53Z
1197674 2023-04-20T06:00:28Z    2023-04-20T21:17:53Z
1212050 2023-04-20T18:47:29Z    0001-01-01T00:00:00Z
1198434 2023-04-22T18:16:53Z    2023-04-22T19:02:59Z
1210450 2023-04-22T19:06:53Z    2023-04-26T03:23:39Z
1210466 2023-04-23T05:34:53Z    2023-04-25T07:09:39Z
1201986 2023-04-24T06:30:53Z    2023-04-24T23:49:53Z
1200122 2023-04-24T17:22:53Z    2023-04-25T05:29:39Z
1209114 2023-04-25T01:07:53Z    2023-04-26T23:03:39Z
1198570 2023-04-25T01:10:53Z    2023-04-27T00:59:38Z

期望输出 #

timestamp               running_process_count
--------------------    ---------------------
2023-04-20T04:54:29Z    1
2023-04-20T06:00:28Z    2
2023-04-20T18:47:29Z    3
2023-04-22T18:16:53Z    1
2023-04-22T19:06:53Z    1
2023-04-23T05:34:53Z    2
2023-04-24T06:30:53Z    3
2023-04-24T17:22:53Z    4
2023-04-25T01:07:53Z    4

当前已有按小时统计的查询，但不符合需求，需要实现类似R语言中基于起止日期计算时间序列计数的逻辑。

解决方案 #

方法1：子查询关联统计（简单直观） #

直接提取所有需要统计的时间戳，关联原表筛选运行中的进程并计数：

SELECT
    t.timestamp,
    COUNT(w.processID) AS running_process_count
FROM
    (SELECT started_at AS timestamp FROM workflows) t
LEFT JOIN workflows w ON
    w.started_at < t.timestamp
    AND (w.ended_at > t.timestamp OR w.ended_at = '0001-01-01T00:00:00Z')
GROUP BY t.timestamp
ORDER BY t.timestamp;

逻辑说明

1. 子查询t提取所有进程的started_at作为待统计的时间戳
2. 关联workflows表，筛选出在该时间戳前启动且**在该时间戳后结束（或仍在运行）**的进程
3. 按时间戳分组计数，得到每个时间点的实时运行进程数

方法2：窗口函数优化（大场景高效） #

对于数据量较大的场景，可通过事件增量+累计求和的方式提升性能：

WITH event_log AS (
    -- 记录启动事件，增量为+1
    SELECT started_at AS event_time, 1 AS delta FROM workflows
    UNION ALL
    -- 记录结束事件，增量为-1；排除仍在运行的进程
    SELECT ended_at AS event_time, -1 AS delta FROM workflows WHERE ended_at != '0001-01-01T00:00:00Z'
),
sorted_events AS (
    SELECT event_time, delta,
           -- 按时间排序，计算累计运行数
           SUM(delta) OVER (ORDER BY event_time) AS running_count
    FROM event_log
),
target_timestamps AS (
    SELECT started_at AS timestamp FROM workflows
)
SELECT
    tt.timestamp,
    se.running_count AS running_process_count
FROM target_timestamps tt
LEFT JOIN sorted_events se ON se.event_time = tt.timestamp
ORDER BY tt.timestamp;

逻辑说明

1. event_log：将进程启动记为+1事件，结束记为-1事件（排除未结束的进程）
2. sorted_events：按事件时间排序，用SUM() OVER (ORDER BY event_time)计算累计运行数，模拟进程数量的动态变化
3. target_timestamps：提取需要统计的时间点，关联到事件表得到对应时间点的运行数

注意事项 #

• 针对ended_at = '0001-01-01T00:00:00Z'的进程：视为仍在运行，不生成结束事件，且只要启动时间早于统计时间戳，就算作运行中
• 可根据业务需求调整关联条件中的</>为<=/>=，包含时间点本身的统计逻辑

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