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

Существует таблица, 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

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

21.08.2009 17:04:04
см. принятый ответ в stackoverflow.com/questions/1379565/… для более эффективного решения
eyaler 25.06.2012 12:45:16
TMS 14.06.2013 20:10:49
Почему вы не можете просто добавить DESC, т.е. выбрать * из группы сообщений по имени DESC
Kim Prince 3.12.2015 06:41:09
Ciro Santilli 冠状病毒审查六四事件法轮功 12.06.2016 22:19:17
@ KimPrince Кажется, что предлагаемый вами ответ не соответствует ожиданиям! Я только что попробовал ваш метод, и он взял ПЕРВУЮ строку для каждой группы и заказал DESC. Это НЕ занимает последний ряд каждой группы
Ayrat 22.05.2017 15:34:15
27 ОТВЕТОВ
РЕШЕНИЕ

MySQL 8.0 теперь поддерживает оконные функции, как почти все популярные реализации SQL. С помощью этого стандартного синтаксиса мы можем писать запросы с наибольшим числом групп:

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

Ниже приведен оригинальный ответ, который я написал на этот вопрос в 2009 году:


Я пишу решение так:

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

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

Например, у меня есть копия дампа данных StackOverflow в августе . Я буду использовать это для сравнительного анализа. В таблице 1114 357 строк Posts. Это работает на MySQL 5.0.75 на моем Macbook Pro 2,40 ГГц.

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

Сначала используйте технику, показанную @Eric с GROUP BYподзапросом в:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

Даже EXPLAINанализ занимает более 16 секунд:

+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
+----+-------------+------------+--------+----------------------------+-------------+---------+--------------+---------+-------------+
3 rows in set (16.09 sec)

Теперь тот же результат запроса , используя мою технику с LEFT JOIN:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

На EXPLAINанализ показывает , что обе таблицы имеют возможность использовать свои индексы:

+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
+----+-------------+-------+------+----------------------------+-------------+---------+-------+------+--------------------------------------+
2 rows in set (0.00 sec)

Вот DDL для моего Postsстола:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
) ENGINE=InnoDB;
957
26.12.2017 20:38:20
В самом деле? Что произойдет, если у вас есть тонна записей? Например, если вы работаете с внутренним управлением версиями, скажем, и у вас есть тонна версий на файл, результат объединения будет огромным. Вы когда-нибудь сравнивали метод подзапроса с этим? Мне довольно любопытно узнать, кто победит, но не настолько любопытно, чтобы не спросить вас первым.
Eric 21.08.2009 18:19:39
Сделал некоторое тестирование. Для небольшой таблицы (~ 300 тыс. Записей, ~ 190 тыс. Групп, а не для массивных групп или чего-либо еще) запросы связывались (по 8 секунд каждый).
Eric 21.08.2009 18:44:43
@ Тим, нет, <=не поможет, если у вас есть неуникальный столбец. Вы должны использовать уникальный столбец как средство разрешения конфликтов.
Bill Karwin 3.07.2015 07:13:14
Производительность снижается экспоненциально по мере увеличения числа строк или увеличения групп. Например, группа, состоящая из 5 дат, выдаст 4 + 3 + 2 + 1 + 1 = 11 строк через левое соединение, из которых одна строка фильтруется в конце. Производительность объединения с сгруппированными результатами практически линейна. Ваши тесты выглядят ошибочными.
Salman A 16.10.2015 12:12:13
Важный комментарий stackoverflow: Это самый сексуальный запрос, который я когда-либо видел.
Seraf 5.06.2019 18:00:10

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

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

select
    a.*
from
    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on
        a.id = b.maxid

Если это не так, idвы хотите максимум:

select
    a.*
from
    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on
        a.name = b.name
        and a.other_col = b.other_col

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

99
21.08.2009 17:14:13
Обратите внимание на предостережение для решения other_col: если этот столбец не уникален, вы можете получить несколько записей обратно с одинаковыми name, если они связаны max(other_col). Я нашел этот пост, который описывает решение для моих нужд, где мне нужна ровно одна запись для каждого name.
Eric Simonton 21.08.2015 13:48:54
В некоторых ситуациях вы можете использовать только это решение, но только одно из принятых.
tom10271 4.09.2015 02:59:40
По моему опыту, это группировка всей таблицы чертовых сообщений, которая имеет тенденцию быть медленной / неэффективной! Другими словами, обратите внимание, что подзапрос требует полного сканирования таблицы и группирует его для загрузки ... если только ваш оптимизатор не делает то, что у меня нет. Таким образом, это решение сильно зависит от хранения всей таблицы в памяти.
Timo 30.04.2018 14:56:43
Те извлекут выгоду из INDEX(name, id)иINDEX(name, other_col)
Rick James 15.01.2020 23:30:19

