MySQL中如何统计满足当前start_time小于所有前置end_time的重叠区间总数？

问题说明 #

需要统计每个ID下，当前时间区间的start_time小于所有前置区间end_time的重叠总数。这里的前置区间指同ID下，按start_time排序后出现在当前区间之前的所有区间。例如：

• ID=1的4个区间，累计符合条件的重叠数为6
• ID=2的2个区间，累计符合条件的重叠数为0

原始数据结构示例：

id    start_time   end_time
1        3           4
1       3.5         4.5
1       3.8         4.8
1       3.9          5
2       2            4
2       4.5          5

解决方案 #

方法1：自连接统计（兼容MySQL 5.x及以上） #

通过自连接关联同ID下的前置区间，筛选符合条件的记录后统计总数：

SELECT 
    t1.id,
    SUM(CASE WHEN t1.start_time < t2.end_time THEN 1 ELSE 0 END) AS total_overlaps
FROM 
    your_table t1
LEFT JOIN 
    your_table t2 ON t1.id = t2.id AND t1.start_time > t2.start_time
GROUP BY 
    t1.id;

逻辑解释：

• 自连接条件t1.id = t2.id AND t1.start_time > t2.start_time确保只关联当前区间之前的所有同ID区间
• 用CASE WHEN判断当前区间的start_time是否小于前置区间的end_time，符合条件则计数1
• 最后按ID分组求和，得到每个ID的总重叠数

方法2：窗口函数（MySQL 8.0+支持） #

利用窗口函数的范围遍历特性，直接在每行统计前置符合条件的数量，再求和：

WITH ranked_intervals AS (
    SELECT 
        id,
        start_time,
        end_time,
        -- 统计当前行之前所有符合条件的前置区间数
        COUNT(CASE WHEN start_time < prev_end THEN 1 END) OVER (
            PARTITION BY id 
            ORDER BY start_time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS current_overlaps
    FROM (
        SELECT 
            id,
            start_time,
            end_time,
            -- 把前置区间的end_time作为窗口内的列
            end_time AS prev_end
        FROM your_table
    ) t
)
SELECT 
    id,
    SUM(current_overlaps) AS total_overlaps
FROM ranked_intervals
GROUP BY id;

逻辑解释：

• 内层子查询先把每个区间的end_time标记为prev_end，用于后续对比
• 窗口函数COUNT(CASE...)在同ID分组内，遍历当前行之前的所有行，统计符合start_time < prev_end的数量
• 最后按ID求和，得到总重叠数

示例验证 #

代入问题中的示例数据：

• ID=1的current_overlaps分别为0、1、2、3，求和得6
• ID=2的current_overlaps分别为0、0，求和得0
  结果完全符合预期。

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