Oracle查询中如何计算两个累积聚合列的差值？

很简单就能实现你的需求，直接在查询中新增一列，用已有的CUMULATIVE_INCOME减去CUMULATIVE_EXPENSE即可。另外我还帮你优化了日期处理的部分，避免重复的字符串-日期转换操作，让查询更高效整洁。

修改后的查询语句（保留原结构） #

SELECT 
    SUM(SUM(INCOME)) OVER(ORDER BY TO_DATE(TO_CHAR(PAYMENT_DATE, 'MM/YYYY'), 'MM/YYYY')) AS CUMULATIVE_INCOME,
    SUM(SUM(EXPENSE)) OVER(ORDER BY TO_DATE(TO_CHAR(PAYMENT_DATE, 'MM/YYYY'), 'MM/YYYY')) AS CUMULATIVE_EXPENSE,
    -- 新增：累计收入与累计支出的差值
    SUM(SUM(INCOME)) OVER(ORDER BY TO_DATE(TO_CHAR(PAYMENT_DATE, 'MM/YYYY'), 'MM/YYYY')) 
    - SUM(SUM(EXPENSE)) OVER(ORDER BY TO_DATE(TO_CHAR(PAYMENT_DATE, 'MM/YYYY'), 'MM/YYYY')) AS CUMULATIVE_BALANCE,
    SUM(INCOME) AS TOTAL_MONTH_INCOME,
    SUM(EXPENSE) AS TOTAL_MONTH_EXPENSE,
    TO_CHAR(PAYMENT_DATE, 'MM/YYYY') AS MONTH
FROM V_CFW_CASH_FLOW
GROUP BY TO_CHAR(PAYMENT_DATE, 'MM/YYYY')
ORDER BY TO_DATE(MONTH, 'MM/YYYY') ASC;

优化版查询（更高效的日期处理） #

原查询中重复使用TO_DATE(TO_CHAR(PAYMENT_DATE, 'MM/YYYY'), 'MM/YYYY')做排序，其实可以用Oracle的TRUNC函数直接截断日期到月份，这样既减少了转换开销，代码也更简洁：

SELECT 
    SUM(SUM(INCOME)) OVER(ORDER BY TRUNC(PAYMENT_DATE, 'MM')) AS CUMULATIVE_INCOME,
    SUM(SUM(EXPENSE)) OVER(ORDER BY TRUNC(PAYMENT_DATE, 'MM')) AS CUMULATIVE_EXPENSE,
    -- 新增的累计收支差额列
    SUM(SUM(INCOME)) OVER(ORDER BY TRUNC(PAYMENT_DATE, 'MM')) 
    - SUM(SUM(EXPENSE)) OVER(ORDER BY TRUNC(PAYMENT_DATE, 'MM')) AS CUMULATIVE_BALANCE,
    SUM(INCOME) AS TOTAL_MONTH_INCOME,
    SUM(EXPENSE) AS TOTAL_MONTH_EXPENSE,
    TO_CHAR(TRUNC(PAYMENT_DATE, 'MM'), 'MM/YYYY') AS MONTH
FROM V_CFW_CASH_FLOW
GROUP BY TRUNC(PAYMENT_DATE, 'MM')
ORDER BY TRUNC(PAYMENT_DATE, 'MM') ASC;

说明 #

• 新增的CUMULATIVE_BALANCE列就是你需要的累计收入与累计支出的差值，正数代表累计盈利，负数代表累计亏损。
• 优化版中TRUNC(PAYMENT_DATE, 'MM')会把日期截断到当月的第一天（比如2024-05-15变成2024-05-01），用这个值分组和排序比字符串转换更高效，也避免了潜在的日期格式问题。

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