如何统计MySQL数据中各状态的最大连续出现次数？

我从MySQL数据库中获取到如下数据：

需要找出Red、Green、Grey各自的最高连续出现次数（注意：不是总出现次数），期望结果为：Green: 4，Red: 2，Grey: 1。

我尝试过两个相关方案，但均不符合需求。自己编写了一段PHP代码，仅在部分场景下有效：

$consecResult = array();
$prev_value = array("value" => null, "amount" => null);
while($row = $result->fetch_assoc()){
    if ($prev_value['value'] != $row['status']) 
    {
       unset($prev_value);
       $prev_value = array('value' => $row['status'], 'amount' => 0);
       $consecResult[$row['status']] =& $prev_value;
    }
    $prev_value['amount']++;
}

可行解决方案 #

方案一：PHP代码实现

上述代码的问题在于仅保留了每个状态最后一段连续序列的长度，未记录历史最大连续次数。修正后的代码如下：

$maxConsecutive = [];
$currentStatus = null;
$currentCount = 0;

// 遍历数据库查询结果
while ($row = $result->fetch_assoc()) {
    $status = $row['status'];
    
    if ($status === $currentStatus) {
        // 当前状态与上一个相同，计数加1
        $currentCount++;
    } else {
        // 切换状态时，更新上一个状态的最大连续次数
        if ($currentStatus !== null) {
            if (!isset($maxConsecutive[$currentStatus]) || $currentCount > $maxConsecutive[$currentStatus]) {
                $maxConsecutive[$currentStatus] = $currentCount;
            }
        }
        // 重置当前状态和计数
        $currentStatus = $status;
        $currentCount = 1;
    }
}

// 处理最后一段连续序列
if ($currentStatus !== null) {
    if (!isset($maxConsecutive[$currentStatus]) || $currentCount > $maxConsecutive[$currentStatus]) {
        $maxConsecutive[$currentStatus] = $currentCount;
    }
}

// 输出结果示例
foreach ($maxConsecutive as $status => $count) {
    echo "{$status}: {$count} ";
}

逻辑说明：

• 遍历过程中跟踪当前状态和连续计数
• 状态切换时，将当前计数与该状态已记录的最大值比较并更新
• 循环结束后处理最后一段未统计的连续序列

方案二：MySQL直接统计

如果希望在数据库层面完成统计，可使用窗口函数实现：

WITH consecutive_groups AS (
    SELECT 
        Status,
        ID,
        -- 生成连续状态组的唯一标识
        ROW_NUMBER() OVER (ORDER BY ID) - 
        ROW_NUMBER() OVER (PARTITION BY Status ORDER BY ID) AS group_id
    FROM your_table_name -- 替换为你的表名
),
group_counts AS (
    SELECT 
        Status,
        COUNT(*) AS consecutive_count
    FROM consecutive_groups
    GROUP BY Status, group_id
)
SELECT 
    Status,
    MAX(consecutive_count) AS max_consecutive
FROM group_counts
GROUP BY Status;

逻辑说明：

1. 使用窗口函数生成group_id，相同连续状态的行拥有同一个group_id
2. 统计每个group_id对应的连续次数
3. 对每个状态取最大的连续次数

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