Snowflake查询除零（Divide by zero）错误解决及Null值替换咨询

一、解决除零（Divide by zero）错误 #

当查询范围内无匹配记录时，cte_ext.total_count会为0，直接执行除法运算会触发除零错误，可通过以下两种方式处理：

方法1：使用NULLIF函数规避分母为0 #

WITH cte_ext AS (
    SELECT COUNT(*) AS total_count
    FROM snowflake.account_usage.query_history
    WHERE start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
), cte_conditional AS (
    SELECT COUNT(*) AS condition_count
    FROM snowflake.account_usage.query_history
    WHERE total_elapsed_time < 1000 
      AND start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
)
SELECT 
    ROUND((cte_conditional.condition_count / NULLIF(cte_ext.total_count, 0)) * 100, 2) 
    AS "查询耗时1秒内占比"
FROM cte_conditional, cte_ext;

NULLIF(cte_ext.total_count, 0)会在total_count为0时返回NULL，此时除法运算结果为NULL，不会触发错误。

方法2：使用CASE WHEN做条件判断 #

WITH cte_ext AS (
    SELECT COUNT(*) AS total_count
    FROM snowflake.account_usage.query_history
    WHERE start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
), cte_conditional AS (
    SELECT COUNT(*) AS condition_count
    FROM snowflake.account_usage.query_history
    WHERE total_elapsed_time < 1000 
      AND start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
)
SELECT 
    CASE 
        WHEN cte_ext.total_count = 0 THEN NULL
        ELSE ROUND((cte_conditional.condition_count / cte_ext.total_count) * 100, 2)
    END AS "查询耗时1秒内占比"
FROM cte_conditional, cte_ext;

通过判断total_count是否为0，直接返回NULL避免运算错误。

二、将NULL值替换为中文提示文本 #

解决除零问题后，无数据时会返回NULL，可通过IFNULL（或NVL）结合TO_CHAR将NULL替换为指定中文文本：

WITH cte_ext AS (
    SELECT COUNT(*) AS total_count
    FROM snowflake.account_usage.query_history
    WHERE start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
), cte_conditional AS (
    SELECT COUNT(*) AS condition_count
    FROM snowflake.account_usage.query_history
    WHERE total_elapsed_time < 1000 
      AND start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
)
SELECT 
    IFNULL(
        TO_CHAR(ROUND((cte_conditional.condition_count / NULLIF(cte_ext.total_count, 0)) * 100, 2)) || '%',
        '未找到或未执行任何查询'
    ) AS "查询耗时1秒内占比"
FROM cte_conditional, cte_ext;

• TO_CHAR将数值型百分比转为字符串，拼接%让结果更直观
• IFNULL判断结果为NULL时，替换为指定中文提示文本

也可以用CASE WHEN实现相同效果：

WITH cte_ext AS (
    SELECT COUNT(*) AS total_count
    FROM snowflake.account_usage.query_history
    WHERE start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
), cte_conditional AS (
    SELECT COUNT(*) AS condition_count
    FROM snowflake.account_usage.query_history
    WHERE total_elapsed_time < 1000 
      AND start_time = :daterange 
      AND database_name = :database_name 
      AND warehouse_name = :warehouse_name
)
SELECT 
    CASE 
        WHEN cte_ext.total_count = 0 THEN '未找到或未执行任何查询'
        ELSE TO_CHAR(ROUND((cte_conditional.condition_count / cte_ext.total_count) * 100, 2)) || '%'
    END AS "查询耗时1秒内占比"
FROM cte_conditional, cte_ext;

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