13

Получение последней записи в каждой группе - MySQL

18

У меня есть таблица messages, содержащая данные, как показано ниже:

Id   Name   Other_Columns
-------------------------
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

Когда я выполняю запрос select * from messages group by name, я получаю следующий результат:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

Какой запрос вернет следующий результат?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

То есть, мне нужно вернуть последнюю запись в каждой группе.

В настоящее время я использую следующий запрос:

SELECT
  *
FROM (SELECT
  *
FROM messages
ORDER BY id DESC) AS x
GROUP BY name

Но этот подход выглядит неэффективным. Есть ли другие способы достичь того же результата?

5 ответ(ов)

1

Я пришел к другому решению, которое заключается в получении идентификаторов последнего сообщения в каждой группе, а затем выборке из таблицы сообщений, используя результаты первого запроса в качестве аргумента для конструкции WHERE x IN:

SELECT id, name, other_columns
FROM messages
WHERE id IN (
    SELECT MAX(id)
    FROM messages
    GROUP BY name
);

Не могу сказать, как это решение будет работать по сравнению с другими вариантами, но оно сработало великолепно для моей таблицы с более чем 3 миллионами строк (время выполнения 4 секунды при более чем 1200 результатах).

Это должно работать как в MySQL, так и в SQL Server.

0

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

SELECT * 
FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)

Результат выполнения запроса:

Id  Name    Other_Columns
3   A       A_data_3
5   B       B_data_2
6   C       C_data_1

Этот запрос выбирает последние записи из таблицы messages на основе имени. Мы используем подзапрос для получения максимального значения Id для каждой уникальной Name, что позволяет эффективно извлекать нужные данные.

0

Вот два предложения. Во-первых, если MySQL поддерживает функцию ROW_NUMBER(), это очень просто:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ROW_NUMBER() OVER (
      PARTITION BY Name
      ORDER BY Id DESC
    ) AS rk
  FROM messages
)
SELECT Id, Name, OtherColumns
FROM Ranked
WHERE rk = 1;

Я предполагаю, что под "последним" вы имеете в виду последний по порядку Id. Если это не так, измените условие ORDER BY в функции ROW_NUMBER() соответственно. Если ROW_NUMBER() недоступна, вот другое решение:

Во-вторых, если функция недоступна, это часто хороший способ решить задачу:

SELECT
  Id, Name, OtherColumns
FROM messages
WHERE NOT EXISTS (
  SELECT * FROM messages AS M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id
)

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

0

Вопрос, который вы задаете, касается эффективных способов получения последних результатов в каждой группе в MySQL. Действительно, существует множество способов достичь одного и того же результата, но, как показал опыт, при больших объемах данных (например, больше 60 миллионов строк) это может стать проблематичным, особенно если вы используете InnoDB и последние версии MySQL (например, 5.7.21 и 8.0.4-rc).

В своих примерах я использую таблицы, содержащие около 1.5 миллиона записей и 100 различных групп. Мы часто возвращаем данные для около 2000 групп, что, гипотетически, не требует анализа большой части данных.

Вот как выглядят таблицы:

CREATE TABLE temperature(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
  groupID INT UNSIGNED NOT NULL, 
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 
  PRIMARY KEY (id)
);

CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id)); 

Таблица temperature содержит рандомные данные о температуре, а таблица selected_group — части групп, для которых мы хотим получить последние значения.

Наша цель — получить список всех выбранных групп по порядку groupID, с последним recordedTimestamp для каждой группы, и если у группы несколько подходящих строк, то вернуть последнюю из них.

В гипотетическом мире, если бы MySQL имел функцию last(), которую можно было бы использовать так:

SELECT 
  last(t1.id) AS id, 
  t1.groupID, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
ORDER BY t1.recordedTimestamp, t1.id
GROUP BY t1.groupID;

Запрос бы выполнялся быстро, так как проверял всего несколько сотен строк. Однако, поскольку MySQL не поддерживает такую функцию, давайте рассмотрим несколько альтернативных решений и их неэффективность.

Пример 1:

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.id = (
  SELECT t2.id
  FROM temperature t2 
  WHERE t2.groupID = g.id
  ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  LIMIT 1
);

Этот запрос проверил 3,009,254 строки и занял ~0.859 секунд на версии 5.7.21.

Пример 2:

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
INNER JOIN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON t5.id = t1.id;

Этот запрос проверил 1,505,331 строки и занял ~1.25 секунд.

Пример 3:

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
WHERE t1.id IN ( 
  SELECT max(t2.id) AS id   
  FROM temperature t2
  INNER JOIN (
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID = g.id
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
)
ORDER BY t1.groupID;

В этом случае было проверено 3,009,685 строк и заняло ~1.95 секунд.

Из приведенных примеров видно, что запросы становятся все более ресурсозатратными, и ни один из предложенных подходов не является действительно эффективным. В случае работы с большими объемами данных стоит рассмотреть возможность оптимизации структуры базы данных или использования других методов хранения данных, таких как индексация или партиционирование.

0

Привет, @Vijay Dev! Если ваша таблица messages содержит поле Id, которое является автоинкрементируемым первичным ключом, то для того, чтобы получить последнюю запись на основе этого первичного ключа, ваш запрос должен выглядеть следующим образом:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id = m2.lastmsgId

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

Чтобы ответить на вопрос, пожалуйста, войдите или зарегистрируйтесь