BigQuery中如何将每日追加的用户分数表转换为行列双向扩展形式？

这是个很常见的用户行为数据宽表转换需求，用**窗口函数+透视（PIVOT）**就能完美解决，比你之前尝试的多次Left Join灵活得多——不仅能覆盖所有用户（包括后期新增的），还能自动按时间顺序排列每个用户的分数。我来一步步给你拆解实现方法：

第一步：确保数据带时间维度 #

你提到可以给表添加Date列，这一步非常关键！因为我们需要按时间顺序给每个用户的分数排序，确定哪个是score1（最早）、哪个是score2（次早）。

如果你的score_FULL是从每日独立表（比如score_20180125）追加而来，在追加时就可以直接带上日期：

-- 示例：将每日表数据追加到score_FULL并带上日期
INSERT INTO `your_project.your_dataset.score_FULL` (visitorID, score, date)
SELECT
  visitorID,
  score,
  PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX, r'(\d{8})')) AS date
FROM
  `your_project.your_dataset.score_20180125` -- 替换为当日表名

如果已经有score_FULL但没有Date列，也可以回溯补全（假设每日表还存在）：

CREATE OR REPLACE TABLE `your_project.your_dataset.score_FULL` AS
SELECT
  visitorID,
  score,
  PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_TABLE_SUFFIX, r'(\d{8})')) AS date
FROM
  `your_project.your_dataset.score_*`
WHERE
  _TABLE_SUFFIX REGEXP r'\d{8}' -- 只匹配日期格式的表后缀

第二步：给每个用户的分数按时间排序编号 #

用ROW_NUMBER()窗口函数，按用户分组、按日期排序，给每个分数分配一个序号（score_num），序号1对应最早的分数，序号2对应下一次的分数，以此类推：

WITH ranked_scores AS (
  SELECT
    visitorID,
    score,
    ROW_NUMBER() OVER (PARTITION BY visitorID ORDER BY date) AS score_num
  FROM
    `your_project.your_dataset.score_FULL`
)

第三步：透视转换为横向宽表 #

接下来用条件聚合或者BigQuery的PIVOT语法，把每个用户的多行分数转成横向的列：

方法1：条件聚合（灵活扩展，推荐） #

这种方式可以自由扩展scoreN列，不管用户有多少条分数记录：

WITH ranked_scores AS (
  SELECT
    visitorID,
    score,
    ROW_NUMBER() OVER (PARTITION BY visitorID ORDER BY date) AS score_num
  FROM
    `your_project.your_dataset.score_FULL`
)
SELECT
  visitorID,
  MAX(IF(score_num = 1, score, NULL)) AS score1,
  MAX(IF(score_num = 2, score, NULL)) AS score2,
  MAX(IF(score_num = 3, score, NULL)) AS score3,
  -- 按需继续添加score4、score5...
FROM
  ranked_scores
GROUP BY
  visitorID
ORDER BY
  visitorID

方法2：BigQuery原生PIVOT语法（更简洁） #

如果能确定最大的分数次数，用PIVOT语法更清爽：

WITH ranked_scores AS (
  SELECT
    visitorID,
    score,
    CONCAT('score', ROW_NUMBER() OVER (PARTITION BY visitorID ORDER BY date)) AS score_col
  FROM
    `your_project.your_dataset.score_FULL`
)
SELECT
  *
FROM
  ranked_scores
PIVOT (
  MAX(score) FOR score_col IN ('score1', 'score2', 'score3') -- 列出所有需要的列
)
ORDER BY
  visitorID

为什么比Left Join更好？ #

你之前用多次Left Join的问题在于：起始表的用户范围是固定的，后期新增的用户（比如例子中的7、10）会被漏掉。而窗口函数+透视的方式是先收集所有用户的所有分数，再统一转换，不管用户第一次出现的时间，自然实现了纵向扩展；同时按时间编号的分数会自动横向排列，新的分数只需要扩展对应的scoreN列即可。

扩展提示 #

如果不确定用户最多有多少条分数记录，可以先查一下最大值，再对应扩展列：

-- 查询每个用户的最大分数次数
SELECT
  MAX(score_count) AS max_score_times
FROM (
  SELECT
    visitorID,
    COUNT(*) AS score_count
  FROM
    `your_project.your_dataset.score_FULL`
  GROUP BY
    visitorID
)

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