PostgreSQL中如何对连续同符号行的金额进行求和分组

问题背景 #

给定如下数据表：

date   |  user    |   amount
-----------------------------
01-01  |  John    |       +3
01-02  |  John    |       +2
01-03  |  John    |       +1
01-04  |  John    |       -5
01-05  |  John    |       +1
01-01  |  Jack    |       +2
01-02  |  Jack    |       -1
01-03  |  Jack    |       -6

需要实现：按用户分组，对每个用户的连续同符号金额执行求和，得到如下输出：

date   |  user    |   amount
-----------------------------
01-01  |  John    |       +6  <- 此为所有连续同符号金额的总和
01-04  |  John    |       -5
01-05  |  John    |       +1
01-01  |  Jack    |       +2
01-02  |  Jack    |       -7  <- 此为所有连续同符号金额的总和

你之前写法的问题 #

你尝试的窗口函数：

select
    sum(amount) over (partition by user, sign(amount) order by date)
from my_table

这个写法会把同一个用户所有同符号的金额归为一组求和，忽略了是否连续的条件，因此无法得到连续区间的求和结果。

正确解法 #

要实现连续同符号区间的分组求和，核心是先识别每个用户的连续同符号区间，再对区间内的金额求和。以下是适用于支持窗口函数（如PostgreSQL、MySQL 8+、SQL Server）的解决方案：

WITH grouped_data AS (
    SELECT
        date,
        user,
        amount,
        SIGN(amount) AS amt_sign,
        -- 标记当前记录与上一条记录的符号是否不同
        CASE 
            WHEN LAG(SIGN(amount)) OVER (PARTITION BY user ORDER BY date) != SIGN(amount) 
            THEN 1 
            ELSE 0 
        END AS sign_change
    FROM my_table
),
interval_groups AS (
    SELECT
        date,
        user,
        amount,
        -- 累加符号变化标记，生成连续同符号区间的唯一ID
        SUM(sign_change) OVER (PARTITION BY user ORDER BY date) AS group_id
    FROM grouped_data
)
SELECT
    MIN(date) AS date,  -- 取连续区间的最早日期
    user,
    SUM(amount) AS amount
FROM interval_groups
GROUP BY user, group_id
ORDER BY user, date;

逻辑说明 #

1. grouped_data CTE：

   • 计算每条记录金额的符号amt_sign
   • 使用LAG()函数获取当前用户上一条记录的符号，判断是否发生变化，生成sign_change标记（变化则为1，否则为0）

2. interval_groups CTE：

   • 对每个用户的sign_change进行累加，得到group_id——相同的group_id代表同一个连续同符号区间

3. 最终查询：

   • 按user和group_id分组，对区间内的amount求和
   • 取区间内最早的date作为结果的日期，排序后得到期望输出

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