如何筛选连续2天以上未通过质量测试的表？

你的原查询用ROW_NUMBER()会累计所有历史失败记录的序号，无法区分被成功中断的失败段，所以会错误统计非连续的失败天数。要实现连续失败天数统计+问题解决后自动排除的需求，核心是先识别出连续的失败日期区间，再结合后续是否有成功来过滤。

解决方案SQL #

WITH daily_failures AS (
    -- 按天去重，一天内同一表同一测试多次失败只算1天
    SELECT DISTINCT
        full_table_name,
        expectation_type,
        date_trunc('day', run_time) AS fail_date,
        meta,
        expectation_columns,
        origin_type
    FROM MY_TABLE
    WHERE is_successful = false
        AND origin_type = 'DWH_TESTS'
),
failure_groups AS (
    -- 标记连续失败的分组：通过日期减去序号天数，连续日期会得到相同的group_id
    SELECT
        *,
        fail_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
            PARTITION BY full_table_name, expectation_type 
            ORDER BY fail_date
        ) AS group_id
    FROM daily_failures
),
failure_segments AS (
    -- 聚合每个连续失败段的起始、结束日期和连续天数
    SELECT
        full_table_name,
        expectation_type,
        meta,
        expectation_columns,
        origin_type,
        MIN(fail_date) AS start_date,
        MAX(fail_date) AS end_date,
        COUNT(*) AS consecutive_days
    FROM failure_groups
    GROUP BY full_table_name, expectation_type, meta, expectation_columns, origin_type, group_id
),
-- 检查每个失败段之后是否有成功记录（问题已解决）
resolved_check AS (
    SELECT
        fs.*,
        CASE 
            WHEN EXISTS (
                SELECT 1 FROM MY_TABLE t
                WHERE t.full_table_name = fs.full_table_name
                  AND t.expectation_type = fs.expectation_type
                  AND t.is_successful = true
                  AND date_trunc('day', t.run_time) > fs.end_date
            ) THEN true
            ELSE false
        END AS is_resolved
    FROM failure_segments fs
)
-- 筛选连续天数>=2且未解决的失败段
SELECT
    start_date,
    end_date,
    full_table_name,
    expectation_type,
    meta,
    expectation_columns,
    origin_type,
    consecutive_days
FROM resolved_check
WHERE consecutive_days >= 2
  AND is_resolved = false
ORDER BY end_date DESC;

关键逻辑说明 #

• daily_failures：先按天去重，避免同一天多次失败被重复统计，确保每天只算一次失败。
• failure_groups：通过fail_date - 序号天数生成group_id，连续的日期会得到相同的group_id，以此区分不同的连续失败段。
• failure_segments：按group_id聚合，得到每个连续失败段的起始日期、结束日期和连续天数。
• resolved_check：关联原表检查该表该测试在失败段结束后是否有成功记录，标记为已解决的失败段不再展示。
• 最终筛选：只保留连续天数≥2且未解决的失败段，符合你"问题解决后不再出现，除非再次触发阈值"的需求。

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