BigQuery：按用户维度计算红、蓝、绿加权平均的实现求助

首先，我先明确下需求：你需要为每个user_id生成唯一一行记录，分别计算红（red）、蓝（blue）、绿（green）三个颜色的加权平均值，公式是对应颜色的rating值除以该用户的总rating值。下面我给你两种可行的BigQuery实现方案，都是基于常见的表结构来写的（假设你的表包含user_id、colour、rating这三列）。

方法一：用CASE表达式+窗口函数实现 #

这个方法兼容性强，适合所有SQL环境，逻辑也很清晰：

• 先计算每个用户的总rating值；
• 按用户分组，用CASE表达式分别计算每个颜色的加权值。

WITH user_rating_totals AS (
  SELECT
    user_id,
    colour,
    rating,
    -- 用窗口函数计算当前用户的所有rating总和
    SUM(rating) OVER (PARTITION BY user_id) AS total_user_rating
  FROM `your_project.your_dataset.your_table`
)
SELECT
  user_id,
  -- 计算红色的加权平均，没有记录则返回0
  SUM(CASE WHEN colour = 'red' THEN rating / total_user_rating ELSE 0 END) AS red_weighted_avg,
  SUM(CASE WHEN colour = 'blue' THEN rating / total_user_rating ELSE 0 END) AS blue_weighted_avg,
  SUM(CASE WHEN colour = 'green' THEN rating / total_user_rating ELSE 0 END) AS green_weighted_avg
FROM user_rating_totals
GROUP BY user_id
ORDER BY user_id;

方法二：用BigQuery专属的PIVOT语法简化代码 #

BigQuery支持PIVOT操作，可以更简洁地把行转列，适合这种需要按类别拆分列的场景：

WITH weighted_colour_values AS (
  SELECT
    user_id,
    colour,
    -- 先算出单个颜色的加权值
    rating / SUM(rating) OVER (PARTITION BY user_id) AS weighted_value
  FROM `your_project.your_dataset.your_table`
)
SELECT
  user_id,
  -- 把NULL转为0，避免缺失颜色时显示空值
  IFNULL(red_weighted_avg, 0) AS red_weighted_avg,
  IFNULL(blue_weighted_avg, 0) AS blue_weighted_avg,
  IFNULL(green_weighted_avg, 0) AS green_weighted_avg
FROM weighted_colour_values
PIVOT (
  -- 聚合同一用户同一颜色的加权值（如果有多条同颜色记录会自动求和）
  SUM(weighted_value) AS weighted_avg
  -- 指定要转成列的颜色值
  FOR colour IN ('red', 'blue', 'green')
)
ORDER BY user_id;

关键细节说明 #

• 如果你的“a或b的rating总和”不是指用户的总rating，而是某个其他分组（比如用户所属的a/b组），只需要把窗口函数里的PARTITION BY user_id改成PARTITION BY user_id, your_group_column即可；
• 如果同一个用户同一颜色有多条记录，上面的代码会自动把这些记录的加权值求和，完全符合加权平均的逻辑；
• 记得把代码里的your_project.your_dataset.your_table替换成你实际的表路径。

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