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

4szc88ey  于 2021-06-21  发布在  Mysql
关注(0)|答案(11)|浏览(349)

有一张table 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

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

ar7v8xwq

ar7v8xwq1#

子查询fiddle链接解决方案

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

用连接条件fiddle-link求解

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。

mwg9r5ms

mwg9r5ms2#

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

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

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

4urapxun

4urapxun3#

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米以上的行:

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

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

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个最大行的想法。
查询的缺点是查询缓存无法缓存其结果。

t8e9dugd

t8e9dugd4#

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

SELECT *, Max(Id) FROM messages GROUP BY Name
aemubtdh

aemubtdh5#

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

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数据转储的副本。我会用它来做基准测试。有1114357行 Posts table。这是运行在MySQL5.0.75在我的MacBookPro 2.40ghz。
我将编写一个查询来查找给定用户id(我的)的最新文章。
首先使用@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;
e1xvtsh3

e1xvtsh36#

下面是另一种使用 GROUP_CONCAT 和订单 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 在这张单子上挑第一张

小提琴演示

13z8s7eq

13z8s7eq7#

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

温度表中有大约150万条随机记录,有100个不同的组。所选的组由这100个组填充(在我们的案例中,对于所有组,这通常小于20%)。
由于此数据是随机的,这意味着多行可以具有相同的RecordedTimeStamp。我们想要的是按照groupid的顺序得到所有选定组的列表,每个组的最后一个recordedtimestamp,如果同一个组有多个这样的匹配行,那么这些行的最后一个匹配id。
如果假设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;

在这种情况下,它只需要检查几百行,因为它不使用任何正常的groupby函数。这将在0秒内执行,因此效率很高。请注意,通常在mysql中,我们会看到一个order by子句紧跟在group by子句之后,但是这个order by子句用于确定last()函数的顺序,如果它在group by之后,那么它将对组进行排序。如果不存在GROUPBY子句,则所有返回行中的最后值都将相同。
然而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
);

这检查了3009254行,在5.7.21上花费了约0.859秒,在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;

这检查了1505331行,在5.7.21上花费了约1.25秒,在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;

这检查了3009685行,在5.7.21上花费了约1.95秒,在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
    )
);

这检查了6137810行,在5.7.21上花费了约2.2秒,在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;

这检查了6017808行,在8.0.4-rc上耗时约4.2秒
例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;

这个检查

oxosxuxt

oxosxuxt8#

速度相当快的方法如下。

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
4xy9mtcn

4xy9mtcn9#

这里有两条建议。首先,如果mysql支持row\u 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;

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

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

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

4smxwvx5

4smxwvx510#

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

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

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

p5cysglq

p5cysglq11#

我们将研究如何使用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 参考点击这里

相关问题