group-by mysql查询最后一条记录 - 检索每个组中的最后一条记录-MySQL





mysql分组最新一条 mysql获取每天最后一条数据 (17)


有没有什么办法可以用这种方法删除表中的重复项? 结果集基本上是唯一记录的集合,因此如果我们可以删除不在结果集中的所有记录,那么我们实际上不会有重复记录? 我试过这个,但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(临时变量)中删除? @Bill感谢您提供非常有用的解决方案。

编辑:认为我找到了解决方案:

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);

有一个表格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运行查询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

但是这看起来非常低效。 任何其他方式来实现相同的结果?




很明显,获得相同结果有很多不同的方法,您的问题似乎是在MySQL中获取每个组的最后结果的有效方式。 如果您正在处理大量数据,并且假设您使用InnoDB以及最新版本的MySQL(例如5.7.21和8.0.4-rc),那么可能没有这样做的有效方式。

我们有时需要使用超过6000万行的表格来完成此操作。

对于这些示例,我将使用只有大约150万行的数据,查询需要查找数据中所有组的结果。 在我们的实际案例中,我们经常需要返回大约2,000组的数据(假设不需要检查非常多的数据)。

我将使用下列表格:

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个不同的组。 selected_group填充了100个组(在我们的例子中,对于所有的组,这通常会少于20%)。

由于该数据是随机的,这意味着多行可以具有相同的记录时间戳。 我们想要的是按照groupID的顺序获得所有选定组的列表,并为每个组记录最后一次记录的时间戳,并且如果同一组具有多于一个的匹配行,那么这些行的最后一个匹配的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;

在这种情况下只需要检查几行100行,因为它不使用任何正常的GROUP BY函数。 这将在0秒内执行,因此效率很高。 请注意,通常在MySQL中,我们将在GROUP BY子句后面看到一个ORDER BY子句,但是此ORDER BY子句用于确定last()函数的ORDER,如果它在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行,在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;

这检查了1,505,331行,在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;

这检查了3,009,685行,在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
    )
);

这检查了6,137,810行,在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;

这检查了6,017,808行,并在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;

这检查了6,017,908行,并在8.0.4-rc上花费了大约17.5秒

例子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;

这一个是永远的,所以我不得不杀死它。




您好@Vijay开发如果您的表消息包含ID是自动递增主键然后获取主键最新的记录基础您的查询应该读取如下:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId



UPD:2017-03-31,MySQL 5.7.5版本默认启用了ONLY_FULL_GROUP_BY开关(因此,非确定性GROUP BY查询被禁用)。 此外,他们更新了GROUP BY实施,即使使用禁用的开关,该解决方案也可能无法按预期工作。 一个需要检查。

上面的Bill Karwin解决方案在组内的项数很小时工作正常,但当组较大时,查询性能变差,因为解决方案仅需要大约n*n/2 + n/2IS NULL比较。

我在1182组的18684446行的InnoDB表上做了测试。 该表包含功能测试的测试结果,并具有(test_id, request_id)作为主键。 因此, test_id是一个组,我正在为每个test_id搜索最后一个request_id

比尔的解决方案已经在我的戴尔e4310上运行了几个小时,我不知道它何时完成,即使它在覆盖索引上运行(因此在EXPLAIN中using index )。

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

  • 如果底层索引是BTREE索引(通常是这种情况),则最大(group_id, item_value)对是每个group_id的最后一个值,如果我们按降序浏览索引,那么这是每个group_id第一个值;
  • 如果我们读取索引覆盖的值,则会按照索引的顺序读取值;
  • 每个索引隐含地包含附加到该索引的主键列(即主键在覆盖索引中)。 在下面的解决方案中,我直接在主键上操作,在这种情况下,您只需要在结果中添加主键列。
  • 在许多情况下,在子查询中按要求的顺序收集所需的行标识并将ID添加到子查询的结果上要便宜得多。 由于对于子查询中的每一行结果,MySQL将需要基于主键的单次获取,因此子查询将首先放入连接,并且这些行将按照子查询中ID的顺序输出(如果我们省略显式ORDER BY为加入)

MySQL使用索引的3种方式是理解一些细节的好文章。

解决方案1

