检索每个组中的最后一条记录-mysql

rpppsulh  于 2021-06-20  发布在  Mysql
关注(0)|答案(11)|浏览(355)

有一张table messages 包含如下数据:

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

如果我运行查询 select * from messages group by name ,我将得到如下结果:

  1. 1 A A_data_1
  2. 4 B B_data_1
  3. 6 C C_data_1

什么查询将返回以下结果?

  1. 3 A A_data_3
  2. 5 B B_data_2
  3. 6 C C_data_1

也就是说,应该返回每组中的最后一条记录。
目前,我使用的查询是:

  1. SELECT
  2. *
  3. FROM (SELECT
  4. *
  5. FROM messages
  6. ORDER BY id DESC) AS x
  7. GROUP BY name

但这看起来效率很低。还有其他方法可以达到同样的效果吗?

sqyvllje

sqyvllje1#

mysql 8.0现在支持窗口功能,就像几乎所有流行的sql实现一样。使用此标准语法,我们可以编写最大n个每组查询:

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

以下是我在2009年为这个问题写的原始答案:
我这样写解:

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

关于性能,根据数据的性质,一种解决方案或另一种解决方案可能更好。因此,您应该测试这两个查询,并在给定数据库的情况下使用性能更好的查询。
例如,我有一个stackoverflow数据转储的副本。我会用它来做基准测试。有1114357行 Posts table。这是运行在MySQL5.0.75在我的MacBookPro 2.40ghz。
我将编写一个查询来查找给定用户id(我的)的最新文章。
首先使用@eric展示的技术 GROUP BY 在子查询中:

  1. SELECT p1.postid
  2. FROM Posts p1
  3. INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
  4. FROM Posts pi GROUP BY pi.owneruserid) p2
  5. ON (p1.postid = p2.maxpostid)
  6. WHERE p1.owneruserid = 20860;
  7. 1 row in set (1 min 17.89 sec)

即使是 EXPLAIN 分析耗时超过16秒:

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

现在使用我的技术生成相同的查询结果 LEFT JOIN :

  1. SELECT p1.postid
  2. FROM Posts p1 LEFT JOIN posts p2
  3. ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
  4. WHERE p2.postid IS NULL AND p1.owneruserid = 20860;
  5. 1 row in set (0.28 sec)

这个 EXPLAIN 分析表明,两个表都可以使用它们的索引:

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

这是我的ddl Posts 表格:

  1. CREATE TABLE `posts` (
  2. `PostId` bigint(20) unsigned NOT NULL auto_increment,
  3. `PostTypeId` bigint(20) unsigned NOT NULL,
  4. `AcceptedAnswerId` bigint(20) unsigned default NULL,
  5. `ParentId` bigint(20) unsigned default NULL,
  6. `CreationDate` datetime NOT NULL,
  7. `Score` int(11) NOT NULL default '0',
  8. `ViewCount` int(11) NOT NULL default '0',
  9. `Body` text NOT NULL,
  10. `OwnerUserId` bigint(20) unsigned NOT NULL,
  11. `OwnerDisplayName` varchar(40) default NULL,
  12. `LastEditorUserId` bigint(20) unsigned default NULL,
  13. `LastEditDate` datetime default NULL,
  14. `LastActivityDate` datetime default NULL,
  15. `Title` varchar(250) NOT NULL default '',
  16. `Tags` varchar(150) NOT NULL default '',
  17. `AnswerCount` int(11) NOT NULL default '0',
  18. `CommentCount` int(11) NOT NULL default '0',
  19. `FavoriteCount` int(11) NOT NULL default '0',
  20. `ClosedDate` datetime default NULL,
  21. PRIMARY KEY (`PostId`),
  22. UNIQUE KEY `PostId` (`PostId`),
  23. KEY `PostTypeId` (`PostTypeId`),
  24. KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  25. KEY `OwnerUserId` (`OwnerUserId`),
  26. KEY `LastEditorUserId` (`LastEditorUserId`),
  27. KEY `ParentId` (`ParentId`),
  28. CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)
  29. ) ENGINE=InnoDB;
展开查看全部
kq4fsx7k

kq4fsx7k2#

