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

Я создал запрос, который возвращает результаты, которые я хочу, но я считаю, что должен быть лучший способ сделать это. Любые рекомендации будут оценены.

Я пытаюсь получить все элементы для конкретного собрания и присоединиться к их максимальной дате собрания < X и присоединиться к сокращенному сокращению даты. X - текущая дата собрания.

Я пробовал несколько разных запросов, но никто, кроме приведенного ниже, не возвращал ожидаемые результаты все время.

Вы можете увидеть этот запрос в действии, перейдя в rextester.

Я пересмотрел тестирование на основе сделанных комментариев.

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

начальный ответ работает и представляется наиболее эффективным вариантом, гораздо большим, чем мой.

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

Этот запрос также возвращает правильные результаты при добавлении DISTINCT в оператор select. Этот запрос не работает так же хорошо, как и первый, хотя (но он близок).

Этот запрос возвращает ожидаемые результаты с помощью DISTINCT. При использовании EXPLAIN и количестве вытягиваемых строк этот запрос более эффективен по сравнению с моим оригинальным, но Пол Шпигель немного лучше.

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

Ответы

Ответ 1

Это немного сумасшедший.. Давайте сделаем это шаг за шагом:

Первым шагом является базовое соединение

set @meeting_id = 2;

select am1.meeting_id,
       am1.agenda_item_id,
       m1.date as meeting_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
where m1.id = @meeting_id;

Мы выбираем встречу (id = 2) и соответствующую тему повестки дня_имя. Это уже вернет строки, которые нам нужны, с первыми тремя столбцами.

Следующий шаг - получить последнюю дату встречи для каждого пункта повестки дня. Мы должны присоединиться к первому запросу с таблицей соединений и соответствующими встречами (кроме одного с id = 2 - am2.meeting_id <> am1.meeting_id). Нам нужны только встречи с датой перед фактическим собранием (m2.date < m1.date). На всех этих встречах мы хотим только получить последнюю дату каждого пункта повестки дня. Поэтому мы группируем по пункту повестки дня и выбираем max(m2.date):

select am1.meeting_id,
       am1.agenda_item_id,
       m1.date as meeting_date,
       max(m2.date) as max_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2 
    on  am2.agenda_item_id = am1.agenda_item_id
    and am2.meeting_id <> am1.meeting_id
left join meetings m2 
    on  m2.id = am2.meeting_id
    and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id;

Таким образом мы получаем четвертый столбец (max_date).

Последний шаг - выбрать acronym встречи с последней датой (max_date). И это сумасшедшая часть. Мы можем использовать коррелированный подзапрос в предложении SELECT. И мы можем использовать max(m2.date) для корреляции:

select c3.acronym
from meetings m3
join join_agenda_items_meetings am3 on am3.meeting_id = m3.id
join committees c3 on c3.id = m3.committee_id
where am3.agenda_item_id = am2.agenda_item_id
  and m3.date = max(m2.date)

Окончательный запрос:

select am1.meeting_id,
       am1.agenda_item_id,
       m1.date as meeting_date,
       max(m2.date) as max_date,
       (   select c3.acronym
           from meetings m3
           join join_agenda_items_meetings am3 on am3.meeting_id = m3.id
           join committees c3 on c3.id = m3.committee_id
           where am3.agenda_item_id = am2.agenda_item_id
             and m3.date = max(m2.date)
       ) as acronym
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2 
    on  am2.agenda_item_id = am1.agenda_item_id
    and am2.meeting_id <> am1.meeting_id
left join meetings m2 
    on  m2.id = am2.meeting_id
    and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id;

http://rextester.com/JKK60222

Чтобы быть правдой, я был удивлен, что вы можете использовать max(m2.date) в подзапросе.

Другое решение. Используйте второй запрос в подзапросе (производная таблица). Присоединяйте комитеты к собраниям и таблице соединений, используя max_date. Удерживайте строки с аббревиатурой и строками без max_date.

