google-bigquery - 如何使用 SPLIT 将行转置为列？

Question

我有一个名为“CampaignName”的 STRING 列，其中包含以下格式：“PRODUCT_PHASE_OBJECTIVE_BRAND_(21/01 - 31/01)”。

我想将所有行的每一部分转换为列。

使用这种结构：

1. 产品
2. 阶段
3. 客观的
4. 牌
5. 日期

如果我只提取一个部分，产品，我可以运行这个查询

SELECT
  cs.Date,
  c.ExternalCustomerId,
  c.CampaignName,
  SPLIT(c.CampaignName, '_')[OFFSET(0)] part1,
  SUM(cs.Impressions) AS Impressions,
  SUM(cs.Interactions) AS Interactions,
  (SUM(cs.Cost) / 1000000) AS Cost
FROM
  `XXX-266809.zzz.Campaign_3867357697` c
LEFT JOIN
  `XXX-266809.zzz.CampaignBasicStats_3867357697` cs
ON
  (c.CampaignId = cs.CampaignId
   AND cs._DATA_DATE BETWEEN
   DATE_ADD(CURRENT_DATE(), INTERVAL -365 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3, 4
ORDER BY
  cs. Date DESC

但是如果我尝试转置到两列，我的查询将失败......

SELECT
  cs.Date,
  c.ExternalCustomerId,
  c.CampaignName,
  SPLIT(c.CampaignName, '_')[OFFSET(0)] part1,
  SPLIT(c.CampaignName, '_')[OFFSET(1)] part12,
  SUM(cs.Impressions) AS Impressions,
  SUM(cs.Interactions) AS Interactions,
  (SUM(cs.Cost) / 1000000) AS Cost
FROM
  `XXX-266809.zzz.Campaign_3867357697` c
LEFT JOIN
  `XXX-266809.zzz.CampaignBasicStats_3867357697` cs
ON
  (c.CampaignId = cs.CampaignId
   AND cs._DATA_DATE BETWEEN
   DATE_ADD(CURRENT_DATE(), INTERVAL -365 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
WHERE
  c._DATA_DATE = c._LATEST_DATE
GROUP BY
  1, 2, 3, 4
ORDER BY
  cs. Date DESC

如何创建良好的结构并转置“CampaignName”中的数据？

谢谢！

标签： google-bigquery