MySQL统计连续数值块的高效SQL查询求助

高效查询SQL（MySQL 8.0+ 推荐） #

使用窗口函数实现，性能远优于子查询和存储过程，配合索引可解决超时问题：

WITH consecutive_groups AS (
    -- 生成连续数值分组标识，连续数值的group_id相同
    SELECT
        Number,
        Number - ROW_NUMBER() OVER (ORDER BY Number) AS group_id
    FROM your_table
    -- 若存在重复Number，需添加DISTINCT去重：SELECT DISTINCT Number FROM your_table
)
-- 统计各连续长度的出现次数
SELECT
    COUNT(*) AS count,
    group_length AS amount
FROM (
    -- 计算每个连续块的长度
    SELECT
        group_id,
        COUNT(*) AS group_length
    FROM consecutive_groups
    GROUP BY group_id
) AS group_lengths
GROUP BY group_length
ORDER BY amount;

兼容MySQL 5.x版本的SQL #

若使用MySQL 5.x不支持窗口函数，可通过用户变量实现：

SELECT
    COUNT(*) AS count,
    group_length AS amount
FROM (
    SELECT
        group_id,
        COUNT(*) AS group_length
    FROM (
        SELECT
            Number,
            -- 当当前Number是前一个Number+1时，保持分组ID，否则新建分组
            @group_id := IF(Number = @prev_number + 1, @group_id, @group_id + 1) AS group_id,
            @prev_number := Number
        FROM 
            -- 先去重并按Number排序，避免重复值干扰连续判断
            (SELECT DISTINCT Number FROM your_table ORDER BY Number) AS sorted_numbers,
            -- 初始化变量
            (SELECT @group_id := 0, @prev_number := NULL) AS init_vars
    ) AS consecutive_groups
    GROUP BY group_id
) AS group_lengths
GROUP BY group_length
ORDER BY amount;

解决查询超时的关键优化 #

1. 添加索引：给Number字段创建索引，大幅提升排序效率：

CREATE INDEX idx_number ON your_table(Number);

2. 去重处理：若表中存在重复的Number值，必须用DISTINCT去重，否则会导致连续分组判断错误。

逻辑说明 #

1. 分组标识生成：连续的数值满足Number - 排序后的行号 = 固定值，利用这个特性将同一连续块的数值归为一组。
2. 计算连续块长度：按分组标识统计每组的行数，即为该连续块的长度。
3. 统计长度出现次数：对连续块长度再次分组统计，得到最终的count（长度出现次数）和amount（连续长度）。

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