基于分组关联规则从Table A提取指定结构的6条记录

原始表数据（Table A） #

text, value, dt
1, 1, 2024-01-01
1, 2, 2024-02-01
1, 3, 2024-03-01
1+1, 4, 2024-04-01
1+1, 5, 2024-05-01
2, 6, 2024-05-01
2+1, 7, 2024-06-01
3, 8, 2024-01-01
3, 9, 2024-06-01

处理规则 #

• 仅保留text字段不含+1的记录（共6条）
• 为每条记录匹配对应前缀的text = 原text+1分组中，dt最新的那条记录的value，作为value2字段
• 若不存在对应原text+1的分组数据，value2设为null

期望输出结果 #

text, value, dt, value2
1, 1, 2024-01-01, 5
1, 2, 2024-02-01, 5
1, 3, 2024-03-01, 5
2, 6, 2024-05-01, 7
3, 8, 2024-01-01, null
3, 9, 2024-06-01, null

SQL实现方案 #

WITH plus1_latest AS (
    SELECT
        REPLACE(text, '+1', '') AS prefix,
        value AS value2
    FROM (
        SELECT
            text,
            value,
            ROW_NUMBER() OVER (PARTITION BY REPLACE(text, '+1', '') ORDER BY dt DESC) AS rn
        FROM TableA
        WHERE text LIKE '%+1'
    ) t
    WHERE rn = 1
)
SELECT
    a.text,
    a.value,
    a.dt,
    p.value2
FROM TableA a
LEFT JOIN plus1_latest p ON a.text = p.prefix
WHERE a.text NOT LIKE '%+1'
ORDER BY a.text, a.dt;

逻辑说明 #

1. 先用CTEplus1_latest处理带+1的记录：通过REPLACE提取前缀，用ROW_NUMBER()按前缀分组、dt倒序排序，取每组第一条（即时间最新的记录），得到每个前缀对应的目标value2。
2. 从原始表筛选出不含+1的记录，左关联plus1_latest匹配前缀，自动为无匹配的记录填充null，最终按text和dt排序输出。

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