如何在SQL BigQuery中将日期（周/月年）设为列名统计用户活动数

静态列透视（适合固定时间段） #

如果已经明确需要统计的周数，可通过CASE WHEN配合COUNT实现行列转换，直接得到预期的列结构：

SELECT
  userid,
  COUNT(CASE WHEN CONCAT(EXTRACT(WEEK FROM activityDate), '-', EXTRACT(YEAR FROM activityDate)) = '31-2022' THEN activityId END) AS `31-2022`,
  COUNT(CASE WHEN CONCAT(EXTRACT(WEEK FROM activityDate), '-', EXTRACT(YEAR FROM activityDate)) = '32-2022' THEN activityId END) AS `32-2022`,
  COUNT(CASE WHEN CONCAT(EXTRACT(WEEK FROM activityDate), '-', EXTRACT(YEAR FROM activityDate)) = '33-2022' THEN activityId END) AS `33-2022`
FROM
  `你的项目ID.你的数据集.你的表名`
WHERE
  activityStatus = 'finished'
GROUP BY
  userid
ORDER BY
  userid

注：用反引号包裹列名避免特殊字符-引发语法错误；COUNT会自动忽略NULL值，未匹配的周会返回0。

动态列透视（自动化适配所有周） #

如果需要自动化适配数据中所有存在的周（无需手动指定列名），可借助BigQuery的EXECUTE IMMEDIATE动态生成SQL：

DECLARE week_columns STRING;

-- 1. 获取所有唯一的周格式字符串，拼接成CASE WHEN语句片段
SET week_columns = (
  SELECT STRING_AGG(
    FORMAT("COUNT(CASE WHEN week_year = '%s' THEN activityId END) AS `%s`", week_year, week_year)
  )
  FROM (
    SELECT DISTINCT CONCAT(EXTRACT(WEEK FROM activityDate), '-', EXTRACT(YEAR FROM activityDate)) AS week_year
    FROM `你的项目ID.你的数据集.你的表名`
    WHERE activityStatus = 'finished'
    ORDER BY week_year
  )
);

-- 2. 执行动态生成的透视SQL
EXECUTE IMMEDIATE FORMAT("""
  SELECT
    userid,
    %s
  FROM (
    SELECT
      userid,
      activityId,
      CONCAT(EXTRACT(WEEK FROM activityDate), '-', EXTRACT(YEAR FROM activityDate)) AS week_year
    FROM `你的项目ID.你的数据集.你的表名`
    WHERE activityStatus = 'finished'
  )
  GROUP BY userid
  ORDER BY userid
""", week_columns);

该方案会自动扫描数据中所有已完成活动对应的周，生成匹配的列，后续新增周数据时无需修改代码，完全自动化适配。

扩展到月维度 #

如果需要按monthYear（比如2022-08）透视，只需将周的提取逻辑替换为月份格式：

• 原周格式：CONCAT(EXTRACT(WEEK FROM activityDate), '-', EXTRACT(YEAR FROM activityDate))
• 修改为月格式：CONCAT(EXTRACT(YEAR FROM activityDate), '-', FORMAT('%02d', EXTRACT(MONTH FROM activityDate)))

内容的提问来源于stack exchange，提问作者Murilo Barbosa