对重复列的连续分组执行Group by的技术需求咨询

这是个很常见的连续分组需求——你要的不是全局按counter值分组，而是把连续重复的counter当成独立的组来处理。核心思路是先给每一段连续的相同counter分配一个唯一的group_id，再基于这个ID做GROUP BY操作。

方法一：用窗口函数（适用于PostgreSQL、MySQL 8.0+、SQL Server等支持窗口函数的数据库） #

窗口函数是最直观的实现方式，步骤如下：

1. 用LAG()获取上一行的counter值，判断当前行和上一行是否相同
2. 生成一个"分组切换标志"：当counter变化时标记为1，否则为0
3. 累加这个标志，得到每个连续组的唯一ID
4. 基于group_id和counter做聚合

具体代码示例：

WITH grouped_data AS (
    SELECT
        counter,
        timestamp,
        -- 生成分组切换标志，累加后得到group_id
        SUM(CASE 
            WHEN counter != LAG(counter) OVER (ORDER BY timestamp) THEN 1 
            ELSE 0 
        END) OVER (
            ORDER BY timestamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM your_table_name -- 替换成你的表名
)
SELECT
    counter,
    group_id,
    MIN(timestamp) AS start_time, -- 组内最早时间
    MAX(timestamp) AS end_time,   -- 组内最晚时间
    COUNT(*) AS row_count         -- 组内行数
FROM grouped_data
GROUP BY group_id, counter
ORDER BY start_time;

方法二：用变量（适用于MySQL 5.x等不支持窗口函数的版本） #

如果你的数据库不支持窗口函数，可以用用户变量来实现分组ID的生成：

SELECT
    counter,
    MIN(timestamp) AS start_time,
    MAX(timestamp) AS end_time,
    COUNT(*) AS row_count
FROM (
    SELECT
        counter,
        timestamp,
        -- 当counter和上一行相同时保持group_id，否则自增
        @group_id := IF(counter = @prev_counter, @group_id, @group_id + 1) AS group_id,
        @prev_counter := counter -- 更新上一行的counter值
    FROM your_table_name,
         -- 初始化变量：group_id从0开始，prev_counter初始为NULL
         (SELECT @group_id := 0, @prev_counter := NULL) AS vars
    ORDER BY timestamp -- 必须按时间排序，保证顺序正确
) AS grouped_data
GROUP BY group_id, counter
ORDER BY start_time;

输出结果示例 #

针对你提供的测试数据，两种方法都会得到如下结果：

这样就完美实现了对每一段连续重复counter的分组聚合。

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