JPA Выберите последний экземпляр для каждого элемента

Question

JPA Выберите последний экземпляр для каждого элемента

Допустим, у меня есть объект Meeting. Каждое собрание имеет одного посетителя и дату встречи. В моем столе собраний у меня может быть несколько встреч для каждого участника с разными датами для каждого. Мне нужен запрос 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

Использование JPA 2 против postgres. Встреча имеет 1-1 к участнику и простую дату отметки времени. Я подозреваю, что мне нужно будет сделать группу по максимуму (бла) и, возможно, присоединиться к себе, но я не уверен, что лучше всего подойти к этому.

Обновление: проведя вечер, играя с этим, у меня все еще нет приемлемого решения JPQL для этого. Вот что у меня так далеко:

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

У меня есть различные другие условия, которые не имеют отношения к этому вопросу, такие как фильтрация по отделам посетителей и так далее. Единственная причина, по которой это работает, заключается в том, что мы отслеживаем дату встречи до второй (или более точной), а вероятность того, что будут две встречи одновременно, минимальна. Мы помещаем вокруг него некоторые java-материалы, чтобы сохранить только последнюю встречу для каждого участника на случай, если мы получим две встречи одновременно, но это довольно дурацкое решение. Это действительно не должно быть слишком сложно, чтобы получить все это в запросе, но мне еще предстоит выяснить это.

Обновление 2: добавление тега sql, потому что если мне нужно использовать sql для создания представления и создания объекта JPA для сопоставления с представлением, я согласен с этим.

25

java sql jpa jpql jpa-2.0

Источник

user67598 09 июн '11 в 16:45

5 ответов

Решение

В 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, пожалуйста, сообщите нам обоим!

18

Источник

user1111601 02 авг '12 в 18:03

Я думаю, что в SQL это будет довольно просто, поэтому я предполагаю, что это может быть сопоставлено с JPA:

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

Редактировать: если вам также нужен сам messageId, вы можете сделать это с помощью простого подзапроса, который возвращает messageId для сообщения, в котором два других значения равны. Просто убедитесь, что вы обрабатываете случай, когда существует несколько идентификаторов сообщений для одного и того же участника и даты (например, выберите первый результат, поскольку все они должны быть одинаково хорошими - хотя я сомневаюсь, что такие данные имеют смысл даже для собраний)

10

Источник

user144746 09 июн '11 в 17:09

Простой SQL

Как сказал Бульба, подходящий способ - присоединиться к подзапросу с помощью группы.

JPA, JPQL

Проблема в том, что вы не можете присоединиться к подзапросу.

Вот обходной путь.

Давайте посмотрим, что вы получите в подзапросе с группой по. Вы получаете список пар (attendee_id, max(meeting_date)), Эта пара похожа на новый уникальный идентификатор строки с максимальной датой, к которой вы хотите присоединиться. Затем обратите внимание, что каждая строка в таблице образует пару (attendee_id, meeting_date), Таким образом, каждая строка имеет идентификатор в виде пары (attendee_id, meeting_date), Давайте возьмем строку, если только она формирует идентификатор, принадлежащий списку, полученному в подзапросе.

Для простоты представим эту id-пару в виде объединения attendee_id а также meeting_date: concat(attendee_id, meeting_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] из-за 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 строк, выбирающих случайным образом attendee_id от [1, 10000] и случайной даты из диапазона [1970-01-01, 2017-09-16]. Я сравнил (с EXPLAIN ANALYZE) запросы с помощью следующих методов:

Коррелированный подзапрос

SELECT * FROM meetings m1 WHERE m1.meeting_date=
(SELECT max(m2.meeting_date) FROM meetings m2 WHERE m2.attendee_id=m1.attendee_id);

Время выполнения: 873260,878 мс

Присоединиться к подзапросу с группой по

SELECT * FROM meetings m
JOIN (SELECT attendee_id, max(meeting_date) from meetings GROUP BY attendee_id) attendee_max_date
ON attendee_max_date.attendee_id = m.attendee_id;</code>

Время выполнения: 103,427 мс

Используйте пару (attendee_id, date) как ключ

Concat attendee_id а также meeting_date как строки

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

Время выполнения: 207,720 мс

шифровать attendee_id а также meeting_date на один номер (код)

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);

Время выполнения: 127,595 мс

Вот git со схемой таблицы, данными таблицы (как csv), кодом для заполнения таблицы и запросами.

9

Источник

user5178765 16 сен '17 в 23:00

Попробуй это

SELECT MAX(m.MeetingDate) FROM Meeting m

-4

Источник

user358163 09 июн '11 в 16:58

Другие вопросы по тегам java sql jpa jpql jpa-2.0

user67598 11 июн '11 в 20:51 2011-06-11 20:51 · Accepted Answer · 2011-06-11 20:51

Я думаю, что получил с этим запросом.

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)

16

Источник

user67598 11 июн '11 в 20:51