如何扩展Snowflake的z_score函数以支持可选PARTITION BY/ORDER BY参数

方案1：使用Snowflake宏（推荐） #

Snowflake宏支持动态生成SQL，完美适配自定义窗口子句的需求，能支持任意合法的窗口语法（多列分区、排序、窗口框架等）。

宏定义

CREATE OR REPLACE MACRO z_score(x_expr STRING, window_clause STRING DEFAULT '')
RETURNS STRING
COMMENT = 'Flexible z-score calculation with custom window clauses'
AS $$
    NVL2($x_expr, 
        ($x_expr - AVG($x_expr) OVER($IF($window_clause != '', $CONCAT('(', $window_clause, ')'), ''))) 
        / STDDEV($x_expr) OVER($IF($window_clause != '', $CONCAT('(', $window_clause, ')'), '')), 
        NULL)
$$;

调用示例

• 全局计算：

SELECT 
    x_val,
    z_score('x_val') AS z_score
FROM tmp_some_values;

• 单分组列：

SELECT 
    x_val,
    z_score('x_val', 'PARTITION BY group_col') AS z_score_group
FROM tmp_some_values;

• 多分组列+排序：

SELECT 
    x_val,
    z_score('x_val', 'PARTITION BY group_col ORDER BY x_val DESC') AS z_score_ordered
FROM tmp_some_values;

方案2：JavaScript UDF支持多分组列 #

如果偏好使用UDF而非宏，可以通过JavaScript UDF实现动态SQL，支持传入多个分组列：

UDF定义

CREATE OR REPLACE FUNCTION z_score(x FLOAT, partition_cols VARIANT DEFAULT NULL)
RETURNS FLOAT
COMMENT = 'Z-score calculation with multi-column partition support'
LANGUAGE JAVASCRIPT
AS $$
    let partition_clause = '';
    if (PARTITION_COLS !== null && PARTITION_COLS.length > 0) {
        partition_clause = 'PARTITION BY ' + PARTITION_COLS.join(', ');
    }
    const sql = `
        SELECT NVL2(?, (? - AVG(?) OVER(${partition_clause})) / STDDEV(?) OVER(${partition_clause}), NULL)
        FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    `;
    const stmt = snowflake.createStatement({sqlText: sql, binds: [X, X, X, X]});
    const res = stmt.execute();
    res.next();
    return res.getColumnValue(1);
$$;

调用示例

• 全局计算：

SELECT x_val, z_score(x_val) AS z_score FROM tmp_some_values;

• 单分组列：

SELECT x_val, z_score(x_val, ['group_col']) AS z_score_group FROM tmp_some_values;

• 多分组列：

SELECT x_val, z_score(x_val, ['group_col', 'another_group_col']) AS z_score_multi FROM tmp_some_values;

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