如何为PostgreSQL的SELECT查询结果生成连续编号索引？

问题原因 #

你遇到的跳号问题本质是SQL执行顺序导致的：row_number() 这类窗口函数的执行时机早于DISTINCT ON，所以序号是在去重前的临时结果集上计算的，去重后自然会出现断号。

解决方案 #

你只需要把现有查询（去掉序号列）作为子查询，在外层查询再生成序号即可，修改后的代码如下：

SELECT 
  row_number() over(ORDER BY game_id asc) AS num,
  opponent_name,
  opponent_score,
  player_score,
  result,
  layout,
  date
FROM (
  SELECT DISTINCT ON (games.id)
   (SELECT nickname FROM users INNER JOIN users_games ON users.id = users_games.user_id WHERE id != 'some_user_id' AND game_id = games.id ) AS opponent_name,
   (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') as opponent_score, 
   (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') as player_score,
   (SELECT
    CASE
     WHEN (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') > (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') THEN 'Won'
     WHEN (SELECT score FROM users_games WHERE game_id = games.id and user_id != 'some_user_id') > (SELECT score FROM users_games WHERE game_id = games.id and user_id = 'some_user_id') THEN 'Lost'
     ELSE 'Tie'
     END) as result,
   layouts.name AS layout, 
   date(end_time) AS date,
   games.id as game_id
  FROM
   users_games
  INNER JOIN
   users ON users_games.user_id = users.id
  INNER JOIN
   games ON users_games.game_id = games.id
  INNER JOIN layouts
   ON games.layout_id = layouts.id 
   WHERE games.id IN (SELECT game_id FROM users_games WHERE user_id = 'some_user_id') 
   ORDER BY games.id asc
) t
ORDER BY game_id asc;

可选优化方案 #

你可以通过聚合逻辑替换大量嵌套子查询，提升查询效率和可读性，同时从根源上避免临时结果集重复的问题，参考写法：

SELECT
  row_number() over(ORDER BY g.id asc) AS num,
  MAX(CASE WHEN ug.user_id != 'some_user_id' THEN u.nickname END) AS opponent_name,
  MAX(CASE WHEN ug.user_id != 'some_user_id' THEN ug.score END) AS opponent_score,
  MAX(CASE WHEN ug.user_id = 'some_user_id' THEN ug.score END) AS player_score,
  CASE
    WHEN MAX(CASE WHEN ug.user_id = 'some_user_id' THEN ug.score END) > MAX(CASE WHEN ug.user_id != 'some_user_id' THEN ug.score END) THEN 'Won'
    WHEN MAX(CASE WHEN ug.user_id = 'some_user_id' THEN ug.score END) < MAX(CASE WHEN ug.user_id != 'some_user_id' THEN ug.score END) THEN 'Lost'
    ELSE 'Tie'
  END AS result,
  l.name AS layout,
  date(g.end_time) AS date
FROM users_games ug
INNER JOIN games g ON ug.game_id = g.id
INNER JOIN users u ON ug.user_id = u.id
INNER JOIN layouts l ON g.layout_id = l.id
WHERE g.id IN (SELECT game_id FROM users_games WHERE user_id = 'some_user_id')
GROUP BY g.id, l.name, g.end_time
ORDER BY g.id asc;

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