如何使用BigQuery将7个季度数据从行转置为列？

当然可以用BigQuery实现行转列，下面针对你的需求给出具体方案：

1. 修正你提供的SQL代码 #

你原来的SQL存在语法错误（THEN AT END是无效写法，需指定要聚合的数值字段，比如业务中的金额、数量等），同时要覆盖7个季度的话，需要补充对应的CASE语句。假设你要聚合的字段为Amount，修正后的代码如下：

SELECT 
  Name,
  SUM(CASE WHEN QuarterEnding = 'Q1, 2020' THEN Amount ELSE 0 END) AS Q12020,
  SUM(CASE WHEN QuarterEnding = 'Q2, 2020' THEN Amount ELSE 0 END) AS Q22020,
  SUM(CASE WHEN QuarterEnding = 'Q3, 2020' THEN Amount ELSE 0 END) AS Q32020,
  SUM(CASE WHEN QuarterEnding = 'Q4, 2020' THEN Amount ELSE 0 END) AS Q42020,
  SUM(CASE WHEN QuarterEnding = 'Q1, 2021' THEN Amount ELSE 0 END) AS Q12021,
  SUM(CASE WHEN QuarterEnding = 'Q2, 2021' THEN Amount ELSE 0 END) AS Q22021,
  SUM(CASE WHEN QuarterEnding = 'Q3, 2021' THEN Amount ELSE 0 END) AS Q32021
FROM `table1`
GROUP BY Name;

2. OFFSET子句的使用思路 #

你尝试的OFFSET子句，通常结合窗口函数ARRAY_AGG来实现行转列，但这种方式要求每个Name对应的季度数量、排序完全一致，否则会出现数据错位，适合固定顺序的场景。示例代码如下：

SELECT 
  Name,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(0)] AS Q12020,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(1)] AS Q22020,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(2)] AS Q32020,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(3)] AS Q42020,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(4)] AS Q12021,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(5)] AS Q22021,
  ARRAY_AGG(Amount ORDER BY QuarterEnding)[OFFSET(6)] AS Q32021
FROM `table1`
GROUP BY Name;

注意：执行前要确认ORDER BY QuarterEnding的排序结果和你的季度顺序完全匹配，避免数据对应错误。

3. 更简洁的PIVOT函数写法 #

BigQuery支持PIVOT函数，专门用于行转列场景，写法更简洁直观，适合固定维度的转换：

SELECT *
FROM `table1`
PIVOT (
  SUM(Amount) FOR QuarterEnding IN (
    'Q1, 2020', 'Q2, 2020', 'Q3, 2020', 'Q4, 2020',
    'Q1, 2021', 'Q2, 2021', 'Q3, 2021'
  )
);

执行后会自动将指定的季度值转为列名，聚合结果填充对应单元格。

内容的提问来源于stack exchange，提问作者mp.kaur