计算用户自上次Eligibility标识以来的累计金额（SQL实现）

现有一张包含用户、月份序列、Eligibility（资格）标识、RunningAmt（累计金额）、new_amt（新增金额）字段的表，需要计算每个用户自上次Eligibility标识为1以来的累计金额，新增一列满足：当Eligibility为1时，若为连续的1则结果为0，否则显示自上次Eligibility为1以来的累计金额；当Eligibility为0时结果为0。

示例数据表 #

SQL建表与插入语句 #

create table example (
    user int,
    month_series int,
    eligibility int,
    running_amt int,
    new_amt int
);
insert into example
    (user, month_series, eligibility, running_amt, new_amt)
values
    (1,1,1,0,0),
    (1,2,0,150,150),
    (1,3,0,150,0),
    (1,4,1,1000,850),
    (1,5,0,1200,200),
    (1,6,0,1200,0),
    (1,7,1,1200,0),
    (1,8,0,1500,300),
    (1,9,0,1550,50),
    (1,10,0,1600,50),
    (1,11,1,1600,0),
    (1,12,1,1600,0);

期望结果表 #

要实现这个需求，核心是分组标记每个Eligibility=1的区间，然后在每个区间内计算累计金额，最后仅在Eligibility=1且不是连续的1时显示累计值，其他情况显示0。

以下是兼容MySQL 8.0+、PostgreSQL、SQL Server等主流数据库的解决方案：

WITH cte AS (
    SELECT 
        *,
        -- 生成分组ID：每次遇到Eligibility=1时，分组ID递增，划分区间
        SUM(CASE WHEN eligibility = 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY user 
            ORDER BY month_series 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id,
        -- 获取前一行的Eligibility值，判断是否为连续的1
        LAG(eligibility) OVER (PARTITION BY user ORDER BY month_series) AS prev_eligibility
    FROM example
),
cte_cumulative AS (
    SELECT 
        *,
        -- 计算每个分组内从区间起始到当前行的累计new_amt
        SUM(new_amt) OVER (
            PARTITION BY user, group_id 
            ORDER BY month_series 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_amt
    FROM cte
)
SELECT 
    user,
    -- 将month_series转换为对应月份名称，匹配示例格式
    CASE month_series
        WHEN 1 THEN 'Jan 20'
        WHEN 2 THEN 'Feb 20'
        WHEN 3 THEN 'Mar 20'
        WHEN 4 THEN 'Apr 20'
        WHEN 5 THEN 'May 20'
        WHEN 6 THEN 'Jun 20'
        WHEN 7 THEN 'Jul 20'
        WHEN 8 THEN 'Aug 20'
        WHEN 9 THEN 'Sep 20'
        WHEN 10 THEN 'Oct 20'
        WHEN 11 THEN 'Nov 20'
        WHEN 12 THEN 'Dec 20'
    END AS Month,
    eligibility,
    running_amt,
    new_amt AS `new Amt`,
    -- 按期望规则生成结果列
    CASE 
        WHEN eligibility = 1 THEN 
            CASE WHEN prev_eligibility = 1 THEN 0 ELSE cumulative_amt END
        ELSE 0 
    END AS `desired result`
FROM cte_cumulative
ORDER BY user, month_series;

逻辑说明 #

1. 分组标记区间：通过窗口函数SUM(CASE...)生成group_id，每次遇到eligibility=1时分组ID加1，将每个eligibility=1到下一个eligibility=1的行划分为同一组。
2. 判断连续1：用LAG()函数获取前一行的eligibility值，用于区分当前的1是否为连续出现。
3. 计算区间累计：在每个分组内，通过窗口函数SUM(new_amt)计算从分组起始到当前行的累计新增金额。
4. 生成结果列：根据规则筛选显示逻辑，仅在非连续的eligibility=1行显示累计金额，其余行显示0。

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