如何优化统计divvy_tripdata.all_rides表列空值的SQL查询，提升效率与可读性？

原代码通过多次UNION ALL全表扫描统计各列空值，效率较低且不易维护。以下是两种更高效、可读性更强的优化方案，输出格式为列名+空值数量的多行结果：

方案1：单次扫描+UNPIVOT（推荐，适配BigQuery、PostgreSQL 11+、SQL Server等环境） #

该方案仅扫描表一次，先聚合统计所有列的空值总数，再转换为目标行格式输出：

SELECT
  column_name,
  null_count
FROM (
  SELECT
    -- 为每个需要统计的列添加一行SUM(CASE)逻辑
    SUM(CASE WHEN ride_id IS NULL THEN 1 ELSE 0 END) AS ride_id_nulls,
    SUM(CASE WHEN rideable_type IS NULL THEN 1 ELSE 0 END) AS rideable_type_nulls,
    -- 示例：可继续添加其他列的统计
    SUM(CASE WHEN started_at IS NULL THEN 1 ELSE 0 END) AS started_at_nulls,
    SUM(CASE WHEN ended_at IS NULL THEN 1 ELSE 0 END) AS ended_at_nulls
  FROM divvy_tripdata.all_rides
) AS aggregated_stats
UNPIVOT (
  null_count FOR column_name IN (
    ride_id_nulls AS 'ride_id',
    rideable_type_nulls AS 'rideable_type',
    started_at_nulls AS 'started_at',
    ended_at_nulls AS 'ended_at'
  )
) AS unpivoted_results;

方案2：通用SQL语法（兼容所有SQL环境） #

如果你的SQL环境不支持UNPIVOT，可以用COUNT(*)-COUNT(column)的差值统计空值，相比原代码仍有性能提升（多数优化器会自动合并全表扫描）：

SELECT 'ride_id' AS column_name, COUNT(*) - COUNT(ride_id) AS null_count
FROM divvy_tripdata.all_rides
UNION ALL
SELECT 'rideable_type' AS column_name, COUNT(*) - COUNT(rideable_type) AS null_count
FROM divvy_tripdata.all_rides
UNION ALL
-- 示例：可继续添加其他列的统计
SELECT 'started_at' AS column_name, COUNT(*) - COUNT(started_at) AS null_count
FROM divvy_tripdata.all_rides;

优化亮点 #

• 效率提升：原代码每统计一列就扫描一次全表，优化方案仅需1次全表扫描，数据量越大性能优势越明显。
• 可读性&可维护性：所有列的统计逻辑集中管理，新增列时仅需添加对应行，无需重复编写冗余的SELECT框架。

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