sql - 将 PIVOT 查询转换为表函数

Question

根据天才 Mikhail 的建议，跟进BigQuery 对更多字段的透视，我想知道是否可以将以下“类似透视的功能”转换为表函数。这是一个例子：

当前接受的答案的查询是：

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], -- rows
        [Year, Quarter, ProductInfo],      -- cols
        [SUM(Revenue), SUM(Units)]         -- vals
    )

我认为可能有点棘手的一些事情：

• FROM子句中的实际表在哪里？
• 别名如何工作？例如，如果我们想SUM(Revenue)显示为TotalRev- 如果不只是做 a ，如何轻松地在主子SELECT句中引用它以便能够给它起别名SELECT *？

标签： sqlgoogle-bigquery