基于 Billing Cycle 汇总用户年度 Spend（SQL）

结论

先将每条月度消费记录归入对应的“账期年度”，再按用户和账期年度汇总。

账期年度的计算规则如下：

cycle_key = Year - CASE WHEN Month < StartMonth THEN 1 ELSE 0 END

以用户 11 为例，账期从 10 月开始：

• 2019-09 属于上一个账期，cycle_key = 2018
• 2019-10 至 2020-09 的 cycle_key = 2019
• 2020-10 至 2021-09 的 cycle_key = 2020

最后按 cycle_key 使用 DENSE_RANK() 排序，即可得到从 1 开始的 Cycle 编号。

SQL 实现

以下示例适用于 MySQL 8.0，以及其他支持 CTE 和窗口函数的数据库。Year 和 Month 可能与数据库关键字或函数名冲突，因此在 MySQL 中需要使用反引号包裹。

WITH cycle_totals AS (
    SELECT
        s.ID,
        s.`Year`
            - CASE
                WHEN s.`Month` < bc.StartMonth THEN 1
                ELSE 0
              END AS cycle_key,
        SUM(s.Spend) AS TotalSpend
    FROM Spend AS s
    INNER JOIN BillingCycle AS bc
        ON bc.ID = s.ID
    GROUP BY
        s.ID,
        s.`Year`
            - CASE
                WHEN s.`Month` < bc.StartMonth THEN 1
                ELSE 0
              END
)
SELECT
    ID,
    DENSE_RANK() OVER (
        PARTITION BY ID
        ORDER BY cycle_key
    ) AS Cycle,
    TotalSpend
FROM cycle_totals
ORDER BY ID, Cycle;

查询结果如下：

+----+-------+------------+
| ID | Cycle | TotalSpend |
+----+-------+------------+
| 11 | 1     | 10         |
| 11 | 2     | 313        |
| 11 | 3     | 51         |
| 50 | 1     | 118        |
| 50 | 2     | 174        |
+----+-------+------------+

计算过程

CTE cycle_totals 先将每条记录归入相应的账期：

s.`Year`
    - CASE
        WHEN s.`Month` < bc.StartMonth THEN 1
        ELSE 0
      END

用户 11 的 StartMonth = 10，具体计算如下：

归入账期后，再按 ID 和 cycle_key 分组并求和：

SUM(s.Spend)

外层查询使用 DENSE_RANK()，把实际的 cycle_key 转换成所需的 Cycle = 1, 2, 3...。

推荐使用真正的日期列

如果能够调整表结构，建议将日期保存为 DATE 类型，例如：

2019-09-01
2020-10-01

不建议长期以字符串形式保存 01-Sep-19。字符串日期容易受语言、格式和数据库配置影响，也不便于排序和日期计算。

假设日期列改名为 SpendDate，MySQL 查询可以写成：

WITH cycle_totals AS (
    SELECT
        s.ID,
        YEAR(s.SpendDate)
            - CASE
                WHEN MONTH(s.SpendDate) < bc.StartMonth THEN 1
                ELSE 0
              END AS cycle_key,
        SUM(s.Spend) AS TotalSpend
    FROM Spend AS s
    INNER JOIN BillingCycle AS bc
        ON bc.ID = s.ID
    GROUP BY
        s.ID,
        YEAR(s.SpendDate)
            - CASE
                WHEN MONTH(s.SpendDate) < bc.StartMonth THEN 1
                ELSE 0
              END
)
SELECT
    ID,
    DENSE_RANK() OVER (
        PARTITION BY ID
        ORDER BY cycle_key
    ) AS Cycle,
    TotalSpend
FROM cycle_totals
ORDER BY ID, Cycle;

关于 EndMonth

如果每个 Billing Cycle 都是连续的 12 个月，EndMonth 可以直接根据 StartMonth 推导：

EndMonth = StartMonth 的前一个月

例如：

• StartMonth = 10，则 EndMonth = 9
• StartMonth = 9，则 EndMonth = 8
• StartMonth = 1，则 EndMonth = 12

因此，查询只需使用 StartMonth。可以添加约束，防止 StartMonth 与 EndMonth 的数据不一致。

注意事项

• 查询会保留首尾不足 12 个月的账期。例如，用户 11 的 Cycle 1 只有 2019 年 9 月，Cycle 3 目前只有 2020 年 10 月和 11 月。
• DENSE_RANK() 根据数据库中实际存在的账期编号。如果某个完整账期没有任何消费记录，查询不会为它生成结果，后续编号也会相应压缩。
• 同一用户在同一个月有多条记录时，查询仍能正确求和，因此不要求“每月一行”。
• BillingCycle.ID 应保持唯一，否则连接可能导致消费金额被重复计算。
• Spend 表通常应在 (ID, SpendDate) 上建立索引，BillingCycle 表应在 ID 上建立唯一索引。

备注：内容仅供参考。