如何按用户分组提取每组TOP3的GROUP和VALUE并转置为宽表？

算力阿峰

2026-8-3

解决方案（BigQuery）

要实现按用户分组提取每组TOP3的分组及对应值并转换为宽表，可通过窗口函数排名 + 条件聚合的方式完成，具体SQL如下：

SELECT
  user,
  -- 提取第1名的分组和值
  MAX(CASE WHEN rank_num = 1 THEN `GROUP` END) AS first_G,
  MAX(CASE WHEN rank_num = 1 THEN VALUE END) AS Fir_G_val,
  -- 提取第2名的分组和值
  MAX(CASE WHEN rank_num = 2 THEN `GROUP` END) AS second_G,
  MAX(CASE WHEN rank_num = 2 THEN VALUE END) AS sec_G_val,
  -- 提取第3名的分组和值
  MAX(CASE WHEN rank_num = 3 THEN `GROUP` END) AS third_G,
  MAX(CASE WHEN rank_num = 3 THEN VALUE END) AS thi_G_val
FROM (
  -- 子查询：给每个用户的记录按VALUE降序排名
  SELECT 
    user,
    `GROUP`,
    VALUE,
    ROW_NUMBER() OVER(PARTITION BY user ORDER BY VALUE DESC) AS rank_num
  FROM original_table -- 替换成你的实际表名
) ranked_records
GROUP BY user
ORDER BY user;

代码说明：

子查询部分：使用ROW_NUMBER()窗口函数，按user分组，对每组内的记录按VALUE从高到低排序，生成排名rank_num，标记每条记录在用户组内的位次。
外层条件聚合：通过CASE语句配合MAX函数，分别提取每个用户排名为1、2、3的GROUP和VALUE。由于每个用户每个排名仅对应一条记录，MAX函数不会改变结果，同时能自动处理不足3条记录的情况（直接返回NULL）。
注意事项：GROUP是SQL关键字，必须用反引号`包裹，避免语法错误。