BigQuery多字段透视查询：如何返回含空值的完整180个单元格结果

解决方案 #

你当前的查询仅返回存在实际业务数据的分组，要补全所有空值单元格，需要先生成全量合法维度组合，再关联聚合结果即可，修改后的查询如下：

WITH valid_row_groups AS (
  -- 生成行维度（Reseller/ProductGroup/Product）所有合法层级分组
  SELECT DISTINCT
    CASE WHEN grp & 1 > 0 THEN Reseller END AS Reseller,
    CASE WHEN grp & 2 > 0 THEN ProductGroup END AS ProductGroup,
    CASE WHEN grp & 4 > 0 THEN Product END AS Product
  FROM `first-outlet-750.biengine_tutorial.Product`,
  UNNEST(GENERATE_ARRAY(1,7)) grp
  WHERE NOT (Reseller IS NULL AND ProductGroup IS NOT NULL)
    AND NOT (ProductGroup IS NULL AND Product IS NOT NULL)
),
valid_col_groups AS (
  -- 生成列维度（Year/Quarter/Product_Info）所有合法层级分组
  SELECT DISTINCT
    CASE WHEN grp & 1 > 0 THEN Year END AS Year,
    CASE WHEN grp & 2 > 0 THEN Quarter END AS Quarter,
    CASE WHEN grp & 4 > 0 THEN Product_Info END AS Product_Info
  FROM `first-outlet-750.biengine_tutorial.Product`,
  UNNEST(GENERATE_ARRAY(1,7)) grp
  WHERE Year IN (2020) AND Quarter IN ('Q1','Q2')
    AND NOT (Year IS NULL AND Quarter IS NOT NULL)
    AND NOT (Quarter IS NULL AND Product_Info IS NOT NULL)
),
all_dimensions AS (
  -- 行列维度笛卡尔积得到完整的90行维度结构，对应180个值单元格
  SELECT * FROM valid_row_groups CROSS JOIN valid_col_groups
),
agg_data AS (
  -- 原有查询的聚合结果，仅返回存在数据的分组
  SELECT 
    (case when grp_set & 1 > 0 then Reseller end) as Reseller,
    (case when grp_set & 2 > 0 then ProductGroup end) as ProductGroup,
    (case when grp_set & 4 > 0 then Product end) as Product,
    (case when grp_set & 8 > 0 then Year end) as Year,
    (case when grp_set & 16 > 0 then Quarter end) as Quarter,
    (case when grp_set & 32 > 0 then Product_Info end) as Product_Info,
    sum(Revenue) as Revenue,
    sum(Units) as Units    
  from `first-outlet-750.biengine_tutorial.Product`, unnest(generate_array(1, 64)) grp_set
  where Year IN (2020) and Quarter in ('Q1', 'Q2')
  group by 1, 2, 3, 4, 5, 6
  having not (Quarter is null and Product_Info is not null)
  and not (Year is null and Quarter is not null)
  and not (Reseller is null and ProductGroup is not null)
  and not (ProductGroup is null and Product is not null)
)
SELECT 
  a.Reseller, a.ProductGroup, a.Product,
  a.Year, a.Quarter, a.Product_Info,
  b.Revenue, b.Units
FROM all_dimensions a
LEFT JOIN agg_data b
USING(Reseller, ProductGroup, Product, Year, Quarter, Product_Info)
ORDER BY 1, 2, 3, 4, 5, 6

核心逻辑说明 #

原有查询的GROUP BY只会生成有实际数据的分组，没有对应业务数据的维度组合不会被输出。我们通过预生成行、列维度的所有合法层级组合，再做笛卡尔积得到完整的90行维度结构，最后左关联聚合结果，无数据的分组的Revenue和Units会自动补为NULL，即可得到包含全部180个单元格的结果。

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