如何在Snowflake数据集中计算基于72小时窗口的代理分配逻辑？

需求规则 #

使用Snowflake数据库处理客户工单数据集，需生成how_to_get_agent_72hr列，规则如下：

• 若工单创建时间处于某窗口起始工单的72小时范围内，则继承该窗口起始工单的代理。
• 窗口的72小时从起始工单的创建时间开始计算，超出该范围的工单将作为新窗口的起始。

重要说明：case_15的创建时间虽在case_14的72小时内，但不分配给Alejandro，因为Alejandro对应的窗口始于2024-04-05 17:47:00，结束于2024-04-08 17:47:00，72小时时长从窗口起始时间开始计算。

数据集示例 #

Snowflake SQL实现方案 #

通过标记窗口起始、生成分组ID，再继承窗口起始代理值，代码如下：

WITH case_with_window_start AS (
    SELECT
        customer_id,
        case_id,
        case_dt,
        agent,
        -- 标记新窗口起始：首行 或 当前时间超出当前累积窗口起始的72小时
        CASE
            WHEN LAG(case_dt) OVER (PARTITION BY customer_id ORDER BY case_dt) IS NULL THEN 1
            WHEN case_dt > DATEADD(HOUR, 72, FIRST_VALUE(case_dt) OVER (
                PARTITION BY customer_id 
                ORDER BY case_dt 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            )) THEN 1
            ELSE 0
        END AS is_window_start
    FROM your_case_table
),
case_with_window_group AS (
    SELECT
        *,
        -- 累积求和生成窗口分组ID
        SUM(is_window_start) OVER (PARTITION BY customer_id ORDER BY case_dt) AS window_group_id
    FROM case_with_window_start
)
SELECT
    customer_id,
    case_id,
    case_dt,
    agent,
    -- 取窗口首行的代理值作为整组的结果
    FIRST_VALUE(agent) OVER (PARTITION BY customer_id, window_group_id ORDER BY case_dt) AS how_to_get_agent_72hr
FROM case_with_window_group
ORDER BY customer_id, case_dt;

关键逻辑说明 #

• PARTITION BY customer_id：确保每个客户的窗口独立计算，避免跨客户干扰。
• is_window_start标记：判断是否需要开启新窗口，核心是检查当前工单时间是否超出当前已累积窗口起始时间的72小时。
• window_group_id：通过累积起始标记的求和，将同一窗口的工单归为同一组。
• FIRST_VALUE(agent)：为同一窗口的所有工单取起始行的代理值，生成目标列。

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