mysql5でGROUP BYごとにLIMIT？？

2011,600
2011,500
2011,450
2011,750
2010,450
2010,540
2010,350
2010,800
2010,700

例えばこのようなデータを年ごとにベスト３までとって次のようにするSQLはありますか

2011,750
2011,600
2011,500
2010,800
2010,700
2010,540

No.1 回答者： nora1962 回答日時： 2011/03/15 10:47

select * from xxx as x1

where ( nen, gaku )
in (
select nen,gaku from xxx as x2
where x1.nen=x2.nen
and x1.gaku<=x2.gaku
having count(*) <= 3
);

(nen,,gaku)でINDEXがないとパフォーマンス的に厳しいと思いますが。

出来れば
select * from xxx as x1
where ( nen, gaku )
in (
select nen,gaku from xxx as x2
where x1.nen=x2.nen
order by x2.gaku desc limit 3 );

と書きたいのですが、現在のバージョンではエラーになります。

この回答へのお礼

こんばんは

試しにやってみたのですが、
やはりパフォーマンス的に厳しいと感じました

お礼日時：2011/03/15 20:59

No.2 回答者： nora1962 回答日時： 2011/03/15 12:46

ユーザ変数を使ったやり方。

set @rnk = 0, @prenen = null,@pregaku = null;
select nen,gaku,rnk
from
(select
@rnk := if( @prenen <=> nen, if( @pregaku <=> gaku, @rnk, @rnk+1 ), 1 ) as rnk
,@prenen := nen nen
,@pregaku := gaku gaku
from xxx
order by nen,gaku desc
) t
where rnk <= 3;

この回答へのお礼

こんばんは

なんとか1個のSQL文で取りたいと考えています；；

お礼日時：2011/03/15 21:02

No.3 回答者： nora1962 回答日時： 2011/03/15 13:00

ちなみにMySQLの場合はユーザ変数をストアドプロシージャ内で使用できるようです。

delimiter //

create procedure proc_test()
begin
set @rnk = 0, @prenen = null,@pregaku = null;
select nen,gaku,rnk
from
(select
@rnk := if( @prenen <=> nen, if( @pregaku <=> gaku, @rnk, @rnk+1 ), 1 ) as rnk
,@prenen := nen nen
,@pregaku := gaku gaku
from xxx
order by nen,gaku desc
) t
where rnk <= 3;
end;
//

delimiter ;

「call proc_test;」で呼び出せます。

この回答へのお礼

こんばんは

ストアドプロシージャは私にとって未知の領域なので回避します

HAVING句のように簡単に使えるものがあれば知りたいと考えていました
副問合せを使った方法は、速度的に難があったため断念しました

不本意ではありますがループでSQLを複数回実行する方向で考えます

お礼日時：2011/03/15 21:19

No.4 ベストアンサー 回答者： nora1962 回答日時： 2011/03/15 22:40

じゃあ、これを試してみてください。

select t.nen, t.gaku
from ( select
@rnk:=if(@prevnen <=> x.nen, if( @prevaku <=> x.gaku, @rnk, @rnk+1), 1 ) as rnk,
@prevnen := x.nen nen,
@prevgaku := x.gaku gaku
from ( select * from xxx order by nen, gaku desc ) as x
cross join ( select @prevnen:=0, @prevgaku:=0, @rnk:=0 ) as dummy ) t
where t.rnk <= 3;

この回答へのお礼

変数に代入した値を即座に使えるということでしょうか

使ったことがない用法が多いので、ひとまずSQLの内容を理解できないまま、
当テーブルの構造に置き換えて実行してみました

最初のt.nenとt.gakuは存在しない旨のエラーになってしまうので、
select x.nen, x.gakuが正解でしょうか

うーん、それでもエラーでした

ユーザー変数というのは馴染みがないので、
私としてはNo.1のやり方がシンプルで好きです
仮にユーザー変数を使ったやり方だと速度的にはどんなもんでしょう

外部プログラムでループすれば解決ですが念のため

お礼日時：2011/03/16 04:06

No.5 回答者： nora1962 回答日時： 2011/03/16 10:19

例示されたデータではエラーにはなりませんよ。

create table xxx
(
nen int,
gaku int
);

insert into xxx values ( 2011,600 );;
insert into xxx values ( 2011,500 );
insert into xxx values ( 2011,450 );
insert into xxx values ( 2011,750 );
insert into xxx values ( 2010,450 );
insert into xxx values ( 2010,540 );
insert into xxx values ( 2010,350 );
insert into xxx values ( 2010,800 );
insert into xxx values ( 2010,700 );

select t.nen, t.gaku
from ( select
@rnk:=if(@prevnen <=> x.nen, if( @prevaku <=> x.gaku, @rnk, @rnk+1), 1 ) as rnk,
@prevnen := x.nen nen,
@prevgaku := x.gaku gaku
from ( select * from xxx order by nen, gaku desc ) as x
cross join ( select @prevnen:=0, @prevgaku:=0, @rnk:=0 ) as dummy ) t
where t.rnk <= 3;

> 最初のt.nenとt.gakuは存在しない旨のエラーになってしまうので、
> select x.nen, x.gakuが正解でしょうか
そんなことはないはずです。
考えられるのはMysqlのバージョンで副問い合わせが使えないとかですが。

