如何高效对同表多次自连接（含升降序）并合并连续错误状态块？

数据定义 #

现有一张状态表，省略与问题无关的属性，结构如下：

任务目标 #

需将该表转换为块视图，将连续的"error"块（如1、4-6、8-9、11）合并为单行，同时包含对应的错误发生前、发生后的状态及时间戳，结果如下：

可选解决方案 #

目前已知以下几种方案：

1. 子查询 #

SELECT 
  value AS "value_before"
  -- created AS "timestamp_before"
FROM t AS t1 
WHERE t1.value != 'error' AND t1.created < t.created 
ORDER BY t1.created DESC 
LIMIT 1

SELECT 
  value AS "value_after"
  -- created AS "timestamp_after"
FROM t AS t2 
WHERE t2.value != 'error' AND t2.created > t.created 
ORDER BY t2.created ASC
LIMIT 1

2. LATERAL JOIN（横向连接） #

使用横向连接可同时提取value和created两个字段，相比子查询能减少一半查询次数。但数据库引擎可能需要为主查询的每一行重新执行排序，因此未深入研究该方案。

3. 自连接 #

创建两个派生表t1和t2（使用CTE效果相同），分别按created降序（最新优先）和升序（最早优先）排序。t1用于匹配“早于当前记录的最新非error记录”，连接条件为t1.value != 'error' AND t1.created < t.created；t2用于匹配“晚于当前记录的最早非error记录”，连接条件为t2.value != 'error' AND t2.created > t.created。

SELECT
  t.created  "error_first_occurance",
  t1.value   "value_before",
  t1.created "timestamp_before",
  t2.value   "value_after",
  t2.created "timestamp_after"
FROM t LEFT
JOIN (
  SELECT value, created
  FROM t WHERE value != 'error'
  ORDER BY created DESC
) t1 ON t1.created < t.created LEFT
JOIN (
  SELECT value, created
  FROM t WHERE value != 'error'
  ORDER BY created ASC
) t2 ON t2.created > t.created
WHERE 
  t.value = 'error'
ORDER BY 
  t.created

该方案已能得到正确的“超集”结果，但无法保证t1.value或t2.value在同一错误块内保持一致，且无法同时获取created的MAX/MIN值及对应记录的value。

技术问询 #

当前难点在于，不仅需要通过聚合函数提取created时间戳，还需要获取对应记录的value字符串，而聚合函数无法直接获取该值。针对数十万条数据的场景，请求解答：

• 生成目标结果表的最优SQL代码是什么？
• 需创建哪些索引以实现最优查询速度？

最优SQL解决方案 #

通过窗口函数分组识别连续error块，再结合LATERAL JOIN获取前后的非error状态，以下是高效实现（兼容PostgreSQL、MySQL 8.0+、SQL Server等支持窗口函数的数据库）：

WITH error_blocks AS (
    -- 识别连续error块，为每个块分配唯一ID
    SELECT
        created,
        value,
        SUM(CASE WHEN value = 'error' AND LAG(value) OVER (ORDER BY created) != 'error' THEN 1 ELSE 0 END) OVER (ORDER BY created) AS block_id
    FROM t
    WHERE value = 'error'
),
block_summary AS (
    -- 聚合每个error块，获取首次出现时间
    SELECT
        MIN(created) AS error_first_occurance,
        block_id
    FROM error_blocks
    GROUP BY block_id
)
-- 关联获取每个块的前后状态
SELECT
    bs.error_first_occurance,
    before_rec.value AS value_before,
    before_rec.created AS timestamp_before,
    after_rec.value AS value_after,
    after_rec.created AS timestamp_after
FROM block_summary bs
-- 获取块之前的最后一条非error记录
LEFT JOIN LATERAL (
    SELECT value, created
    FROM t
    WHERE value != 'error' AND created < bs.error_first_occurance
    ORDER BY created DESC
    LIMIT 1
) before_rec ON true
-- 获取块之后的第一条非error记录
LEFT JOIN LATERAL (
    SELECT value, created
    FROM t
    WHERE value != 'error' AND created > (SELECT MAX(created) FROM error_blocks eb WHERE eb.block_id = bs.block_id)
    ORDER BY created ASC
    LIMIT 1
) after_rec ON true
ORDER BY bs.error_first_occurance;

方案优势：

• 窗口函数一次扫描完成连续error块分组，避免多次全表扫描
• LATERAL JOIN仅针对每个error块执行两次小范围查询，而非每条error记录
• 逻辑清晰，聚合与关联分离，便于维护

索引优化建议 #

针对数十万条数据场景，创建以下索引可大幅提升查询效率：

1. 核心复合索引：

CREATE INDEX idx_t_created_value ON t (created, value);

• 作用：覆盖窗口函数排序、error记录筛选、LATERAL JOIN时间范围查询等场景，避免全表扫描

2. 非error记录专用索引（可选，若非error记录占比低）：

CREATE INDEX idx_t_value_created ON t (value, created) WHERE value != 'error';

• 作用：当非error记录较少时，可让LATERAL JOIN更快定位目标记录

索引选择逻辑：

• 优先创建idx_t_created_value，适用性最广，覆盖所有核心查询场景
• 若数据库支持部分索引（如PostgreSQL），第二个索引可进一步优化非error记录查询；若不支持，第一个索引已足够

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