生成日期范围内每月1日交易记录：TEST_TAB表SQL需求

我来帮你搞定这个每月余额快照的问题！先理清楚你的需求和现有代码的问题，再给你一个能完美输出预期结果的解决方案。

源表结构与数据 #

你的源表TEST_TAB结构和数据如下：

业务需求 #

你需要为每条交易记录生成该记录发生后到下一条交易发生前的所有每月1日快照记录，余额直接沿用当前交易的DP_AD_CURR_BAL，同时保留原始的交易记录本身。预期输出会包含原始交易和中间每个月1日的快照，比如2014-11-18之后的2014-12-01、2015-01-01直到2018-03-01，然后才是下一条交易2018-03-04。

你尝试的SQL问题分析 #

你写的SQL有几个明显的问题，导致没得到预期结果：

1. 日期格式转换错误：源表的日期是YYYY-MM-DD格式，但你用了TO_DATE(DP_AD_LST_MDFSN_DATE, 'MM/DD/YY')，这会导致日期解析错误，尤其是年份部分。如果DP_AD_LST_MDFSN_DATE本身是DATE类型，甚至不需要转换。
2. 每月1日的生成逻辑不对：ADD_MONTHS(start_date, level-1)只是简单加月份，没有确保日期是当月第一天，而且计算level的方式没有考虑起始日期和结束日期的实际范围，容易多生成或少生成快照。
3. 没保留原始交易记录：你的查询只生成了月份快照，但需求要求保留原始的交易记录。
4. 分层查询的重复问题：CONNECT BY缺少必要的分层条件，会导致生成大量重复数据，因为Oracle会默认关联所有行。

正确的SQL解决方案 #

这里以Oracle SQL为例，我写了一个能完美实现需求的查询，逻辑清晰且能避免上述问题：

WITH test_tab_with_next_date AS (
    SELECT 
        DP_AD_ACCT_NBR,
        DP_AD_CCY_CDE,
        DP_AD_CURR_BAL,
        TO_DATE(DP_AD_LST_MDFSN_DATE, 'YYYY-MM-DD') AS trans_date,
        -- 获取下一条交易的日期，最后一条记录用当前日期作为结束点
        LEAD(TO_DATE(DP_AD_LST_MDFSN_DATE, 'YYYY-MM-DD'), 1, SYSDATE) OVER (
            PARTITION BY DP_AD_ACCT_NBR, DP_AD_CCY_CDE 
            ORDER BY TO_DATE(DP_AD_LST_MDFSN_DATE, 'YYYY-MM-DD')
        ) AS next_trans_date
    FROM TEST_TAB
),
monthly_snapshots AS (
    SELECT 
        DP_AD_ACCT_NBR,
        DP_AD_CCY_CDE,
        DP_AD_CURR_BAL,
        -- 生成每月1日的快照日期：从当前交易日期的下月1日开始，到下一条交易日期的上月1日
        TRUNC(ADD_MONTHS(trans_date, level), 'MM') AS snapshot_date
    FROM test_tab_with_next_date
    CONNECT BY 
        -- 控制生成的月份数量，不超过两条交易之间的月份差
        LEVEL <= MONTHS_BETWEEN(next_trans_date, trans_date)
        -- 确保分层只在同一个账户和币种内进行
        AND PRIOR DP_AD_ACCT_NBR = DP_AD_ACCT_NBR
        AND PRIOR DP_AD_CCY_CDE = DP_AD_CCY_CDE
        -- 避免Oracle分层查询的循环问题
        AND PRIOR SYS_GUID() IS NOT NULL
    -- 过滤掉超过下一条交易日期的快照，比如如果下一条交易在2018-03-04，就不要生成2018-03-01之后的快照
    WHERE TRUNC(ADD_MONTHS(trans_date, level), 'MM') < next_trans_date
)
-- 合并原始交易记录和生成的快照记录，最后按日期排序
SELECT 
    DP_AD_ACCT_NBR,
    DP_AD_CCY_CDE,
    DP_AD_CURR_BAL,
    TO_CHAR(trans_date, 'YYYY-MM-DD') AS DP_AD_LST_MDFSN_DATE
FROM test_tab_with_next_date
UNION ALL
SELECT 
    DP_AD_ACCT_NBR,
    DP_AD_CCY_CDE,
    DP_AD_CURR_BAL,
    TO_CHAR(snapshot_date, 'YYYY-MM-DD') AS DP_AD_LST_MDFSN_DATE
FROM monthly_snapshots
ORDER BY DP_AD_ACCT_NBR, DP_AD_CCY_CDE, TO_DATE(DP_AD_LST_MDFSN_DATE, 'YYYY-MM-DD');

代码逻辑解释 #

1. test_tab_with_next_date：这个CTE的作用是给每条交易记录绑定下一条交易的日期，最后一条记录的结束日期用当前日期SYSDATE，这样能确保生成到最近的月份快照。
2. monthly_snapshots：用CONNECT BY分层查询生成每月1日的快照，TRUNC(..., 'MM')强制日期为当月第一天，同时用WHERE条件过滤掉超过下一条交易日期的快照，避免生成多余数据。PRIOR SYS_GUID() IS NOT NULL是Oracle中防止分层查询产生重复数据的小技巧。
3. 最终合并：把原始交易记录和生成的快照记录用UNION ALL合并，然后按账户、币种和日期排序，就能得到你想要的预期结果了。

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