如何用SQL查询存在时长重叠事件的唯一EventParentId

数据表结构 #

需求说明 #

需要查询所有存在时长重叠事件的唯一EventParentId，其中：

• eastern_ts 和 pacific_ts 是事件的开始时间戳（二者必有一个非空）
• Duration 是事件持续时长（单位：秒）

示例数据 #

规则与预期结果 #

• 同一EventParentId下的两个事件，若时间范围有重叠，则该EventParentId需要被返回
• 示例中：EventParentId 98765、34567下的事件存在重叠，需返回；65432下无重叠，无需返回
• 预期结果（返回每个重叠组的其中一条记录即可）：

解决方案：用SQL完全可以实现，且比Java筛选更高效 #

核心逻辑 #

1. 先统一计算每个事件的开始时间戳和结束时间戳：
   • 开始时间：用COALESCE函数取pacific_ts或eastern_ts中的非空值（start_ts = COALESCE(pacific_ts, eastern_ts)）
   • 结束时间：开始时间 + 持续时长（end_ts = start_ts + Duration）
2. 通过自连接或EXISTS子查询，找到同一EventParentId下存在时间重叠的事件组

实现SQL语句 #

方式1：获取所有存在重叠的唯一EventParentId

SELECT DISTINCT a.EventParentId
FROM your_table a
JOIN your_table b 
  ON a.EventParentId = b.EventParentId 
  AND a.EventId != b.EventId  -- 排除和自身比较
WHERE 
  -- 判断a事件的开始时间早于b事件的结束时间
  COALESCE(a.pacific_ts, a.eastern_ts) < COALESCE(b.pacific_ts, b.eastern_ts) + b.Duration
  -- 判断b事件的开始时间早于a事件的结束时间（双向判断确保重叠）
  AND COALESCE(b.pacific_ts, b.eastern_ts) < COALESCE(a.pacific_ts, a.eastern_ts) + a.Duration;

方式2：获取预期结果中的具体事件记录

如果需要返回像示例中的具体事件行，可以用EXISTS子查询：

SELECT *
FROM your_table main
WHERE EXISTS (
  SELECT 1
  FROM your_table sub
  WHERE sub.EventParentId = main.EventParentId
    AND sub.EventId != main.EventId
    AND COALESCE(sub.pacific_ts, sub.eastern_ts) < COALESCE(main.pacific_ts, main.eastern_ts) + main.Duration
    AND COALESCE(main.pacific_ts, main.eastern_ts) < COALESCE(sub.pacific_ts, sub.eastern_ts) + sub.Duration
)
-- 可选：每个EventParentId只返回一条记录
GROUP BY main.EventParentId;

为什么选SQL而非Java？ #

• 性能更优：数据库擅长处理这类关联、聚合查询，数据量较大时，在数据库层面过滤比把全量数据拉到Java内存中处理快得多
• 代码更简洁：几行SQL就能完成逻辑，而Java需要写分组、循环、时间判断等大量代码
• 维护更方便：SQL逻辑直接在数据库层面，后续调整规则只需修改SQL，无需改动应用代码

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