MySQL 统计订阅期内 billing date 的出现次数

2026-10-24数据阿明

结论

把订阅区间按包含起止日期的闭区间 [start_date, end_date] 处理，然后统计每月 27 日落入该区间的次数。

在 2015-01-26 至 2016-09-24 之间，计费日期从 2015-01-27 到 2016-08-27，共 20 次。

直接计算

计费日是每月 27 日，不会遇到二月日期不存在的问题。先找出订阅开始后的第一个计费日期，再计算该日期与结束日期之间的月份差：

WITH params AS (
    SELECT
        DATE('2015-01-26') AS start_date,
        DATE('2016-09-24') AS end_date,
        27 AS billing_day
),
first_billing AS (
    SELECT
        end_date,
        CASE
            WHEN DATE_ADD(
                     start_date,
                     INTERVAL (billing_day - DAYOFMONTH(start_date)) DAY
                 ) >= start_date
            THEN DATE_ADD(
                     start_date,
                     INTERVAL (billing_day - DAYOFMONTH(start_date)) DAY
                 )
            ELSE DATE_ADD(
                     DATE_ADD(
                         start_date,
                         INTERVAL (billing_day - DAYOFMONTH(start_date)) DAY
                     ),
                     INTERVAL 1 MONTH
                 )
        END AS first_billing_date
    FROM params
)
SELECT
    CASE
        WHEN first_billing_date > end_date THEN 0
        ELSE TIMESTAMPDIFF(
                 MONTH,
                 first_billing_date,
                 end_date
             ) + 1
    END AS completed_bill_cycles
FROM first_billing;

返回结果：

completed_bill_cycles
---------------------
20

计算逻辑

订阅从 2015-01-26 开始，当月的计费日期 2015-01-27 仍在订阅期内，因此算作第一次计费。

订阅在 2016-09-24 结束，此时还没到当月 27 日，所以最后一次计费发生在 2016-08-27。

TIMESTAMPDIFF(MONTH, '2015-01-27', '2016-09-24') 返回 19。加上首个计费日期本身，结果就是 20。

用于表字段的查询

假设表名是 subscriptions，包含以下字段：

start_date：订阅开始日期
end_date：订阅结束日期
billing_day：每月计费日

查询可以写成：

WITH first_billing AS (
    SELECT
        id,
        end_date,
        CASE
            WHEN DATE_ADD(
                     start_date,
                     INTERVAL (billing_day - DAYOFMONTH(start_date)) DAY
                 ) >= start_date
            THEN DATE_ADD(
                     start_date,
                     INTERVAL (billing_day - DAYOFMONTH(start_date)) DAY
                 )
            ELSE DATE_ADD(
                     DATE_ADD(
                         start_date,
                         INTERVAL (billing_day - DAYOFMONTH(start_date)) DAY
                     ),
                     INTERVAL 1 MONTH
                 )
        END AS first_billing_date
    FROM subscriptions
)
SELECT
    id,
    CASE
        WHEN end_date IS NULL THEN NULL
        WHEN first_billing_date > end_date THEN 0
        ELSE TIMESTAMPDIFF(MONTH, first_billing_date, end_date) + 1
    END AS completed_bill_cycles
FROM first_billing;

注意事项

这个算法适用于每个月都存在的日期，即 billing_day 为 1 至 28。如果计费日可能是 29、30 或 31，需要先确定当月没有该日期时如何处理，例如跳过该月、按当月最后一天计费，或者顺延到下个月。

这些规则会得到不同的结果，不能直接依赖 DATE_ADD(..., INTERVAL 1 MONTH)，因为月底日期可能发生偏移。

示例按闭区间计算，billing date 等于 start_date 或 end_date 时也会计数。如果结束日期当天不应计费，需要把判断条件改为严格小于 end_date。