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


Answers

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)=@group:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

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

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

Question

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

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




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



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

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



如果您想为每个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 
  DISTINCT NAME,
  MAX(MESSAGES) OVER(PARTITION BY NAME) MESSAGES 
FROM MESSAGE;



select * from messages group by name desc



我到达了一个不同的解决方案,即获取每个组中最后一篇文章的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上使用。




您也可以从这里观看。

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 ;



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

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;



很明显,获得相同结果有很多不同的方法,您的问题似乎是在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;

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




Links