[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
);

/* 日毎にレコード数が最大のページを取る場合 */

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

);

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

ふむふむー。

- mysql

PREV: [sql][mysql]URLのパラーメータ削除 + index.html の削除
NEXT: gdippで文字が崩れると言うか、汚くなると言うか...

: mysql 内部結合・外部結合違い inner join,left join,right join

参考サイト]http://mathemathiko.hatenablog.com …

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

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

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

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

: [mysql]どういうSQL文のとき、どういうインデックスが使われるのか整理

mysqlのチューニングをしていて、どうしてもインデックスが、どの指定の時に何が …

: [mysql][db]データ増大に伴う、データ表示処理・データ蓄積処理の変更履歴…

背景ログの蓄積について、とりあえずでやっていたら処理がドンドンおもくなっていき …

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

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

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

もう一つおまけで….

関連記事