如何用数据库查询替代Ruby代码实现主挑战的聚合成绩排行？

要替代Ruby代码实现主挑战的成绩聚合排行，我们可以用纯SQL完成所有计算，利用数据库的聚合和窗口函数能力提升性能，具体语句如下：

WITH client_subchallenge_best AS (
  -- 提取每个客户端在每个子挑战中的最佳成绩
  SELECT
    a.client_id,
    a.challenge_id,
    MAX(a.分数) AS best_score
  FROM achievements a
  JOIN challenges c ON a.challenge_id = c.id
  WHERE c.主挑战ID = 2 -- 这里替换为目标主挑战的ID
  GROUP BY a.client_id, a.challenge_id
),
client_total_scores AS (
  -- 按客户端累加所有子挑战的最佳成绩总和
  SELECT
    client_id,
    SUM(best_score) AS 总米数
  FROM client_subchallenge_best
  GROUP BY client_id
)
-- 计算排名并输出最终结果
SELECT
  client_id AS 客户端ID,
  总米数,
  RANK() OVER (ORDER BY 总米数 DESC) AS 排名
FROM client_total_scores
ORDER BY 排名;

逻辑说明： #

• 第一步（client_subchallenge_best）：关联achievements和challenges表，筛选出属于目标主挑战的所有子挑战记录，按客户端ID和子挑战ID分组，取每组的最高分数（即该客户端在对应子挑战的最佳成绩）。
• 第二步（client_total_scores）：将同一客户端的所有子挑战最佳成绩求和，得到该客户端在主挑战中的总分数。
• 第三步：使用RANK()窗口函数按总分数降序计算排名，最终输出符合要求的结果。

Rails ActiveRecord适配写法 #

如果要在Rails中用ActiveRecord实现（保持纯数据库查询），可以这样写：

# 替换主挑战ID为实际值
main_challenge_id = 2

# 第一步：获取每个客户端在子挑战的最佳成绩
subchallenge_bests = Achievement.joins(:challenge)
                                .where(challenges: { 主挑战ID: main_challenge_id })
                                .group(:client_id, :challenge_id)
                                .select('client_id, challenge_id, MAX(分数) AS best_score')

# 第二步：累加每个客户端的总分数并计算排名
ranked_results = subchallenge_bests.group(:client_id)
                                   .select(
                                     'client_id AS 客户端ID',
                                     'SUM(best_score) AS 总米数',
                                     'RANK() OVER (ORDER BY SUM(best_score) DESC) AS 排名'
                                   )
                                   .order('排名')

# 执行查询获取结果
ranked_results.to_a

这种纯数据库查询的方式，相比Ruby代码先拉取所有数据再处理，能大幅减少内存占用和数据传输量，在数据量较大时性能提升尤为明显。

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