Вот два предложения. Во-первых, если 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 messages
  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
)

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

6
21.08.2009 17:26:12
MySQL не поддерживает ROW_NUMBER () или CTE.
Bill Karwin 21.08.2009 17:37:56
MySQL 8.0 (и MariaDB 10.2) теперь поддерживают ROW_NUMBER()и CTE.
Rick James 28.01.2020 00:52:55

Есть ли способ, которым мы могли бы использовать этот метод для удаления дубликатов в таблице? Результирующий набор в основном представляет собой набор уникальных записей, поэтому, если бы мы могли удалить все записи, не входящие в результирующий набор, у нас фактически не было бы дубликатов? Я пробовал это, но MySQL выдал ошибку 1093.

DELETE FROM messages WHERE id NOT IN
 (SELECT m1.id  
 FROM messages m1 LEFT JOIN messages m2  
 ON (m1.name = m2.name AND m1.id < m2.id)  
 WHERE m2.id IS NULL)

Есть ли способ сохранить вывод во временную переменную, а затем удалить из NOT IN (временная переменная)? @ Билл, спасибо за очень полезное решение.

РЕДАКТИРОВАТЬ: Думаю, я нашел решение:

DROP TABLE IF EXISTS UniqueIDs; 
CREATE Temporary table UniqueIDs (id Int(11)); 

INSERT INTO UniqueIDs 
    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 
    WHERE T2.ID IS NULL); 

