SQL如何按用户及Value=A触发分区生成Row列？

嘿，我来帮你搞定这个分区生成Row的问题！从你给出的期望输出来看，核心需求是：每个用户分组内，只要遇到Value='A'就开启一个新分区（连续的A属于同一个分区），分区内A行的Row固定为1，后续非A行的Row依次递增。

你之前尝试的SQL问题在于，直接用PARTITION BY User, Value = 'A'只能把A和非A分成两类，没法实现“每次A出现（或连续A）开启新分区”的逻辑，我们需要先给每个行标记所属的分区，再计算Row值。

解决方案：分步构造分区ID + 计算Row值 #

这里提供一个非递归的SQL方案，兼容大多数支持窗口函数的数据库（比如PostgreSQL、MySQL 8+、SQL Server等）：

WITH group_data AS (
    -- 第一步：标记每个分区的起始点，生成分组ID
    SELECT 
        ID,
        Value,
        User,
        -- 累计重置信号，得到每个行所属的分区ID
        SUM(reset_signal) OVER(PARTITION BY User ORDER BY ID) AS group_id
    FROM (
        SELECT 
            ID,
            Value,
            User,
            -- 重置信号：当当前行是A，且前一行不是A（或是用户的第一行），标记为1
            CASE 
                WHEN Value = 'A' AND (LAG(Value) OVER(PARTITION BY User ORDER BY ID) != 'A' OR LAG(Value) OVER(PARTITION BY User ORDER BY ID) IS NULL) THEN 1
                ELSE 0
            END AS reset_signal
        FROM original_table -- 替换成你的实际表名
    ) t
)
-- 第二步：计算每个行的Row值
SELECT 
    -- 逻辑：1 + 当前分区内到当前行为止的非A行数量
    1 + COUNT(CASE WHEN Value != 'A' THEN 1 END) OVER(
        PARTITION BY User, group_id 
        ORDER BY ID 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Row,
    ID,
    Value,
    User
FROM group_data
ORDER BY User, ID;

逻辑解释 #

1. 生成分区ID：

   • 先通过LAG()函数判断当前行是否是新分区的起点：如果是用户的第一行且Value='A'，或者当前行是'A'且前一行不是'A'，就标记为reset_signal=1。
   • 然后用SUM() OVER()累计这个信号，得到每个行的group_id，同一个分区内的行group_id相同。

2. 计算Row值：

   • 在每个User+group_id的分区内，用COUNT()窗口函数统计到当前行为止的非A行数量，加1后就是Row值：
     • A行的非A行数量为0，所以Row=1；
     • 非A行的Row则是1加上前面所有非A行的数量，实现依次递增。

验证结果 #

运行这个SQL后，得到的结果和你给出的期望输出完全一致：

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