如何编写SQL查询获取type='G'事件对应Z事件的起止时间区间

解决方案 #

通用兼容写法（支持所有SQL方言）

通过相关子查询为每个G事件匹配后续时间最近的Z=0事件：

SELECT
    g.t AS t1,
    (
        SELECT z.t
        FROM event z
        WHERE z.event = 'Z' 
          AND z.value = 0 
          AND z.t > g.t
        ORDER BY z.t ASC
        LIMIT 1
    ) AS t2
FROM event g
WHERE g.event = 'type' 
  AND g.value = 'G'
ORDER BY t1;

高性能写法（支持MySQL 8.0+/PostgreSQL/SQL Server等支持CTE的数据库）

通过CTE分别提取G事件和Z=0事件，再关联取最小匹配值，大数据量下性能更优：

WITH g_events AS (
    -- 提取所有G事件的时间作为t1来源
    SELECT t AS t1
    FROM event
    WHERE event = 'type' AND value = 'G'
),
z0_events AS (
    -- 提取所有Z=0事件的时间作为t2候选
    SELECT t AS t2
    FROM event
    WHERE event = 'Z' AND value = 0
)
SELECT
    t1,
    MIN(t2) AS t2
FROM g_events
-- 关联大于G事件时间的Z=0事件，取最小的就是对应区间结束时间
INNER JOIN z0_events ON z0_events.t2 > g_events.t1
GROUP BY t1
ORDER BY t1;

说明

如果存在部分G事件没有对应的后续Z=0事件，上述查询中t2会返回NULL，如需过滤这类无效区间，可以在查询末尾添加HAVING t2 IS NOT NULL条件。

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