如何按起始 InvoiceStage ID 对记录进行账单周期分组

2026-10-24支付小周

结论

可以先用窗口函数按时间顺序统计每个 Project 中出现 StageFlag = 'S' 的次数，得到周期序号，再将该周期起始记录的 ID 填充到周期内的所有记录。

这里需要把最近一次起始记录的 ID 向后填充，并非简单比较相邻行，因此使用窗口聚合比 LAG() 或 LEAD() 更合适。

WITH CycleGroups AS
(
    SELECT
        ID,
        Project,
        StageDate,
        InvoiceStage,
        StageFlag,
        SUM(CASE WHEN StageFlag = 'S' THEN 1 ELSE 0 END) OVER
        (
            PARTITION BY Project
            ORDER BY StageDate, ID
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CycleNo
    FROM InvoiceStages
)
SELECT
    ID,
    Project,
    StageDate,
    InvoiceStage,
    StageFlag,
    MAX(CASE WHEN StageFlag = 'S' THEN ID END) OVER
    (
        PARTITION BY Project, CycleNo
    ) AS BillCycle
FROM CycleGroups
ORDER BY Project, StageDate, ID;

实现原理

第一个窗口函数分别计算每个项目中起始记录的累计数量：

SUM(CASE WHEN StageFlag = 'S' THEN 1 ELSE 0 END)

每遇到一条起始记录，累计值就增加 1，所以同一账单周期内的记录会得到相同的 CycleNo。

例如，项目 abc123 的分组结果如下：

ID  StageFlag  CycleNo
1   S          1
2              1
4              1
7   E          1
9   S          2
12             2
...
40  E          2

第二个窗口函数再从每个 Project + CycleNo 分组中取出起始记录的 ID：

MAX(CASE WHEN StageFlag = 'S' THEN ID END)

正常情况下，每个周期只有一条 StageFlag = 'S' 的记录。这个 ID 因而会出现在该周期的所有记录中，包括 StageFlag = 'E' 的结束记录。

更简洁的写法

如果能保证以下条件：

ID 按业务发生顺序持续递增；
每个周期都以 S 开始；
记录顺序可以通过 StageDate, ID 唯一确定；

也可以直接取当前行及之前记录中最大的起始 ID：

SELECT
    ID,
    Project,
    StageDate,
    InvoiceStage,
    StageFlag,
    MAX(CASE WHEN StageFlag = 'S' THEN ID END) OVER
    (
        PARTITION BY Project
        ORDER BY StageDate, ID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS BillCycle
FROM InvoiceStages
ORDER BY Project, StageDate, ID;

这条查询会将最近出现的起始 ID 向后填充，直到遇到下一条 S 记录。

这种写法要求后一个周期的起始 ID 大于前一个周期的起始 ID。如果 ID 可能乱序，应使用前面的两步分组方案。

注意事项

必须明确记录顺序

账单周期取决于记录的业务顺序，因此窗口函数中的 ORDER BY 必须能够稳定排序。如果多个阶段可能拥有相同的 StageDate，需要增加唯一字段作为第二个排序条件：

ORDER BY StageDate, ID

如果表中还有更准确的时间戳或阶段序号，应优先使用那个字段。

起始周期之前的记录

如果某个项目在第一条 S 之前还有普通记录，两步方案会将这些记录的 CycleNo 设为 0，对应的 BillCycle 为 NULL。这通常便于发现异常数据。

`E` 不直接决定分组

上述查询只有在遇到下一条 S 时才会创建新周期。因此，位于 E 之后、下一条 S 之前的记录仍会归入前一个周期。

这符合“由 S 开启新周期”的规则。如果业务要求 E 之后的记录返回 NULL，还需要额外判断周期是否已经结束。

数据完整性

查询假定每个周期只有一条 S，正常情况下也有对应的 E。建议另外检查这些异常：

一个项目连续出现多个 S；
出现 E 时，此前没有对应的 S；
一个周期中出现多个 E；
周期开始后一直没有结束记录。

对于题目给出的数据，两步窗口函数方案会得到预期的 BillCycle：1、9、3 和 10。