BigQuery中为2020年每日生成虚拟列的实现咨询，无需大量CASE WHEN

在BigQuery中可以通过动态SQL方案实现该需求，完全不需要手动编写366条CASE WHEN语句，以下是两种可行的实现方式：

方案一：动态拼接CASE WHEN语句 #

DECLARE column_list STRING;
DECLARE query_str STRING;

-- 自动生成2020年所有日期对应的虚拟列定义
SET column_list = (
  SELECT STRING_AGG(
    "CASE WHEN date = '" || date_str || "' THEN 1 ELSE 0 END AS " || FORMAT_DATE('%Y_%m_%d', date_str)
  )
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-12-31')) AS date_str
);

-- 拼接完整查询语句，替换为你自己的表路径
SET query_str = "SELECT ID, date, " || column_list || " FROM `你的项目名.你的数据集名.你的表名`";

-- 执行动态生成的SQL
EXECUTE IMMEDIATE query_str;

说明：

• GENERATE_DATE_ARRAY会自动生成2020年全年的366个日期，无需手动枚举
• STRING_AGG会自动把每个日期对应的判断逻辑拼接成完整的列定义，列名自动按年_月_日格式生成
• 如需将结果保存为新表，只需把查询语句修改为CREATE TABLE 你的新表路径 AS SELECT ...即可

方案二：使用PIVOT行转列语法（更简洁） #

DECLARE pivot_columns STRING;
DECLARE pivot_query STRING;

-- 生成PIVOT需要的列定义
SET pivot_columns = (
  SELECT STRING_AGG("'" || date_str || "' AS " || FORMAT_DATE('%Y_%m_%d', date_str))
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-12-31')) AS date_str
);

-- 构造PIVOT查询，替换为你自己的表路径
SET pivot_query = """
SELECT * FROM `你的项目名.你的数据集名.你的表名`
PIVOT(COUNT(*) FOR date IN (""" || pivot_columns || """))
""";

EXECUTE IMMEDIATE pivot_query;

说明：

• 该方案用BigQuery原生的PIVOT行转列语法实现，逻辑更简洁，执行性能更优
• 由于你的数据中同一个ID和date组合不会有重复行，COUNT(*)匹配到就是1，没匹配到就是0，刚好符合需求

注意事项 #

• 代码中的表路径需要替换为你自己的表的完整路径
• 如果你的date字段是STRING类型而非DATE类型，需要先做类型转换再进行匹配

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