mysqlでグループ内の最大値を取得する方法

質問

はじめまして。

voteというテーブルにcategory_id、individual_id、timeという３つのフィールドがあります。

(1)category_idは名前の通りカテゴリーのIDで、0～100まであります。
(2)individual_idはcategory_idごとに0～100まであます。
(3)timeは行が挿入された時間が記入されています。投票されるたびに行が挿入されます。
(4)category_idとindividual_idとdateでユニークキーを構成しています。
(5)category_idとindividual_idの２つで、個人が特定されます。

select categry_id, individual_id, count(*) from vote group by category_id, individual_id
という命令文で、誰に何票voteされているかが分かります。

この先ですが、各カテゴリの中で最も多く投票されている3人ずつを表示したいのですが、どのようなSQLを書けばよいでしょうか。

似たような質問もあり、自分なりに四苦八苦して試してみたのですがうまくいきませんでした。
回答いただけると助かります。よろしくお願いいたします。

回答No.2

yambejp · ベストアンサー

4人以上を3人に絞り込むのは適当でいいとのことですので
投票数が同じ場合はユーザーのidが小さいものを上位とします
性質上一度集計をするためにviewをつくっておくとよいでしょう。

//テーブルをつくり
create table vote (cID int,iID int,cTime datetime,unique(cID,iID,CTime));

//ビューをつくります
create view v_vote as select cID,iID,count(*) as count from vote group by cID,iID;

//ダミーデータをいれます
insert ignore into vote values (1,1,'2013-01-01 00:00:00'),(1,1,'2013-01-01 00:00:01'),(1,1,'2013-01-01 00:00:02'),(1,1,'2013-01-01 00:00:03'),(1,1,'2013-01-01 00:00:04'),(1,1,'2013-01-01 00:00:05'),(1,2,'2013-01-01 00:00:06'),(1,2,'2013-01-01 00:00:07'),(1,2,'2013-01-01 00:00:08'),(1,3,'2013-01-01 00:00:09'),(1,3,'2013-01-01 00:00:10'),(1,4,'2013-01-01 00:00:11'),(1,4,'2013-01-01 00:00:12'),(1,4,'2013-01-01 00:00:13'),(2,1,'2013-01-01 00:00:14'),(2,2,'2013-01-01 00:00:15'),(2,3,'2013-01-01 00:00:16'),(2,4,'2013-01-01 00:00:17'),(2,5,'2013-01-01 00:00:18'),(2,1,'2013-01-01 00:00:19'),(3,2,'2013-01-01 00:00:20');

//まずビューを確認します
select * from v_vote;

//カテゴリごとにカウントの多い人順
select(
select count(*) +1
from v_vote AS v2
where (
v1.count=v2.count and v1.iID>v2.iID
or v1.count<v2.count
) and v1.cID=v2.cID
) AS rank
,cID,iID,count
from v_vote AS v1
having rank<=3
order by cID asc,rank asc;

お礼

yambejpさんありがとうございます。
エラーも出ることなくうまく動きました。

ビューを遣うというテクニックは知りませんでした。
ありがとうございました。

回答No.1

yambejp

＞最も多く投票されている3人ずつ

定義があいまい、上位3名という場合、同位のものがいた場合どうするのですか？
たとえば1位が10人いたら4人目以降をどうやって除外するつもりでしょう？
新しいtimeを持つ順、古いtimeを持つ順、individual_idの昇順・降順など
どれかかなぁとは思いますが・・・

あと、timeとかdateとか語句にぶれがあります。
実質datetimeのフィールドだとおもいますが、予約語をフィールド名に使うのは
混乱の元なので注意が必要です

補足

yambejpさん、コメントありがとうございます。
timeはcurrent_timestampで取得した値となっています。

なお10人同順位となった場合ですが、特にこだわらず10人中3人が選出できれば問題ないです。
例えばorder by count(*) desc limit 0, 3でmysqlが取得する値そのもので大丈夫です。

よろしくお願いいたします。