☰

各グループの最後のレコードを取得する - MySQL 質問する

tag-icon • tag-icon mysql sql group-by greatest-n-per-group groupwise-maximum
各グループの最後のレコードを取得する - MySQL 質問する

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

しかし、これは非常に非効率に思えます。同じ結果を達成する他の方法はありますか?

ベストアンサー1

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;

これと他のアプローチグループごとの最大行MySQL マニュアルに説明されています。

以下は、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 8 月のデータダンプこれをベンチマークに使用します。Postsテーブルには1,114,357行あります。これはマイグレーション私のMacbook Pro 2.40GHzでは5.0.75です。

特定のユーザー 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)

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;

コメント投稿者への注意: 別のバージョンの MySQL、別のデータセット、または別のテーブル設計を使用した別のベンチマークが必要な場合は、ご自由にご自分で実行してください。上記で手法を示しました。Stack Overflow は、ソフトウェア開発作業のやり方を示すために存在しており、すべての作業を代わりに行うために存在しているわけではありません。

おすすめ記事