MySQLでグループ毎に最新の情報を習得したい

Question

MySQL5.5で下記の商品データベースから、各商品ID毎の最新情報を取得したいです。
《product》
| product_id | price shop_id | shop_id | modified |
| 1 | 100 | 001 | 2016-03-30 14:00|
| 1 | 148 | 015 | 2016-03-30 15:00|
| 2 | 254 | 001 | 2016-02-26 14:31|
| 2 | 384 | 022 | 2016-03-31 13:50|
| 2 | 482 | 016 | 2016-01-24 10:00|
| 3 | 851 | 015 | 2016-03-30 14:00|
| 3 | 650 | 022 | 2016-03-14 16:00|
| 3 | 500 | 016 | 2016-03-26 14:30|

《結果》
| product_id | price shop_id | shop_id | modified |
| 1 | 148 | 015 | 2016-03-30 15:00|
| 2 | 384 | 022 | 2016-03-31 13:50|
| 3 | 851 | 015 | 2016-03-30 14:00|

また、実際のDBには大量に情報があるため、できるだけ軽い処理にしたいです。
処理時間のかかるサブクエリは避けたいです。

Answer 1

以下のページが、まさに同じ問題に対する解決策を提示してくれています。
https://dev.mysql.com/doc/refman/5.6/ja/example-maximum-column-group-row.html

ご質問のテーブル構造に当てはめると、以下のようになるかと思います。

■相関サブクエリ バージョン

SELECT main.* FROM product AS main WHERE modified = (
    SELECT MAX(modified) FROM product AS sub WHERE main.product_id = sub.product_id
);

■非相関サブクエリ バージョン

SELECT main.* FROM product AS main 
INNER JOIN (
    SELECT product_id, MAX(modified) AS modified FROM product GROUP BY product_id
) AS sub 
  ON main.product_id = sub.product_id AND main.modified = sub.modified;

■外部結合バージョン

SELECT main.* FROM product AS main 
    LEFT OUTER JOIN product AS sub ON main.product_id = sub.product_id AND main.modified < sub.modified 
WHERE sub.modified IS NULL;

ただし、

できるだけ軽い処理にしたいです。

処理時間のかかるサブクエリは避けたいです。

について、クエリの実行時間はインデックスの有無やデータの分布具合によっても変わるため、一概にサブクエリが重いとは限りません。

実装しようとしているクエリの性能を確認したければ、実際にテストデータを投入したDBに流してみるのと、実行計画を確認するのがよいです。
実行計画については、以下のサイトが参考になります。
https://dev.mysql.com/doc/refman/5.6/ja/explain.html
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html

ちなみに、サブクエリにも相関サブクエリと非相関サブクエリの２種類があり、よく「重い」と言われるのは相関サブクエリの方です。

Comments

[noripi]

詳しく解説していただきありがとうございます。正直サブクエリに2種類あるのはしりませんでした。勉強不足を痛感しています。
3パターン試した結果、外部結合バージョンが一番早かったです。

[KiyoshiMotoki]

> 3パターン試した結果、外部結合バージョンが一番早かったです。
ご丁寧な報告、ありがとうございます。

お役に立てたなら幸いです。

Answer 2

nabe3さんのユーザ変数を使った方法ですが、

OracleACEのAketiJyuuzouさんとyoku0825さんと
日本オラクルの木村さんによると
MySQLのユーザ変数は評価順序が未定義だそうです。
http://qiita.com/AketiJyuuzou/items/cced9b70cc714b382d98

ゆえに、結果が保証されないと思います。

Comments

[noripi]

なるほど。勉強になります。

Answer 3

JOINかEXISTSを使うべきかと

JOIN

SELECT
	  A.*
FROM product A
JOIN (
	SELECT
		  B.product_id
		, MAX(modified) modified
	FROM product B
	GROUP BY
		  B.product_id
) C
ON C.product_id = A.product_id
AND C.modified = A.modified

EXISTS

SELECT
	  *
FROM procudt A
WHERE NOT EXISTS(
	SELECT 1
	FROM product B
	WHERE B.product_id = A.product_id
	AND B.modified > A.modified
)

Comments

[noripi]

ありがとうございます。試してみます。

Answer 4

サブクエリ必要な気がします。
最初に各商品IDの最新日付を取得してからJOINと言う形が一般的ですね。

SELECT p.product_id, p.price, p.shop_id, p.modified FROM product p
INNER JOIN (
   SELECT product_id, MAX(modified) modified
   FROM product
   GROUP BY product_id
) max_p
ON p.product_id = max_p.product_id
AND p.modified = max_p.modified

Comments

[noripi]

やはりサブクエリは必要ですか。

Answer 5

こちらのSQLではいかがでしょうか？

product_idごとにmodifiedの新しいもの順に1, 2, 3とナンバリングし、
No1のレコードのみを抽出します。

SELECT t.product_id, t.price_shop_id, t.shop_id, t.modified
FROM
(
	SELECT  @row_num := IF(@prev_value=p.product_id,@row_num+1,1) AS RowNumber,
	       p.product_id,
	       p.price_shop_id,
	       p.shop_id,
	       p.modified,
	       @prev_value := p.product_id
	FROM product p,
	      (SELECT @row_num := 1) x,
	      (SELECT @prev_value := 'x') y
	ORDER BY p.product_id, p.modified DESC
) t
WHERE t.RowNumber = 1
ORDER BY product_id;

Comments

[noripi]

このような書き方もあるのですね。

[退会済みユーザー]

幸運にも期待通りの結果を得られるかもしれませんが、それが確約されないSQLで、
論外の品質なので、マイナス投票します。

http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html
>基本的なルールは、ステートメントの一部でユーザ変数値を割り当てないこと
>および同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、これは確約されていません。

[aiou]

退会済ユーザ様

減点するなら下記の2点がより適切です。

select文でのユーザ変数への値の代入は5.7で非推奨になり、8.0以降で削除予定の機能であることが1点目。
http://www.mysql.gr.jp/mysqlml/mysql/msg/16488

MySQLの開発者がブログに
https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/
It works. But it relies on the fact that MySQL sets @rownum before setting @prev_sex,
i.e. evaluates selected expressions from left to right. Which is true for this query, but not for all queries
と書いてあるのが2点目。

Answer 6

group by と max ですかね

select product_id, price_shop_id, shop_id, MAX(modified) as modified from product group by product_id;

```な感じ？
この問題はもしかして
[https://teratail.com/questions/31016](https://teratail.com/questions/31016)
と同じでは無いですか？

Comments

[nabe3]

price_id = 2 の期待結果が、
price shop_id = 384, shop_id = 022, modified = 2016-03-31 13:50 ですが、
単純な group by では、
price shop_id = 254, shop_id = 001, modified = 2016-03-31 13:50 を返してしまうことがあります。
group by に含まれていない price_shop_id と shop_id の値がproduct_idの
最新のmodifiedに対する値と一致しないことがあります。

[HiroshiWatanabe]

あー…なるほど。うっかりしてました。ご指摘ありがとうございます。

[noripi]

確かにそうですね。