MySQL 按客户 billing_cycle_day 查询 30 天用量

2026-10-24数据小许

结论

不需要 stored procedure，使用普通 SQL 就能完成。

问题在于，DAY(m.report_date)只会返回日期中的“几号”，年月信息会被丢弃。billing_cycle_day + INTERVAL 30 DAY又混用了整数和日期区间，无法得到有效的日期范围。

正确的做法是先算出完整的周期开始日期和结束日期，再使用左闭右开的区间筛选：

m.report_date >= cycle_start
AND m.report_date < cycle_end

按自然月计算账期

“10 月 7 日至 11 月 6 日”属于按自然月滚动的账期，并不一定正好是 30 天。可以把结束日期设为下个月的账期起始日，并采用左闭右开区间。

下面的示例适用于 MySQL 8.0。这里假设 billing_cycle_day的取值范围是 1到 28，以免遇到二月份或大小月不存在对应日期的问题：

WITH customer AS (
    SELECT mobile_number, billing_cycle_day
    FROM device
    WHERE mobile_number = '123'
),
cycle_bounds AS (
    SELECT
        mobile_number,
        billing_cycle_day,
        CASE
            WHEN DAY(CURDATE()) >= billing_cycle_day THEN
                DATE_ADD(
                    DATE_SUB(
                        CURDATE(),
                        INTERVAL (DAY(CURDATE()) - 1) DAY
                    ),
                    INTERVAL (billing_cycle_day - 1) DAY
                )
            ELSE
                DATE_ADD(
                    DATE_SUB(
                        DATE_SUB(
                            CURDATE(),
                            INTERVAL (DAY(CURDATE()) - 1) DAY
                        ),
                        INTERVAL 1 MONTH
                    ),
                    INTERVAL (billing_cycle_day - 1) DAY
                )
        END AS cycle_start
    FROM customer
)
SELECT
    DATE(m.report_date) AS usage_date,
    SUM(m.kilowatts) AS total_kilowatts,
    b.billing_cycle_day
FROM cycle_bounds b
JOIN reports_month m
    ON m.mobile_number = b.mobile_number
   AND m.report_date >= b.cycle_start
   AND m.report_date < DATE_ADD(b.cycle_start, INTERVAL 1 MONTH)
GROUP BY
    DATE(m.report_date),
    b.billing_cycle_day
ORDER BY usage_date;

假设查询日期是 10 月 20 日，billing_cycle_day = 7，计算结果为：

cycle_start = 10 月 7 日
cycle_end   = 11 月 7 日

查询范围包括 10 月 7 日零点，以及此后至 11 月 7 日零点之前的记录，也就是通常所说的 10 月 7 日至 11 月 6 日。

如果查询日期是 10 月 3 日，则查询上一个账期：

cycle_start = 9 月 7 日
cycle_end   = 10 月 7 日

查询指定日期所在的账期

业务报表通常需要支持重复查询，因此不宜始终依赖 CURDATE()。可以传入一个基准日期：

SET @as_of_date = DATE('2024-10-20');

再将前面 SQL 中的 CURDATE()替换为 @as_of_date，就可以查询任意历史日期所属的账期。

如果确实要求固定 30 天

如果业务规则确实是固定 30 天，而不是“每月同一天开始”，只需修改结束边界：

AND m.report_date >= b.cycle_start
AND m.report_date < DATE_ADD(b.cycle_start, INTERVAL 30 DAY)

不过，这种算法会逐渐偏离“每个月 7 日开始”的规则。二月通常不足 30 天，部分月份则有 31 天。因此，需要先确认实际采用哪一种规则：

每个自然月的指定日期开始；
从某个锚点开始，每隔固定 30 天生成一个周期。

两者的计算方式不同。

为什么原查询得不到正确结果

原来的条件是：

DAY(m.report_date) BETWEEN d.billing_cycle_day
                       AND d.billing_cycle_day + INTERVAL 30 DAY

这个条件有两个问题。

DAY(m.report_date)返回的是整数：

2024-10-07 -> 7
2024-11-07 -> 7

不同月份的同一天都会变成相同的值，所以无法判断记录属于哪个账期。

另一个问题是：

d.billing_cycle_day + INTERVAL 30 DAY

这段表达式试图给“日号整数”增加一个日期区间，但 billing_cycle_day并不是完整日期。MySQL 无法判断这个“7”指的是哪一年、哪一个月的 7 日。

日期范围必须使用完整的 DATE或DATETIME值判断，不能只比较 DAY()的结果。

分组时也要保留完整日期

不要按下面的方式分组：

GROUP BY DAY(m.report_date)

否则，不同年月的同一天可能会被合并。例如，10 月 7 日和 11 月 7 日会进入同一个分组。

应该使用：

GROUP BY DATE(m.report_date)

这样才能分别统计每天的用量。

注意事项

如果允许 billing_cycle_day取 29、30或31，需要先规定缺少对应日期的月份该如何处理。例如，账期日是 31 日，但二月没有 31 日。常见规则包括：

使用当月最后一天；
禁止配置大于 28 的账期日；
跳过没有该日期的月份。

SQL 应按实际业务规则实现，不能只依赖 DATE_ADD(..., INTERVAL 1 MONTH)的自动日期调整。

范围条件中也不要对 m.report_date使用 DATE()：

-- 不建议作为范围条件
DATE(m.report_date) >= cycle_start

直接比较原字段更有利于使用索引：

m.report_date >= cycle_start
AND m.report_date < cycle_end

还可以为明细表建立联合索引：

CREATE INDEX idx_reports_mobile_date
ON reports_month (mobile_number, report_date);

这样 MySQL 可以先定位客户，再扫描该客户账期内的记录。