基于首次记录日期与记录日期计算符合30天间隔要求的前一个账单周期的技术实现问询

我来帮你解决这个账单周期计算的问题——你的现有SQL没法处理闰年（比如2月29日）和目标月份没有对应天数（比如4月没有31日）的场景，我们可以通过分步生成候选日期并做校验的方式来解决。

先明确需求与规则 #

我们需要基于first_record_date（首次记录日期）和date_recorded（记录日期）计算prev_bill_cycle，要求该日期处于首次记录日期的30天间隔范围内，同时遵循：

• 年份通常与记录日期一致，仅记录日期为1月时可能例外
• 日期优先与首次记录日期保持一致，若目标月份没有该日期则取当月最后一天
• 若记录日期的日小于首次记录日期的日，优先取记录日期的前一个月作为账单周期月份

现有代码的问题 #

你的原代码直接拼接年月和首次记录的日，会遇到目标日期不存在的情况（比如非闰年2月拼接29日、4月拼接31日），同时没有正确处理闰年2月29日的特殊场景，导致结果错误。

修正后的SQL实现 #

下面的代码用CTE先生成两个候选日期（同月份和前一个月），自动处理日期不存在的情况，再通过逻辑判断筛选出符合要求的账单周期：

WITH candidate_dates AS (
    SELECT
        first_record_date,
        date_recorded,
        -- 生成记录日期当月的候选日期：优先用首次记录的日，不存在则取当月最后一天
        CASE
            WHEN EXISTS (
                SELECT 1 
                FROM generate_series(
                    TO_DATE(CONCAT(TO_CHAR(date_recorded, 'YYYY-MM-'), TO_CHAR(first_record_date, 'DD')), 'YYYY-MM-DD'),
                    TO_DATE(CONCAT(TO_CHAR(date_recorded, 'YYYY-MM-'), TO_CHAR(first_record_date, 'DD')), 'YYYY-MM-DD'),
                    INTERVAL '1 day'
                )
            ) THEN TO_DATE(CONCAT(TO_CHAR(date_recorded, 'YYYY-MM-'), TO_CHAR(first_record_date, 'DD')), 'YYYY-MM-DD')
            ELSE (DATE_TRUNC('month', date_recorded) + INTERVAL '1 month - 1 day')::DATE
        END AS same_month_candidate,
        -- 生成记录日期前一个月的候选日期：同样处理日期不存在的情况
        CASE
            WHEN EXISTS (
                SELECT 1 
                FROM generate_series(
                    TO_DATE(CONCAT(TO_CHAR(date_recorded - INTERVAL '1 month', 'YYYY-MM-'), TO_CHAR(first_record_date, 'DD')), 'YYYY-MM-DD'),
                    TO_DATE(CONCAT(TO_CHAR(date_recorded - INTERVAL '1 month', 'YYYY-MM-'), TO_CHAR(first_record_date, 'DD')), 'YYYY-MM-DD'),
                    INTERVAL '1 day'
                )
            ) THEN TO_DATE(CONCAT(TO_CHAR(date_recorded - INTERVAL '1 month', 'YYYY-MM-'), TO_CHAR(first_record_date, 'DD')), 'YYYY-MM-DD')
            ELSE (DATE_TRUNC('month', date_recorded - INTERVAL '1 month') + INTERVAL '1 month - 1 day')::DATE
        END AS prev_month_candidate
    FROM records_table
)
SELECT
    first_record_date,
    date_recorded,
    CASE
        -- 记录日期早于等于首次记录日期时，直接返回首次记录日期
        WHEN date_recorded <= first_record_date THEN first_record_date
        -- 满足以下条件时使用前一个月的候选：
        -- 1. 记录日期的日小于首次记录的日；2. 同月份候选日期超过记录日期（比如非闰年2月29日的情况）
        WHEN EXTRACT(DAY FROM date_recorded) < EXTRACT(DAY FROM first_record_date) 
             OR same_month_candidate > date_recorded THEN
            -- 检查前一个月候选是否在30天范围内，否则返回首次记录日期
            CASE WHEN prev_month_candidate >= date_recorded - INTERVAL '30 days' 
                 THEN prev_month_candidate 
                 ELSE first_record_date 
            END
        ELSE
            -- 检查同月份候选是否在30天范围内，否则返回首次记录日期
            CASE WHEN same_month_candidate >= date_recorded - INTERVAL '30 days' 
                 THEN same_month_candidate 
                 ELSE first_record_date 
            END
    END AS prev_bill_cycle
FROM candidate_dates;

逻辑说明 #

1. 候选日期生成：通过EXISTS和generate_series判断拼接的日期是否合法，不合法则取当月最后一天，完美处理闰年和月份天数不足的场景。
2. 规则匹配：
   • 当记录日期早于首次记录日期，直接返回首次记录日期
   • 当记录日小于首次记录日，或同月份候选日期无效（超过记录日期），则使用前一个月的候选
   • 最后校验候选日期是否在记录日期的30天范围内，确保符合要求

示例验证 #

拿你给出的测试数据举例：

• 首次记录日期2020-02-29，记录日期2022-02-28：同月份候选尝试2022-02-29（不存在），自动取2022-02-28，符合预期
• 首次记录日期2021-01-05，记录日期2022-01-03：记录日3小于5，取前一个月候选2021-12-05，且在30天范围内，符合预期
• 首次记录日期2021-06-30，记录日期2021-11-15：记录日15小于30，取前一个月候选2021-10-30，符合预期

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