SQL query 计算当前月与上月 billing usage

2026-10-24数据小许

结论

仅凭现有的 created_at 和 deleted_at，无法可靠判断某段 usage 是否已经开过 invoice。建议增加 billed_through_at，记录每条 offering 已计费到的时间点。

计算时采用左闭右开的区间 [usage_start, usage_end)：

首次开票：从 created_at 计算到下个月 1 日。
后续开票：从 billed_through_at 计算到再下个月 1 日。
开票成功后，更新 billed_through_at。
不要使用 endOfMonth() 或 + 1 hour，以免产生月末微秒、重复计费等边界问题。

表结构调整

ALTER TABLE offerings
ADD COLUMN billed_through_at DATETIME NULL;

这个字段表示该 offering 在 invoice 成功生成后已经计费到哪个时间点，但不包含该时间点。

生产环境中，更完整的做法是增加 invoice_items 表，保存每次计费的区间，并为 (offering_id, period_start, period_end) 添加唯一约束，防止任务重试时重复收费。如果只需要较简单的实现，也可以使用 billed_through_at。

SQL 示例

假设 :invoice_start 是 invoice 所属月份的第一天，例如：

2022-08-01 00:00:00

:invoice_end 是下个月第一天：

2022-09-01 00:00:00

SQL 可以写成：

WITH billing_ranges AS (
    SELECT
        o.id,
        o.name,
        o.total_price,

        CASE
            WHEN o.billed_through_at IS NULL
                THEN o.created_at
            ELSE GREATEST(o.billed_through_at, :invoice_start)
        END AS usage_start,

        LEAST(
            COALESCE(o.deleted_at, :invoice_end),
            :invoice_end
        ) AS usage_end
    FROM offerings AS o
    WHERE o.created_at < :invoice_start
      AND (
          o.billed_through_at IS NULL
          OR o.billed_through_at < :invoice_end
      )
),
calculated_usage AS (
    SELECT
        id,
        name,
        total_price,
        usage_start,
        usage_end,
        GREATEST(
            TIMESTAMPDIFF(HOUR, usage_start, usage_end),
            0
        ) AS usage_hour
    FROM billing_ranges
)
SELECT
    id,
    name,
    total_price,
    usage_start,
    usage_end,
    usage_hour,
    usage_hour * total_price AS usage_price
FROM calculated_usage
WHERE usage_end > usage_start;

这段 SQL 基于以下假设：

total_price 是每小时价格。
月中创建的 offering 在下个月 1 日首次开票。
deleted_at 之前的时间可以正常计费。
不足一小时的部分由 TIMESTAMPDIFF(HOUR, ...) 截断。如果需要向上取整，应另外调整计费公式。

计算过程

假设 offering 创建于：

2022-07-15 00:00:00

第一次开票时间是 2022 年 8 月 1 日：

usage_start = 2022-07-15 00:00:00
usage_end   = 2022-09-01 00:00:00

这个区间包括：

7 月 15 日到 8 月 1 日的补计 usage。
8 月 1 日到 9 月 1 日的 advance usage。

invoice 成功后，将该 offering 更新为：

UPDATE offerings
SET billed_through_at = :invoice_end
WHERE id = :offering_id;

此时：

billed_through_at = 2022-09-01 00:00:00

到 2022 年 9 月 1 日再次开票时：

usage_start = 2022-09-01 00:00:00
usage_end   = 2022-10-01 00:00:00

这样就不会重复计算 7 月或 8 月的 usage。

生成 invoice、写入 invoice 明细和更新 billed_through_at 应在同一个数据库事务中完成。只有 invoice 成功写入数据库后，才能推进 billed_through_at。

Carbon 日期参数

结束时间不要使用 endOfMonth()。直接取下个月第一天，更容易保证各个区间连续且互不重叠：

$invoiceStart = now()->startOfMonth();
$invoiceEnd = $invoiceStart->copy()->addMonth();

绑定 SQL 参数时：

[
    'invoice_start' => $invoiceStart->toDateTimeString(),
    'invoice_end'   => $invoiceEnd->toDateTimeString(),
]

这里使用的区间是：

[2022-08-01 00:00:00, 2022-09-01 00:00:00)

而不是：

2022-08-01 00:00:00 到 2022-08-31 23:59:59

因此不需要原查询中的 + 1。加入 + 1 后，首尾相连的两个 invoice 可能会包含同一个小时。

需要修正的日期示例

问题中的日期有几处不一致：

数据记录中写的是 2022-08-15，却又要求在 2022-08-01 为它开票，时间顺序不成立。按照预期描述，创建时间应为 2022-07-15，或者首次 invoice 应在 2022-09-01 生成。
2 月没有 30 日。
9 月没有 31 日。

统一使用“当月第一天到下个月第一天”的半开区间后，就不需要为不同月份的天数或闰年编写特殊逻辑。

另外，从 2022-07-15 00:00:00 到 2022-08-01 00:00:00 实际是 408 小时，不是 360 小时。如果起始时间是 2022-07-15 12:00:00，则为 396 小时。最终金额应以数据库中的准确时间戳和明确的小时取整规则为准。