如何用Snowflake SQL计算工单实际处理时长（扣除非工作时间）

需求：编写Snowflake SQL查询，计算客服处理工单的实际时长。公司工作时间为周一至周六8:30 AM-5:30 PM，需从包含非工作时间的REPLY_TIME中扣除非工作时长。

工单数据 #

TICKET_ID   CREATED_AT          REPLY_TIME
157         2023-05-14 19:15:04.000  690
193         2023-05-20 11:11:19.000  2,634
72          2023-04-18 07:12:08.000  81
186         2023-05-19 07:04:23.000  16
165         2023-05-15 14:15:27.000  1
60          2023-04-04 08:10:52.000  1,344
32          2023-02-22 19:05:46.000  
93          2023-04-29 15:45:57.000  3,730
70          2023-04-15 16:47:07.000  2,268
83          2023-04-27 07:29:40.000  

原代码报错 #

运行原有SQL时出现错误：incompatible types: [TIME(9)] and [TIMESTAMP_NTZ(9)]

错误原因 #

原代码将TIME_SLICE转换后的TIME类型与start_time/end_time的TIMESTAMP类型直接进行范围比较，导致类型不兼容。此外，原代码的工作时间区间计算逻辑错误（错误设置为次日的工作时间，且结束时间不符合需求的17:30）。

修正后的完整SQL #

WITH ticket_times AS (
    SELECT
        TICKET_ID,
        CREATED_AT,
        -- 处理REPLY_TIME的千分位逗号，转换为数值类型
        NULLIF(REGEXP_REPLACE(REPLY_TIME, ',', ''), '')::INT AS REPLY_TIME_MINS,
        -- 计算回复完成时间
        DATEADD(MINUTE, NULLIF(REGEXP_REPLACE(REPLY_TIME, ',', ''), '')::INT, CREATED_AT) AS REPLY_COMPLETED_AT
    FROM tickets_metrics
),
business_hours AS (
    SELECT
        TICKET_ID,
        CREATED_AT,
        REPLY_TIME_MINS,
        REPLY_COMPLETED_AT,
        -- 生成从创建日期到回复日期的所有日期
        DATEADD(DAY, seq4(), DATE_TRUNC('DAY', CREATED_AT)) AS business_date,
        -- 当天的工作开始时间（8:30 AM）
        TIMESTAMP_FROM_PARTS(YEAR(business_date), MONTH(business_date), DAY(business_date), 8, 30, 0) AS work_start,
        -- 当天的工作结束时间（5:30 PM）
        TIMESTAMP_FROM_PARTS(YEAR(business_date), MONTH(business_date), DAY(business_date), 17, 30, 0) AS work_end
    FROM ticket_times
    -- 生成足够的日期范围，覆盖创建到回复的所有天数
    LEFT JOIN TABLE(GENERATOR(ROWCOUNT => 30)) 
        ON DATEADD(DAY, seq4(), DATE_TRUNC('DAY', CREATED_AT)) <= DATE_TRUNC('DAY', REPLY_COMPLETED_AT)
    WHERE REPLY_TIME_MINS IS NOT NULL
),
daily_work_duration AS (
    SELECT
        TICKET_ID,
        CREATED_AT,
        REPLY_TIME_MINS,
        -- 判断日期是否为工作日（周一至周六，1=周一，6=周六）
        CASE WHEN DAYOFWEEK(business_date) BETWEEN 1 AND 6 THEN 1 ELSE 0 END IS_WORKDAY,
        -- 计算当天实际的工作时长（分钟）
        DATEDIFF(MINUTE, 
            -- 取当天工作开始时间和创建时间的较大值
            GREATEST(work_start, CASE WHEN business_date = DATE_TRUNC('DAY', CREATED_AT) THEN CREATED_AT ELSE work_start END),
            -- 取当天工作结束时间和回复完成时间的较小值
            LEAST(work_end, CASE WHEN business_date = DATE_TRUNC('DAY', REPLY_COMPLETED_AT) THEN REPLY_COMPLETED_AT ELSE work_end END)
        ) AS daily_work_mins
    FROM business_hours
)
SELECT
    TICKET_ID,
    CREATED_AT,
    REPLY_TIME_MINS AS original_reply_time_mins,
    -- 总实际工作时长：所有工作日的有效工作分钟数之和
    SUM(CASE WHEN IS_WORKDAY = 1 AND daily_work_mins > 0 THEN daily_work_mins ELSE 0 END) AS adjusted_reply_time_mins
FROM daily_work_duration
GROUP BY TICKET_ID, CREATED_AT, REPLY_TIME_MINS
-- 补充未回复的工单
UNION ALL
SELECT
    TICKET_ID,
    CREATED_AT,
    NULL AS original_reply_time_mins,
    NULL AS adjusted_reply_time_mins
FROM ticket_times
WHERE REPLY_TIME_MINS IS NULL
ORDER BY TICKET_ID;

关键修改说明 #

• 类型兼容修复：不再进行TIME与TIMESTAMP的跨类型比较，全程基于完整的时间戳计算，避免类型冲突
• 工作时间修正：正确设置每日工作时间为8:30至17:30，并通过日期生成器覆盖工单跨天的场景
• 非工作时长计算：
  • 自动判断日期是否为工作日（周一至周六）
  • 针对创建或回复在非工作时间的情况，计算当天实际有效的工作时长
  • 对跨多天的工单，累加所有工作日的有效工作分钟数
• 数据预处理：处理REPLY_TIME中的千分位逗号，转换为数值类型，并处理空值场景

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