使用当前日期和账单日设置 SQL 滚动月周期边界

结论

先确定当前月份的账单日。如果当前日期已到该账单日，周期从本月账单日开始；如果还没到，则从上月账单日开始。周期在下个月的账单日结束。

当前日期为 2022-06-14 时，结果如下：

题目中的期望结果使用了 2021 年，与给定的当前日期 2022-06-14 不一致。只有当前日期为 2021-06-14 时，才会得到题目列出的 2021 年结果。

Snowflake SQL

下面的写法适用于 9 日和 25 日，也处理了短月份没有 29、30、31 日的情况。这里的规则是将不存在的账单日调整为当月最后一天。

WITH users AS (
    SELECT 1 AS user_id, 9 AS billing_day
    UNION ALL
    SELECT 2 AS user_id, 25 AS billing_day
),
params AS (
    SELECT TO_DATE('2022-06-14') AS current_date_value
),
candidate AS (
    SELECT
        u.user_id,
        u.billing_day,
        p.current_date_value,
        DATEADD(
            day,
            LEAST(
                u.billing_day,
                DAY(LAST_DAY(p.current_date_value))
            ) - 1,
            DATE_TRUNC('month', p.current_date_value)
        ) AS current_month_billing_date
    FROM users u
    CROSS JOIN params p
),
cycle_start AS (
    SELECT
        user_id,
        billing_day,
        current_date_value,
        CASE
            WHEN current_date_value >= current_month_billing_date
                THEN current_month_billing_date
            ELSE
                DATEADD(
                    day,
                    LEAST(
                        billing_day,
                        DAY(LAST_DAY(DATEADD(month, -1, current_date_value)))
                    ) - 1,
                    DATE_TRUNC('month', DATEADD(month, -1, current_date_value))
                )
        END AS billing_cycle_start
    FROM candidate
)
SELECT
    user_id,
    billing_day,
    billing_cycle_start,
    DATEADD(
        day,
        LEAST(
            billing_day,
            DAY(LAST_DAY(DATEADD(month, 1, billing_cycle_start)))
        ) - 1,
        DATE_TRUNC('month', DATEADD(month, 1, billing_cycle_start))
    ) AS billing_cycle_end
FROM cycle_start
ORDER BY user_id;

查询结果：

USER_ID  BILLING_DAY  BILLING_CYCLE_START  BILLING_CYCLE_END
1        9            2022-06-09           2022-07-09
2        25           2022-05-25           2022-06-25

如果要使用系统当前日期，将：

TO_DATE('2022-06-14')

替换为：

CURRENT_DATE()

判断逻辑

账单日为 9 日时，本月的账单边界是 2022-06-09。当前日期 2022-06-14 已经过了这个边界，所以周期从 2022-06-09 开始，到 2022-07-09 结束。

账单日为 25 日时，本月的账单边界是 2022-06-25。当前日期 2022-06-14 还没到这个边界，所以当前周期从上个月的 2022-05-25 开始，到 2022-06-25 结束。

简化写法

如果能确定 billing_day 始终不超过 28，可以省略月底修正逻辑：

WITH users AS (
    SELECT 1 AS user_id, 9 AS billing_day
    UNION ALL
    SELECT 2 AS user_id, 25 AS billing_day
),
data AS (
    SELECT
        user_id,
        billing_day,
        TO_DATE('2022-06-14') AS current_date_value,
        DATEADD(
            day,
            billing_day - 1,
            DATE_TRUNC('month', TO_DATE('2022-06-14'))
        ) AS current_month_billing_date
    FROM users
)
SELECT
    user_id,
    billing_day,
    CASE
        WHEN current_date_value >= current_month_billing_date
            THEN current_month_billing_date
        ELSE DATEADD(month, -1, current_month_billing_date)
    END AS billing_cycle_start,
    CASE
        WHEN current_date_value >= current_month_billing_date
            THEN DATEADD(month, 1, current_month_billing_date)
        ELSE current_month_billing_date
    END AS billing_cycle_end
FROM data
ORDER BY user_id;

注意事项

账单周期通常使用左闭右开区间：

[billing_cycle_start, billing_cycle_end)

也就是包含开始时间，不包含结束时间。查询账单记录时可以这样写：

WHERE transaction_date >= billing_cycle_start
  AND transaction_date <  billing_cycle_end

这样不会重复计算恰好发生在下一个账单日的数据。对于包含时间部分的 TIMESTAMP 字段，这种写法也比 BETWEEN 更合适。

如果账单日允许设置为 29、30 或 31，需要先确定短月份的处理规则：是调整到当月月底，还是顺延到下个月。上面的完整 SQL 采用前一种规则，将账单日调整为当月最后一天。

备注：内容仅供参考。