时间戳

首页 > 解决方案 > 使用 distinct + 过滤器时选择特定行的最快查询

postgresql - 使用 distinct + 过滤器时选择特定行的最快查询

问题描述

表答案：

Answer ID  | User ID | Question ID | deleted
1          | 1       |  1          | f
2          | 1       |  2          | f
3          | 1       |  2          | f
4          | 1       |  1          | t
5          | 2       |  1          | f
6          | 2       |  2          | f
7          | 2       |  2          | f

我想选择所有与(userID, questionID)使用最新答案（基于最高 id）不同的答案，并从这个结果集中删除所有具有deleted = t.

所以我的结果应该是

Answer ID  | User ID | Question ID | deleted
3          | 1       |  2          | f
5          | 2       |  1          | f
7          | 2       |  2          | f

我想我们无法使用从界面生成的查询方法来做到这一点？我正在使用@Query注释：

@Query("SELECT a1 FROM answer a1 WHERE ... ")
findLatestAnswers();

我想出了这个（sql fiddle：http ://sqlfiddle.com/#!15/02339/8/0 ），甚至没有使用distinctor group bynor order by。它正在完成这项工作，但对于更大的数据集似乎效率很低？什么是更快的声明？

SELECT * FROM answer a1
WHERE NOT EXISTS ( -- where no newer answer exists
           SELECT * FROM answer a2
           WHERE a1.user_id = a2.user_id
                   AND a1.question_id = a2.question_id
                   AND a1.id < a2.id
  )
  AND a1.deleted = FALSE;

标签： postgresqljpaspring-data-jpa

解决方案

distinct使用orgroup by或聚合函数没有问题。这些在数据仓库或分析软件中是必不可少的，在这些软件中，每个请求都会处理数百万或记录（数十亿和数万亿的大数据）。

唯一的调整是根据您的数据和查询生成索引。

您的 scanrio 所需的功能是max. 您必须选择分组max如下：anser_iduser_id, question_id

解决方案 1

@Query("select max(answer) from Answer answer where answer.deleted = false group by answer.userId, answer.questionId")
List<Answer> findLatestAnswersByUserQuestionNotDeleted();

该语句返回 4 条记录，因为如果您不考虑删除答案，则问题 1 中用户 1 的最新答案将变为 1。

我不知道您为什么不考虑这一点，但我会照原样关注您的问题。

因此，您必须按照您的描述以编程方式删除过滤器，因此@Query变为：

@Query("select max(answer) from Answer answer group by answer.userId, answer.questionId")
List<Answer> findLatestAnswersByUserQuestion();

再次正确地，您有 4 条记录，因为也存在已删除并且必须以编程方式过滤

解决方案2（两个查询，因为您要求忽略已删除而不考虑旧的）

第 1 步 - findId 的答案，包括已删除（仅 id）：

@Query("select max(answer.id) from Answer answer group by answer.userId, answer.questionId")
List<Long> findLatestAnswersId();

第 2 步 - 按 id 加载答案，不包括已删除

List<Answer> findAllByDeletedIsFalseAndIdIn(List<Long> ids);

解决方案 3（一个查询）

@Query("select answer from Answer answer where answer.deleted = false and  answer.id in (select max(inAnswer.id) from Answer inAnswer)")
List<Answer> findLastestNotDeleted()

推荐阅读