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

2017/04/27

標題のとおり

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

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

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

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

(だめな例)

SELECT *, max(age) FROM 社員テーブル
GROUP BY 部署;

1 2	SELECT *, max(age) FROM 社員テーブル GROUP BY 部署;

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

(ただしい例)

SELECT * FROM 社員テーブル
WHERE age = ( SELECT max(age) 
              FROM 社員テーブル 
              GROUP BY 部署)

SELECT * FROM 社員テーブル

WHERE age = ( SELECT max(age)

FROM 社員テーブル

GROUP BY 部署)

これでOK。

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

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

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

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

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

SELECT * FROM 社員テーブル main
JOIN 
  /*相関サブクエリでつかったものと同じサブクエリ*/
  ( SELECT  max(age) FROM 社員テーブル GROUP BY 部署) sub

  /*相関サブクエリで whereの条件に指定したものを ONの条件にする*/
  ON  main.age =  sub.max(age)

SELECT * FROM 社員テーブル main

JOIN

/*相関サブクエリでつかったものと同じサブクエリ*/

( SELECT max(age) FROM 社員テーブル GROUP BY 部署) sub

/*相関サブクエリで whereの条件に指定したものを ONの条件にする*/

ON main.age = sub.max(age)

万歳万歳

もう一つおまけで….

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

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

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

/* 日毎にレコード数が最大のページを取る場合 */
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
);