两种方法解决连续分组问题

两种方法处理连续分组问题

论坛有个问题是需要写一段sql实现以下图片处理结果：

方案一：行号差值法（原答案）

目标： 将数据按某字段分组，每组内连续的记录合并为一段

核心思路： 利用行号差值识别连续段

SELECT MIN(fname) AS fmin, MAX(fname) AS fmax, rn, fsex 
FROM (
    SELECT fname, fsex,
           ROW_NUMBER() OVER (ORDER BY fname) - 
           ROW_NUMBER() OVER (PARTITION BY fsex ORDER BY fname) AS rn
    FROM temp
) t 
GROUP BY fsex, rn 
ORDER BY fmin;

算法原理

步骤	说明
1	全局排序行号：ROW_NUMBER() OVER (ORDER BY fname)
2	分组内排序行号：ROW_NUMBER() OVER (PARTITION BY fsex ORDER BY fname)
3	计算差值：相同差值表示连续段
4	按差值分组：GROUP BY fsex, rn

方案二：LAG函数判断法（推荐）

核心思路： 通过LAG函数判断分组字段是否发生变化，累计变化次数作为分组标识

SELECT MIN(fname) AS fmin
      ,MAX(fname) AS fmax
      ,fsex
      ,COUNT(*) AS cnt
FROM (
     SELECT fname
            ,fsex
            ,SUM(is_equal_last_one) OVER (ORDER BY fname) AS grp -- 组序号
     FROM (
             SELECT fname
                    ,fsex
                    ,CASE WHEN LAG(fsex) OVER (ORDER BY fname) != fsex OR LAG(fsex) OVER (ORDER BY fname) IS NULL THEN 1 
                          ELSE 0 END AS is_equal_last_one  -- 是否等于上一个值
                FROM temp
           ) t 
   ) t1 
GROUP BY fsex, grp
ORDER BY fmin;

算法分析

三层嵌套逻辑：

1. 内层查询：使用LAG函数判断当前行与上一行的fsex是否相同

   • LAG(fsex) OVER (ORDER BY fname)：获取按fname排序的上一行fsex值
   • 当fsex发生变化或为第一行时，标记为1，否则为0

2. 中层查询：使用SUM窗口函数累计变化标记

   • SUM(is_equal_last_one) OVER (ORDER BY fname)：累计变化次数
   • 相同的累计值表示属于同一个连续段

3. 外层查询：按分组标识聚合数据

   • 计算每个连续段的最小值、最大值和记录数

优势对比：

• 逻辑直观：直接判断相邻记录是否属于同一组
• 性能更好：避免了复杂的行号差值计算
• 易于理解：分步骤处理，每层逻辑清晰

应用场景

1. 连续日期分组（算连续登录天数，连续异常的天数等，也可以用这个思路）

-- 将连续的日期分组
SELECT MIN(date_col) AS start_date, MAX(date_col) AS end_date, group_id
FROM (
    SELECT date_col,
           ROW_NUMBER() OVER (ORDER BY date_col) - 
           ROW_NUMBER() OVER (PARTITION BY status ORDER BY date_col) AS group_id
    FROM date_table
) t
GROUP BY group_id;

2. 连续数字分组

-- 将连续的数字分组
SELECT MIN(num) AS start_num, MAX(num) AS end_num
FROM (
    SELECT num,
           num - ROW_NUMBER() OVER (ORDER BY num) AS group_id
    FROM number_table
) t
GROUP BY group_id;

方案选择建议

方案	优势	适用场景	推荐度
行号差值法	经典算法，通用性强	各种分组场景	⭐⭐⭐⭐
LAG函数法	逻辑直观，性能更好	大数据量，复杂业务	⭐⭐⭐⭐⭐

总结

两种方案都能有效解决分组分段问题：

• 行号差值法：数学技巧巧妙，代码简洁
• LAG函数法：业务逻辑清晰，便于维护和扩展

推荐在实际项目中优先使用LAG函数法，特别是在需要处理复杂业务逻辑或大数据量的场景下。

参考： FR社区讨论

补充构建数据的语句

-- 建表语句
CREATE TABLE `temp` (
  `fname` varchar(100) DEFAULT NULL,
  `fsex` varchar(100) DEFAULT NULL,
  `id` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

-- 插入模拟数据语句
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A001', '男', '1');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A002', '男', '2');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A003', '女', '4');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A004', '女', '5');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A005', '男', '6');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A006', '男', '7');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A007', '男', '8');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A008', '男', '9');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A009', '男', '10');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A010', '男', '15');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A011', '女', '16');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A012', '男', '17');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A013', '男', '18');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A014', '男', '19');
INSERT INTO ecpdb.temp (fname, fsex, id) VALUES('A015', '男', '20');

方案一，结果图：

方案二，结果图：