如何用SQL将最近一次余额下降前月份的营收设置为0

核心思路 #

仅通过2次窗口函数扫描即可完成计算，无需表自关联，时间复杂度为O(n)，是性能最优的实现方式，适配所有支持标准SQL窗口函数的数据库（MySQL 8.0+、PostgreSQL、Hive、Spark SQL、Flink SQL等）。
计算逻辑分3步：

1. 对每个客户的余额数据按月份升序排序，取上月余额用于判断当月是否发生余额下降
2. 标记所有余额下降的月份，提取每个客户最近一次余额下降的月份
3. 月份早于最近一次下降月份的余额置为0，其余保留原始值

完整SQL代码 #

WITH step1 AS (
    -- 第一步：获取每个客户上月余额，判断当月是否为下降月份
    SELECT 
        client_id,
        balance_month,
        balance,
        -- 当月余额小于上月余额则标记为下降月
        CASE WHEN balance < LAG(balance) OVER(PARTITION BY client_id ORDER BY balance_month) THEN 1 ELSE 0 END AS is_drop_month
    FROM client_balance
),
step2 AS (
    -- 第二步：计算每个客户最近一次下降的月份
    SELECT 
        *,
        MAX(CASE WHEN is_drop_month = 1 THEN balance_month ELSE NULL END) OVER(PARTITION BY client_id) AS last_drop_month
    FROM step1
)
-- 第三步：按规则输出最终余额
SELECT 
    client_id,
    balance_month,
    CASE WHEN balance_month >= COALESCE(last_drop_month, balance_month) THEN balance ELSE '$0' END AS balance
FROM step2
ORDER BY client_id DESC, balance_month DESC;

逻辑说明 #

• 若客户没有任何余额下降记录，COALESCE函数会自动保留所有原始余额，兼容边界场景
• 所有计算均基于单次表扫描+窗口函数聚合，无JOIN操作，数据量越大相比自关联写法的性能优势越明显
• 兼容balance_month为日期类型、字符串类型的大部分场景，无需额外格式转换

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