按user_id分区筛选Begin后数据并生成Rank列的SQL实现

原始数据表 #

user_id   |   timestamp |   value
----------------------------------
123-456      2023-01-02    open
123-456      2023-01-03    open
123-456      2023-01-05    Begin
123-456      2023-01-07    open
123-456      2023-01-09    solved
123-456      2023-01-11    open
123-456      2023-01-13    solved
234-567      2023-01-04    Begin
234-567      2023-01-05    open
234-567      2023-01-07    open
234-567      2023-01-13    solved

需求说明 #

• 按user_id分区，仅保留每个用户的第一条Begin记录
• 筛选Begin之后的记录，将连续的open（直到下一个solved）视为同一组，每个open→solved的完整序列（含对应Begin）分配同一个递增的Rank值
• 最终输出需包含Begin、对应组的open和solved记录，以及每组的Rank

用户尝试的SQL（存在问题） #

Select user_id, 
first_value(timestamp over (partition by user_id, value order by timestamp asc),
dense_rank() over (partition by value,user_id) 
from table_1

问题分析 #

1. 语法错误：first_value函数括号位置错误，缺少闭合括号，且未明确指定取数字段（正确写法应为first_value(timestamp) over (...)）
2. 逻辑偏差：
   • 按user_id, value分区取first_value无法定位每个用户的第一条Begin
   • dense_rank按value, user_id分区，会给同一用户的同状态记录打相同排名，完全无法实现open→solved组的排名需求

正确SQL实现 #

WITH user_records AS (
    -- 标记每个用户的第一条Begin，过滤Begin之前的无效open
    SELECT 
        user_id,
        timestamp,
        value,
        CASE WHEN value = 'Begin' AND ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp) = 1 THEN 1 ELSE 0 END AS is_first_begin,
        SUM(CASE WHEN value = 'Begin' THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS begin_count
    FROM table_1
),
filtered_records AS (
    -- 保留第一条Begin及之后的所有记录
    SELECT 
        user_id,
        timestamp,
        value,
        is_first_begin
    FROM user_records
    WHERE begin_count >= 1
),
rank_groups AS (
    -- 按用户分区，以solved为分组标记计算Rank
    SELECT 
        user_id,
        timestamp,
        value,
        DENSE_RANK() OVER (PARTITION BY user_id ORDER BY solved_group) AS Rank
    FROM (
        SELECT 
            *,
            SUM(CASE WHEN value = 'solved' THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS solved_group
        FROM filtered_records
        WHERE NOT (value = 'Begin' AND is_first_begin = 0)
    ) t
)
-- 最终输出并排序
SELECT 
    user_id,
    timestamp,
    value,
    Rank
FROM rank_groups
ORDER BY user_id, timestamp;

步骤解释 #

1. user_records CTE：给每个用户的第一条Begin打标记，同时计算累计Begin数量，用于过滤Begin之前的无效open
2. filtered_records CTE：只保留Begin出现后的所有记录，以及第一条Begin
3. rank_groups CTE：以solved记录为分组节点，统计每条记录之前的solved数量作为分组依据，再用DENSE_RANK生成用户内的组排名
4. 最终查询：按用户和时间排序输出，得到符合需求的分组结果

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