MySQL统计连续值：已尝试分区未达预期，求正确实现方案

核心思路 #

通过生成分组标识将连续相同值的行归为同一组，再对每组做聚合统计，就能得到你要的连续值计数结果。

针对MySQL 8.0+版本的实现（推荐） #

假设你的数据表为test，包含自增主键id（保证数据顺序）和需要统计的value字段，执行以下SQL：

WITH grouped_data AS (
    SELECT 
        id,
        value,
        -- 同一连续值的行，id减去行号的结果会一致，以此作为分组标识
        id - ROW_NUMBER() OVER (PARTITION BY value ORDER BY id) AS group_id
    FROM test
)
SELECT
    MIN(id) AS start_id,
    MAX(id) AS end_id,
    value,
    COUNT(*) AS consecutive_count
FROM grouped_data
GROUP BY value, group_id
ORDER BY start_id;

代码说明 #

1. 先用CTE生成分组数据：通过ROW_NUMBER()按value分区、id排序，给每行分配行号；用id - 行号得到的group_id，会让连续相同value的行归为同一组。
2. 再对分组数据聚合：按value和group_id分组，统计每组的起止ID、目标值和连续出现次数，最后按起始ID排序得到预期结果。

适配MySQL 5.x版本（无窗口函数） #

如果你的MySQL版本不支持窗口函数，用变量实现相同逻辑：

SELECT
    MIN(id) AS start_id,
    MAX(id) AS end_id,
    value,
    COUNT(*) AS consecutive_count
FROM (
    SELECT
        id,
        value,
        @group_id := IF(@prev_value = value, @group_id, @group_id + 1) AS group_id,
        @prev_value := value
    FROM test,
         (SELECT @prev_value := NULL, @group_id := 0) AS init_vars
    ORDER BY id
) AS grouped_data
GROUP BY value, group_id
ORDER BY start_id;

通过变量@prev_value记录上一行的value，如果当前行与上一行值相同则保持分组ID，否则递增分组ID，以此实现连续值分组。

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