Как получить последнюю запись в каждой группе в MySQL

Для эффективного получения последней записи в каждой группе в MySQL следует использовать оконные функции в MySQL 8.0+ или альтернативные методы, такие как self-JOIN для более старых версий. Традиционный подход с GROUP BY возвращает произвольные строки, тогда как оконные функции с ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) обеспечивают чистое и производительное решение, которое ранжирует записи в группах и возвращает только записи с наивысшим рангом (последние).

Содержание

• Понимание проблемы
• Решение с использованием оконных функций (MySQL 8.0+)
• Метод Self-JOIN (Все версии MySQL)
• Подход с использованием подзапроса MAX()
• Сравнение производительности
• Лучшие практики и рекомендации
• Полный пример с тестовыми данными

Понимание проблемы

Проблема, с которой вы сталкиваетесь, заключается в том, что оператор GROUP BY в MySQL не гарантирует, какая строка из каждой группы будет возвращена. При использовании SELECT * FROM messages GROUP BY name MySQL возвращает произвольную строку из каждой группы, а не обязательно первую или последнюю. Это поведение задокументировано в документации MySQL, где объясняется, что GROUP BY не гарантирует возврат какой-либо конкретной строки из каждой группы.

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

Решение с использованием оконных функций (MySQL 8.0+)

Наиболее эффективное и читаемое решение для MySQL 8.0 и более новых версий — использование оконных функций. Эти функции позволяют выполнять вычисления по набору строк таблицы, как-то связанных с текущей строкой.

Использование ROW_NUMBER()

WITH numbered_messages AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) as rn
    FROM messages
)
SELECT * FROM numbered_messages
WHERE rn = 1;

Этот запрос работает следующим образом:

1. Использование Common Table Expression (CTE) для присвоения номера строки каждой записи
2. Разбиение на разделы по name (создание групп по имени)
3. Сортировка внутри каждого раздела по id DESC (так что наибольший ID получает ранг 1)
4. Фильтрация строк, где rn = 1 (последняя запись в каждой группе)

Использование LAST_VALUE()

WITH latest_messages AS (
    SELECT *,
           LAST_VALUE(id) OVER (PARTITION BY name ORDER BY id DESC 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as latest_id
    FROM messages
)
SELECT DISTINCT
    name,
    (SELECT * FROM messages m 
     WHERE m.id = latest_messages.latest_id 
     AND m.name = latest_messages.name) as latest_record
FROM latest_messages;

Согласно документации Oracle, LAST_VALUE() может быть особенно полезен для этого типа операций при использовании с правильным оконным кадрированием.

Метод Self-JOIN (Все версии MySQL)

Для версий MySQL до 8.0, которые не поддерживают оконные функции, подход с self-JOIN является надежной альтернативой:

SELECT m1.*
FROM messages m1
LEFT JOIN messages m2 ON m1.name = m2.name AND m1.id < m2.id
WHERE m2.id IS NULL;

Этот запрос работает следующим образом:

1. Соединение таблицы самой с собой по столбцу name
2. Поиск записей, где m1.id < m2.id (что означает наличие более новой записи с тем же именем)
3. Оставление только тех записей, для которых не существует более новой записи (m2.id IS NULL)

Подход с использованием подзапроса MAX()

Другой подход, который работает во всех версиях MySQL, — использование подзапросов с MAX():

SELECT m.*
FROM messages m
JOIN (
    SELECT name, MAX(id) as max_id
    FROM messages
    GROUP BY name
) latest ON m.name = latest.name AND m.id = latest.max_id;

Этот метод:

1. Сначала находит максимальный (последний) ID для каждого имени с помощью подзапроса
2. Затем соединяется обратно с исходной таблицей, чтобы получить полные записи для этих ID

Сравнение производительности

Различные подходы имеют разные характеристики производительности:

Как отмечено в статье в блоге Virtueinfo, оконные функции обычно являются наиболее производительным подходом в MySQL 8.0+ при правильном индексировании.

Лучшие практики и рекомендации

1. Используйте оконные функции для MySQL 8.0+: Это наиболее эффективное и читаемое решение
2. Добавьте правильные индексы: Убедитесь, что столбцы группировки и сортировки проиндексированы для оптимальной производительности
3. Учитывайте объем данных: Для очень больших наборов данных тестируйте разные подходы, так как производительность может различаться
4. Используйте EXPLAIN: Всегда анализируйте планы выполнения запросов для понимания узких мест производительности

Учебное пособие GeeksforGeeks подчеркивает, что оконные функции предоставляют наиболее современное и поддерживаемое решение, когда оно доступно.

Полный пример с тестовыми данными

Вот полный рабочий пример с использованием предоставленных вами тестовых данных:

-- Создание тестовой таблицы
CREATE TABLE messages (
    id INT PRIMARY KEY,
    name VARCHAR(10),
    other_columns VARCHAR(20)
);

-- Вставка тестовых данных
INSERT INTO messages VALUES
(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');

-- Метод 1: Оконные функции (MySQL 8.0+)
WITH numbered_messages AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) as rn
    FROM messages
)
SELECT * FROM numbered_messages
WHERE rn = 1;

-- Метод 2: Self-JOIN (Все версии)
SELECT m1.*
FROM messages m1
LEFT JOIN messages m2 ON m1.name = m2.name AND m1.id < m2.id
WHERE m2.id IS NULL;

-- Метод 3: Подзапрос с MAX (Все версии)
SELECT m.*
FROM messages m
JOIN (
    SELECT name, MAX(id) as max_id
    FROM messages
    GROUP BY name
) latest ON m.name = latest.name AND m.id = latest.max_id;

Все три метода вернут одинаковый результат:

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

Вывод

Основные выводы для получения последней записи в каждой группе в MySQL:

1. Для MySQL 8.0+: Используйте оконные функции с ROW_NUMBER() OVER (PARTITION BY name ORDER BY id DESC) для наиболее эффективного и читаемого решения.

2. Для более старых версий MySQL: Подход с self-JOIN или методы с подзапросом MAX() предоставляют надежные альтернативы, хотя они могут иметь разные характеристики производительности.

3. Оптимизация производительности: Всегда обеспечивайте правильное индексирование как столбца группировки, так и столбца сортировки для достижения лучших результатов.

4. Тестирование запросов: Используйте EXPLAIN для анализа планов выполнения запросов и тестирования разных подходов на вашем конкретном размере и структуре набора данных.

Подход с использованием оконных функций, рекомендованный SQLpey, обычно является наиболее рекомендуемым и читаемым подходом для современных установок MySQL, использующим как стандарты SQL, так и оптимизации производительности.

Источники

1. SQL - Получение последней записи в каждой группе - MySQL - Stack Overflow
2. Как получить последнюю запись в каждой группе в MySQL? - GeeksforGeeks
3. Решено: Как получить последнюю запись в каждой группе в MySQL - SQLpey
4. MySQL - Выбор последней записи для каждой группы - Codelabs365
5. Получение последней записи в каждой группе MySQL - Virtueinfo
6. Оконные функции в MySQL - Документация MySQL
7. Документация функции LAST_VALUE
8. Как получить последние 2 записи в каждой группе - Database Administrators Stack Exchange