For Want Of A Better Word

[sql][mysql]特定グループ毎に最大の値を持つ行を取得する(相関サブクエリー、JOIN)

   

標題のとおり

ぱっと考えるとMAX()でとればいいように思えるが、GROUP BYで指定した値以外は、特定レコードを示さない不定の値となってしまうため、それではだめ。

 

特定の項目の値が最大のレコードをとる場合

最大値と判定したい取得したい値がテーブルの1項目として存在していれば、相関サブクエリーかINNER JOINで行ける。

例 ) 社員テーブルから部署ごとに年齢が最大の人のレコードをとる。

(だめな例)

これは前述したとおり、GROUP BY で指定した部署 とmax(age)以外の値が、特定の1レコードの値となることを保障できない。

(ただしい例)

これでOK。

ここではMAX()だが、他の集合関数(AVG()やMIN())との比較もこれで同じように出来る。

 

※ちょくちょく相関サブクエリーの使いどころがわからなくなるのだが、、下記のパターンが一つあると考えておけば良さそう。

★特定のレコードを取得したく、その基準が集合関数で取得する値である場合、相関サブクエリーを使う

 

相関サブクエリーはJOINでも表現できる

上のパターンをJOIN で実現すると下記のようになる。

 

万歳万歳

 

もう一つおまけで….

“レコード数が最大”、、、というように、テーブル自体には比較したい値が入っていない場合。
(私の場合は、アクセスログのデータから、日毎にアクセス数が最大のページをとりたい…とか)

 

この場合は、そもそもMAXの対象とする値がテーブルに無いので、一度その値が入った集合を作って、

そこに前述の相関サブクエリーやJOINを行うことになる。

 

1つのSQLで表現すると長くて面倒だが、(1)の部分をviewにしてみると、最初と同じパターンをやっていることになります。

 

ふむふむー。

 

 - mysql

  関連記事

[sql][mysql]URLのパラーメータ削除 + index.html の 削除

よく忘れるのでメモ。 ログを集計するときに、パラメータを削除したり、URLの/と …

[PHP][PDO]prepareでのバインド方法整理

データをstmtに渡す部分をちょくちょく待ちがえて混乱するのでメモ 参考:htt …

【MySQL】query_logを取得した場合の速度影響

あくまでWEB画面表示について。 環境は下記。 Linux CentOs7.1 …

[mysql]なぜかindexが使われない、、、クォーテーションで囲っている?

whereの対象列にindexを張っている。 しかし、検索時にやたら重いことがあ …

[mysql]集合差を求める場合の処理速度比較(NOT IN(), NOT EXISTS, LEFT JOIN … IS NULL)

  http://qiita.com/Hiraku/items/718 …