如何在BigQuery（BQ）中忽略列空值 按项目规整多角色列数据

可直接运行的SQL代码 #

WITH role_values AS (
  -- 处理Role1非空值，按项目分组分配行号
  SELECT 
    Project_Name,
    'Role1' AS role_type,
    Role1 AS role_val,
    ROW_NUMBER() OVER(PARTITION BY Project_Name ORDER BY Role1) AS rn
  FROM `你的项目ID.你的数据集名.你的原表名`
  WHERE Role1 IS NOT NULL
  UNION ALL
  -- 处理Role2非空值，按项目分组分配行号
  SELECT 
    Project_Name,
    'Role2' AS role_type,
    Role2 AS role_val,
    ROW_NUMBER() OVER(PARTITION BY Project_Name ORDER BY Role2) AS rn
  FROM `你的项目ID.你的数据集名.你的原表名`
  WHERE Role2 IS NOT NULL
  UNION ALL
  -- 处理Role3非空值，按项目分组分配行号，剩余12个角色按照相同格式补充UNION ALL块即可
  SELECT 
    Project_Name,
    'Role3' AS role_type,
    Role3 AS role_val,
    ROW_NUMBER() OVER(PARTITION BY Project_Name ORDER BY Role3) AS rn
  FROM `你的项目ID.你的数据集名.你的原表名`
  WHERE Role3 IS NOT NULL
),
-- 计算每个项目需要输出的总行数（即该项目下所有角色的最大有效条数）
project_max_rn AS (
  SELECT 
    Project_Name,
    MAX(rn) AS max_rn
  FROM role_values
  GROUP BY Project_Name
),
-- 为每个项目生成1到总行数的连续行号序列，用于对齐各角色取值
project_rn_series AS (
  SELECT 
    p.Project_Name,
    s.rn
  FROM project_max_rn p,
  UNNEST(GENERATE_ARRAY(1, p.max_rn)) AS rn
)
-- 行转列得到最终规整结果
SELECT 
  Project_Name,
  MAX(IF(role_type = 'Role1', role_val, NULL)) AS Role1,
  MAX(IF(role_type = 'Role2', role_val, NULL)) AS Role2,
  MAX(IF(role_type = 'Role3', role_val, NULL)) AS Role3
FROM (
  SELECT s.Project_Name, s.rn, r.role_type, r.role_val
  FROM project_rn_series s
  LEFT JOIN role_values r 
    ON s.Project_Name = r.Project_Name AND s.rn = r.rn
)
GROUP BY Project_Name, rn
ORDER BY Project_Name, rn

逻辑说明 #

1. 先将各角色列的非空值拆分为纵向结构，同时为每个项目下的同角色有效值按顺序标记行号，自动过滤前置空值
2. 统计每个项目下所有角色的最大有效条数，作为该项目最终输出的行数
3. 为每个项目生成连续行号序列，和各角色的带行号有效值左关联，没有对应有效值的位置自动补空
4. 最后通过行转列将数据还原为角色列横向排布的格式，刚好满足有效值按顺序排列、末尾补空的需求

扩展说明 #

• 你有15个角色列的话，只要按照Role1的格式补充role_values中的UNION ALL块，同时在最后的SELECT部分补充对应角色的MAX(IF(...))语句即可
• 若你需要调整有效值的排列顺序，修改ROW_NUMBER()中的ORDER BY条件即可，比如可以按用户ID、入职时间等字段排序
• 如果原表中空白值是空字符串而非NULL，把WHERE条件中的IS NOT NULL改成!= ''即可

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