select t.*, c.acronym
from (
    select am1.meeting_id,
           am1.agenda_item_id,
           m1.date as meeting_date,
           max(m2.date) as max_date
    from meetings m1
    join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
    left join join_agenda_items_meetings am2 
        on  am2.agenda_item_id = am1.agenda_item_id
        and am2.meeting_id <> am1.meeting_id
    left join meetings m2 
        on  m2.id = am2.meeting_id
        and m2.date < m1.date
    where m1.id = @meeting_id
    group by m1.id, am1.agenda_item_id
) t
left join join_agenda_items_meetings am
    on  am.agenda_item_id = t.agenda_item_id
    and t.max_date is not null
left join meetings m
    on  m.id   = am.meeting_id
    and m.date = t.max_date
left join committees c on c.id = m.committee_id
where t.max_date is null or c.acronym is not null;

http://rextester.com/BBMDFL23101

Ответ 2

Используя вашу схему, я использовал следующий запрос, считая, что все записи meetings являются последовательными:

 set @mymeeting = 2;

 select j.agenda_item_id, m.id, m.date, mp.date, c.acronym
 from meetings m 
 left join join_agenda_items_meetings j on j.meeting_id = m.id
 left join join_agenda_items_meetings jp on jp.meeting_id = m.id -1 and jp.agenda_item_id = j.agenda_item_id
 left join meetings mp on mp.id = jp.meeting_id
 left join committees c on mp.committee_id = c.id
 where m.id = @mymeeting;

Я создаю переменную, чтобы упростить изменение собраний на лету.

Вот функциональный пример в Rextester

Спасибо за то, что ваша схема так легко воспроизвести!

Ответ 3

Я нашел эту проблему довольно сложной задачей, и результаты, которые я достиг, не являются челюстями, но мне удалось избавиться от одного из подзапросов и, возможно, нескольких объединений, и это результат:

select    distinct me.ID, me.DATE, rel.AGENDA_ITEM_ID, sub.MAX_DATE, co.ACRONYM
from      MEETINGS me
join      JOIN_AGENDA_ITEMS_MEETINGS rel /* Note 1*/
  on      me.ID = rel.MEETING_ID
left join (   
              select  t1.AGENDA_ITEM_ID, max(t2.DATE) MAX_DATE
              from    JOIN_AGENDA_ITEMS_MEETINGS t1
              join    MEETINGS t2
                on    t2.ID = t1.MEETING_ID
              where   t2.DATE < '2017-02-02'
              group by t1.AGENDA_ITEM_ID
          ) sub
  on      rel.AGENDA_ITEM_ID = sub.AGENDA_ITEM_ID /* Note 2 */
left join JOIN_AGENDA_ITEMS_MEETINGS rel2
  on      rel2.AGENDA_ITEM_ID = rel.AGENDA_ITEM_ID /* Note 3 */
left join MEETINGS me2
  on      rel2.MEETING_ID = me2.ID and
          sub.MAX_DATE = me2.DATE /* Note 4 */
left join COMMITTEES co
  on      co.ID = me2.COMMITTEE_ID
where     me.ID = 2 and
          (sub.MAX_DATE is null or me2.DATE is not null) /* Note 5 */
order by  rel.AGENDA_ITEM_ID, rel2.MEETING_ID;

Примечания

вам не нужно соединение с AGENDA_ITEMS, так как ID уже доступен в таблице отношений
до этого момента у нас есть текущее собрание, его пункты повестки дня и их "рассчитанная" максимальная дата
мы получаем все встречи по каждому пункту повестки дня...
... чтобы мы могли выбрать встречу, дата которой соответствует максимальной дате, которую мы рассчитали ранее
это условие необходимо, потому что все соединения из rel2 on должны быть оставлены (потому что у какого-то пункта повестки дня нет предыдущего собрания и, следовательно, MAX_DATE = null), но таким образом me2 предоставит некоторые пункты повестки дня нежелательные встречи.