DELETE FROM Table WHERE id NOT IN (SELECT ID FROM UniqueIDs);
2
8.10.2010 01:57:49

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

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
       INNER JOIN (SELECT *
                   FROM   (SELECT `title`,
                                  catid,
                                  `created`,
                                  introtext
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = jos_categories.id )  
3
15.07.2011 13:47:27

Приведенный ниже запрос будет хорошо работать в соответствии с вашим вопросом.

SELECT M1.* 
FROM MESSAGES M1,
(
 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
 FROM MESSAGES
 GROUP BY 1
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;
2
18.11.2011 20:21:00

UPD: 2017-03-31, версия MySQL 5.7.5 сделала переключатель ONLY_FULL_GROUP_BY включенным по умолчанию (следовательно, недетерминированные запросы GROUP BY стали отключены). Более того, они обновили реализацию GROUP BY, и решение могло работать не так, как ожидалось, даже с отключенным коммутатором. Нужно проверить.

Приведенное выше решение Билла Карвина прекрасно работает, когда количество элементов в группах довольно мало, но производительность запроса становится плохой, если группы довольно большие, поскольку решение требует n*n/2 + n/2только IS NULLсравнений.

Я сделал свои тесты на таблице 18684446строк InnoDB с 1182группами. Таблица содержит результаты тестов для функциональных тестов и имеет (test_id, request_id)первичный ключ. Таким образом, test_idэто группа, и я искал последнее request_idдля каждого test_id.

Решение Билла уже несколько часов работает на моем Dell E4310, и я не знаю, когда оно закончится, даже если оно работает с индексом покрытия (следовательно, using indexв EXPLAIN).

У меня есть несколько других решений, основанных на тех же идеях:

  • если базовым индексом является индекс BTREE (который обычно имеет место), самая большая (group_id, item_value)пара является последним значением в каждом group_id, то есть первым для каждого, group_idесли мы пройдемся по индексу в порядке убывания;
  • если мы читаем значения, которые покрываются индексом, значения читаются в порядке индекса;
  • каждый индекс неявно содержит столбцы первичного ключа, добавленные к нему (то есть первичный ключ находится в индексе покрытия). В приведенных ниже решениях я работаю непосредственно с первичным ключом, в вашем случае вам просто нужно добавить столбцы первичного ключа в результат.
  • во многих случаях гораздо дешевле собрать требуемые идентификаторы строк в требуемом порядке в подзапросе и присоединить результат подзапроса к идентификатору. Поскольку для каждой строки в результате подзапроса MySQL потребуется отдельная выборка, основанная на первичном ключе, подзапрос будет помещен первым в объединении, а строки будут выведены в порядке идентификаторов в подзапросе (если мы опускаем явный ORDER BY для объединения)

3 способа, которыми MySQL использует индексы, - отличная статья для понимания некоторых деталей.

Решение 1

Этот невероятно быстрый, он занимает около 0,8 секунд на моих строках 18M +:

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

Если вы хотите изменить порядок на ASC, поместите его в подзапрос, верните только идентификаторы и используйте его в качестве подзапроса для соединения с остальными столбцами:

SELECT test_id, request_id
FROM (
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

Это занимает около 1,2 секунд на моих данных.

Решение 2

Вот еще одно решение, которое занимает около 19 секунд для моего стола:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

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

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

144
28.01.2020 00:47:27
Пожалуйста, дайте ссылку на дамп ваших таблиц, чтобы люди могли проверить его на своих платформах.
Pacerier 3.02.2015 03:44:42
Решение 1 не может работать, вы не можете выбрать REQUEST_ID без , что в группе по статье,
giò 9.03.2017 09:57:16
@ giò, этому ответу 5 лет. До MySQL 5.7.5 ONLY_FULL_GROUP_BY был отключен по умолчанию, и это решение работало из коробки dev.mysql.com/doc/relnotes/mysql/5.7/en/… . Теперь я не уверен, что решение все еще работает, когда вы отключаете режим, потому что реализация GROUP BY была изменена.
newtover 31.03.2017 14:58:37
Если бы вы хотели ASC в первом решении, сработало бы, если бы вы переключили MAX на MIN?
Jin 9.05.2017 15:45:58
@JinIzzraeel, у вас по умолчанию MIN в верхней части каждой группы (это порядок индекса покрытия): SELECT test_id, request_id FROM testresults GROUP BY test_id;будет возвращен минимальный request_id для каждого test_id.
newtover 9.05.2017 20:10:05

Я пришел к другому решению: получить идентификаторы для последнего сообщения в каждой группе, а затем выбрать из таблицы сообщений, используя результат первого запроса в качестве аргумента для 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.

53
20.02.2012 21:46:38
Просто убедитесь, что у вас есть индекс (имя, идентификатор).
Samuel Åslund 22.04.2016 11:58:45
Намного лучше, что я присоединяюсь
anwerj 23.12.2016 07:40:49
Я узнал кое-что от вас, что это хорошая работа, и этот запрос быстрее
Humphrey 23.02.2018 07:48:08

Я еще не тестировал большие БД, но думаю, что это может быть быстрее, чем объединение таблиц:

SELECT *, Max(Id) FROM messages GROUP BY Name
6
14.02.2013 07:07:11
Это возвращает произвольные данные. Другими словами, возвращаемые столбцы могут отсутствовать в записи с MAX (Id).
harm 3.07.2014 15:05:51
Полезно для выбора максимального Id из набора записей с условием WHERE: «SELECT Max (Id) FROM Prod WHERE Pn = '" + Pn + "'" Возвращает максимальный Id из набора записей с тем же Pn.In c # используйте reader.GetString (0), чтобы получить результат
Nicola 8.04.2015 09:24:07

Решение по подзапросу скрипта Ссылка

select * from messages where id in
(select max(id) from messages group by Name)

Решение по условной соединительной ссылке

select m1.* from messages m1 
left outer join messages m2 
on ( m1.id<m2.id and m1.name=m2.name )
where m2.id is null

Причина этого поста - дать ссылку на скрипку. Тот же SQL уже предоставлен в других ответах.

33
25.12.2013 08:36:42
@AlexanderSuraphel mysql5.5 теперь недоступен в скрипте, ссылка скрипта была создана с использованием этого. Теперь дневная скрипка поддерживает mysql5.6, я изменил базу данных на mysql 5.6 и смог построить схему и запустить sql.
Vipin 4.07.2018 17:21:14

Вот еще один способ получить последнюю связанную запись с GROUP_CONCATпомощью order by и SUBSTRING_INDEXвыбрать одну из записей из списка

SELECT 
  `Id`,
  `Name`,
  SUBSTRING_INDEX(
    GROUP_CONCAT(
      `Other_Columns` 
      ORDER BY `Id` DESC 
      SEPARATOR '||'
    ),
    '||',
    1
  ) Other_Columns 
FROM
  messages 
GROUP BY `Name` 

Вышеупомянутый запрос сгруппирует все те, Other_Columnsкоторые находятся в той же Nameгруппе, и использование ORDER BY id DESCобъединит все Other_Columnsв определенной группе в порядке убывания с предоставленным разделителем в моем случае, который я использовал ||, используя SUBSTRING_INDEXболее этот список выберет первый

Скрипка Демо

5
30.03.2014 06:01:52
Имейте в виду, что group_concat_max_lenограничивает количество строк, которые вы можете обрабатывать.
Rick James 28.01.2020 00:55:33
SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;
4
4.05.2014 11:38:30
Не могли бы вы немного рассказать о своем ответе? Почему ваш запрос предпочтительнее оригинального запроса Vijays?
janfoeh 4.05.2014 11:57:54

Здравствуйте, @Vijay Dev, если в ваших табличных сообщениях есть Id, который является первичным ключом с автоматическим приращением, тогда для получения самой последней записи на основе первичного ключа, который ваш запрос должен прочитать следующим образом:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId
3
21.10.2014 14:08:16
Это один быстрый я нашел
CORSAIR 10.04.2019 12:01:29

Вы также можете посмотреть отсюда.

http://sqlfiddle.com/#!9/ef42b/9

ПЕРВОЕ РЕШЕНИЕ

SELECT d1.ID,Name,City FROM Demo_User d1
INNER JOIN
(SELECT MAX(ID) AS ID FROM Demo_User GROUP By NAME) AS P ON (d1.ID=P.ID);

ВТОРОЕ РЕШЕНИЕ

SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;
3
28.09.2015 09:07:12

Если вам нужна последняя строка для каждой Name, вы можете присвоить номер каждой группе строк по Nameпорядку и Idв порядке убывания.

QUERY

SELECT t1.Id, 
       t1.Name, 
       t1.Other_Columns
FROM 
(
     SELECT Id, 
            Name, 
            Other_Columns,
    (
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
)t1
WHERE t1.rn = 1
ORDER BY t1.Id;

SQL Fiddle

2
19.11.2015 04:36:11
select * from messages group by name desc
0
18.06.2016 14:21:07

Как насчет этого:

SELECT DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;

У меня была похожая проблема (на жестком postgresql) и в таблице записей 1M. Это решение занимает 1,7 с против 44 с, созданных LEFT JOIN. В моем случае мне пришлось отфильтровать соответствующий компонент вашего имени по значениям NULL, что привело к еще лучшей производительности на 0,2 с.

2
30.11.2016 10:50:40

Вот мое решение:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;
4
8.06.2017 19:03:49
Это не возвращает последнее сообщение по имени. И это просто слишком сложная версия SELECT NAME, MAX(MESSAGES) MESSAGES FROM MESSAGE GROUP BY NAME.
Paul Spiegel 10.12.2019 14:31:26
Кроме того, эта формулировка крайне неэффективна.
Rick James 28.01.2020 00:44:06

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

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
8
10.03.2018 20:33:11
Это предполагает, idчто заказан так, как вам нужно. В общем случае нужен какой-то другой столбец.
Rick James 28.01.2020 00:51:44

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

Иногда нам нужно делать это с таблицами с более чем 60 миллионами строк.

В этих примерах я буду использовать данные только с примерно 1,5 миллионами строк, где запросам нужно будет найти результаты для всех групп данных. В наших реальных случаях нам часто приходилось возвращать данные примерно из 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)); 

Таблица температур содержит около 1,5 миллиона случайных записей и 100 различных групп. Selected_group заполняется этими 100 группами (в наших случаях это обычно составляет менее 20% для всех групп).

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

Если гипотетически MySQL имеет функцию last (), которая возвращает значения из последней строки в специальном предложении ORDER BY, то мы можем просто сделать:

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;

который должен был бы изучить только несколько 100 строк в этом случае, поскольку он не использует ни одну из обычных функций GROUP BY. Это будет выполнено за 0 секунд и, следовательно, будет очень эффективным. Обратите внимание, что обычно в MySQL мы видим предложение ORDER BY, следующее за предложением GROUP BY, однако это предложение ORDER BY используется для определения ORDER для функции last (), если это было после GROUP BY, то это было бы упорядочением GROUPS. Если предложение GROUP BY отсутствует, то последние значения будут одинаковыми во всех возвращаемых строках.

Однако в 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 и немного дольше на 8.0.4-rc

Пример 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;

Это проверило 1505,331 рядов и заняло ~ 1,25 секунды на 5.7.21 и немного дольше на 8.0.4-rc

Пример 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 секунды на 5.7.21 и немного дольше на 8.0.4-rc

Пример 4

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

Это проверило 6,137,810 строк и заняло ~ 2,2 секунды на 5.7.21 и немного дольше на 8.0.4-rc

Пример 5

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

Это проверило 6 017 808 строк и заняло ~ 4,2 секунды на 8.0.4-rc

Пример 6

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM (
  SELECT 
    last_value(t2.id) OVER w AS id, 
    t2.groupID, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID = g.id
  WINDOW w AS (
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, t2.id 
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  )
) t1
GROUP BY t1.groupID;

Это проверило 6 017 908 строк и заняло ~ 17.5 секунд на 8.0.4-rc

Пример 7

SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID = g.id
LEFT JOIN temperature t2 
  ON t2.groupID = g.id 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND t2.id > t1.id)
  )
