如何使用SQL将带双时间戳的代理任务记录拆分为15分钟时间间隔？

嘿，我来帮你搞定这个问题！核心思路其实很清晰：用你的15分钟参考表和任务记录表做关联匹配，筛选出那些和任务时间段有重叠的15分钟间隔，再把任务信息和对应的间隔绑定起来就行。我给你具体的操作步骤和SQL示例：

先明确表结构（假设） #

先假设你的两张表结构大概是这样的，你可以根据实际情况调整：

• 任务记录表（比如叫agent_tasks）：包含task_id（任务ID）、agent_id（代理ID）、start_time（任务开始时间戳）、end_time（任务结束时间戳）
• 15分钟参考表（比如叫15min_intervals）：包含interval_start（每个15分钟段的开始时间，格式和任务表的时间戳一致，比如2024-05-20 09:00:00、2024-05-20 09:15:00）

核心SQL查询 #

用JOIN关联两张表，通过时间重叠的条件筛选出对应间隔，还能算出每个间隔实际被任务覆盖的时长：

SELECT
    t.task_id,
    t.agent_id,
    i.interval_start,
    -- 计算当前15分钟间隔中，任务实际覆盖的分钟数（处理非整点开始/结束的任务）
    TIMESTAMPDIFF(MINUTE,
        GREATEST(t.start_time, i.interval_start),
        LEAST(t.end_time, i.interval_start + INTERVAL 15 MINUTE)
    ) AS covered_minutes
FROM
    agent_tasks t
JOIN
    15min_intervals i
ON
    -- 关键条件：确保15分钟间隔和任务时间段有重叠
    i.interval_start < t.end_time
    AND i.interval_start + INTERVAL 15 MINUTE > t.start_time
ORDER BY
    t.task_id, i.interval_start;

逻辑解释 #

1. 关联条件：i.interval_start < t.end_time 保证间隔开始在任务结束前，i.interval_start + INTERVAL 15 MINUTE > t.start_time 保证间隔结束在任务开始后，这样就能筛选出所有和任务有重叠的15分钟间隔。
2. 覆盖时长计算：用GREATEST取任务开始和间隔开始的较晚时间，LEAST取任务结束和间隔结束的较早时间，再算两者的分钟差，就能得到这个间隔里任务实际运行的时长（比如任务从09:05开始，09:22结束，那09:00-09:15的间隔覆盖10分钟，09:15-09:30的间隔覆盖7分钟）。

特殊情况处理：如果参考表缺少所需时间段 #

要是你的15分钟参考表没有覆盖任务涉及的所有时间范围，可以用递归CTE生成需要的间隔，不用依赖现成表：

-- 先递归生成指定时间范围内的所有15分钟间隔
WITH RECURSIVE 15min_intervals AS (
    SELECT '2024-01-01 00:00:00' AS interval_start -- 起始时间
    UNION ALL
    SELECT interval_start + INTERVAL 15 MINUTE
    FROM 15min_intervals
    WHERE interval_start < '2024-12-31 23:45:00' -- 结束时间
)
-- 再和任务表关联查询
SELECT
    t.task_id,
    t.agent_id,
    i.interval_start,
    TIMESTAMPDIFF(MINUTE,
        GREATEST(t.start_time, i.interval_start),
        LEAST(t.end_time, i.interval_start + INTERVAL 15 MINUTE)
    ) AS covered_minutes
FROM
    agent_tasks t
JOIN
    15min_intervals i
ON
    i.interval_start < t.end_time
    AND i.interval_start + INTERVAL 15 MINUTE > t.start_time
ORDER BY
    t.task_id, i.interval_start;

你只需要把上面的表名、字段名、时间范围改成你实际的情况就行，这个方法能完美拆分所有任务的15分钟间隔条目~

内容的提问来源于stack exchange，提问作者E.Garcia