如何在BigQuery中将多列聚合为数组列并处理空值？

需求说明 #

需要将原表中OrganizationUnitID、OrganizationUnitLevel1、OrganizationUnitLevel2、OrganizationUnitLevel3四列的非NULL值提取合并为单列OrganizationUnitID，仅在第一行保留OrganizationUnitSetID和OrganizationUnitSetName，其余行这两列为空，同时去除重复的组织单元ID。

解决方案SQL #

CREATE OR REPLACE VIEW `your-project.your-dataset.target_view` AS
WITH org_unit_list AS (
  -- 提取所有非空的组织单元ID并去重
  SELECT DISTINCT
    OrganizationUnitSetID,
    OrganizationUnitSetName,
    org_id AS OrganizationUnitID
  FROM
    `your-project.your-dataset.source_table`,
    -- 将四列转为行，自动展开数组
    UNNEST([
      OrganizationUnitID,
      OrganizationUnitLevel1,
      OrganizationUnitLevel2,
      OrganizationUnitLevel3
    ]) AS org_id
  WHERE org_id IS NOT NULL
),
ranked_units AS (
  -- 给同组内的ID排序，让原表的OrganizationUnitID排在首位
  SELECT
    *,
    ROW_NUMBER() OVER(
      PARTITION BY OrganizationUnitSetID, OrganizationUnitSetName 
      ORDER BY CASE WHEN OrganizationUnitID = org_id THEN 1 ELSE 2 END
    ) AS row_rank
  FROM org_unit_list
)
-- 仅第一行保留SetID和SetName，其余行置空
SELECT
  IF(row_rank = 1, OrganizationUnitSetID, NULL) AS OrganizationUnitSetID,
  IF(row_rank = 1, OrganizationUnitSetName, NULL) AS OrganizationUnitSetName,
  OrganizationUnitID
FROM ranked_units
ORDER BY row_rank, OrganizationUnitID;

代码解释 #

1. org_unit_list CTE：
   • 使用UNNEST将四列组织单元ID转为行数据，同时过滤掉NULL值
   • 通过DISTINCT去除重复的组织单元ID（比如原表中重复出现的47、48等）
2. ranked_units CTE：
   • 按OrganizationUnitSetID和OrganizationUnitSetName分组，对组内的ID排序，让原表的OrganizationUnitID排在第一位置
3. 最终查询：
   • 仅在排序后的第一行保留OrganizationUnitSetID和OrganizationUnitSetName，其余行这两列设为NULL
   • 按排序和ID顺序输出结果，匹配目标视图的结构

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