データの件数を集計してTOPを取得するためのSQL

質問

データの件数を集計して、その結果からもっとも件数が多いものだけを抽出する SQL について教えてください。
例えば以下のようなテーブルがあります。

▼テーブル

名前 |交通手段|日付
-----+--------+----
Aさん|バス |5/1
Aさん|バス |5/2
Aさん|バス |5/3
Bさん|バス |5/1
Bさん|電車 |5/2

※「日付」については、本質問に直接の関係はありません。

このテーブルにクエリを発行して、以下の結果を取得したいと考えています。

▼取得したい結果 (ある交通手段をもっとも使った人を抽出)

名前 |交通手段|回数
-----+--------+----
Aさん|バス |3
Bさん|電車 |1

※回数が同数の人がいる場合は、「ユーザID」が一番若い人を採用します。
　「ユーザID」はこのテーブル例には含まれておりませんが、
　「名前」が「ユーザID」であると読み替えてください。

なお、以下の結果を取得するクエリは 質問番号:6719239 にご協力いただけたおかげで解決しました。

SELECT 名前, 交通手段, COUNT(*) AS 回数
FROM テーブル
GROUP BY 名前, 交通手段;
　↓
名前 |交通手段|回数
-----+--------+----
Aさん|バス |3
Bさん|バス |1
Bさん|電車 |1

今回、この結果から 1件目 と 3件目 を抜き出したいと考えております。
プログラム側で対処はできるのですが、SQL の方がスマートに書けそうだと思っております。

なおレンタルサーバ上 (MySQL 5.0.77) で稼働させるため、
なるべく高速な SQL を希望いたします。

回答No.3

nora1962 · ベストアンサー

訂正です。

select 名前,交通手段,件数
from ( select
@rnk:= if( @access<=>交通手段, @rnk+1, 1 ) as rnk,
名前,
@access:=交通手段 as 交通手段,
件数
from ( select
名前,
交通手段,
count(*) as 件数 from テーブル
group by 名前,交通手段
order by 交通手段 desc,件数 desc,名前
) T cross join
( select @access:=null, @rnk:=0 ) as dummy ) S
where rnk = 1
order by 名前,交通手段;

お礼

誠にありがとうございます。

前質問がかなりスマートに書けてしまったことから
こちらもかなりスマートに書けるのかと思ってしまいましたが、やはり長くなってしまうのですね。
SQL は難しい。。。

ともあれ、本当にありがとうございました。
レスポンスが早かったので、とっかかりができて非常に助かりました。

実際のテーブルはもう少し複雑なため、このやり方を参考にしつつ進めております。

回答No.2

yambejp

ざっとフローどおりやるとこんな感じでいけます。
もう少し効率的な書き方がありそうな気もしますが・・・

SELECT MIN(名前) AS 名前,交通手段,回数
FROM(
SELECT 名前, 交通手段, COUNT(*) AS 回数
FROM テーブル
GROUP BY 名前, 交通手段
HAVING (交通手段,回数) IN(
SELECT 交通手段,MAX(回数)
FROM (
SELECT 交通手段, COUNT(*) AS 回数
FROM テーブル
GROUP BY 名前, 交通手段
) AS SUB
GROUP BY 交通手段
)
) AS SUB2
GROUP BY 交通手段,回数

お礼

誠にありがとうございます。

前質問がかなりスマートに書けてしまったことから
こちらもかなりスマートに書けるのかと思ってしまいましたが、やはり長くなってしまうのですね。
SQL は難しい。。。

ともあれ、本当にありがとうございました。

ベストアンサーはレスポンスが早かった #1 の方に差し上げますが、こちらの方法も勉強になります。
実際のテーブルはもう少し複雑なため、このやり方を参考にしつつ進めます。

回答No.1

nora1962

select 名前,交通手段,件数
from ( select
@rnk:= if( @name<=>名前, @rnk+1, 1 ) as rnk,
@name:=名前 as 名前,
交通手段,
件数
from ( select
名前,
交通手段,
count(*) as 件数 from テーブル
group by 名前,交通手段
order by 名前 desc,交通手段 desc
) T cross join
( select @name:=null, @rnk:=0 ) as dummy ) S
where rnk = 1
order by 名前,交通手段