请求协助：如何按位置统计列中连续重复值的出现次数？

问题描述 #

原始列数据：

column1
x
x
x
y
y
x
x
y

期望统计连续重复值的出现次数，结果如下：

x   3
y   2
x   2
y   1

原脚本问题分析 #

你提供的SQL无法得到正确结果，原因在于：

• 按column1和countt（全局行号）分组，相当于每行单独成组，无法识别连续的相同值区间。
• 用ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY (select null))得到的是每个值在全局的序号，不是连续组内的计数。

修正方案：使用间隙法分组 #

核心思路是通过计算行号差值，标记出连续相同值的分组，再统计每组的行数。

修正后的SQL：

WITH numbered AS (
    -- 生成全局行号，确保数据顺序（如果表有自增列/时间列，建议替换为实际排序字段）
    SELECT 
        column1,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS global_row
    FROM [vf_test_v02]
),
grouped AS (
    -- 计算每个连续相同值的组标识：全局行号 - 同值内的行号
    SELECT 
        column1,
        global_row - ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY global_row) AS group_id
    FROM numbered
)
-- 按值和组标识分组，统计每组行数，最后按组的起始行号排序
SELECT 
    column1,
    COUNT(*) AS consecutive_count
FROM grouped
GROUP BY column1, group_id
ORDER BY MIN(global_row);

代码解释 #

1. numbered CTE：给每行生成全局行号，保证数据的顺序（如果表有明确的排序字段，比如id或create_time，一定要替换ORDER BY (SELECT NULL)，避免排序不稳定）。
2. grouped CTE：对每个column1的值生成组内行号，用全局行号减去组内行号，得到的group_id会对连续相同的值保持一致，不同的连续组则不同。
3. 最终分组统计：按column1和group_id分组，COUNT(*)就是该组连续重复的次数，最后按组的最小全局行号排序，确保结果顺序和原始数据一致。

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