PostgreSQL中实现游戏各关卡完成玩家累计统计的SQL方法

嘿，这个需求其实很好解决，PostgreSQL的窗口函数或者自连接都能搞定，我给你两种实用的方案，你可以根据自己的场景选择：

方案一：基于现有查询+窗口函数（推荐） #

这个方案是在你已经写好的查询基础上，加上窗口函数来计算累计值，非常简洁高效：

SELECT 
  level,
  count_per_level,
  SUM(count_per_level) OVER (ORDER BY level DESC) AS cumulative_players
FROM (
  -- 你的原查询：获取每个最高关卡的玩家数
  SELECT 
    x1.level, 
    count(*) AS count_per_level
  FROM ( 
    SELECT player_id, max(player_level) as level 
    FROM session_end 
    GROUP BY player_id 
  ) x1 
  GROUP BY level
) x2
ORDER BY level;

解释： #

• 内层的x2子查询就是你原来得到的结果：每个最高关卡对应的玩家数量。
• 外层用SUM(count_per_level) OVER (ORDER BY level DESC)这个窗口函数，按关卡数从高到低排序，计算从当前行到最顶部的累计和。这样：
  • 最高关卡的累计数就是它自己的玩家数（因为没有更高的关卡了）
  • 关卡n的累计数 = 关卡n的玩家数 + 关卡n+1的玩家数 + ... + 最高关卡的玩家数
    正好符合你要的逻辑——完成关卡2的玩家必然完成了关卡1，所以关卡1的累计数包含所有最高关卡≥1的玩家。

方案二：自连接实现（适合不熟悉窗口函数的场景） #

如果对窗口函数不太熟悉，用自连接的方式也能达到同样的效果：

SELECT 
  t1.level,
  COUNT(DISTINCT t2.player_id) AS cumulative_players
FROM (
  -- 获取所有存在的关卡
  SELECT DISTINCT player_level AS level
  FROM session_end
) t1
JOIN (
  -- 获取每个玩家的最高关卡
  SELECT player_id, max(player_level) AS max_level
  FROM session_end
  GROUP BY player_id
) t2 ON t2.max_level >= t1.level
GROUP BY t1.level
ORDER BY t1.level;

解释： #

• t1先提取出所有出现过的关卡值。
• t2得到每个玩家的最高关卡。
• 通过JOIN关联所有最高关卡≥当前关卡的玩家，最后统计去重后的玩家数，就是该关卡的累计完成人数。

额外补充：显示所有连续关卡（包括无人达到的关卡） #

如果你的游戏里有连续的关卡，比如1到10，但有些关卡暂时没有玩家达到，上面的方案不会显示这些关卡。如果需要显示所有连续关卡，可以用generate_series生成连续的关卡序列：

WITH max_level AS (
  -- 获取游戏中的最高关卡
  SELECT max(player_level) AS max_lvl FROM session_end
),
all_levels AS (
  -- 生成从1到最高关卡的连续序列
  SELECT generate_series(1, (SELECT max_lvl FROM max_level)) AS level
),
player_max_levels AS (
  -- 每个玩家的最高关卡
  SELECT player_id, max(player_level) AS level 
  FROM session_end 
  GROUP BY player_id
),
level_counts AS (
  -- 每个最高关卡的玩家数
  SELECT level, count(*) AS count_per_level
  FROM player_max_levels
  GROUP BY level
)
SELECT 
  al.level,
  -- 用COALESCE处理无人达到的关卡，累计数设为0
  COALESCE(SUM(lc.count_per_level) OVER (ORDER BY al.level DESC), 0) AS cumulative_players
FROM all_levels al
LEFT JOIN level_counts lc ON al.level = lc.level
ORDER BY al.level;

这样哪怕某个关卡没有玩家达到，也会显示出来，累计数也会正确计算（比如关卡3没人到，它的累计数等于关卡4及以上的玩家数之和）。

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