如何在SQL中为分区内符合条件的每个前置行生成对应数据行？

看起来你之前用LEAD()函数的时候碰到了瓶颈——LEAD()只能返回分区内的下一个单值，但你需要的是把当前行复制多份，每一份对应分区内一个符合条件的前置行的val作为lead字段对吧？我来给你讲清楚怎么实现这个需求。

核心思路 #

你要的不是单个前置值，而是行展开：让每个当前行和它所有符合条件的前置行做关联，生成多行结果。这种场景下，LEAD()这类单行窗口函数就不够用了，得用自连接或者横向关联的方式来实现。

从你的示例结果来看，你的“前置行”定义是：同一part分区中，属于当前行col值的上一组连续相同col的所有行（比如col=3的行要关联所有col=2的行，col=4的行关联所有col=3的行）。下面我就基于这个条件给出具体实现，你也可以根据实际需求调整条件。

具体实现代码（以PostgreSQL为例） #

我把代码拆成了几个步骤，方便你理解：

-- 第一步：给每个分区内连续相同的col分组，标记分组序号grp
WITH grouped_cols AS (
    SELECT
        id, part, col, val,
        -- 当当前行col和上一行不同时，分组序号+1，否则保持不变
        SUM(CASE WHEN col = LAG(col) OVER (PARTITION BY part ORDER BY id) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY part ORDER BY id) AS grp
    FROM your_table
),
-- 第二步：获取每个分组对应的前一个分组的col值
ranked_groups AS (
    SELECT
        *,
        MAX(col) OVER (PARTITION BY part, grp) AS current_col,
        -- 前一个分组的col值，第一组的prev_col会是NULL
        MAX(col) OVER (PARTITION BY part, grp - 1) AS prev_col
    FROM grouped_cols
)
-- 第三步：关联当前行和前一个分组的所有行，展开结果
SELECT
    rg.id,
    rg.part,
    rg.col,
    rg.val,
    yt.val AS lead,
    -- 生成解释字段（可选）
    CASE 
        WHEN rg.prev_col IS NULL THEN NULL
        ELSE '来自分区' || rg.part || '中col=' || rg.prev_col || '的前置行的值' 
    END AS explanation
FROM ranked_groups rg
-- 用LEFT JOIN保留没有前置行的行（比如每个分区的第一组）
LEFT JOIN your_table yt
    ON yt.part = rg.part
    AND yt.col = rg.prev_col
-- 按分区、id排序，和你的示例结果顺序一致
ORDER BY rg.part, rg.id, yt.val;

按需调整条件 #

如果你的“前置行”判断条件不是“上一组连续col”，只需要修改LEFT JOIN的条件即可：

• 要是需要同一分区中id小于当前行的所有行：把关联条件改成yt.part = rg.part AND yt.id < rg.id
• 要是需要同一分区中col小于当前行的所有行：把关联条件改成yt.part = rg.part AND yt.col < rg.col

其他数据库适配 #

• SQL Server：把横向关联换成CROSS APPLY/OUTER APPLY，窗口函数用法基本一致
• MySQL 8.0+：支持窗口函数和LATERAL JOIN，代码可以直接调整使用
• Oracle：用CROSS JOIN LATERAL或者OUTER JOIN LATERAL，窗口函数语法类似

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