如何在Node.js中创建排序映射实现PostgreSQL查询数值的降序排名

你可以根据自己的业务规模选择以下两种实现方式，优先推荐数据库侧计算排名，性能更优。

方案1：PostgreSQL 侧直接计算排名 #

你可以直接用 PostgreSQL 内置的窗口函数一步算出排名，不需要在应用层额外处理：

获取整个服务器所有用户的排名 #

SELECT 
  user_id,
  total_xp,
  ROW_NUMBER() OVER (ORDER BY total_xp DESC) AS rank
FROM levels 
WHERE guild_id = $1
ORDER BY rank ASC

其中窗口函数可以根据你的需求替换：

• ROW_NUMBER()：严格按顺序生成排名，即使两个用户 total_xp 相同也会分配不同排名
• RANK()：同分用户排名相同，后续排名跳号（比如两个第1名，下一名就是第3名）
• DENSE_RANK()：同分用户排名相同，后续排名不跳号（比如两个第1名，下一名还是第2名）

仅查询指定用户的排名 #

如果你只需要获取单个用户在当前服务器的排名，不需要拉取全量数据，可以用以下查询：

WITH guild_ranks AS (
  SELECT 
    user_id,
    ROW_NUMBER() OVER (ORDER BY total_xp DESC) AS rank
  FROM levels 
  WHERE guild_id = $1
)
SELECT rank, total_xp 
FROM guild_ranks
JOIN levels ON levels.user_id = guild_ranks.user_id AND levels.guild_id = $1
WHERE guild_ranks.user_id = $2

调用的时候直接传入 [message.guild.id, member.id] 即可，直接返回目标用户的排名和对应 total_xp。

方案2：Node.js 侧排序生成排名 #

如果你的数据量很小，也可以把全量数据拉到 Node.js 侧处理：

// 1. 拉取当前服务器所有用户的经验值数据
const { rows } = await pool.query(
  'SELECT user_id, total_xp FROM levels WHERE guild_id = $1',
  [message.guild.id]
)

// 2. 按经验值从大到小排序
const sortedRows = rows.sort((a, b) => b.total_xp - a.total_xp)

// 3. 生成带排名的列表和映射表，方便快速查询指定用户排名
const rankedMap = new Map()
const rankedList = sortedRows.map((row, index) => {
  const rankInfo = {
    rank: `#${index + 1}`,
    userId: row.user_id,
    totalXp: row.total_xp
  }
  rankedMap.set(row.user_id, rankInfo)
  return rankInfo
})

// 4. 查询目标用户的排名
const memberRank = rankedMap.get(member.id)

如果需要实现同分同排名的逻辑，可以把第三步的代码替换为以下实现：

const rankedMap = new Map()
const rankedList = []
let currentRank = 1
for (let i = 0; i < sortedRows.length; i++) {
  // 和上一名经验值不同时更新当前排名
  if (i > 0 && sortedRows[i].total_xp !== sortedRows[i-1].total_xp) {
    currentRank = i + 1
  }
  const rankInfo = {
    rank: `#${currentRank}`,
    userId: sortedRows[i].user_id,
    totalXp: sortedRows[i].total_xp
  }
  rankedList.push(rankInfo)
  rankedMap.set(sortedRows[i].user_id, rankInfo)
}

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