如何查询获取最长连续相同值的行及0、1的最长连续行

以下是针对Learning表，获取values为0和1各自最长连续序列行数据的SQL查询语句：

WITH grouped_data AS (
    SELECT
        rowID,
        values,
        -- 为连续相同的values生成唯一分组ID
        rowID - ROW_NUMBER() OVER (PARTITION BY values ORDER BY rowID) AS group_id
    FROM Learning
),
group_lengths AS (
    SELECT
        values,
        group_id,
        COUNT(*) AS sequence_length
    FROM grouped_data
    GROUP BY values, group_id
),
max_lengths AS (
    SELECT
        values,
        MAX(sequence_length) AS max_length
    FROM group_lengths
    GROUP BY values
)
SELECT ld.rowID, ld.values
FROM Learning ld
JOIN grouped_data gd ON ld.rowID = gd.rowID AND ld.values = gd.values
JOIN group_lengths gl ON gd.values = gl.values AND gd.group_id = gl.group_id
JOIN max_lengths ml ON gl.values = ml.values AND gl.sequence_length = ml.max_length
ORDER BY ld.values, ld.rowID;

逻辑说明： #

1. grouped_data CTE：通过rowID减去按values分区、rowID排序的行号，让连续相同值的行得到一致的分组ID，以此标记连续序列。
2. group_lengths CTE：统计每个分组的连续行数，也就是序列的长度。
3. max_lengths CTE：找出每个values对应的最长连续序列长度。
4. 最后通过多表关联，筛选出属于最长序列分组的所有原始行数据，并按values和rowID排序输出。

执行该查询后，会返回：

• values为0时，rowID3-5的所有行
• values为1时，rowID8-10的所有行

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