upd:2017-03-31,mysql 5.7.5版本默认启用了唯一的\u full \u group \u by开关(因此,禁用了不确定的groupby查询)。此外,他们通过实现更新了组,即使禁用了交换机,解决方案也可能不再按预期工作。一个人需要检查一下。
当组内的项计数非常小时,bill karwin的上述解决方案可以很好地工作,但是当组非常大时,查询的性能会变差,因为该解决方案需要大约 n*n/2 + n/2 仅限 IS NULL 比较。
我在innodb的 18684446 带的行 1182 组。该表包含功能测试的testresults,并具有 (test_id, request_id) 作为主键。因此, test_id 是一个小组,我在寻找最后一个 request_id 对于每个 test_id .
bill的解决方案已经在我的dell e4310上运行了几个小时,我不知道它什么时候才能完成,即使它在覆盖率索引上运行(因此 using index 在解释中)。
我有几个基于相同想法的其他解决方案:
如果基础索引是btree index(通常是这种情况),则 (group_id, item_value) pair是每个 group_id ,这是每个 group_id 如果我们按降序遍历索引;
如果我们读取索引所覆盖的值,则这些值将按索引的顺序读取;
每个索引都隐式地包含附加到该索引的主键列(即主键位于覆盖率索引中)。在下面的解决方案中,我直接对主键进行操作,在您的情况下,您只需要在结果中添加主键列。
在许多情况下,在子查询中按所需顺序收集所需的行id并将子查询的结果连接到id上要便宜得多。因为对于子查询结果中的每一行,mysql都需要一个基于主键的获取,子查询将放在联接中的第一位,行将按照子查询中id的顺序输出(如果我们省略联接的显式order by)
mysql使用索引的3种方法是一篇很好的文章,可以帮助您了解一些细节。
解决方案1
这是一个难以置信的速度,它需要约0.8秒我的18米以上的行:

  1. SELECT test_id, MAX(request_id) AS request_id
  2. FROM testresults
  3. GROUP BY test_id DESC;

如果要将顺序更改为asc,请将其放入子查询中,仅返回id,并将其用作子查询以联接到其余列:

  1. SELECT test_id, request_id
  2. FROM (
  3. SELECT test_id, MAX(request_id) AS request_id
  4. FROM testresults
  5. GROUP BY test_id DESC) as ids
  6. ORDER BY test_id;

这一次我的数据大约需要1,2秒。
解决方案2
下面是另一个需要19秒的解决方案:

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

它还按降序返回测试。它的速度要慢得多,因为它会执行完整的索引扫描,但这里提供了一个如何为每个组输出n个最大行的想法。
查询的缺点是查询缓存无法缓存其结果。

展开查看全部
dced5bon

dced5bon3#

子查询fiddle链接解决方案

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

用连接条件fiddle-link求解

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

这个职位的原因是给小提琴链接只。其他答案中已经提供了相同的sql。

kb5ga3dv

kb5ga3dv4#

我还没有使用大型数据库进行测试,但我认为这可能比连接表更快:

  1. SELECT *, Max(Id) FROM messages GROUP BY Name
mwngjboj

mwngjboj5#

使用子查询返回正确的分组,因为已经完成了一半。
试试这个:

  1. select
  2. a.*
  3. from
  4. messages a
  5. inner join
  6. (select name, max(id) as maxid from messages group by name) as b on
  7. a.id = b.maxid

如果不是的话 id 你想要最大值:

  1. select
  2. a.*
  3. from
  4. messages a
  5. inner join
  6. (select name, max(other_col) as other_col
  7. from messages group by name) as b on
  8. a.name = b.name
  9. and a.other_col = b.other_col

通过这种方式,您可以避免子查询中的相关子查询和/或排序,这往往非常缓慢/低效。

展开查看全部
d6kp6zgx

d6kp6zgx6#

我们将研究如何使用mysql获取一组记录中的最后一条记录。例如,如果你有这个结果集的职位。

身份证类别身份证职务

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 keyboard。 select * from posts group by category_id 但是我们从这个查询中得到的结果是。

身份证类别身份证职务 1 1 Title 1 4 2 Title 4 6 3 Title 6 groupby将始终返回结果集中组中的第一条记录。 SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id ); 这将返回每个组中ID最高的帖子。

身份证类别身份证职务 3 1 Title 3 5 2 Title 5 6 3 Title 6 参考点击这里

wz3gfoph

wz3gfoph7#

