基于账户和交易日期实现累计求和的SQL技术问询

核心思路 #

使用窗口函数计算每个账户的累计交易金额，再针对指定截止日期，筛选出该日期前的最后一笔交易记录，同时带出对应累计金额。

单个截止日期查询示例 #

以查询截止到2023-09-06（即TRANSACTION_DATE < TO_DATE('06-09-2023', 'DD-MM-YYYY')）的结果为例：

WITH transaction_tbl AS (
    SELECT 581 ACCOUNT_ID, TO_DATE('05-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 309.32 AMOUNT FROM dual
    UNION ALL
    SELECT 581 ACCOUNT_ID, TO_DATE('08-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 1863.76 AMOUNT FROM dual
    UNION ALL
    SELECT 581 ACCOUNT_ID, TO_DATE('15-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 0.26 AMOUNT FROM dual
    UNION ALL
    SELECT 581 ACCOUNT_ID, TO_DATE('21-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 23.17 AMOUNT FROM dual
),
cumulative_data AS (
    SELECT
        ACCOUNT_ID,
        TRANSACTION_DATE,
        SUM(AMOUNT) OVER(PARTITION BY ACCOUNT_ID ORDER BY TRANSACTION_DATE) AS CUMULATIVE_AMOUNT,
        ROW_NUMBER() OVER(PARTITION BY ACCOUNT_ID ORDER BY TRANSACTION_DATE DESC) AS RN
    FROM transaction_tbl
    WHERE TRANSACTION_DATE < TO_DATE('06-09-2023', 'DD-MM-YYYY')
)
SELECT
    ACCOUNT_ID,
    TO_CHAR(TRANSACTION_DATE, 'DD-MON-RR') AS TRANSACTION_DATE,
    CUMULATIVE_AMOUNT
FROM cumulative_data
WHERE RN = 1;

执行结果：

581 05-SEP-23   309.32

批量处理多个截止日期 #

如果需要一次性查询多个截止日期的结果，可预先定义所有目标截止日期，再关联计算：

WITH transaction_tbl AS (
    SELECT 581 ACCOUNT_ID, TO_DATE('05-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 309.32 AMOUNT FROM dual
    UNION ALL
    SELECT 581 ACCOUNT_ID, TO_DATE('08-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 1863.76 AMOUNT FROM dual
    UNION ALL
    SELECT 581 ACCOUNT_ID, TO_DATE('15-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 0.26 AMOUNT FROM dual
    UNION ALL
    SELECT 581 ACCOUNT_ID, TO_DATE('21-09-2023', 'DD-MM-YYYY') TRANSACTION_DATE, 23.17 AMOUNT FROM dual
),
asof_dates AS (
    SELECT TO_DATE('06-09-2023', 'DD-MM-YYYY') AS ASOF_DATE FROM dual
    UNION ALL
    SELECT TO_DATE('09-09-2023', 'DD-MM-YYYY') AS ASOF_DATE FROM dual
    UNION ALL
    SELECT TO_DATE('18-09-2023', 'DD-MM-YYYY') AS ASOF_DATE FROM dual
    UNION ALL
    SELECT TO_DATE('22-09-2023', 'DD-MM-YYYY') AS ASOF_DATE FROM dual
),
cumulative_with_rn AS (
    SELECT
        t.ACCOUNT_ID,
        t.TRANSACTION_DATE,
        SUM(t.AMOUNT) OVER(PARTITION BY t.ACCOUNT_ID ORDER BY t.TRANSACTION_DATE) AS CUMULATIVE_AMOUNT,
        a.ASOF_DATE,
        ROW_NUMBER() OVER(PARTITION BY t.ACCOUNT_ID, a.ASOF_DATE ORDER BY t.TRANSACTION_DATE DESC) AS RN
    FROM transaction_tbl t
    CROSS JOIN asof_dates a
    WHERE t.TRANSACTION_DATE < a.ASOF_DATE
)
SELECT
    ACCOUNT_ID,
    TO_CHAR(TRANSACTION_DATE, 'DD-MON-RR') AS TRANSACTION_DATE,
    CUMULATIVE_AMOUNT,
    TO_CHAR(ASOF_DATE, 'DD-MM-YYYY') AS ASOF_DATE
FROM cumulative_with_rn
WHERE RN = 1
ORDER BY ASOF_DATE;

执行结果：

581 05-SEP-23   309.32    06-09-2023
581 08-SEP-23   2173.08   09-09-2023
581 15-SEP-23   2173.34   18-09-2023
581 21-SEP-23   2196.51   22-09-2023

关键逻辑说明 #

• SUM(AMOUNT) OVER(PARTITION BY ACCOUNT_ID ORDER BY TRANSACTION_DATE)：按账户分组、交易日期排序，计算每笔记录对应的累计金额，确保每一行都包含到当前日期为止的交易总和。
• ROW_NUMBER() OVER(...)：在每个账户（或账户+截止日期）分组内，按交易日期倒序排序，取第一行即为截止日期前的最后一笔交易，对应的累计金额就是该截止日期前的总金额。

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