[sql][mysql]特定グループ毎に最大の値を持つ行を取得する(相関サブクエリー、JOIN)
標題のとおり
ぱっと考えるとMAX()でとればいいように思えるが、GROUP BYで指定した値以外は、特定レコードを示さない不定の値となってしまうため、それではだめ。
特定の項目の値が最大のレコードをとる場合
最大値と判定したい取得したい値がテーブルの1項目として存在していれば、相関サブクエリーかINNER JOINで行ける。
例 ) 社員テーブルから部署ごとに年齢が最大の人のレコードをとる。
(だめな例)
1 2 |
SELECT *, max(age) FROM 社員テーブル GROUP BY 部署; |
これは前述したとおり、GROUP BY で指定した部署 とmax(age)以外の値が、特定の1レコードの値となることを保障できない。
(ただしい例)
1 2 3 4 |
SELECT * FROM 社員テーブル WHERE age = ( SELECT max(age) FROM 社員テーブル GROUP BY 部署) |
これでOK。
ここではMAX()だが、他の集合関数(AVG()やMIN())との比較もこれで同じように出来る。
※ちょくちょく相関サブクエリーの使いどころがわからなくなるのだが、、下記のパターンが一つあると考えておけば良さそう。
★特定のレコードを取得したく、その基準が集合関数で取得する値である場合、相関サブクエリーを使う
相関サブクエリーはJOINでも表現できる
上のパターンをJOIN で実現すると下記のようになる。
1 2 3 4 5 6 7 |
SELECT * FROM 社員テーブル main JOIN /*相関サブクエリでつかったものと同じサブクエリ*/ ( SELECT max(age) FROM 社員テーブル GROUP BY 部署) sub /*相関サブクエリで whereの条件に指定したものを ONの条件にする*/ ON main.age = sub.max(age) |
万歳万歳
もう一つおまけで….
“レコード数が最大”、、、というように、テーブル自体には比較したい値が入っていない場合。
(私の場合は、アクセスログのデータから、日毎にアクセス数が最大のページをとりたい…とか)
この場合は、そもそもMAXの対象とする値がテーブルに無いので、一度その値が入った集合を作って、
そこに前述の相関サブクエリーやJOINを行うことになる。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
/* 日毎にレコード数が最大のページを取る場合 */ SELECT * FROM ( /*(1) 日毎・url毎のレコード数の集合*/ SELECT date, url count(*) as count FROM access_log GROUP BY date, url ) sq1 WHERE (sq1.date, sq1.count ) IN /*(2) 日毎・url毎のカウントから、日毎に最大の数を取得*/ SELECT date, max(count) FROM ( /*(3) (1)と同様*/ SELECT date, url count(*) as count FROM access_log GROUP BY date, url ) sq2 GROUP BY date ); |
1つのSQLで表現すると長くて面倒だが、(1)の部分をviewにしてみると、最初と同じパターンをやっていることになります。
ふむふむー。
関連記事
-
-
【MySQL】query_logを取得した場合の速度影響
あくまでWEB画面表示について。 環境は下記。 Linux CentOs7.1 …
-
-
mysql 内部結合・外部結合違い inner join,left join,right join
参考サイト]http://mathemathiko.hatenablog.com …
-
-
[mysql][db]データ増大に伴う、データ表示処理・データ蓄積処理の変更履歴…
背景 ログの蓄積について、とりあえずでやっていたら処理がドンドンおもくなっていき …
-
-
[mysql]集合差を求める場合の処理速度比較(NOT IN(), NOT EXISTS, LEFT JOIN … IS NULL)
http://qiita.com/Hiraku/items/718 …
-
-
[mysql]なぜかindexが使われない、、、クォーテーションで囲っている?
whereの対象列にindexを張っている。 しかし、検索時にやたら重いことがあ …