下面是另一种使用 GROUP_CONCAT 和订单 SUBSTRING_INDEX 从列表中选择一条记录

  1. SELECT
  2. `Id`,
  3. `Name`,
  4. SUBSTRING_INDEX(
  5. GROUP_CONCAT(
  6. `Other_Columns`
  7. ORDER BY `Id` DESC
  8. SEPARATOR '||'
  9. ),
  10. '||',
  11. 1
  12. ) Other_Columns
  13. FROM
  14. messages
  15. GROUP BY `Name`

上面的查询将对所有 Other_Columns 在同一个地方 Name 分组和使用 ORDER BY id DESC 将加入所有 Other_Columns 在特定的组中按降序排列,在我的例子中使用了分隔符 || ,使用 SUBSTRING_INDEX 在这张单子上挑第一张

小提琴演示

展开查看全部
kninwzqo

kninwzqo8#

这里有两条建议。首先,如果mysql支持row\u number(),那么非常简单:

  1. WITH Ranked AS (
  2. SELECT Id, Name, OtherColumns,
  3. ROW_NUMBER() OVER (
  4. PARTITION BY Name
  5. ORDER BY Id DESC
  6. ) AS rk
  7. FROM messages
  8. )
  9. SELECT Id, Name, OtherColumns
  10. FROM messages
  11. WHERE rk = 1;

我猜“最后”是指身份证顺序中的最后一个。如果不是,则相应地更改row\ u number()窗口的order by子句。如果行号()不可用,这是另一种解决方案:
其次,如果没有,这通常是一个很好的方法:

  1. SELECT
  2. Id, Name, OtherColumns
  3. FROM messages
  4. WHERE NOT EXISTS (
  5. SELECT * FROM messages as M2
  6. WHERE M2.Name = messages.Name
  7. AND M2.Id > messages.Id
  8. )

换言之,选择没有相同名称的后续id消息的消息。

展开查看全部
6bc51xsx

6bc51xsx9#

我找到了一个不同的解决方案,即获取每个组中最后一篇文章的id,然后使用第一个查询的结果作为参数从messages表中进行选择 WHERE x IN 构造:

  1. SELECT id, name, other_columns
  2. FROM messages
  3. WHERE id IN (
  4. SELECT MAX(id)
  5. FROM messages
  6. GROUP BY name
  7. );

