google-bigquery - BigQuery 大表数据透视和查找

Question

我必须通过将小表与列名的查找表连接起来，使用大表创建数据透视表，这是我作为一种解决方案所做的：

SELECT bt.bt_id as id,
ANY_VALUE(if(st.name in (select val from MyDataSet.lookuptable where col = 'A'), bt.val,null)) AS  Col1, 
ANY_VALUE(if(st.name in (select val from MyDataSet.lookuptable where col = 'B'), bt.val,null)) AS  Col2, 
ANY_VALUE(if(st.name in (select val from MyDataSet.lookuptable where col = 'C'), bt.val,null)) AS  Col3 

FROM `MyDataSet.BigTable` bt
JOIN `MyDataSet.SmallTable`  st
ON bt.bt_id = st.st_id
GROUP BY bt.Primaryid

这更具可读性。但是很慢。另一种选择是：

SELECT id ,
  coalesce(A,B,C) as Col1,
  coalesce(D,E,F) as Col2,
  coalesce(F,G) as Col3
FROM
(
  SELECT bt.bt_id as id, bt.name , bt.val

 FROM `MyDataSet.BigTable` bt 
  JOIN `MyDataSet.SmallTable`  st
  ON bt.bt_id = st.st_id
)
PIVOT
(
  -- #2 aggregate
  STRING_AGG( val) 
  -- #3 pivot_column
  FOR name  in ('A','B','C','E','E','F','G')  --Around 400 values, any way to do a look up from table here, BigQuery is not letting me to do
)

这里最好的选择是什么？考虑代码的性能和可维护性

标签： google-bigquery