如何使用Prisma或原生SQL查询胜率最高的前10名玩家

Prisma 原生实现方案 #

目前Prisma已经支持单查询完成胜场、总对局的统计，不需要执行N+1查询，一次数据库IO即可拉取所有需要的统计数据：

const playerStats = await prisma.player.findMany({
  select: {
    id: true,
    name: true,
    _count: {
      select: {
        game: { as: "totalGames" },
        game: {
          where: { isWin: true },
          as: "winGames"
        }
      }
    }
  },
  // 过滤无对局的玩家，避免除零错误
  where: {
    game: { some: {} }
  }
})

// 应用侧计算胜率、排序取前10
const top10Players = playerStats
  .map(player => {
    const total = player._count.totalGames
    const win = player._count.winGames
    return {
      ...player,
      winRate: win / total
    }
  })
  .sort((a, b) => b.winRate - a.winRate)
  .slice(0, 10)

该方案适合玩家量级较小的场景，所有统计逻辑在数据库侧完成，仅需一次查询。

最高效原生SQL实现方案 #

如果玩家量级较大，不需要把所有玩家统计数据拉到应用侧处理，可以直接通过原生SQL在数据库层完成计算、排序、截取操作，性能最优：

SELECT 
  p.id,
  p.name,
  COUNT(g.id) AS total_games,
  SUM(CASE WHEN g.is_win = true THEN 1 ELSE 0 END) AS win_games,
  -- 计算胜率，注意类型转换避免整数除法
  SUM(CASE WHEN g.is_win = true THEN 1 ELSE 0 END)::FLOAT / COUNT(g.id) AS win_rate
FROM player p
INNER JOIN game g ON p.id = g.player_id
GROUP BY p.id, p.name
-- 可自行添加对局数过滤条件，避免低对局玩家胜率虚高，例：
-- HAVING COUNT(g.id) >= 10
ORDER BY win_rate DESC
LIMIT 10

在Prisma中可以直接通过$queryRaw执行上述SQL：

const top10Players = await prisma.$queryRaw`
  SELECT 
    p.id,
    p.name,
    COUNT(g.id) AS total_games,
    SUM(CASE WHEN g.is_win = true THEN 1 ELSE 0 END) AS win_games,
    SUM(CASE WHEN g.is_win = true THEN 1 ELSE 0 END)::FLOAT / COUNT(g.id) AS win_rate
  FROM player p
  INNER JOIN game g ON p.id = g.player_id
  GROUP BY p.id, p.name
  ORDER BY win_rate DESC
  LIMIT 10
`

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