如何在BigQuery中对Struct类型执行Unpivot操作？

报错原因 #

你执行UNPIVOT时直接引用的Last_7_Days等字段属于嵌套struct的子字段，并非表的顶层字段，SQL解析时无法直接识别到这些子字段名称，因此触发报错。

解决方案 #

方案1：先提取struct子字段为顶层列再UNPIVOT #

先通过子查询将struct内部的所有需要转换的子字段提取为顶层列，再执行UNPIVOT操作，语法如下：

SELECT 
  UA.ID, -- 可在此处添加你需要保留的其余非struct字段
  UA.struct字段名.Last_7_Days,
  UA.struct字段名.Last_14_Days,
  UA.struct字段名.Last_30_Days,
  UA.struct字段名.Last_90_Days,
  UA.struct字段名.First_Date_AEST,
  UA.struct字段名.Last_Date_AEST
FROM `business-analytics-workbench.RAW.User_Activity` as UA
UNPIVOT(
  Activity FOR PERIOD IN (
    Last_7_Days,
    Last_14_Days,
    Last_30_Days,
    Last_90_Days,
    First_Date_AEST,
    Last_Date_AEST
  )
)

注意：将上述代码中的struct字段名替换为你表中存储周期指标的struct类型字段的实际名称。

方案2：使用UNNEST直接拆分struct生成行 #

无需提前展开字段，通过UNNEST函数直接构造行数据，代码更简洁：

SELECT 
  UA.ID, -- 可在此处添加你需要保留的其余非struct字段
  PERIOD,
  Activity
FROM `business-analytics-workbench.RAW.User_Activity` as UA,
UNNEST([
  STRUCT("Last_7_Days" AS PERIOD, UA.struct字段名.Last_7_Days AS Activity),
  STRUCT("Last_14_Days" AS PERIOD, UA.struct字段名.Last_14_Days AS Activity),
  STRUCT("Last_30_Days" AS PERIOD, UA.struct字段名.Last_30_Days AS Activity),
  STRUCT("Last_90_Days" AS PERIOD, UA.struct字段名.Last_90_Days AS Activity),
  STRUCT("First_Date_AEST" AS PERIOD, UA.struct字段名.First_Date_AEST AS Activity),
  STRUCT("Last_Date_AEST" AS PERIOD, UA.struct字段名.Last_Date_AEST AS Activity)
])

注意：同样需要将struct字段名替换为你表中对应struct字段的实际名称。

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