如何高效为type1行填充对应分组的下一个type2的id值

需求说明 #

现有logs表包含id、name、type、date列，需要为每条type1记录新增type2_id列，存储同name分组下该记录之后的第一个type2记录的id，最终仅保留type1行数据。

原始表 #

期望输出 #

现有方案的不足 #

原方案通过JOIN+LAG实现，但关联逻辑复杂，容易产生不必要的笛卡尔积，大数据量下性能表现一般。

高效替代方案 #

方案1：使用LATERAL JOIN（推荐，兼容MySQL 8+/PostgreSQL/SQL Server） #

针对每个type1行，直接精准查找同分组下最早的、日期晚于当前行的type2记录，逻辑直观且性能优异：

WITH logs AS (
  SELECT 1 AS id, 'name1' AS name, 'type1' AS type, '2022-01-01' AS date
  UNION ALL
  SELECT 2 AS id, 'name1' AS name, 'type1' AS type, '2022-01-02' AS date
  UNION ALL
  SELECT 3 AS id, 'name1' AS name, 'type2' AS type, '2022-01-03' AS date
  UNION ALL
  SELECT 4 AS id, 'name1' AS name, 'type1' AS type, '2022-01-03' AS date
  UNION ALL
  SELECT 5 AS id, 'name1' AS name, 'type2' AS type, '2022-01-04' AS date
  UNION ALL
  SELECT 6 AS id, 'name1' AS name, 'type1' AS type, '2022-01-05' AS date
  UNION ALL
  SELECT 7 AS id, 'name2' AS name, 'type1' AS type, '2022-01-03' AS date
  UNION ALL
  SELECT 8 AS id, 'name2' AS name, 'type2' AS type, '2022-01-08' AS date
)
SELECT 
  t1.id,
  t1.name,
  t1.type,
  t1.date,
  t2.id AS type2_id
FROM logs t1
LEFT JOIN LATERAL (
  SELECT id 
  FROM logs 
  WHERE name = t1.name 
    AND type = 'type2' 
    AND date > t1.date
  ORDER BY date ASC
  LIMIT 1
) t2 ON TRUE
WHERE t1.type = 'type1'
ORDER BY t1.name, t1.date;

优势

• 避免全量LAG计算，针对每条type1行精准查找目标数据
• LIMIT 1直接终止查找，减少无效计算
• 若建立(name, type, date)联合索引，查询效率会大幅提升

方案2：窗口函数一次扫描实现 #

通过一次全表扫描，用窗口函数计算每个位置之后的第一个type2信息，适合大数据量场景：

WITH logs AS (
  SELECT 1 AS id, 'name1' AS name, 'type1' AS type, '2022-01-01' AS date
  UNION ALL
  SELECT 2 AS id, 'name1' AS name, 'type1' AS type, '2022-01-02' AS date
  UNION ALL
  SELECT 3 AS id, 'name1' AS name, 'type2' AS type, '2022-01-03' AS date
  UNION ALL
  SELECT 4 AS id, 'name1' AS name, 'type1' AS type, '2022-01-03' AS date
  UNION ALL
  SELECT 5 AS id, 'name1' AS name, 'type2' AS type, '2022-01-04' AS date
  UNION ALL
  SELECT 6 AS id, 'name1' AS name, 'type1' AS type, '2022-01-05' AS date
  UNION ALL
  SELECT 7 AS id, 'name2' AS name, 'type1' AS type, '2022-01-03' AS date
  UNION ALL
  SELECT 8 AS id, 'name2' AS name, 'type2' AS type, '2022-01-08' AS date
),
ranked_logs AS (
  SELECT 
    *,
    -- 取当前行之后最近的type2的id
    LAST_VALUE(CASE WHEN type = 'type2' THEN id END) OVER (
      PARTITION BY name 
      ORDER BY date 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS temp_type2_id,
    -- 取当前行之后第一个type2的日期
    MIN(CASE WHEN type = 'type2' THEN date END) OVER (
      PARTITION BY name 
      ORDER BY date 
      ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS next_type2_date
  FROM logs
)
SELECT 
  id,
  name,
  type,
  date,
  -- 判断是否存在后续的type2，存在则取id
  CASE WHEN next_type2_date > date THEN temp_type2_id ELSE NULL END AS type2_id
FROM ranked_logs
WHERE type = 'type1'
ORDER BY name, date;

优势

• 仅需一次全表扫描，避免多次关联操作
• 逻辑清晰，兼容大多数支持窗口函数的数据库

总结 #

如果数据库支持LATERAL JOIN/CROSS APPLY，优先选择方案1，性能和可读性最优；若需兼容更多数据库或处理超大数据量，方案2是高效的替代选择。

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