> 私としてはNo.1のやり方がシンプルで好きです
> 仮にユーザー変数を使ったやり方だと速度的にはどんなもんでしょう
私もNo.1のやり方が基本だと思いますが、グループごとのレコード件数が増えると
Nの2乗の割合で実行時間が増加するのが難点です。
ユーザ変数を使った場合は、コスト的には元データのソートが一番重い処理になり
ますが、これはnlog(ｎ)のオーダーなので件数が増えてきた場合、有利になります。
とりあえず、使っているMySQLのバージョンを教えてください。

この回答へのお礼

失礼しました

私の置き換え方が間違っていました

再度、注意深く置き換えて実行したら通りました
速度も申し分ありません！

しかしながら、SQLの内容を理解できていないことと、
すでに独自の代替案で実装が済んでいることの2点を考慮し、

このSQLは今度必要になったときに役立たせていただきます

お礼日時：2011/03/16 20:26

No.6 回答者： nora1962 回答日時： 2011/03/16 20:55

> すでに独自の代替案で実装が済んでいることの2点を考慮し、

> このSQLは今度必要になったときに役立たせていただきます

そのほうがいいでしょう。
このやり方はSELECT句の評価順が左から右に行われることが前提になってますので、もしかするとバージョンアップの際に動作しなくなる危険がありますので。

この回答へのお礼

なるほどバージョンアップのことを考えると、SQLはあまりトリッキーな記述にせず、単純にした方がよさそうですね。

今後の考慮点に加えます^^;

お礼日時：2011/03/17 06:00

No.7 回答者： nora1962 回答日時： 2011/03/17 06:29

まあ、そこは難しいところなんですが。

どうしてもパフォーマンス出さないといけない場面もありますから。安定性・保守性と性能のどっちを取るかいう時に、環境的にチューニングにも限界があります。
結構、しんどそうな案件のようですね。がんばってください。

この回答へのお礼

ありがとうございます！

励みになります！

お礼日時：2011/03/17 23:37

No.8 回答者： chukenkenkou 回答日時： 2011/03/19 15:00

横から失礼。

回答に対し、何か具体的な提示があれば回答しようと思っていました。しかし、他人にアドバイスを求めているのに、具体的な情報を出さないので、ずっと様子を見ていました。

具体的に、

(1)MySQLのバージョン
MySQL 5とかでなく、MySQL 5.0、5.1、5.5といったレベルまで提示するようにしてください。
　SQLの実装に関係する大きな機能追加や一部の仕様変更があります。特にMySQL 5.1では、「MySQL 5.1.xで仕様変更」なんていうのもあります。
(2)母体データ件数と得たい結果の件数
(3)どういうSQLを実際に実行して、「速度に難」といった話をしているのか
(4)どういうインデクスを定義しているのか
(5)EXPLAINの結果を見ているのか。その内容は提示できないのか

といったことを提示してくれれば、もっと多くの人からもアドバイスをもらえる可能性が出てきます。

さて、本題です。

MySQLの複合キー（複数列）のインデクスは、列の昇順、降順の混在を許していません。昇順、降順のインデクスを定義した場合、定義自体は正常終了しますが、実際に作成されるインデクスは全列が昇順に変更されます。

例えば、

create index t1ix on t1(c1,c2 desc)

というインデクスを定義した場合、実際には

create index t1ix on t1(c1,c2)

というインデクスが作成されます。

また、これにより、order byで昇順、降順を混在した指定を行うと、「インデクスを活用し、作業ファイルを使ってのソートを抑止」ができません。

サブクエリ中でのlimit句の使用には、他の回答者さんの回答どおり、MySQLでは制限があります。その一方で、1件だけ取り出す場合は利用可能です。
そのため、

select
c1
,(select c2 from t1 where x.c1=c1 order by c1 desc,c2 desc limit 0,1) as rank1
,(select c2 from t1 where x.c1=c1 order by c1 desc,c2 desc limit 1,1) as rank2
from t1 as x
[where c1 between 2010 and 2011]
group by c1 desc


といった書き方が可能です。

インデクスを、

create index t1ix on t1(c1,c2)

と定義したとします。

母体件数がある程度多く、その中からある程度、絞り込めるなら、

(1)サブクエリ中のorder byで、c1を含むことで、作業ファイルを使ったソートを抑止できる可能性がある。
(2)MySQLでは、group byで昇順、降順を指定できる。MySQLの独自機能、独自構文である点に注意。

といった結果が、私のMySQL 5.1.36の環境では、EXPLAINの結果として得られています。

実際には以下のような定義で、テストしてみました。

create table t1
(id int primary key auto_increment
,nen smallint
,kubun varchar(5)
,data int
);

create index t1ix on t1(nen,kubun,data);

-- explain
select
x.nen
,x.kubun
,(select data from t1
where x.nen=nen and x.kubun=kubun
order by nen desc,kubun desc,data desc limit 0,1) as rank1
,(select data from t1
where x.nen=nen and x.kubun=kubun
order by nen desc,kubun desc,data desc limit 1,1) as rank2
,(select data from t1
where x.nen=nen and x.kubun=kubun
order by nen desc,kubun desc,data desc limit 2,1) as rank3
from t1 as x
where x.nen between 2010 and 2011
and kubun='A'
group by x.nen desc,x.kubun desc