如何在Snowflake中筛选全表行均为NULL值的所有列

针对250列的大表，手动逐个检查列是否全为NULL效率极低，以下是不同数据库系统的高效解决方案：

MySQL/MariaDB #

方法1：批量统计各列非NULL行数 #

通过系统表information_schema.COLUMNS自动生成统计查询，一次性获取所有列的非NULL行数，值为0的列即为全NULL列：

SELECT 
  GROUP_CONCAT(
    CONCAT('SUM(CASE WHEN `', column_name, '` IS NOT NULL THEN 1 ELSE 0 END) AS `', column_name, '_non_null`')
    SEPARATOR ', '
  ) INTO @sql
FROM information_schema.COLUMNS 
WHERE table_schema = '你的数据库名' 
  AND table_name = '你的表名';

SET @sql = CONCAT('SELECT ', @sql, ' FROM 你的表名');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方法2：直接筛选全NULL列 #

直接查询系统表，结合子判断列是否无任何非NULL值：

SELECT column_name
FROM information_schema.COLUMNS
WHERE table_schema = '你的数据库名' 
  AND table_name = '你的表名'
AND (
  SELECT COUNT(*) 
  FROM 你的表名 
  WHERE column_name IS NOT NULL
) = 0;

PostgreSQL #

方法1：利用系统元数据生成统计查询 #

通过pg_catalog.pg_attribute获取列名，生成批量统计语句：

DO $$
DECLARE
  sql_query TEXT;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN %I IS NOT NULL THEN 1 ELSE 0 END) AS %I_non_null', attname, attname),
    ', '
  ) INTO sql_query
  FROM pg_catalog.pg_attribute
  WHERE attrelid = '你的模式名.你的表名'::regclass
    AND attnum > 0
    AND NOT attisdropped;

  EXECUTE format('SELECT %s FROM 你的模式名.你的表名', sql_query);
END $$;

方法2：利用统计信息快速筛选 #

PostgreSQL的pg_stats表存储了列的NULL比例，直接筛选null_frac = 1（即100%为NULL）的列：

SELECT attname AS column_name
FROM pg_catalog.pg_attribute
JOIN pg_catalog.pg_stats ON pg_stats.tablename = '你的表名' 
  AND pg_stats.schemaname = '你的模式名' 
  AND pg_stats.attname = pg_attribute.attname
WHERE attrelid = '你的模式名.你的表名'::regclass
  AND attnum > 0
  AND NOT attisdropped
AND null_frac = 1;

SQL Server #

方法1：批量生成统计查询 #

通过sys.columns获取列名，动态生成统计语句：

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = STRING_AGG(
  CONCAT('SUM(CASE WHEN ', QUOTENAME(name), ' IS NOT NULL THEN 1 ELSE 0 END) AS ', QUOTENAME(name + '_non_null')),
  ', '
)
FROM sys.columns
WHERE object_id = OBJECT_ID('你的模式名.你的表名');

SET @sql = CONCAT('SELECT ', @sql, ' FROM 你的模式名.你的表名');
EXEC sp_executesql @sql;

方法2：直接筛选全NULL列 #

SELECT c.name AS column_name
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = '你的表名' 
  AND SCHEMA_NAME(t.schema_id) = '你的模式名'
AND (
  SELECT COUNT(*) 
  FROM 你的模式名.你的表名 
  WHERE c.name IS NOT NULL
) = 0;

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