Snowflake中按周透视统计各小组每周打开状态工单数量

现有工单表结构 #

需求 #

生成按周的透视表，统计每个小组在每周内任何时间处于打开状态的工单数量（即工单的打开-关闭周期与该周存在重叠，而非统计当周创建的工单），预期输出示例：

已实现的代码片段 #

1. 单周统计SQL：

SELECT 
   group,
   COUNT_IF(
      date_opened <= DATEADD(week, 1, '2023-05-07') AND 
      (date_closed >= '2023-05-07' OR date_closed IS NULL)) as "2023-05-07"
FROM tickets
GROUP BY 1
ORDER BY 1;

2. 周日期序列生成SQL：

SELECT 
   DATEADD(week, '+' || SEQ4(), '2023-01-01'::DATE) as dte 
from table 
  (generator(rowcount => WEEKOFYEAR(current_date())));

核心思路 #

通过笛卡尔积关联周日期序列与工单表，先判断每个工单在对应周是否处于活跃状态，再用Snowflake的PIVOT函数将周日期转为列，生成透视表。

完整静态SQL（固定周范围） #

适用于周数固定、可提前列出所有周日期的场景：

WITH week_dates AS (
    -- 生成2023年全年周起始日期，可调整起始日期和行数（一年最多53周）
    SELECT 
        DATEADD(week, SEQ4(), '2023-01-01'::DATE) AS week_start
    FROM TABLE(GENERATOR(ROWCOUNT => 53))
),
ticket_week_check AS (
    SELECT
        t.group,
        w.week_start,
        -- 判断工单与当前周是否重叠：工单打开时间 ≤ 周结束日，且（工单关闭时间 ≥ 周起始日 或 未关闭）
        IFF(
            t.date_opened <= DATEADD(week, 1, w.week_start) 
            AND (t.date_closed >= w.week_start OR t.date_closed IS NULL),
            1, 0
        ) AS is_active
    FROM tickets t
    CROSS JOIN week_dates w
)
-- 生成透视表
SELECT
    group,
    "2023-01-01", "2023-01-08", "2023-01-15",
    -- 按实际生成的周日期补充更多列
    "2023-01-22", "2023-01-29"
FROM ticket_week_check
PIVOT(
    SUM(is_active) -- 求和得到该周活跃工单数量
    FOR week_start IN (
        '2023-01-01'::DATE, '2023-01-08'::DATE, '2023-01-15'::DATE,
        '2023-01-22'::DATE, '2023-01-29'::DATE
    )
) AS pivot_table
ORDER BY group;

动态SQL（自动适配所有周日期） #

如果周范围不固定，可通过动态SQL自动生成所有周列，无需手动维护：

DECLARE
    week_column_list STRING;
BEGIN
    -- 生成周日期的列定义字符串
    SELECT LISTAGG('''' || week_start || '''::DATE AS "' || week_start || '"', ', ') 
    INTO week_column_list
    FROM (
        SELECT DATEADD(week, SEQ4(), '2023-01-01'::DATE) AS week_start 
        FROM TABLE(GENERATOR(ROWCOUNT => 53))
    );

    -- 执行动态透视SQL
    EXECUTE IMMEDIATE '
        WITH week_dates AS (
            SELECT DATEADD(week, SEQ4(), ''2023-01-01''::DATE) AS week_start 
            FROM TABLE(GENERATOR(ROWCOUNT => 53))
        ),
        ticket_week_check AS (
            SELECT
                t.group,
                w.week_start,
                IFF(
                    t.date_opened <= DATEADD(week, 1, w.week_start) 
                    AND (t.date_closed >= w.week_start OR t.date_closed IS NULL),
                    1, 0
                ) AS is_active
            FROM tickets t
            CROSS JOIN week_dates w
        )
        SELECT group, ' || week_column_list || '
        FROM ticket_week_check
        PIVOT(
            SUM(is_active) 
            FOR week_start IN (' || REPLACE(week_column_list, ' AS "', '"') || ')
        ) AS pivot_table
        ORDER BY group;
    ';
END;

关键逻辑说明 #

1. 日期重叠判断：
   • 周结束日用DATEADD(week, 1, week_start)计算，确保覆盖整个周的时间范围
   • 未关闭工单用date_closed IS NULL处理，视为持续处于活跃状态
2. 笛卡尔积关联：让每个工单与所有周日期配对，逐一判断活跃状态
3. 透视表生成：通过PIVOT函数将行格式的周数据转为列，实现需求的表格样式

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