如何用Presto SQL检测从非Other类到Other类的首次转换

需要从给定数据集中提取**首次从class不属于(Other, Others)转换到class属于(Other, Others)**的记录——即找到每个连续非Other/Others组的最后一行，以及它紧跟的第一个Other/Others行。当前已实现检测最后一次转换的逻辑，但无法获取历史中所有这类转换，使用Presto查询引擎。

原始数据集 #

期望结果 #

Presto解决方案 #

通过窗口函数对连续同类型行分组，再关联相邻分组提取转换记录：

WITH grouped_data AS (
    SELECT
        row_id,
        class,
        CASE WHEN class IN ('Other', 'Others') THEN 1 ELSE 0 END AS is_other,
        -- 生成连续分组ID：类型变化时分组ID递增
        SUM(CASE WHEN prev_is_other != CASE WHEN class IN ('Other', 'Others') THEN 1 ELSE 0 END THEN 1 ELSE 0 END) OVER (ORDER BY row_id) AS group_id
    FROM (
        SELECT
            row_id,
            class,
            -- 获取上一行的类型标记
            LAG(CASE WHEN class IN ('Other', 'Others') THEN 1 ELSE 0 END) OVER (ORDER BY row_id) AS prev_is_other
        FROM your_table_name -- 替换为你的实际表名
    ) t
),
group_info AS (
    SELECT
        group_id,
        MAX(CASE WHEN is_other = 0 THEN row_id END) AS non_other_last_row,
        MAX(CASE WHEN is_other = 1 THEN row_id END) AS other_first_row,
        MAX(CASE WHEN is_other = 0 THEN class END) AS non_other_class,
        MAX(CASE WHEN is_other = 1 THEN class END) AS other_class
    FROM grouped_data
    GROUP BY group_id
)
SELECT
    gi.non_other_last_row AS row_id,
    gi.non_other_class AS class,
    next_g.other_first_row AS prev_row_id,
    next_g.other_class AS prev_class
FROM group_info gi
JOIN group_info next_g ON gi.group_id + 1 = next_g.group_id
WHERE gi.non_other_last_row IS NOT NULL
  AND next_g.other_first_row IS NOT NULL
ORDER BY gi.non_other_last_row;

逻辑说明 #

1. 标记分组：用LAG()获取上一行的类型（是否为Other/Others），通过累加类型变化次数生成连续分组ID，把连续相同类型的行归为同一组。
2. 提取分组关键信息：每个分组中，分别提取非Other/Others组的最后一行ID和class，以及Other/Others组的第一行ID和class。
3. 关联相邻分组：将非Other/Others分组与紧随其后的Other/Others分组关联，得到所需的转换记录。

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