Подтвердить что ты не робот

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

Скажем, у меня есть объект собрания. На каждом собрании есть один участник и дата встречи. В моей таблице встреч у меня может быть несколько встреч для каждого участника с разными датами для каждого. Мне нужен запрос 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 для участника и простую дату. Я подозреваю, что мне понадобится сделать группу по и макс (бла) и, возможно, присоединиться к себе, но я не уверен, как лучше подойти к этому.

Update: Проведя вечер с этим, у меня до сих пор нет приемлемого решения 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 для сопоставления с представлением, я в порядке с ним.

4b9b3361

ОТВЕТЫ

Ответ 1

Я думаю, что у меня есть этот запрос.

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)

Ответ 2

В 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, сообщите нам об этом!

Ответ 3

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

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

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

Ответ 4

Обычный SQL

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

JPA, JPQL

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

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

Давайте посмотрим, что вы получаете в подзапросе с группой. Вы получаете список пар (attendee_id, max(meeting_date)). Эта пара похожа на новый уникальный идентификатор для строки с максимальной датой, к которой вы хотите присоединиться. Затем обратите внимание, что каждая строка в таблице образует пару (attendee_id, meeting_date). Таким образом, каждая строка имеет id как пару (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 time. Мы исправим значение максимальной даты, которое может получить наш код, поскольку конечный код имеет максимальное значение (например, bigint (int8) < 2 63). Давайте возьмем для удобства максимальную дату как 2149-06-07 03:00:00. Он равен 5662310400 в секундах и 65536 в днях. Я предполагаю, что нам нужна точность для даты в днях (поэтому мы игнорируем часы и ниже). Для построения уникального кода мы можем интерпретировать его как число в числовой системе с базой 65536. Последний символ (число от 0 до 2 16 -1) или код в такой числовой системе - это число дней. Другие символы будут захватывать attendee_id. В таком коде интерпретации выглядит как XXXX, где каждый X находится в диапазоне [0,2 16 -1] (чтобы быть более точным, первый X находится в диапазоне [0,2 15 -1] из-за 1 бит для знака), первые три X представляют attendee_id, а последний X представляет meeting_date. Таким образом, максимальное значение attendee_id нашего кода может захватить 2 47 -1. Код можно вычислить как attendee_id * 65536 + "date in days".

В 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) в качестве ключа

    • Конкат 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), кодом для заполнения таблицы и запросами.

Ответ 5

Попробуйте это

SELECT MAX(m.MeetingDate) FROM Meeting m