如何统计数字序列（Pattern）？附原始数据表与预期输出示例

输入数据表 #

期望输出 #

解决方案（支持窗口函数的SQL数据库：MySQL 8.0+/PostgreSQL/SQL Server） #

通过窗口函数识别连续相同值的分组，累计变化次数生成序列编号：

SELECT 
    ID,
    MW1,
    SUM(change_flag) OVER (ORDER BY ID) AS CountSequenz
FROM (
    SELECT 
        ID,
        MW1,
        CASE 
            WHEN MW1 != LAG(MW1) OVER (ORDER BY ID) THEN 1
            ELSE 0
        END AS change_flag
    FROM your_table_name
) t
ORDER BY ID;

逻辑说明 #

1. LAG(MW1) OVER (ORDER BY ID)：按ID排序，获取当前行的上一行MW1值，第一行无前置行时返回NULL
2. CASE语句：判断当前行MW1与上一行是否不同，不同则标记为1（表示序列发生变化），否则为0
3. SUM(change_flag) OVER (ORDER BY ID)：对变化标记进行累计求和，每遇到一次变化就递增序列编号，最终得到连续相同值的分组编号

兼容旧版数据库（如MySQL 5.x）的写法 #

如果数据库不支持窗口函数，可使用变量实现：

SET @seq = 1;
SET @prev_mw1 = (SELECT MW1 FROM your_table_name ORDER BY ID LIMIT 1);

SELECT 
    ID,
    MW1,
    @seq := CASE WHEN MW1 != @prev_mw1 THEN @seq + 1 ELSE @seq END AS CountSequenz,
    @prev_mw1 := MW1
FROM your_table_name
ORDER BY ID;

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