如何修正TypeORM查询以正确获取用户最高WPM及准确率的60秒测试

需要检索用户时长为60秒、拥有最高WPM（每分钟字数）和accuracy（准确率）的测试。当存在WPM与准确率相同的测试时，选取createdAt更早的记录。但当前查询在存在两条相同WPM&准确率的测试，且另有一条更高WPM的测试时，无法返回任何结果。

原查询代码 #

const qb = ctx.em
      .createQueryBuilder(Test, 'test')
      .innerJoin(
        (subQuery) =>
          subQuery
            .select('MAX(t.wpm)', 'max_wpm')
            .addSelect('t.creatorId', 'creatorId')
            .addSelect('MAX(t.accuracy)', 'max_accuracy')
            .from(Test, 't')
            .where('t.time = :time', { time: '60' })
            .groupBy('t.creatorId'),
        'max_tests',
        'max_tests.max_wpm = test.wpm AND max_tests."creatorId" = test."creatorId" AND max_tests.max_accuracy = test.accuracy'
      )
      .leftJoinAndSelect('test.creator', 'creator')
      .where('test.time = :time', { time: '60' })
      .andWhere((qb) => {
        const subQuery = qb
          .subQuery()
          .select('MIN(t2.createdAt)')
          .from(Test, 't2')
          .where('t2.creatorId = test.creatorId')
          .andWhere('t2.wpm = test.wpm')
          .andWhere('t2.accuracy = test.accuracy')
          .getQuery();
        return `test."createdAt" = (${subQuery})`;
      })
      .orderBy('test.wpm', 'DESC')
      .addOrderBy('test.accuracy', 'DESC')

测试数据示例 #

[
  {
    "id": 1,
    "wpm": 96,
    "accuracy": 96.7,
    "time": "60",
    "createdAt": "2023-05-09T11:26:42.003917Z",
    "creatorId": 1
  },
  {
    "id": 2,
    "wpm": 96,
    "accuracy": 96.7,
    "time": "60",
    "createdAt": "2023-05-09T12:58:48.956275Z",
    "creatorId": 1
  },
  {
    "id": 3,
    "wpm": 97,
    "accuracy": 97,
    "time": "60",
    "createdAt": "2023-05-09T13:18:21.991219Z",
    "creatorId": 1
  }
]

问题分析 #

原查询的核心错误在于：子查询中同时取MAX(wpm)和MAX(accuracy)，这会获取两个字段各自的最大值，而非同一测试记录中的组合最大值。这种逻辑会导致查询试图匹配不存在的"最高WPM+最高准确率"组合，最终无结果返回。此外，内层的createdAt筛选子查询逻辑冗余，进一步增加了出错概率。

修正后的查询代码 #

改用PostgreSQL的窗口函数ROW_NUMBER()来实现正确的排序筛选，确保每个用户只返回符合要求的最优测试记录：

const qb = ctx.em
  .createQueryBuilder()
  .select('t.*')
  .addSelect('creator.*')
  .from((subQuery) => {
    return subQuery
      .select('test.*')
      .addSelect(`ROW_NUMBER() OVER (
        PARTITION BY test."creatorId" 
        ORDER BY test.wpm DESC, test.accuracy DESC, test."createdAt" ASC
      ) as rn`)
      .from(Test, 'test')
      .where('test.time = :time', { time: '60' });
  }, 't')
  .leftJoin('t.creator', 'creator')
  .where('t.rn = 1')
  .orderBy('t.wpm', 'DESC')
  .addOrderBy('t.accuracy', 'DESC');

代码说明 #

1. 窗口函数排序：通过ROW_NUMBER()按用户ID分组（PARTITION BY creatorId），并按照「WPM降序 → 准确率降序 → 创建时间升序」的优先级为每条记录分配排名rn。
2. 筛选最优记录：外层查询仅保留排名为1的记录，即每个用户的最优测试。
3. 关联用户信息：保留原查询中关联creator的逻辑，同时避免了原查询中MAX字段组合的错误。

运行修正后的查询，会正确返回测试ID为3的记录，符合需求。

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