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

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

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

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

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

DROP TABLE IF EXISTS `committees`;
CREATE TABLE committees
    (`id` int, `acronym` varchar(4))
;

INSERT INTO committees
    (`id`, `acronym`)
VALUES
    (1, 'Com1'),
    (2, 'Com2'),
    (3, 'Com3')
;

DROP TABLE IF EXISTS `meetings`;
CREATE TABLE meetings
    (`id` int, `date` datetime, `committee_id` int)
;

INSERT INTO meetings
    (`id`, `date`, `committee_id`)
VALUES
    (1, '2017-01-01 00:00:00', 1),
    (2, '2017-02-02 00:00:00', 2),
    (3, '2017-03-03 00:00:00', 2)
;

DROP TABLE IF EXISTS `agenda_items`;
CREATE TABLE agenda_items
    (`id` int, `name` varchar(6))
;

INSERT INTO agenda_items
    (`id`, `name`)
VALUES
    (1, 'Item 1'),
    (2, 'Item 2'),
    (3, 'Item 3')
;

DROP TABLE IF EXISTS `join_agenda_items_meetings`;
CREATE TABLE join_agenda_items_meetings
    (`id` int, `agenda_item_id` int, `meeting_id` int)
;

INSERT INTO join_agenda_items_meetings
    (`id`, `agenda_item_id`, `meeting_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1),
    (4, 3, 2),
    (5, 2, 1),
    (6, 1, 3)
;




SELECT agenda_items.id, 
       meetings.id, 
       meetings.date, 
       sub_one.max_date, 
       sub_two.acronym 
FROM   agenda_items 
       LEFT JOIN (SELECT ai.id                AS ai_id, 
                         me.id                AS me_id, 
                         Max(me.date) AS max_date 
                  FROM   agenda_items AS ai 
                         JOIN join_agenda_items_meetings AS jaim 
                           ON jaim.agenda_item_id = ai.id 
                         JOIN meetings AS me 
                           ON me.id = jaim.meeting_id 
                  WHERE  me.date < '2017-02-02' 
                  GROUP  BY ai_id) sub_one 
              ON sub_one.ai_id = agenda_items.id 
       LEFT JOIN (SELECT agenda_items.id       AS age_id, 
                         meetings.date AS meet_date, 
                         committees.acronym    AS acronym 
                  FROM   agenda_items 
                         JOIN join_agenda_items_meetings 
                           ON join_agenda_items_meetings.agenda_item_id = agenda_items.id 
                         JOIN meetings 
                           ON meetings.id = join_agenda_items_meetings.meeting_id 
                         JOIN committees 
                           ON committees.id = meetings.committee_id 
                  WHERE  meetings.date) sub_two 
              ON sub_two.age_id = agenda_items.id 
                 AND sub_one.max_date = sub_two.meet_date 
       JOIN join_agenda_items_meetings 
         ON agenda_items.id = join_agenda_items_meetings.agenda_item_id 
       JOIN meetings 
         ON meetings.id = join_agenda_items_meetings.meeting_id 
WHERE  meetings.id = 2;

ОБЗОР/ИСПЫТАНИЕ ОТВЕТОВ (ПЕРЕСМОТРЕННОЕ): *

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

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

Для тестирования я просмотрел запросы:

начальный реестр
a измененная версия исходного рекстера со всеми 4 запросами для 2 отдельных наборов данных
больший набор данных из моей фактической базы данных

Мой оригинальный запрос с EXPLAIN

+----+-------------+---------------------------+------+----------------------------------------------+
| id | select_type | table                     | rows | Extra                                        |
+----+-------------+---------------------------+------+----------------------------------------------+
|  1 | PRIMARY     | meetings                  |    1 |                                              |
|  1 | PRIMARY     | join_agenda_item_meetings | 1976 | Using where; Using index                     |
|  1 | PRIMARY     | agenda_items              |    1 | Using index                                  |
|  1 | PRIMARY     | <derived2>                | 1087 |                                              |
|  1 | PRIMARY     | <derived3>                | 2202 |                                              |
|  3 | DERIVED     | join_agenda_item_meetings | 1976 | Using index                                  |
|  3 | DERIVED     | meetings                  |    1 | Using where                                  |
|  3 | DERIVED     | committees                |    1 |                                              |
|  3 | DERIVED     | agenda_items              |    1 | Using index                                  |
|  2 | DERIVED     | jaim                      | 1976 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | me                        |    1 | Using where                                  |
|  2 | DERIVED     | ai                        |    1 | Using index                                  |
+----+-------------+---------------------------+------+----------------------------------------------+
12 rows in set (0.02 sec)

Пол Шпигель отвечает.

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

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

+----+--------------------+-------+------+--------------------------+
| id | select_type        | table | rows | Extra                    |
+----+--------------------+-------+------+--------------------------+
|  1 | PRIMARY            | m1    |    1 |                          |
|  1 | PRIMARY            | am1   | 1976 | Using where; Using index |
|  1 | PRIMARY            | am2   |    1 | Using index              |
|  1 | PRIMARY            | m2    |    1 |                          |
|  2 | DEPENDENT SUBQUERY | am3   |    1 | Using index              |
|  2 | DEPENDENT SUBQUERY | m3    |    1 | Using where              |
|  2 | DEPENDENT SUBQUERY | c3    |    1 | Using where              |
+----+--------------------+-------+------+--------------------------+
7 rows in set (0.00 sec)

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

+----+-------------+------------++------+-------------------------+
| id | select_type | table      | rows | Extra                    |
+----+-------------+------------++------+-------------------------+
|  1 | PRIMARY     | <derived2> |    5 | Using temporary          |
|  1 | PRIMARY     | am         |    1 | Using index              |
|  1 | PRIMARY     | m          |    1 |                          |
|  1 | PRIMARY     | c          |    1 | Using where              |
|  2 | DERIVED     | m1         |    1 |                          |
|  2 | DERIVED     | am1        | 1787 | Using where; Using index |
|  2 | DERIVED     | am2        |    1 | Using index              |
|  2 | DERIVED     | m2         |    1 |                          |
+----+-------------+------------+------+--------------------------+
8 rows in set (0.00 sec)

Ответ Стефано Занини

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

+----+-------------+------------+------+---------------------------------+
| id | select_type | table      | rows | Extra                           |
+----+-------------+------------+------+---------------------------------+
|  1 | PRIMARY     | me         |    1 | Using temporary; Using filesort |
|  1 | PRIMARY     | rel        | 1787 | Using where; Using index        |
|  1 | PRIMARY     | <derived2> | 1087 |                                 |
|  1 | PRIMARY     | rel2       |    1 | Using index                     |
|  1 | PRIMARY     | me2        |    1 | Using where                     |
|  1 | PRIMARY     | co         |    1 |                                 |
|  2 | DERIVED     | t1         | 1787 | Using index                     |
|  2 | DERIVED     | t2         |    1 | Using where                     |
+----+-------------+------------+------+---------------------------------+
8 rows in set (0.00 sec)

Ответ EoinS

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

Ответ 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 предоставит некоторые пункты повестки дня нежелательные встречи.