top - sql where max




檢索每個組中的最後一條記錄-MySQL (14)

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

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

但是這看起來非常低效。 任何其他方式來實現相同的結果?


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行。

查詢的缺點是它的結果不能被查詢緩存緩存。


使用你的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

這樣,您就避免了子查詢中的相關子查詢和/或排序,這往往是非常緩慢/低效的。


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

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

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


很明顯,獲得相同結果有很多不同的方法,您的問題似乎是在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

我到達了一個不同的解決方案,即獲取每個組中最後一篇文章的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給了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);

解決方案通過子查詢提琴鏈接

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。


這是另一種使用GROUP_CONCAT獲取最後一條相關記錄的方法,其中order by和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將會選擇第一個

小提琴演示


這是我的解決方案:

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 ;

select * from messages group by name desc




greatest-n-per-group