SQL如何基于单列分组聚合部分行 按交易类型生成起止日期及汇总金额

• 首先全局按日期升序排序，识别出连续相同交易类型的记录块（业内常称数据孤岛）：相邻记录交易类型不同时，就判定为新的孤岛开始
• 每个孤岛内部按日期升序排序生成行号，对行号做除以2向上取整的计算得到分组标识，每2条连续记录会被分到同一组，不足2条的单独成组
• 按孤岛标识+交易类型+分组标识聚合，取组内最小日期为Start_date、最大日期为End_date，对金额求和即可得到结果

以下代码兼容所有支持标准窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）：

WITH add_prev_type AS (
    SELECT
        Date,
        Transaction_type,
        amount,
        -- 取当前记录上一条的交易类型，第一条记录默认返回空
        LAG(Transaction_type) OVER (ORDER BY STR_TO_DATE(Date, '%d/%m/%Y')) AS prev_type
    FROM your_table_name
),
add_island_id AS (
    SELECT
        Date,
        Transaction_type,
        amount,
        -- 计算孤岛编号：和上一条交易类型不同时编号+1，累加得到所有记录的孤岛归属
        SUM(CASE WHEN prev_type = Transaction_type THEN 0 ELSE 1 END) OVER (ORDER BY STR_TO_DATE(Date, '%d/%m/%Y')) AS island_id
    FROM add_prev_type
),
add_rn_in_island AS (
    SELECT
        Date,
        Transaction_type,
        amount,
        island_id,
        -- 每个孤岛内部按日期排序生成行号
        ROW_NUMBER() OVER (PARTITION BY island_id ORDER BY STR_TO_DATE(Date, '%d/%m/%Y')) AS rn
    FROM add_island_id
)
SELECT
    MIN(Date) AS Start_date,
    MAX(Date) AS End_date,
    Transaction_type,
    SUM(amount) AS amount
FROM add_rn_in_island
GROUP BY island_id, Transaction_type, CEIL(rn / 2)
-- 输出结果按开始日期升序排序，和示例输出顺序一致
ORDER BY STR_TO_DATE(Start_date, '%d/%m/%Y');

• 代码中的your_table_name请替换为你实际使用的表名
• 示例中日期为日/月/年格式的字符串，所以用了MySQL的STR_TO_DATE函数做日期转换保证排序正确，其他数据库可替换为对应日期转换函数：
  • SQL Server：替换为CONVERT(DATE, Date, 103)
  • Oracle：替换为TO_DATE(Date, 'dd/mm/yyyy')
• 如果你的表中Date字段本身是日期类型而非字符串，可以直接删除转换函数，仅保留ORDER BY Date即可

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