如何创建自定义周起始日的Snowflake表返回型UDF/存储过程

问题核心 #

Snowflake的SQL表返回型UDF不允许执行ALTER SESSION这类会话配置语句，这就是取消注释后执行失败的直接原因。要实现需求，有两种可行方案：

方案1：用存储过程整合会话设置与查询 #

存储过程支持多语句执行，可以先设置周起始日，再执行分组查询并返回结果。示例代码如下：

CREATE OR REPLACE PROCEDURE nr_sessacts_application_overview(reportStart DATE, reportEnd DATE)
RETURNS TABLE (BLOCKSTART DATE, BLOCKEND DATE, ACTIONCOUNT NUMBER)
LANGUAGE SQL
AS
$$
BEGIN
    -- 设置周起始日为周三（WEEK_START=3，1=周一，7=周日）
    ALTER SESSION SET WEEK_START = 3;
    
    -- 执行分组查询并返回结果
    RETURN TABLE(
        SELECT
            TO_DATE(TIME_SLICE(s_startdateutc, 1, 'WEEK', 'START')) AS BLOCKSTART,
            TO_DATE(TIME_SLICE(s_startdateutc, 1, 'WEEK', 'END')) AS BLOCKEND,
            COUNT(*) AS ACTIONCOUNT
        FROM mytable
        WHERE s_startdateutc BETWEEN reportStart AND reportEnd
        GROUP BY BLOCKSTART, BLOCKEND
        ORDER BY BLOCKSTART
    );
END;
$$;

调用存储过程的方式：

CALL nr_sessacts_application_overview('2024-01-01', '2024-06-30');

方案2：不修改会话，手动计算周起始/结束日 #

如果不想修改会话参数（避免影响其他操作），可以通过日期函数手动调整周的起始，替代TIME_SLICE对会话参数的依赖：

CREATE OR REPLACE FUNCTION nr_sessacts_application_overview(reportStart DATE, reportEnd DATE)
RETURNS TABLE (BLOCKSTART DATE, BLOCKEND DATE, ACTIONCOUNT NUMBER)
LANGUAGE SQL
AS
$$
SELECT
    -- 计算周三为起始的周起始日：先将日期往前调2天，用DATE_TRUNC得到默认周一起始的周，再加2天回到周三
    DATEADD(DAY, 2, DATE_TRUNC('WEEK', DATEADD(DAY, -2, s_startdateutc))) AS BLOCKSTART,
    -- 周结束日为起始日加7天
    DATEADD(DAY, 7, BLOCKSTART) AS BLOCKEND,
    COUNT(*) AS ACTIONCOUNT
FROM mytable
WHERE s_startdateutc BETWEEN reportStart AND reportEnd
GROUP BY BLOCKSTART, BLOCKEND
ORDER BY BLOCKSTART
$$;

说明 #

• 这种方法不需要修改会话参数，更适合多场景复用的函数，且不会影响当前会话的其他查询逻辑。

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