sql - 检索每个group最后一个记录

  显示原文与译文双语对照的内容

有一个表 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

但这看起来很低效。 实现相同结果的其他方法?

时间:

我这样写解决方案:


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八月数据转储的副本。 我将用它来做基准测试。 在 Posts table,有 1,114,357 rows. 这是我的苹果笔记本上运行在 MySQL 5.0.75 2.40千兆赫。

我将编写一个查询来查找给定用户标识( 矿井)的最新日志。

在一个 subquery, 首先采用该技术由 @Eric


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)


下面是 Posts 表的DDL:


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;

比尔解决方案上面Karwin在组合中都可以正常工作当项数是十分高效的,但是该查询的性能变得糟糕时,有关的只有 IS NULLn*n/2 + n/2 组是相当大,因为该解决方案要求比较。

1182 groups, 18684446的我做了我的测试在一个MySQL表 rows. 表包含功能测试的testresults,并将 (test_id, request_id) 作为主键。 因此,test_id 是一组和我当时正在寻找的上次 request_id 每个 test_id

我已经在戴尔e4310上运行了几个小时的帐单,但不知道它什么时候会完成,即使它在覆盖索引( 因此 using index 在解释中) 上操作。

我还有一些基于相同想法的解决方案:

  • 如果基础索引是索引索引( 通常情况下),则最大的(group_id, item_value) 对是每个 group_id 中的最后一个值,如果我们按降序遍历索引,则为每个 group_id的第一个值;
  • 如果我们读取一个索引所涵盖的值,则按照索引的顺序读取值;
  • 每个索引隐式包含附加到( 主键索引中的主键)的主键列。 在下面的解决方案中,我直接在主关键字上操作,在你的情况下,你只需要在结果中添加主键列。
  • 在许多情况下,在子查询中按要求的顺序收集所需的行标识并将子查询的结果加入到标识中需要更便宜。 由于子查询结果中的每一行都需要基于主键的单个提取,所以子查询将首先在子查询中输入,然后按照子查询的顺序输出( 如果省略了显式的ORDER BY )

3 方法MySQL使用索引是一个很好的文章来理解一些细节。

解决方案 1

这是令人难以置信的快,大约需要 0,8秒我 18 M+行:


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

如果要将顺序更改为 ASC,请将它的放入子查询中,只返回一个子查询,并将它的用作联接到其余列的子查询:


SELECT test_id, request_id
FROM (
 SELECT test_id, MAX(request_id), 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马克斯行。

查询的缺点是查询缓存无法缓存它的结果。

使用你的子查询返回正确的分组,因为你正处于中途。

尝试这个:


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

这样,你可以在子查询中避免相关子查询和/或者排序,它们可能非常慢/低效。

按子查询小提琴fiddle的解决方案


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

这里贴子的原因是只给出fiddle链接。 其他的答案中已经提供了相同的SQL 。

我到达了一个不同的解决方案,它是为了获取每个组内最后一个帖子的标识,然后使用第一个查询的结果作为 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 上都能工作。

下面是两个建议。首先,如果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
)

换句话说,选择没有相同名称的later-Id消息。

尝试这个:


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 ) 

下面的查询根据你的问题可以正常工作。


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;

...