如何用SQL ROW_NUMBER实现triggerTable指定规则分组取数

核心规则匹配思路 #

完全贴合需求的取数逻辑可以拆成两个独立场景匹配，不需要复杂的嵌套关联：

• 对历史出现过任意一次运行失败的触发器：全量返回该触发器下所有isError=1的失败记录，不限制失败发生的时间节点。
• 对从未出现过运行失败的触发器：仅返回该触发器最新运行时间对应的isError=0正常记录。

标准窗口函数实现（兼容MySQL 8.0+、PostgreSQL、Hive、ClickHouse等主流数据库） #

通过CTE结合窗口函数，仅需一次扫描原表即可完成所有标记逻辑，在大数据量场景下的性能远优于多次关联扫表的写法：

WITH trigger_meta AS (
    SELECT
        trigger,
        date,
        isError,
        -- 标记当前触发器是否存在历史失败：只要出现过isError=1，max值即为1
        MAX(isError) OVER (PARTITION BY trigger) AS has_error_history,
        -- 给每个触发器的所有运行记录按时间倒序生成排名
        ROW_NUMBER() OVER (
            PARTITION BY trigger
            ORDER BY date DESC
        ) AS run_rank
    FROM triggerTable
)
SELECT
    trigger,
    date,
    isError
FROM trigger_meta
WHERE
    -- 匹配第一类场景：有失败历史的触发器，返回所有关联的失败记录
    (has_error_history = 1 AND isError = 1)
    -- 匹配第二类场景：无失败历史的触发器，返回最新的那条正常运行记录
    OR (has_error_history = 0 AND run_rank = 1 AND isError = 0)

低版本数据库兼容实现（无窗口函数支持场景） #

如果使用的数据库版本较低、不支持窗口函数（比如MySQL 5.x系列），可以通过分组聚合+关联的方式实现完全相同的逻辑：

SELECT
    t.trigger,
    t.date,
    t.isError
FROM triggerTable t
LEFT JOIN (
    -- 先统计每个触发器是否存在历史失败标记
    SELECT
        trigger,
        MAX(isError) AS has_error_history
    FROM triggerTable
    GROUP BY trigger
) err_flag ON t.trigger = err_flag.trigger
WHERE
    -- 有失败历史：返回该触发器的所有失败记录
    (err_flag.has_error_history = 1 AND t.isError = 1)
    -- 无失败历史：匹配该触发器的最新运行时间对应的记录
    OR (
        err_flag.has_error_history = 0
        AND t.isError = 0
        AND t.date = (
            SELECT MAX(date)
            FROM triggerTable t2
            WHERE t2.trigger = t.trigger
        )
    )

落地注意事项 #

• 如果存在同一触发器在同一运行时间下有多条重复记录的情况，可以根据业务需要把窗口函数里的ROW_NUMBER()替换为RANK()或DENSE_RANK()，避免同时间点的多条有效记录被误过滤。
• 该逻辑严格匹配规则要求：只要触发器历史上出现过哪怕一次运行失败，就不会返回它后续的正常运行记录，仅输出所有对应的失败行。

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