Snowflake中如何查询全空值列及导出Snowsight空值占比数据？

1. 如何查询列出所有值均为NULL的列？ #

可以通过动态SQL结合聚合函数实现，核心逻辑是利用COUNT()函数忽略NULL值的特性——若某列的COUNT()结果为0，说明该列所有值都是NULL。

示例代码（针对示例表TAB）： #

SET table_name = 'TAB';
SET schema_name = CURRENT_SCHEMA();
SET database_name = CURRENT_DATABASE();

-- 生成动态查询片段，统计每列非NULL值数量
SET query_fragment = (
    SELECT STRING_AGG(
        '''' || COLUMN_NAME || ''' AS col_name, COUNT(' || COLUMN_NAME || ') AS non_null_cnt',
        ', '
    )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = $table_name
      AND TABLE_SCHEMA = $schema_name
      AND TABLE_CATALOG = $database_name
);

-- 拼接完整查询语句，筛选出全空列
SET full_query = 'SELECT col_name FROM (SELECT ' || $query_fragment || ' FROM ' || $table_name || ') t UNPIVOT (non_null_cnt FOR col_name IN (' || (SELECT STRING_AGG('''' || COLUMN_NAME || '''', ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = $table_name AND TABLE_SCHEMA = $schema_name AND TABLE_CATALOG = $database_name) || ')) WHERE non_null_cnt = 0';

-- 执行查询
EXECUTE IMMEDIATE $full_query;

执行后会返回所有全为NULL的列（示例表中为Z列）。

2. 如何通过查询获取空值占比/从Snowsight导出？ #

通过查询获取空值占比 #

用动态SQL结合COUNT_IF()函数计算每列空值占总行数的比例：

SET table_name = 'TAB';
SET schema_name = CURRENT_SCHEMA();
SET database_name = CURRENT_DATABASE();

-- 获取表的总行数
SET total_rows = (SELECT COUNT(*) FROM $table_name);

-- 生成动态查询片段，计算每列空值占比
SET query_fragment = (
    SELECT STRING_AGG(
        '''' || COLUMN_NAME || ''' AS col_name, COUNT_IF(' || COLUMN_NAME || ' IS NULL)/' || $total_rows || '::FLOAT AS null_ratio',
        ', '
    )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = $table_name
      AND TABLE_SCHEMA = $schema_name
      AND TABLE_CATALOG = $database_name
);

-- 拼接完整查询语句
SET full_query = 'SELECT * FROM (SELECT ' || $query_fragment || ' FROM ' || $table_name || ') t UNPIVOT (null_ratio FOR col_name IN (' || (SELECT STRING_AGG('''' || COLUMN_NAME || '''', ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = $table_name AND TABLE_SCHEMA = $schema_name AND TABLE_CATALOG = $database_name) || '))';

-- 执行查询
EXECUTE IMMEDIATE $full_query;

该查询会返回每列的空值占比，筛选null_ratio = 1即可得到全空列。

从Snowsight导出空值占比数据 #

在Snowsight中操作步骤：

• 进入目标表的详情页，找到列统计模块
• 点击模块右上角的下载图标，选择CSV/JSON等格式即可导出所有列的空值占比数据

3. 从含1000+列的表创建新表，排除全空值列 #

先获取非全空列的列表，再用动态SQL生成建表语句：

SET table_name = 'TAB';
SET schema_name = CURRENT_SCHEMA();
SET database_name = CURRENT_DATABASE();

-- 获取所有非全空列的名称
SET non_null_cols = (
    SELECT STRING_AGG(col_name, ', ')
    FROM (
        SELECT col_name
        FROM (
            SELECT 'dummy' AS dummy, ' || STRING_AGG('''' || COLUMN_NAME || ''' AS col_name, COUNT(' || COLUMN_NAME || ') AS non_null_cnt', ', ') || ' FROM ' || $table_name
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = $table_name
              AND TABLE_SCHEMA = $schema_name
              AND TABLE_CATALOG = $database_name
        ) t
        UNPIVOT (non_null_cnt FOR col_name IN (' || (SELECT STRING_AGG('''' || COLUMN_NAME || '''', ', ') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = $table_name AND TABLE_SCHEMA = $schema_name AND TABLE_CATALOG = $database_name) || '))
        WHERE non_null_cnt > 0
    )
);

-- 生成并执行建表语句
SET create_table_sql = 'CREATE OR REPLACE TABLE NEW_' || $table_name || ' AS SELECT ' || $non_null_cols || ' FROM ' || $table_name;

EXECUTE IMMEDIATE $create_table_sql;

执行后会创建NEW_TAB表，仅保留原表中非全空的列（示例表中为Q、X、Y列）。

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