BigQuery如何用COUNTIF统计每位玩家得分最高/最低的对战对手

需求说明 #

针对每位玩家，统计两个核心指标：

• 对阵累计获得积分最高的对手，及对应累计积分
• 对阵累计获得积分最低的对手，及对应累计积分

原始表结构与样例数据 #

对战记录表包含以下字段：

• points_name_one：对局中位置为name_one的玩家获得的积分
• points_name_two：对局中位置为name_two的玩家获得的积分
• name_one：对局中第一位玩家的名称
• name_two：对局中第二位玩家的名称
• concat_name：两位玩家名称拼接字段
  样例数据如下：

points_name_one  |points_name_two |name_one |name_two |concat_name  |   
---------------------------------------------------------------------
1                |  1             |Lou      |Max      |Lou - Max    |    
1                |  1             |Max      |Elie     |Max - Elie   |     
3                |  0             |Elie     |Zoe      |Elie - Zoe   |      
1                |  1             |Max      |Elie     |Max - Elie   |     
1                |  1             |Lou      |Max      |Lou - Max    |    
0                |  3             |Lou      |Max      |Lou - Max    |    
1                |  1             |Julien   |Max      |Julien - Max |         
1                |  1             |Max      |Elie     |Max - Elie   |     
3                |  0             |Elie     |Zoe      |Elie - Zoe   |      
0                |  3             |Zoe      |Lou      |Zoe - Lou    | 
3                |  0             |Lou      |Max      |Lou - Max    |    
1                |  1             |Max      |Elie     |Max - Elie   |   

实现思路 #

最初尝试用COUNTIF+OVER的方案无法得到正确结果，核心原因是COUNTIF为条件计数函数，仅适用于统计符合指定条件的行数场景，和本次需要累计求和、取极值的需求不匹配。
正确实现分三步：

1. 打平双边记录：原表每一行存储了两位玩家的对局积分，需要拆成「当前玩家-对阵对手-本局获得积分」的单行结构，避免漏算任意一方的积分数据
2. 分组聚合：以「玩家+对手」为维度分组，求和得到每个玩家对阵每一位对手的历史累计总积分
3. 窗口排序取极值：按玩家分区，对累计积分分别做升序、降序排名，取排名第一的记录即为积分最低、最高的对阵对手

可直接运行的BigQuery SQL #

WITH
-- 拆分每行双边对战记录为单玩家得分记录
flat_battle_records AS (
  SELECT
    name_one AS player,
    name_two AS opponent,
    points_name_one AS battle_points
  FROM `替换为你的实际对战表名`
  UNION ALL
  SELECT
    name_two AS player,
    name_one AS opponent,
    points_name_two AS battle_points
  FROM `替换为你的实际对战表名`
),
-- 计算每个玩家对阵每个对手的累计总积分
player_opponent_points AS (
  SELECT
    player,
    opponent,
    SUM(battle_points) AS total_points
  FROM flat_battle_records
  GROUP BY player, opponent
),
-- 按玩家分区对累计积分排序
ranked_opponents AS (
  SELECT
    player,
    opponent,
    total_points,
    ROW_NUMBER() OVER (PARTITION BY player ORDER BY total_points DESC, opponent) AS high_rank,
    ROW_NUMBER() OVER (PARTITION BY player ORDER BY total_points ASC, opponent) AS low_rank
  FROM player_opponent_points
)
-- 聚合得到最终结果
SELECT
  player,
  MAX(IF(high_rank = 1, opponent, NULL)) AS highest_points_opponent,
  MAX(IF(high_rank = 1, total_points, NULL)) AS highest_total_points,
  MAX(IF(low_rank = 1, opponent, NULL)) AS lowest_points_opponent,
  MAX(IF(low_rank = 1, total_points, NULL)) AS lowest_total_points
FROM ranked_opponents
GROUP BY player
ORDER BY player;

说明：如果出现同一玩家对阵多个对手累计积分同为最高/最低的情况，上述SQL会按对手名称字典序选取第一个返回；如果需要返回所有同分极值对手，可将ROW_NUMBER替换为ARRAY_AGG按条件聚合即可。

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