如何为每个构建计算仓库连续构建结果的Streak及Streak起始时间？

嘿，这个问题我之前也碰到过！单纯用LAG()确实搞不定，因为它只能固定看前几行，没法动态识别连续的相同结果序列。咱们换个思路，用分组标识法就能完美解决——核心是给每个连续相同Result的构建序列打上同一个分组ID，之后基于这个分组统计streak次数和起始时间就很简单了。

核心思路 #

1. 先按仓库分组，把每个仓库的构建按BuildDateTime降序排序（因为我们要从最新的构建往回数连续相同结果的次数）
2. 用窗口函数标记分组：当当前行的Result和上一行（更早的构建）不同时，生成一个新的分组ID，这样连续相同Result的行会被分到同一个组
3. 基于分组ID，统计每个分组的总构建数（就是该序列的最大streak），以及分组内最早的构建时间（streak的起始时间）
4. 最后给每个分组内的构建按时间倒序编号，这个编号就是当前构建的Streak值

完整SQL代码（以PostgreSQL为例，其他数据库可适配） #

CREATE VIEW BuildStreaks AS
WITH RankedBuilds AS (
    SELECT
        BuildID,
        Repository,
        BuildDateTime,
        Result,
        -- 给连续相同Result的序列分配唯一分组ID
        SUM(CASE WHEN prev_result != Result THEN 1 ELSE 0 END) OVER (
            PARTITION BY Repository 
            ORDER BY BuildDateTime DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS streak_group_id
    FROM (
        SELECT
            *,
            -- 获取同仓库中，上一个（更早的）构建的Result
            LAG(Result) OVER (
                PARTITION BY Repository 
                ORDER BY BuildDateTime DESC
            ) AS prev_result
        FROM BuildTable -- 替换成你的实际表名
    ) AS WithPrevResult
),
StreakStats AS (
    SELECT
        streak_group_id,
        Repository,
        COUNT(*) AS total_streak,
        MIN(BuildDateTime) AS streak_start -- 分组内最早的时间就是streak的起始时间
    FROM RankedBuilds
    GROUP BY streak_group_id, Repository
)
SELECT
    rb.BuildID,
    rb.Repository,
    rb.BuildDateTime,
    rb.Result,
    -- 按时间倒序给分组内的构建编号，就是当前构建的Streak值
    ROW_NUMBER() OVER (
        PARTITION BY rb.streak_group_id 
        ORDER BY rb.BuildDateTime DESC
    ) AS Streak,
    ss.streak_start AS StreakStartDateTime
FROM RankedBuilds rb
JOIN StreakStats ss 
    ON rb.streak_group_id = ss.streak_group_id 
    AND rb.Repository = ss.Repository
ORDER BY rb.Repository, rb.BuildDateTime DESC;

代码逐段解释 #

1. RankedBuilds CTE：标记连续序列分组 #

• 内层子查询用LAG()获取同仓库中，按时间倒序的上一行Result（也就是更早的构建结果）
• 外层用SUM(...) OVER()生成分组ID：每当当前Result和上一行不同时，就加1，这样连续相同Result的行会被分到同一个streak_group_id下

2. StreakStats CTE：统计分组的基础信息 #

• 按streak_group_id和Repository分组，统计每个连续序列的总构建数（total_streak），以及序列的起始时间（分组内最早的BuildDateTime）

3. 最终查询：生成目标结果 #

• 用ROW_NUMBER()给每个分组内的构建按时间倒序编号，这个编号就是当前构建的Streak值（最新的构建是该序列的第N次，往前依次递减）
• 关联StreakStats获取对应分组的起始时间，最终输出所有字段

适配不同数据库 #

• MySQL：可以省略ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，因为MySQL的窗口函数默认就是这个范围
• SQL Server：语法基本一致，无需大调整
• Oracle：把ROW_NUMBER()的语法调整为ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)即可，其他逻辑不变

验证示例数据 #

用你提供的示例数据测试，这个视图会输出你期望的结果：

• Repo1的两个Fail被分到同一组，最新的Fail（BuildID=1234）Streak为2，前一个（BuildID=2224）为1，起始时间都是2017-01-02T01:22:56
• Repo2的三个Success（BuildID=5234、6234、8234）被分到同一组，Streak分别为3、2、1，起始时间为2017-01-01T01:17:00

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