基于Trino/Presto SQL按组生成序列变化周期编号

需求说明 #

按user_id分组，组内按my_date升序排列，为连续相同color的行分配同一个period_number，当color发生变化时编号递增。

可复现数据SQL #

WITH my_table AS (
    SELECT *
    FROM (VALUES
        ('a', DATE '2023-02-01', 'red'),
        ('a', DATE '2023-03-22', 'red'),
        ('a', DATE '2023-03-30', 'red'),
        ('a', DATE '2023-06-10', 'red'),
        ('a', DATE '2023-06-11', 'red'),
        ('a', DATE '2023-07-03', 'green'),
        ('a', DATE '2023-07-09', 'green'),
        ('a', DATE '2024-01-11', 'green'),
        ('a', DATE '2024-02-11', 'yellow'),
        ('a', DATE '2024-02-12', 'yellow'),
        ('a', DATE '2024-02-13', 'yellow'),
        ('a', DATE '2024-02-14', 'yellow'),
        ('b', DATE '2022-10-20', 'blue'),
        ('b', DATE '2022-10-21', 'blue'),
        ('b', DATE '2022-10-22', 'blue'),
        ('b', DATE '2022-10-23', 'brown'),
        ('b', DATE '2022-10-24', 'brown'),
        ('b', DATE '2022-10-25', 'brown'),
        ('b', DATE '2022-10-26', 'blue'),
        ('b', DATE '2022-10-27', 'blue')
    ) AS t(user_id, my_date, color)
)
SELECT * FROM my_table;

正确实现SQL #

基于你已经标记断点的思路，只需将断点标记转换为数值后累加，即可得到连续的period_number：

WITH my_table AS (
    SELECT *
    FROM (VALUES
        ('a', DATE '2023-02-01', 'red'),
        ('a', DATE '2023-03-22', 'red'),
        ('a', DATE '2023-03-30', 'red'),
        ('a', DATE '2023-06-10', 'red'),
        ('a', DATE '2023-06-11', 'red'),
        ('a', DATE '2023-07-03', 'green'),
        ('a', DATE '2023-07-09', 'green'),
        ('a', DATE '2024-01-11', 'green'),
        ('a', DATE '2024-02-11', 'yellow'),
        ('a', DATE '2024-02-12', 'yellow'),
        ('a', DATE '2024-02-13', 'yellow'),
        ('a', DATE '2024-02-14', 'yellow'),
        ('b', DATE '2022-10-20', 'blue'),
        ('b', DATE '2022-10-21', 'blue'),
        ('b', DATE '2022-10-22', 'blue'),
        ('b', DATE '2022-10-23', 'brown'),
        ('b', DATE '2022-10-24', 'brown'),
        ('b', DATE '2022-10-25', 'brown'),
        ('b', DATE '2022-10-26', 'blue'),
        ('b', DATE '2022-10-27', 'blue')
    ) AS t(user_id, my_date, color)
),
breaking_points_marked AS (
    SELECT 
        *, 
        -- 标记新周期起始行：第一行或颜色与上一行不同时为true
        lag(color) OVER (PARTITION BY user_id ORDER BY my_date) IS DISTINCT FROM color AS is_starting_a_new_period
    FROM my_table
)
SELECT 
    user_id, 
    my_date, 
    color,
    -- 将boolean转换为1/0，然后累加得到周期编号
    SUM(CASE WHEN is_starting_a_new_period THEN 1 ELSE 0 END) OVER (PARTITION BY user_id ORDER BY my_date) AS period_number
FROM breaking_points_marked
ORDER BY user_id, my_date;

实现思路 #

• 标记断点：使用lag()窗口函数获取当前行的上一行color，对比后标记是否为新周期的起始行（is_starting_a_new_period）。
• 累加生成编号：将断点标记转换为数值（新周期为1，否则为0），再通过SUM() OVER()窗口函数按user_id分组、my_date排序累加，得到连续的period_number。

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