BigQuery同dag_id前行字段为FALSE置空及CTE别名调用问题

• 你定义的Started_On_Time、Ended_On_Time别名是在summary CTE内生成的，但最终查询直接读取原始表sla_table，没有引用summary的计算结果，自然无法识别这两个别名。
• 原逻辑用LAG()仅能获取相邻上一行的值，无法满足「同dag_id下只要出现过首个FALSE，后续所有行对应字段置空」的需求——如果异常行和后续行之间隔了正常行，LAG()判断会直接失效。
• 原summary CTE的end_date_tracker字段末尾多了冗余逗号，会触发语法错误。

WITH summary AS (
    SELECT 
        *,
        TIMESTAMP_DIFF(start_date, expected_start_date, MINUTE) AS Difference_Start_Time,
        TIMESTAMP_DIFF(end_date, expected_end_date, MINUTE)     AS Difference_End_Time,
        CASE
            WHEN TIMESTAMP_DIFF(start_date, expected_start_date, MINUTE) <= 0 THEN TRUE
            WHEN TIMESTAMP_DIFF(start_date, expected_start_date, MINUTE) > 0 THEN FALSE
        END AS Started_On_Time,
        CASE
            WHEN TIMESTAMP_DIFF(end_date, expected_end_date, MINUTE) <= 0 THEN TRUE
            WHEN TIMESTAMP_DIFF(end_date, expected_end_date, MINUTE) > 0 THEN FALSE
        END AS Ended_On_Time,
        CASE
            WHEN end_date IS NULL THEN 'FAILED'
            ELSE 'COMPLETED'
        END AS end_date_tracker
    FROM `np-inventory-planning-thd.IPP_SLA.sla_table`
),
mark_first_exception AS (
    SELECT 
        *,
        -- 标记每个dag_id下首次出现启动延迟的记录时间
        MIN(CASE WHEN Started_On_Time = FALSE THEN start_date END)
            OVER (PARTITION BY dag_id) AS first_start_delay,
        -- 标记每个dag_id下首次出现结束延迟的记录时间
        MIN(CASE WHEN Ended_On_Time = FALSE THEN start_date END)
            OVER (PARTITION BY dag_id) AS first_end_delay
    FROM summary
)
SELECT
    * EXCEPT(Started_On_Time, Ended_On_Time, first_start_delay, first_end_delay),
    -- 晚于首次启动延迟的行，字段置空，否则保留原校验值
    CASE
        WHEN start_date > first_start_delay THEN NULL
        ELSE Started_On_Time
    END AS Started_On_Time,
    -- 晚于首次结束延迟的行，字段置空，否则保留原校验值
    CASE
        WHEN start_date > first_end_delay THEN NULL
        ELSE Ended_On_Time
    END AS Ended_On_Time
FROM mark_first_exception

• 第一层CTE完全保留原有的时间差计算、准时性校验、运行状态判断逻辑，仅去掉了冗余的语法逗号。
• 第二层CTE用窗口函数MIN() OVER(PARTITION BY dag_id)直接定位每个dag_id下两类异常最早出现的时间，不需要逐行递归判断，查询性能远高于嵌套LAG的写法。
• 最终输出时，判断当前行时间是否晚于对应异常的首次出现时间：晚于则说明该dag已经出过同类型告警，当前行对应字段置NULL；否则保留原TRUE/FALSE校验值，刚好实现「每个dag仅保留首个FALSE告警，其余行同字段置空」的规则。
• 所有别名的引用都基于前序CTE的输出，不会出现字段无法识别的问题。如果某dag从未出现过对应异常，首次异常时间为NULL，判断逻辑会自动保留所有行的TRUE值，符合预期。

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