SQLite中如何按连续行分组？基于ID聚合连续同版本数据

我完全理解你的需求：普通的GROUP BY version会把所有相同version的记录合并到一起，不管它们在id序列里是不是连续的，但你需要的是仅聚合id顺序中连续出现的相同version的数据。

举个更直观的例子，假设你的原始表数据如下：

执行你提到的普通聚合语句：

SELECT MIN(id), version, SUM(count) 
FROM your_table 
GROUP BY version;

会得到这样的结果（所有v1的count被直接加总，忽略了连续性）：

但你期望的是把连续的v1分开聚合，得到这样的结果：

实现方案：用窗口函数标记连续分组 #

要实现这个需求，我们可以借助窗口函数来给连续的相同version生成唯一的分组标识，再基于分组做聚合：

WITH grouped_data AS (
    SELECT 
        id,
        version,
        count,
        -- 当当前行version与上一行不同时，生成分组切换标记，累计后得到分组ID
        SUM(CASE WHEN version = LAG(version) OVER (ORDER BY id) THEN 0 ELSE 1 END) 
            OVER (ORDER BY id) AS group_id
    FROM your_table
)
SELECT 
    MIN(id) AS min_id,
    version,
    SUM(count) AS total_count
FROM grouped_data
GROUP BY group_id, version
ORDER BY min_id;

原理拆解： #

1. LAG(version) OVER (ORDER BY id)：按id排序后，获取当前行的上一行version值。
2. CASE语句：如果当前行和上一行version相同，标记为0；不同则标记为1（表示开启新分组）。
3. 累计标记值：通过SUM(...) OVER (ORDER BY id)将标记值累加，这样连续相同的version会被分配到同一个group_id中。
4. 最终聚合：按group_id和version分组，就能得到连续相同version的聚合结果，同时保留每个连续组的最小id和总count。

内容的提问来源于stack exchange，提问作者Hiten Naresh Vasnani