如何在Snowflake中按Job ID计算仅时间部分的平均值

算力阿峰

2026-7-11

解决方案

要计算时间部分的平均值，不能直接对TIME/TIMETZ类型使用AVG()，因为这类类型不支持算术平均运算。正确的做法是先把时间转换为从午夜开始的总秒数（数值型），计算平均值后再转回带时区的时间格式。

具体SQL实现（适配Snowflake语法）

SELECT
    JOB_ID,
    -- 将平均秒数转回带时区的时间格式
    TO_TIME(AVG(
        -- 提取时间部分并转换为从午夜开始的总秒数
        DATE_PART('second', TO_TIME(OOB_TIMESTAMP)) +
        DATE_PART('minute', TO_TIME(OOB_TIMESTAMP)) * 60 +
        DATE_PART('hour', TO_TIME(OOB_TIMESTAMP)) * 3600
    )) || ' -0700' AS AVG_OOB_TIME
FROM
    your_table_name
WHERE
    OOB_TIMESTAMP IS NOT NULL -- 忽略NULL值
GROUP BY
    JOB_ID;

步骤说明

过滤无效值：用WHERE OOB_TIMESTAMP IS NOT NULL排除空值，避免干扰平均值计算。
时间转数值：通过DATE_PART分别提取时、分、秒，转换为从午夜0点开始的总秒数（小时×3600 + 分钟×60 + 秒），把时间转换成可参与算术运算的数值。
平均后转回时间：对秒数求平均后，用TO_TIME()把数值转回时间格式，再拼接固定时区后缀（你的样本数据时区统一为-0700）。

如果需要自动匹配原数据的时区（而非硬编码），可以调整为：

SELECT
    JOB_ID,
    TO_TIME(AVG(
        DATE_PART('second', TO_TIME(OOB_TIMESTAMP)) +
        DATE_PART('minute', TO_TIME(OOB_TIMESTAMP)) * 60 +
        DATE_PART('hour', TO_TIME(OOB_TIMESTAMP)) * 3600
    )) || ' ' || SUBSTR(OOB_TIMESTAMP::STRING, 20) AS AVG_OOB_TIME
FROM
    your_table_name
WHERE
    OOB_TIMESTAMP IS NOT NULL
GROUP BY
    JOB_ID;