这个速度非常快,我的18M +行大概需要0.8秒。

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

如果要将订单更改为ASC,请将其置于子查询中,仅返回id并将其用作子查询以加入其余列:

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)[email protected]:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

它也以降序的方式返回测试。 由于它进行了完整的索引扫描,速度要慢得多,但它可以让您知道如何为每个组输出N max行。

查询的缺点是它的结果不能被查询缓存缓存。




这是我的解决方案:

SELECT 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;



SELECT 
  column1,
  column2 
FROM
  table_name 
WHERE id IN 
  (SELECT 
    MAX(id) 
  FROM
    table_name 
  GROUP BY column1) 
ORDER BY column1 ;



您也可以从这里观看。

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 ;



我到达了一个不同的解决方案,即获取每个组中最后一篇文章的ID,然后使用第一个查询的结果作为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 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;

关于性能,根据数据的性质,一种解决方案或另一种解决方案可能会更好。 因此,您应该测试两个查询,并使用数据库性能更好的查询。

例如,我有一个 August数据转储的副本。 我会用它来进行基准测试。 在Posts表中有1,114,357行。 这是在我的Macbook Pro 2.40GHz的MySQL 5.0.75上运行。

我将编写一个查询来查找给定用户ID(我的)的最新帖子。

首先在子查询中使用@Eric和GROUP BY shown的技术:

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;



下面的查询将按您的问题正常工作。

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;



具有相当速度的方法如下。

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



使用你的subquery返回正确的分组,因为你在那里。

尝试这个:

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

这样,您就避免了子查询中的相关子查询和/或排序,这往往是非常缓慢/低效的。




如果您想为每个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小提琴




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

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



这里有两点建议。 首先,如果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;

我以“最后”的形式假设你是以最后的顺序排列。 如果不是,则相应地更改ROW_NUMBER()窗口的ORDER BY子句。 如果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
)

换句话说,选择不存在具有相同名称的晚期Id消息的消息。




解决方案通过子查询提琴链接

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。




我最喜欢的答案是这个主题中的第一句话提示。 使用邻接列表来维护层次结构并使用嵌套集来查询层次结构。

迄今为止的问题是,从Adjacecy List到Nested Sets的覆盖方法一直非常缓慢,因为大多数人使用称为“Push Stack”的极端RBAR方法进行转换,并且被认为是成本昂贵的方式通过邻接列表以及Nested Sets的卓越性能来达到Nirvana的简单维护。 结果,大多数人最终不得不为其中一个或另一个而定居,特别是如果不止有100,000个左右的节点等等。 使用推式堆栈方法可能需要一整天时间才能对MLM人员认为是小型百万节点层次结构进行转换。

我想我会给Celko一点点竞争力,想出一种将邻接列表转换成嵌套集合的方法,其速度看起来不可能。 以下是我的i5笔记本电脑上的push stack方法的性能。

Duration for     1,000 Nodes = 00:00:00:870 
Duration for    10,000 Nodes = 00:01:01:783 (70 times slower instead of just 10)
Duration for   100,000 Nodes = 00:49:59:730 (3,446 times slower instead of just 100) 
Duration for 1,000,000 Nodes = 'Didn't even try this'

这里是新方法的持续时间(在括号中使用推式堆栈方法)。

Duration for     1,000 Nodes = 00:00:00:053 (compared to 00:00:00:870)
Duration for    10,000 Nodes = 00:00:00:323 (compared to 00:01:01:783)
Duration for   100,000 Nodes = 00:00:03:867 (compared to 00:49:59:730)
Duration for 1,000,000 Nodes = 00:00:54:283 (compared to something like 2 days!!!)

对,那是正确的。 在不到一分钟的时间内转换100万个节点,在4秒内转换100,000个节点。

您可以阅读有关新方法的信息,并通过以下URL获取代码的副本。 http://www.sqlservercentral.com/articles/Hierarchy/94040/

我还使用类似的方法开发了“预先聚合”的层次结构。 传销人员和制作物料清单的人对本文特别感兴趣。 http://www.sqlservercentral.com/articles/T-SQL/94570/

如果您确实停下来看看这两篇文章,请跳到“加入讨论”链接,让我知道您的想法。





sql mysql group-by greatest-n-per-group