JPA 为每个项目选择最新实例

Question

假设我有一个会议实体。每次会议都有一个与会者和一个会议日期。在我的会议表中，我可能为每个与会者举行多个会议，每个会议都有不同的日期。我需要一个 JPA 查询，该查询将为所有与会者仅选择最新的会议。例如，如果我的表如下所示，

Meeting ID | Attendee ID | Meeting Date
1          | 1           |  6/1/2011
2          | 2           |  6/1/2011
3          | 1           |  6/6/2011
4          | 3           |  6/6/2011

我的结果应该是

Meeting ID | Attendee ID | Meeting Date
2          | 2           |  6/1/2011
3          | 1           |  6/6/2011
4          | 3           |  6/6/2011

针对 postgres 使用 JPA 2。会议有 1-1 与会者和简单的时间戳日期。我怀疑我需要通过和 max(blah) 进行分组，也许还需要加入自己，但我不确定解决这个问题的最佳方法。

更新： 经过一晚上的研究，我仍然没有一个可接受的 JPQL 解决方案。到目前为止，这是我所拥有的：

select m from Meeting m 
where m.meetingDate in 
    ( select max(meet.meetingDate) 
      from Meeting meet group by meet.attendee )

我还有与此问题无关的各种其他条件，例如按参加者部门进行过滤等。这种方法有效的唯一原因是我们将会议日期跟踪到秒（或更精确），并且两次会议同时举行的可能性很小。我们在它周围放置了一些 java 东西，只为每个与会者保留最后一次会议，以防万一我们同时有两个与会者，但这是一个非常蹩脚的解决方案。在查询中获取所有内容确实应该不会太困难，但我还没有弄清楚。

Update2： 添加 sql 标签，因为如果我需要使用 sql 创建视图并创建 JPA 对象来映射到视图，我可以接受。

Answer 1

在 SQL 中，解决方案非常简单 - 使用子查询连接表，这会为您提供每个与会者最近的会议：

select * from Meeting ALL
join ( select max(meetingDate) as newest, attendee
from Meeting group by attendee ) LATEST
on ALL.meetingDate = LATEST.newest AND ALL.attendee = LATEST.attendee

这很有效，而且工作速度很快！

JPA 的问题是它（或大多数实现）不允许使用子查询进行连接。在花了几个小时尝试首先编译什么之后，然后，它有多慢，我决定我讨厌 JPA。像上面这样的解决方案 - 例如 EXISTS (SELECT .. ) 或 IN (SELECT .. ) - 需要很长时间才能执行，比应有的速度慢了几个数量级。

拥有一个有效的解决方案意味着我只需要从 JPA 访问该解决方案即可。 SQL 中有两个神奇的词可以帮助您做到这一点：

CREATE VIEW

生活变得如此简单......只需定义这样的实体并使用它。
注意：它是只读的。

当然，当你这样做时，任何 JPA 纯粹主义者都会看不起你，所以如果有人有纯 JPA 解决方案，请让我们都知道！

Answer 2

我想我已经通过这个查询得到了它。

select m from Meeting m 
    where m.meetingDate = 
        (select max(m1.meetingDate) 
            from Meeting m1 
            where m1.attendee = m.attendee )
    and not exists 
        (select m2 from Meeting m2 
            where m2.attendee = m.attendee 
            and m2.meetingDate > m.meetingDate)

Answer 3

我认为在 SQL 中这将非常简单，所以我假设可以映射到 JPA：

SELECT m.AttendeeId, MAX(m.MeetingDate) from Meeting m GROUP BY m.AttendeeId

编辑：如果您还需要 messageId 本身，您可以使用一个简单的子查询来完成，该子查询返回消息的 messageId，其中其他两个值是相等的。只需确保您处理同一参加者和日期有多个 messageId 的情况（例如，选择第一个结果，因为它们应该都同样好 - 尽管我怀疑此类数据对于会议是否有意义）

Answer 4

Plain SQL

As Bulba 说过 合适的方法是将子查询与 group by 结合起来。

JPA、JPQL

问题是无法加入子查询。

这是一个解决方法。

让我们看看使用 group by 在子查询中得到了什么。您将获得(attendee_id, max(meeting_date)) 对的列表。
这对就像您想要加入的最大日期的行的新唯一 ID。
然后请注意，表中的每一行形成一对(attendee_id, meet_date)。
因此，每一行都有一个 id 作为一对 (attendee_id, meet_date)。
如果一行的 id 属于子查询中收到的列表，那么我们就取一行。

