SQL如何实现不同行跨列求和计算月度确认收入

逻辑梳理 #

先对齐核心计算规则与样例预期：

• 原始表字段：CustomerID 客户ID、Month 统计月份（格式为MM.YY）、Curr_Month_Revenue 当月分摊收入额、Next_Month_Revenue 次月分摊收入额
• 实际确认收入计算公式：当月Recognised_Revenue = 当月行Curr_Month_Revenue + 上一相邻月份行的Next_Month_Revenue，首月无上期结转值时，上期部分计0
• 需补全订阅终止后首个月份的收尾收入行：该月无当期Curr_Month_Revenue，收入额等于最后一个业务月的Next_Month_Revenue
• 样例预期结果：客户A 06.21确认$10、07.21确认$20、08.21确认$20、09.21确认$10

实现代码 #

以下代码兼容MySQL 8.0+、PostgreSQL、Hive、Spark SQL等支持窗口函数的SQL引擎，实现时先将字符串格式月份转为标准日期，避免字符串排序、日期计算错误：

WITH base_data AS (
    -- 预处理：将MM.YY格式的月份转为月初标准日期，适配后续计算
    SELECT
        CustomerID,
        Month,
        -- 日期转换函数可根据自身使用的SQL引擎调整，以下为MySQL通用写法
        STR_TO_DATE(CONCAT('01.', Month), '%d.%m.%y') AS month_dt,
        Curr_Month_Revenue,
        Next_Month_Revenue
    FROM revenue_raw_table
),
calc_exist_month AS (
    -- 计算所有已有业务月份的实际确认收入
    SELECT
        CustomerID,
        Month,
        month_dt,
        Curr_Month_Revenue + COALESCE(
            LAG(Next_Month_Revenue, 1) OVER (PARTITION BY CustomerID ORDER BY month_dt),
            0
        ) AS Recognised_Revenue
    FROM base_data
),
add_tail_month AS (
    -- 补全最后一个业务月的次月收尾收入记录
    SELECT
        CustomerID,
        DATE_FORMAT(DATE_ADD(month_dt, INTERVAL 1 MONTH), '%m.%y') AS Month,
        DATE_ADD(month_dt, INTERVAL 1 MONTH) AS month_dt,
        Next_Month_Revenue AS Recognised_Revenue
    FROM (
        SELECT
            CustomerID,
            month_dt,
            Next_Month_Revenue,
            ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY month_dt DESC) AS rn
        FROM base_data
    ) t
    WHERE rn = 1
)
-- 合并结果按客户、月份排序输出
SELECT CustomerID, Month, Recognised_Revenue
FROM calc_exist_month
UNION ALL
SELECT CustomerID, Month, Recognised_Revenue
FROM add_tail_month
ORDER BY CustomerID, month_dt;

结果验证 #

针对客户A的样例数据，上述代码执行后输出完全匹配预期：

若业务存在客户多次订阅、断订后重新订阅的场景，仅需在窗口函数的PARTITION BY子句中增加订阅订单ID作为分区字段即可，核心计算逻辑无需调整。

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