BigQuery入门求助：查询仅返回非空列的实现方法

嗨，别担心，初学者遇到这种需求太正常啦！咱们先理清楚核心问题：SQL的查询结果列数是在编译时就确定好的，没法直接根据每行数据的非空情况动态增减列。不过有两种思路可以实现你要的效果，咱们一个个来看：

方案一：静态查询（适合Name数量少的场景） #

如果你的表中只有少数几个固定的Name（比如Ben和Bob），最直接的方式就是针对每个Name单独写查询语句，只选择该Name对应行中非空的列。

比如查询Bob的话：

SELECT
  Name,
  Value1,
  Value3,
  Value5,
  Value6
FROM
  `your-project.your_dataset.your_table` -- 替换成你的项目、数据集和表名
WHERE
  Name = 'Bob'

查询Ben的话：

SELECT
  Name,
  Value1,
  Value2,
  Value4,
  Value5
FROM
  `your-project.your_dataset.your_table`
WHERE
  Name = 'Ben'

这种方法简单易懂，不需要复杂的语法，适合Name不多的情况。

方案二：动态SQL（适合Name数量多的场景） #

如果你的表中有很多不同的Name，一个个写静态查询太麻烦，那就可以用BigQuery支持的动态SQL来自动生成只包含非空列的查询语句。

下面是完整的代码示例，记得替换里面的项目、数据集和表名：

-- 第一步：设置要查询的目标Name
DECLARE target_name STRING DEFAULT 'Bob'; -- 这里可以换成你要查的任意Name
DECLARE column_list STRING;

-- 第二步：获取该Name对应的所有非空Value列名
SET column_list = (
  SELECT STRING_AGG(column_name, ', ')
  FROM (
    -- 先获取表中所有以Value开头的列
    SELECT column_name
    FROM `your-project.your_dataset.your_table`.INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'your_table' AND column_name LIKE 'Value%'
  ) all_value_cols
  JOIN (
    -- 筛选出目标Name行中所有非空的列
    SELECT
      CASE WHEN Value1 IS NOT NULL THEN 'Value1' END AS col1,
      CASE WHEN Value2 IS NOT NULL THEN 'Value2' END AS col2,
      CASE WHEN Value3 IS NOT NULL THEN 'Value3' END AS col3,
      CASE WHEN Value4 IS NOT NULL THEN 'Value4' END AS col4,
      CASE WHEN Value5 IS NOT NULL THEN 'Value5' END AS col5,
      CASE WHEN Value6 IS NOT NULL THEN 'Value6' END AS col6
    FROM `your-project.your_dataset.your_table`
    WHERE Name = target_name
  ) non_null_cols
  ON all_value_cols.column_name IN (non_null_cols.col1, non_null_cols.col2, non_null_cols.col3, non_null_cols.col4, non_null_cols.col5, non_null_cols.col6)
);

-- 第三步：拼接并执行动态查询语句
EXECUTE IMMEDIATE FORMAT("""
  SELECT Name, %s
  FROM `your-project.your_dataset.your_table`
  WHERE Name = '%s'
""", column_list, target_name);

代码解释： #

1. 先定义要查询的target_name，你可以随时修改这个值来查询不同的Name；
2. 通过INFORMATION_SCHEMA.COLUMNS获取表中所有以Value开头的列，再和目标Name的行关联，筛选出那些非空的列名，用STRING_AGG把它们拼成逗号分隔的字符串；
3. 最后用EXECUTE IMMEDIATE执行动态生成的SELECT语句，这样返回的结果就只会包含非空的列啦。

如果以后你的表中新增了Value7、Value8这类列，只需要在第二步的CASE语句里对应新增一行就行，扩展性很强。

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