sql max seq
各グループの最後のレコードを取得する-MySQL (14)
MySQL 8.0は、ほぼすべての一般的なSQL実装のように、ウィンドウ機能をサポートしています。 この標準構文では、グループごとに最大のクエリを書くことができます:
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;
パフォーマンスに関しては、データの性質に応じて、1つの解決策または他の解決策を改善することができます。 したがって、両方のクエリをテストして、データベースに与えられたパフォーマンスが優れているクエリを使用する必要があります。
たとえば、私はStackOverflow Augustのデータダンプのコピーを持っています。 私はベンチマークのためにそれを使用します。 Posts
テーブルには1,114,357行あります。 これは私のMacBook Pro 2.40GHz上でMySQL 5.0.75で動作していMySQL 。
特定のユーザーID(私)の最新の投稿を検索するためのクエリを作成します。
最初に、サブクエリ内のGROUP BY
を使用して@Ericによって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
しかし、これは非常に非効率的に見えます。 同じ結果を達成するための他の方法はありますか?
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
それはあなたが最大のを望んでいない場合は:
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
このようにして、サブクエリでの相関サブクエリや順序付けは避けられますが、サブクエリは非常に遅く/非効率的です。
GROUP_CONCAT
を使ってorder byとSUBSTRING_INDEX
を使って最後の関連するレコードを取得する別の方法は、リストからレコードの1つを選択する方法です
SELECT
`Id`,
`Name`,
SUBSTRING_INDEX(
GROUP_CONCAT(
`Other_Columns`
ORDER BY `Id` DESC
SEPARATOR '||'
),
'||',
1
) Other_Columns
FROM
messages
GROUP BY `Name`
上記のクエリは、同じName
グループにあるすべてのOther_Columns
をグループ化し、 ORDER BY id DESC
を使用しますOther_Columns
ORDER BY id DESC
は、特定のグループ内のすべてのOther_Columns
を、指定された区切り文字で降順に結合します。 このリストのSUBSTRING_INDEX
を使用すると、最初のものが選択されます
フィドルのデモ
UPD:2017-03-31、MySQLのバージョン5.7.5では、デフォルトでONLY_FULL_GROUP_BYスイッチが有効になりました(したがって、非決定論的なGROUP BYクエリが無効になりました)。 さらに、彼らはGROUP BYの実装を更新し、ディスエーブルされたスイッチでもソリューションが期待どおりに機能しない可能性があります。 1つはチェックする必要があります。
上記のBill Karwinのソリューションは、グループ内の項目数が少ない場合には問題ありませんが、ソリューションがn*n/2 + n/2
のIS NULL
比較のみを必要とするため、グループがかなり大きい場合はクエリのパフォーマンスが悪くなりIS NULL
。
私は18684446
行のInnoDBテーブルで1182
グループのテストを行いました。 この表には、機能テストのためのテスト結果が含まれており、 (test_id, request_id)
が主キーとして含まれています。 したがって、 test_id
はグループであり、私は各test_id
最後のrequest_id
を探していました。
Billのソリューションはすでにe4310で数時間稼働していますが、カバレッジ・インデックスで動作していても終了する時期はわかりません(EXPLAINのusing index
を使用しています)。
私は、同じ考え方に基づいた他の解決策をいくつか持っています:
- 基になるインデックスがBTREEインデックス(通常はそうです)の場合、最大
(group_id, item_value)
ペアは各group_id
内の最後の値です。インデックスを降順で参照すると、group_id
それぞれの最初の値になりgroup_id
。 - インデックスでカバーされている値を読み取ると、インデックスの順に値が読み込まれます。
- 各インデックスには暗黙的にプライマリキー列が追加されています(プライマリキーはカバレッジインデックスにあります)。 以下のソリューションでは、私は主キーで直接操作します。その場合は、主キー列を結果に追加するだけです。
- 多くの場合、必要な行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
ここに私のテーブルのために約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行を出力する方法をあなたに知らせるためにここにあります。
問合せの欠点は、その結果を問合せキャッシュでキャッシュできないことです。
ここからも眺めることができます。
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);
第2の解決策
SELECT * FROM (SELECT * FROM Demo_User ORDER BY ID DESC) AS T GROUP BY NAME ;
ここに2つの提案があります。 まず、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;
私は "最後に"あなたが最後のId順であると仮定しています。 そうでなければ、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メッセージがないメッセージを選択します。
このメソッドを使用してテーブル内の重複を削除する方法はありますか? 結果セットは基本的にユニークなレコードのコレクションなので、結果セットに含まれていないすべてのレコードを削除できれば、重複はありません。 私はこれを試したが、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 DISTINCT ON (name) *
FROM messages
ORDER BY name, id DESC;
私は似たような問題(postgresql toughで)と1Mレコードテーブルを持っていました。 このソリューションはLEFT JOINを使用して作成されたものと比較して1.7秒対44秒です。 私の場合は、あなたの名前欄の代理人をNULL値と比較してフィルタリングしなければならず、結果として0.2秒でさらに優れたパフォーマンスが得られました
こんにちは@ Vijay Devあなたのテーブルメッセージが自動インクリメントのプライマリキーであるIdを含んでいたら、あなたのクエリが以下のように読むべきプライマリキーに関する最新のレコードベースをフェッチする:
SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId
パフォーマンスが本当に問題であれば、BITタイプのIsLastInGroup
というIsLastInGroup
のテーブルに新しい列を導入することができます。
最後の列ではtrueに設定し、挿入/更新/削除のたびに維持します。 書き込みは遅くなりますが、読み込みにはメリットがあります。 あなたのユースケースにもよりますが、私はあなたが読んでフォーカスを持っている場合にのみそれをお勧めします。
したがって、クエリは次のようになります。
SELECT * FROM Messages WHERE IsLastInGroup = 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 Fiddle
明らかに、同じ結果を得るにはさまざまな方法がありますが、あなたの質問は、MySQLの各グループで最後の結果を得る効率的な方法と思われます。 膨大な量のデータを扱っていて、MySQLの最新バージョン(5.7.21や8.0.4-rcなど)でInnoDBを使用していると仮定すると、効率的な方法がないかもしれません。
時には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%未満です)。
このデータはランダムなので、複数の行で同じrecordsTimestampsを持つことができます。 私たちが望むのは、選択されたすべてのグループのリストをgroupIDの順番で取得し、各グループの最後のrecordedTimestampで、同じグループに一致する行が複数存在する場合、それらの行の最後に一致するIDです。
仮にMySQLが特別なORDER BY節の最後の行から値を返すlast()関数を持っていたならば、単純に次のようにすることができます:
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;
この場合、通常のGROUP BY関数を使用しないため、わずか100行しか調べる必要がありません。 これは0秒で実行されるため、非常に効率的です。 通常MySQLでは、GROUP BY句の後にORDER BY句があることに注意してください。ただし、このORDER BY句は、GROUP BYの後にあってGROUPSの順序を指定する場合は、last()関数のORDERを決定するために使用されます。 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;
これは永遠に取っていたので、私はそれを殺さなければならなかった。
私は別のソリューションに到着しました。これは、各グループ内の最後の投稿のIDを取得し、 WHERE x IN
構文の引数として最初のクエリの結果を使用してメッセージテーブルから選択することです。
SELECT id, name, other_columns
FROM messages
WHERE id IN (
SELECT MAX(id)
FROM messages
GROUP BY name
);
私はこれが他のソリューションのいくつかと比較してどのように動作するのかわかりませんが、3百万行のテーブルで驚くほど機能しました。 (1200秒以上の結果で4秒実行)
これは、MySQLとSQL Serverの両方で動作するはずです。
SELECT
column1,
column2
FROM
table_name
WHERE id IN
(SELECT
MAX(id)
FROM
table_name
GROUP BY column1)
ORDER BY column1 ;