月度计费SQL查询优化：跨周期使用时长与费用计算需求

针对你的月中创建服务需纳入下月发票、包含上月剩余时长+当月整月预付费的需求，以下是优化后的MySQL查询方案：

核心逻辑说明 #

每月1日生成发票时，需分两部分计算时长：

• 上月剩余时长：仅针对上月中创建且未在上月删除的服务，计算从创建日到上月末的时长
• 当月计费时长：针对所有当月有效的服务（未删除或删除时间在当月及之后），计算当月整月时长（或到删除日为止）

优化后的SQL代码 #

SELECT
  `total_price`,
  -- 总费用 = 总时长 × 单价
  (prev_month_usage_hour + current_month_usage_hour) * total_price AS usage_price,
  -- 总使用时长
  prev_month_usage_hour + current_month_usage_hour AS usage_hour
FROM (
  SELECT
    `total_price`,
    `created_at`,
    `deleted_at`,
    -- 计算上月使用时长（仅上月中创建的服务）
    GREATEST(
      TIMESTAMPDIFF(
        HOUR,
        created_at,
        LEAST(IFNULL(deleted_at, '9999-12-31 23:59:59'), prev_month_end)
      ) + 1,
      0
    ) AS prev_month_usage_hour,
    -- 计算当月使用时长（当月有效的服务）
    GREATEST(
      TIMESTAMPDIFF(
        HOUR,
        current_month_start,
        LEAST(IFNULL(deleted_at, '9999-12-31 23:59:59'), current_month_end)
      ) + 1,
      0
    ) AS current_month_usage_hour
  FROM
    `offerings`,
    -- 动态计算发票相关日期参数
    (
      SELECT
        DATE_SUB($invoiceDate, INTERVAL 1 MONTH) AS prev_month_start,
        LAST_DAY(DATE_SUB($invoiceDate, INTERVAL 1 MONTH)) AS prev_month_end,
        $invoiceDate AS current_month_start,
        LAST_DAY($invoiceDate) AS current_month_end
    ) AS date_params
  -- 过滤掉上月前已删除的无效服务
  WHERE IFNULL(deleted_at, '9999-12-31 23:59:59') >= prev_month_start
) AS usage_calculations
-- 排除总时长为0的记录
WHERE (prev_month_usage_hour + current_month_usage_hour) > 0

参数传递说明 #

使用Carbon传入$invoiceDate参数，对应每月1日的发票生成日期：

// 生成当月1日的发票
$invoiceDate = now()->startOfMonth()->toDateString();
// 或指定历史发票日期
$invoiceDate = Carbon::parse('2022-09-01')->toDateString();

示例验证 #

针对你提供的服务记录（ID=1，created_at=2022-08-15 12:00:00，deleted_at=NULL）：

• 生成2022-09-01的发票时：
  • 上月（8月）时长：从8月15日12:00到8月31日23:59，共397小时
  • 当月（9月）时长：9月整月共721小时
  • 总费用：(397+721)*100=111800
• 生成2022-10-01的发票时：
  • 上月（9月）时长：0（服务非9月创建）
  • 当月（10月）时长：10月整月共745小时
  • 总费用：745*100=74500

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