我不知道与其他一些解决方案相比,它的性能如何,但是对于我的300多万行的表来说,它的工作非常出色(4秒执行(1200+个结果)
这应该可以在mysql和sqlserver上使用。

4szc88ey

4szc88ey10#

速度相当快的方法如下。

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

结果

  1. Id Name Other_Columns
  2. 3 A A_data_3
  3. 5 B B_data_2
  4. 6 C C_data_1
gkn4icbw

gkn4icbw11#

显然,有很多不同的方法可以得到相同的结果,你的问题似乎是什么是一种有效的方法来获得mysql中每个组的最后一个结果。如果您使用的是大量数据,并且假设您使用的是innodb,甚至是最新版本的mysql(如5.7.21和8.0.4-rc),那么可能没有一种有效的方法来实现这一点。
我们有时需要对超过6000万行的表执行此操作。
对于这些示例,我将使用只有大约150万行的数据,其中查询需要查找数据中所有组的结果。在我们的实际案例中,我们通常需要返回大约2000组的数据(假设不需要检查太多数据)。
我将使用以下表格:

  1. CREATE TABLE temperature(
  2. id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3. groupID INT UNSIGNED NOT NULL,
  4. recordedTimestamp TIMESTAMP NOT NULL,
  5. recordedValue INT NOT NULL,
  6. INDEX groupIndex(groupID, recordedTimestamp),
  7. PRIMARY KEY (id)
  8. );
  9. CREATE TEMPORARY TABLE selected_group(id INT UNSIGNED NOT NULL, PRIMARY KEY(id));

温度表中有大约150万条随机记录,有100个不同的组。所选的组由这100个组填充(在我们的案例中,对于所有组,这通常小于20%)。
由于此数据是随机的,这意味着多行可以具有相同的RecordedTimeStamp。我们想要的是按照groupid的顺序得到所有选定组的列表,每个组的最后一个recordedtimestamp,如果同一个组有多个这样的匹配行,那么这些行的最后一个匹配id。
如果假设mysql有一个last()函数,它以特殊的order by子句返回最后一行的值,那么我们可以简单地执行以下操作:

  1. SELECT
  2. last(t1.id) AS id,
  3. t1.groupID,
  4. last(t1.recordedTimestamp) AS recordedTimestamp,
  5. last(t1.recordedValue) AS recordedValue
  6. FROM selected_group g
  7. INNER JOIN temperature t1 ON t1.groupID = g.id
  8. ORDER BY t1.recordedTimestamp, t1.id
  9. GROUP BY t1.groupID;

在这种情况下,它只需要检查几百行,因为它不使用任何正常的groupby函数。这将在0秒内执行,因此效率很高。请注意,通常在mysql中,我们会看到一个order by子句紧跟在group by子句之后,但是这个order by子句用于确定last()函数的顺序,如果它在group by之后,那么它将对组进行排序。如果不存在GROUPBY子句,则所有返回行中的最后值都将相同。
然而mysql没有这样的功能,所以让我们看看它有什么不同的想法,并证明这些都不是有效的。
例1

  1. SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
  2. FROM selected_group g
  3. INNER JOIN temperature t1 ON t1.id = (
  4. SELECT t2.id
  5. FROM temperature t2
  6. WHERE t2.groupID = g.id
  7. ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  8. LIMIT 1
  9. );

这检查了3009254行,在5.7.21上花费了约0.859秒,在8.0.4-rc上花费了稍长的时间
例2

  1. SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
  2. FROM temperature t1
  3. INNER JOIN (
  4. SELECT max(t2.id) AS id
  5. FROM temperature t2
  6. INNER JOIN (
  7. SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
  8. FROM selected_group g
  9. INNER JOIN temperature t3 ON t3.groupID = g.id
  10. GROUP BY t3.groupID
  11. ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  12. GROUP BY t2.groupID
  13. ) t5 ON t5.id = t1.id;

这检查了1505331行,在5.7.21上花费了约1.25秒,在8.0.4-rc上花费了稍长的时间
例3

  1. SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
  2. FROM temperature t1
  3. WHERE t1.id IN (
  4. SELECT max(t2.id) AS id
  5. FROM temperature t2
  6. INNER JOIN (
  7. SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
  8. FROM selected_group g
  9. INNER JOIN temperature t3 ON t3.groupID = g.id
  10. GROUP BY t3.groupID
  11. ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  12. GROUP BY t2.groupID
  13. )
  14. ORDER BY t1.groupID;

这检查了3009685行,在5.7.21上花费了约1.95秒,在8.0.4-rc上花费了稍长的时间
例4

  1. SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
  2. FROM selected_group g
  3. INNER JOIN temperature t1 ON t1.id = (
  4. SELECT max(t2.id)
  5. FROM temperature t2
  6. WHERE t2.groupID = g.id AND t2.recordedTimestamp = (
  7. SELECT max(t3.recordedTimestamp)
  8. FROM temperature t3
  9. WHERE t3.groupID = g.id
  10. )
  11. );

这检查了6137810行,在5.7.21上花费了约2.2秒,在8.0.4-rc上花费了稍长的时间
例5

  1. SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
  2. FROM (
  3. SELECT
  4. t2.id,
  5. t2.groupID,
  6. t2.recordedTimestamp,
  7. t2.recordedValue,
  8. row_number() OVER (
  9. PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, t2.id DESC
  10. ) AS rowNumber
  11. FROM selected_group g
  12. INNER JOIN temperature t2 ON t2.groupID = g.id
  13. ) t1 WHERE t1.rowNumber = 1;

这检查了6017808行,在8.0.4-rc上耗时约4.2秒
例6

  1. SELECT t1.id, t1.groupID, t1.recordedTimestamp, t1.recordedValue
  2. FROM (
  3. SELECT
  4. last_value(t2.id) OVER w AS id,
  5. t2.groupID,
  6. last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp,
  7. last_value(t2.recordedValue) OVER w AS recordedValue
  8. FROM selected_group g
  9. INNER JOIN temperature t2 ON t2.groupID = g.id
  10. WINDOW w AS (
  11. PARTITION BY t2.groupID
  12. ORDER BY t2.recordedTimestamp, t2.id
  13. RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  14. )
  15. ) t1
  16. GROUP BY t1.groupID;

这个检查

展开查看全部

相关问题