MySQL：统计订阅周期内计费日期的总出现次数

哈哈，这个计费周期统计的需求太常见了！我之前帮公司做订阅系统的时候就踩过坑——得精准筛选出订阅期内已经完成的计费日，绝对不能把订阅结束后还没到的计费日算进去，也不能漏了订阅开始后第一个有效的计费日。

先拿你给的例子拆解明白：订阅周期是2015-01-26到2016-09-24，计费日固定每月27号。第一个有效计费日是2015-01-27（因为开始日26号早于27号，当月的计费日已经在订阅期内了），最后一个有效计费日是2016-08-27（结束日24号早于27号，当月的27号还没到，所以只能算到上个月的）。从2015年1月到2016年8月，一共是20个完整的计费周期，结果正好是20，完全符合你的要求。

下面直接上可复用的MySQL查询代码，你可以把变量换成自己的实际业务字段：

SET @start_date = '2015-01-26';
SET @end_date = '2016-09-24';
SET @billing_day = 27;

-- 计算第一个有效的计费日
SET @first_billing_date = CASE
    WHEN DAY(@start_date) <= @billing_day THEN
        DATE_FORMAT(@start_date, '%Y-%m-') + INTERVAL @billing_day DAY
    ELSE
        DATE_FORMAT(DATE_ADD(@start_date, INTERVAL 1 MONTH), '%Y-%m-') + INTERVAL @billing_day DAY
END;

-- 计算最后一个有效的计费日
SET @last_billing_date = CASE
    WHEN DAY(@end_date) >= @billing_day THEN
        DATE_FORMAT(@end_date, '%Y-%m-') + INTERVAL @billing_day DAY
    ELSE
        DATE_FORMAT(DATE_SUB(@end_date, INTERVAL 1 MONTH), '%Y-%m-') + INTERVAL @billing_day DAY
END;

-- 计算最终的计费次数
SELECT 
    CASE
        WHEN @first_billing_date > @last_billing_date THEN 0
        ELSE PERIOD_DIFF(DATE_FORMAT(@last_billing_date, '%Y%m'), DATE_FORMAT(@first_billing_date, '%Y%m')) + 1
    END AS completed_billing_cycles;

关键逻辑解释： #

• 第一个计费日判断：如果订阅开始日的天数小于等于计费日，当月的计费日就生效；否则要等到下个月的计费日（比如开始日是2015-01-28，计费日27号，第一个有效日就是2015-02-27）。
• 最后一个计费日判断：如果订阅结束日的天数大于等于计费日，当月的计费日已经完成；否则只能算到上个月的计费日（就是你例子里的情况）。
• 次数计算：用PERIOD_DIFF计算两个日期的月份差，再加1（因为要包含首尾两个计费日）；如果第一个计费日晚于最后一个，说明没有有效周期，直接返回0。

把你的例子参数代入后，运行结果正好是20，完美匹配需求。其他场景也可以直接复用，只需要替换变量值就行。

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