如何使用SQL查询表中各任务分组下最近连续N次失败的记录

现有表名为JOBS的表，表结构及示例数据如下：

| job_name |   created_on   | result |
| -------- | -------------  |--------
|    x     |  2021-01-20    | fail   |
|    y     |  2021-01-01    | success|
|    y     |  2021-01-02    | success|
|    x     |  2021-01-22    | fail   |
|    z     |  2021-03-15    | fail   |
|    z     |  2021-03-11    | success|
|    z     |  2021-03-16    | fail   |
|    x     |  2021-01-18    | fail   |
|    z     |  2021-01-03    | fail   |
|    x     |  2021-01-19    | success|

需求说明 #

按创建时间降序排序，查询表中每个job_name分组下最近连续N次执行失败的记录。
举例：当N=3时，需要找出最近连续3次执行失败的job_name对应记录；上述示例中N=2时，预期输出如下：

| job_name |   created_on   | result|
| -------- | -------------- |
|    x     |  2021-01-22    | fail  |
|    x     |  2021-01-20    | fail  |
|    z     |  2021-03-16    | fail  |
|    z     |  2021-03-15    | fail  |

你之前写法的问题：仅通过lag/lead判断单条记录的相邻结果，无法准确统计连续失败的总次数，也没有限定统计范围是「最近」的执行序列，会把历史上的连续失败记录也纳入匹配，导致结果错误。

核心思路用分组打标法识别连续失败的批次，再筛选最近批次的失败记录数≥N即可，支持动态适配任意N值，兼容MySQL 8.0+/PostgreSQL/Spark SQL/Hive等绝大多数支持窗口函数的数据库：

-- 需查询连续N次失败时，把下文HAVING条件里的2改成对应N值即可
WITH ranked_jobs AS (
    -- 给每个job的执行记录按创建时间倒序编号
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY created_on DESC) AS rn
    FROM JOBS
),
fail_group_flag AS (
    -- 给连续的失败记录打相同分组标识：遇到执行成功就累加标记值，失败则值不变
    SELECT 
        *,
        SUM(CASE WHEN result = 'success' THEN 1 ELSE 0 END) OVER (PARTITION BY job_name ORDER BY rn) AS fail_group
    FROM ranked_jobs
),
qualified_groups AS (
    -- 筛选出每个job下满足连续失败次数要求的最近批次
    SELECT 
        job_name,
        fail_group,
        COUNT(*) AS fail_count
    FROM fail_group_flag
    WHERE result = 'fail'
    GROUP BY job_name, fail_group
    HAVING COUNT(*) >= 2
    -- 取最近的失败批次，不支持QUALIFY语法的数据库可嵌套子查询加行号筛选
    QUALIFY ROW_NUMBER() OVER (PARTITION BY job_name ORDER BY MIN(rn) ASC) = 1
)
-- 取出符合条件的批次下所有失败记录，按要求排序
SELECT f.job_name, f.created_on, f.result
FROM fail_group_flag f
INNER JOIN qualified_groups q 
    ON f.job_name = q.job_name 
    AND f.fail_group = q.fail_group
ORDER BY f.job_name, f.created_on DESC;

如果仅需要返回符合条件的job_name列表，不需要返回明细记录，直接查询qualified_groups的job_name字段去重即可。

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