Snowflake中kurtosis和skew函数除零错误的处理方法

解决方案 #

1. 原生错误捕获函数实现需求 #

Snowflake原生支持高阶函数TRY()，作用和你提到的iferror()完全一致：包裹任意表达式后，若表达式执行报错会自动返回NULL，正好适配你的场景。你可以直接修改kurtosis和skew的计算逻辑：

TRY(kurtosis(iff(typeof(f.value) in ('DECIMAL', 'INTEGER'), f.value, null))) kurtosis,
TRY(skew(iff(typeof(f.value) in ('DECIMAL', 'INTEGER'), f.value, null))) skewness

如果需要排除出错的记录，在HAVING子句中过滤掉这两个字段为NULL的分组即可。

2. 前置条件判断（性能更优） #

你的报错原因判断准确：当分组内数值型样本量小于3、或所有数值完全相同（标准差为0）时，偏度、峰度计算的分母会为0触发错误。你可以直接利用已计算的指标前置判断，避免无效计算，性能比TRY()更高：

iff(unique_val_cnt >=3 AND std != 0, kurtosis(iff(typeof(f.value) in ('DECIMAL', 'INTEGER'), f.value, null)), null) kurtosis,
iff(unique_val_cnt >=3 AND std != 0, skew(iff(typeof(f.value) in ('DECIMAL', 'INTEGER'), f.value, null)), null) skewness

3. 自定义UDF实现方案 #

如果需要更灵活的控制逻辑，完全可以通过UDF封装安全计算逻辑，示例如下：

-- 安全计算偏度UDF
CREATE OR REPLACE FUNCTION SAFE_SKEW(val_arr ARRAY)
RETURNS FLOAT
AS $$
SELECT iff(COUNT(*) >=3 AND STDDEV(value) != 0, SKEW(value), NULL)
FROM TABLE(FLATTEN(input => val_arr))
WHERE value IS NOT NULL AND IS_DECIMAL(value)
$$;

-- 安全计算峰度UDF
CREATE OR REPLACE FUNCTION SAFE_KURTOSIS(val_arr ARRAY)
RETURNS FLOAT
AS $$
SELECT iff(COUNT(*) >=4 AND STDDEV(value) != 0, KURTOSIS(value), NULL)
FROM TABLE(FLATTEN(input => val_arr))
WHERE value IS NOT NULL AND IS_DECIMAL(value)
$$;

使用时将分组内的数值列聚合为数组传入UDF即可。

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