如何在SQL中按连续交易类型迭代汇总数据？

这是典型的连续分组（岛屿）问题，普通的PARTITION BY type会把所有同类型记录归为一组，忽略是否连续的特性，所以需要先给连续的同类型记录生成唯一分组ID，再进行聚合。

实现步骤 #

• 标记类型变化点：用LAG()窗口函数获取当前行的上一行交易类型，判断是否与当前行类型不同，生成变化标识。
• 生成连续组ID：对变化标识做累加，相同连续类型的记录会得到同一个组ID。
• 按组聚合：基于生成的组ID，分组计算最小日期、最大日期和金额总和。

示例SQL代码（通用SQL，适配多数数据库） #

WITH grouped_transactions AS (
    SELECT
        ID,
        date,
        type,
        amount,
        -- 标记当前行与上一行type是否不同，不同则为1，否则为0
        CASE 
            WHEN LAG(type) OVER (PARTITION BY ID ORDER BY date) != type THEN 1
            ELSE 0
        END AS type_change,
        -- 累加变化标识，得到连续组的ID
        SUM(CASE 
            WHEN LAG(type) OVER (PARTITION BY ID ORDER BY date) != type THEN 1
            ELSE 0
        END) OVER (PARTITION BY ID ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM transactions
)
SELECT
    ID,
    type,
    MIN(date) AS min_date,
    MAX(date) AS max_date,
    SUM(amount) AS amount
FROM grouped_transactions
GROUP BY ID, group_id, type
ORDER BY min_date;

代码说明 #

• LAG(type) OVER (PARTITION BY ID ORDER BY date)：按ID分组、日期排序，获取当前行的上一行type值。
• type_change列：当当前行type和上一行不同时标记为1，否则为0，用来识别连续组的起始点。
• group_id列：对type_change做累加，每遇到一个类型变化，累加值就加1，同一连续组的记录会有相同的group_id。
• 最后按ID、group_id、type分组，聚合得到每个连续组的汇总结果。

数据库适配细节 #

• MySQL 8.0+：支持窗口函数，代码可直接运行。
• Oracle：确保date列为日期类型，或用TO_DATE(date, 'DD/MM/YYYY')转换后排序。
• SQL Server：窗口函数语法一致，无需额外调整。

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