如何在Snowflake中统计任务30天平均执行耗时并排序

核心问题分析 #

你遇到的information_schema.task_history()数据不足（仅2天而非文档标注的7天），是因为该视图仅保留当前数据库/ schema下任务最近7天的运行记录，部分场景下实际保留时长可能更短；同时它无法覆盖超过7天的历史数据，无法满足30天统计需求。要获取长期历史数据，建议使用ACCOUNT_USAGE.TASK_HISTORY视图，该视图默认保留1年的任务运行记录，权限允许的话是最优选择。

修改后的查询语句 #

以下查询可获取30天内各任务的平均调度延迟（计划执行时间到实际启动时间的间隔）、平均执行耗时，并按平均执行时长排序：

SELECT
    task_name,
    task_id,
    COUNT(*) AS total_runs,
    -- 平均调度延迟：计划时间到实际启动的秒数
    AVG(DATEDIFF(SECOND, scheduled_time, query_start_time)) AS avg_schedule_delay_sec,
    -- 平均执行耗时：实际启动到执行结束的秒数
    AVG(DATEDIFF(SECOND, query_start_time, query_end_time)) AS avg_execution_duration_sec
FROM
    snowflake.account_usage.task_history
WHERE
    -- 过滤最近30天的数据
    scheduled_time >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
    -- 排除未实际执行的调度状态
    AND state != 'SCHEDULED'
GROUP BY
    task_name, task_id
-- 按平均执行耗时从高到低排序，可替换为avg_schedule_delay_sec
ORDER BY
    avg_execution_duration_sec DESC;

替代方案（无ACCOUNT_USAGE权限时） #

如果没有账户级权限访问ACCOUNT_USAGE，可以使用task_history()函数的日期范围参数强制拉取30天数据（需注意：若Snowflake实际未保留这么久的任务记录，结果仍会受限）：

SELECT
    task_name,
    COUNT(*) AS total_runs,
    AVG(DATEDIFF(SECOND, scheduled_time, query_start_time)) AS avg_schedule_delay_sec,
    AVG(DATEDIFF(SECOND, query_start_time, query_end_time)) AS avg_execution_duration_sec
FROM
    TABLE(information_schema.task_history(
        DATE_RANGE_START => DATEADD(DAY, -30, CURRENT_TIMESTAMP()),
        DATE_RANGE_END => CURRENT_TIMESTAMP()
    ))
WHERE
    state != 'SCHEDULED'
GROUP BY
    task_name
ORDER BY
    avg_execution_duration_sec DESC;

关键说明 #

• ACCOUNT_USAGE.TASK_HISTORY的数据会有1-2小时的延迟，若需实时数据仍需使用information_schema.task_history()
• 分组时同时使用task_name和task_id是为了避免不同任务重名的情况
• 可根据需求切换排序字段：若关注调度延迟则用avg_schedule_delay_sec，关注执行效率则用avg_execution_duration_sec

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