为了简单起见，我们将此 id 对表示为 attendee_id 和 meeting_date 的串联：concat(attendee_id, meet_date)。

那么 SQL 中的查询（对于 JPQL 和 JPA CriteriaBuilder 也类似）将如下所示：

SELECT * FROM meetings 
WHERE concat(attendee_id, meeting_date) IN
(SELECT concat(attendee_id, max(meeting_date)) FROM meetings GROUP BY attendee_id)

请注意，每个查询只有一个子查询，而不是像某些 答案。

害怕比较字符串？

我们为您提供特别优惠！

让我们将该 id 对编码为数字。
它将是 attendee_id 和 meeting_date 的总和，但会进行修改以确保代码的唯一性。我们可以将日期的数字表示作为Unix时间。
我们将修复代码可以捕获的最大日期的值，因为最终代码有最大值限制（例如 bigint(int8)<2⁶³）。为了方便起见，我们将最大日期设为 2149-06-07 03:00:00。它等于 5662310400（秒）和 65536（天）。
我在这里假设我们需要以天为单位的日期精度（因此我们忽略小时及以下）。
为了构造唯一的代码，我们可以将其解释为以 65536 为基数的数字系统中的数字。该数字系统中的最后一个符号（从 0 到 2¹⁶-1 的数字）或代码是以下数字天。其他符号将捕获 attendee_id。在这种解释中，代码看起来像 XXXX，其中每个 X 的范围为 [0,2¹⁶-1]（更准确地说，第一个 X 的范围为 [0,2¹⁶-1]） 2¹⁵-1] 因为 1 位用于符号），前三个 X 代表 attendee_id，最后一个 X 代表 meeting_date。
因此我们的代码可以捕获的 attendee_id 最大值为 2⁴⁷-1。
该代码可以计算为 attendee_id*65536 +“日期（以天为单位）”。

在 postgresql 中，它将是：

attendee_id*65536 + date_part('epoch', meeting_date)/(60*60*24)

其中 date_part 返回以秒为单位的日期，我们通过除以常量将其转换为天数。

最后的查询是获取所有与会者的最新会议：

SELECT * FROM meetings
WHERE attendee_id*65536 + date_part('epoch', meeting_date)/(60*60*24)
IN (SELECT attendee_id*65536 + date_part('epoch', max(meeting_date))/(60*60*24) from meetings GROUP BY attendee_id);

基准测试

我创建了一个表，其结构如问题中所示，并用 100000 行填充它，从 [1, 10000] 中随机选择 attendee_id 和随机日期范围[1970-01-01，2017-09-16]。我使用以下技术对查询进行了基准测试（使用 EXPLAIN ANALYZE） :

1. 相关子查询

   从会议 m1 中选择 *，其中 m1.meeting_date=
   (SELECT max(m2.meeting_date) FROM 会议 m2 WHERE m2.attendee_id=m1.attendee_id);
   

   执行时间：873260.878 ms

2. 使用 group by 连接子查询

   从会议中选择 * m
   JOIN（从会议中选择 attendee_id、max(meeting_date) GROUP BY attendee_id) attendee_max_date
   ON attendee_max_date.attendee_id = m.attendee_id;;
   

   执行时间：103.427 毫秒

3. 使用对 (attendee_id, date) 作为键

   • 将 attendee_id 和 meeting_date 连接为字符串

     从会议中选择 * WHERE concat(attendee_id,meeting_date) IN
     (从会议中选择 concat(attendee_id, max(meeting_date)) GROUP BY attendee_id);
     

     执行时间：207.720毫秒

   • 将 attendee_id 和 meeting_date 编码为单个数字（代码）

     从会议中选择 *
     WHERE attendee_id*65536 + date_part('epoch',meeting_date)/(60*60*24)
     IN (SELECT attendee_id*65536 + date_part('epoch',max(meeting_date))/(60*60*24) 来自会议 GROUP BY attendee_id);
     

     执行时间：127.595毫秒

这是一个 git 包含表方案、表数据（作为 csv）、填充代码表和查询。

Answer 5

试试这个

SELECT MAX(m.MeetingDate) FROM Meeting m