WHERE t2.id IS NULL
ORDER BY t1.groupID;

Этот брал навсегда, поэтому мне пришлось его убить.

5
30.04.2018 06:20:59
Это другая проблема. И решением является огромный запрос UNION ALL.
Paul Spiegel 10.12.2019 14:46:52
@PaulSpiegel Я думаю, вы шутите насчет огромного Союза ВСЕХ. Помимо того факта, что нужно знать все выбранные группы заранее и что с 2000 выбранными группами это будет невероятно большой запрос, он будет работать даже хуже, чем самый быстрый пример выше, так что нет, это не будет решение.
Yoseph 12.12.2019 10:06:00
Я абсолютно серьезен. Я проверял это в прошлом с парой сотен групп. Когда вам нужно обрабатывать связи в больших группах, UNION ALL - единственный способ в MySQL создать оптимальный план выполнения. SELECT DISTINCT(groupID)это быстро и даст вам все данные, которые вам нужны для построения такого запроса. Вы должны быть в порядке с размером запроса, пока он не превышает max_allowed_packet, который по умолчанию равен 4 МБ в MySQL 5.7.
Paul Spiegel 12.12.2019 17:40:50

Если производительность действительно важна, вы можете ввести в таблицу новый столбец с именем IsLastInGroupBIT.

