如何将BigQuery多字段PIVOT查询封装为可复用自定义表函数

继BigQuery多字段透视问题讨论后，参考Mikhail的建议，计划将类PIVOT功能封装为表函数，示例场景如下：

当前高赞实现查询语句 #

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 (ProductGroup is null and Product is not null)
order by 1, 2, 3, 4 , 5, 6

期望封装后的调用形式 #

封装后的表函数调用语法如下：

PIVOT(
    [row_agg1, row_agg2, ...], 
    [col_agg1, col_agg2, ...], 
    [agg_val1, agg_val2, ...]
)

按该语法，上述查询可简化为：

SELECT
    *
FROM
    PIVOT(
        [Reseller, ProductGroup, Product], -- 行维度
        [Year, Quarter, ProductInfo],      -- 列维度
        [SUM(Revenue), SUM(Units)]         -- 聚合指标
    )

待解决难点 #

• 原始查询中的表数据源应该放在FROM子句的哪个位置？
• 别名机制如何实现？例如若需要将SUM(Revenue)别名设置为TotalRev，在不使用SELECT *的情况下如何在主查询中便捷引用该字段？

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