Установите значение true в столбцах, которые являются последними, и сохраняйте его для каждой строки вставки / обновления / удаления. Запись будет медленнее, но вы получите пользу от чтения. Это зависит от вашего варианта использования, и я рекомендую его, только если вы ориентированы на чтение.

Таким образом, ваш запрос будет выглядеть так:

SELECT * FROM Messages WHERE IsLastInGroup = 1
1
2.05.2018 15:05:59
В некоторых таблицах в Moodle есть такой столбец флага.
Lawrence 26.08.2019 19:10:51
SELECT * FROM table_name WHERE primary_key IN (SELECT MAX(primary_key) FROM table_name GROUP BY column_name )
3
30.08.2019 00:19:24

Вы можете группировать путем подсчета, а также получить последний элемент группы, как:

SELECT 
    user,
    COUNT(user) AS count,
    MAX(id) as last
FROM request 
GROUP BY user
0
7.04.2019 06:26:53

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

id category_id post_title

1 1 Title 1

2 1 Title 2

3 1 Title 3

4 2 Title 4

5 2 Title 5

6 3 Title 6

Я хочу иметь возможность получать последние сообщения в каждой категории: «Заголовок 3», «Заголовок 5» и «Заголовок 6.». Чтобы получить посты по категориям, вы будете использовать клавиатуру MySQL Group By.

select * from posts group by category_id

Но результаты, которые мы получаем от этого запроса, таковы.

id category_id post_title

1 1 Title 1

4 2 Title 4

6 3 Title 6

Группировка по всегда возвращает первую запись в группе в наборе результатов.

SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );

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

id category_id post_title

3 1 Title 3

5 2 Title 5

6 3 Title 6

Ссылка Нажмите здесь

5
24.05.2019 05:37:02

**

Привет, этот запрос может помочь:

**

SELECT 
  *
FROM 
  message 

WHERE 
  `Id` IN (
    SELECT 
      MAX(`Id`) 
    FROM 
      message 
    GROUP BY 
      `Name`
  ) 
ORDER BY 
   `Id` DESC
3
17.10.2019 10:18:49

Другой подход:

Найдите свойство с max m2_price в каждой программе (n свойств в 1 программе):

select * from properties p
join (
    select max(m2_price) as max_price 
    from properties 
    group by program_id
) p2 on (p.program_id = p2.program_id)
having p.m2_price = max_price
0
20.02.2020 10:42:06

Надеюсь, что ниже Oracle запрос может помочь:

WITH Temp_table AS
(
    Select id, name, othercolumns, ROW_NUMBER() over (PARTITION BY name ORDER BY ID 
    desc)as rank from messages
)
Select id, name,othercolumns from Temp_table where rank=1
0
15.01.2020 07:44:02