- ベストアンサー
mysql5でGROUP BYごとにLIMIT??
- みんなの回答 (8)
- 専門家の回答
質問者が選んだベストアンサー
じゃあ、これを試してみてください。 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;
その他の回答 (7)
- chukenkenkou
- ベストアンサー率43% (833/1926)
横から失礼。 回答に対し、何か具体的な提示があれば回答しようと思っていました。しかし、他人にアドバイスを求めているのに、具体的な情報を出さないので、ずっと様子を見ていました。 具体的に、 (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
- nora1962
- ベストアンサー率60% (431/717)
まあ、そこは難しいところなんですが。どうしてもパフォーマンス出さないといけない場面もありますから。安定性・保守性と性能のどっちを取るかいう時に、環境的にチューニングにも限界があります。 結構、しんどそうな案件のようですね。がんばってください。
お礼
ありがとうございます! 励みになります!
- nora1962
- ベストアンサー率60% (431/717)
> すでに独自の代替案で実装が済んでいることの2点を考慮し、 > このSQLは今度必要になったときに役立たせていただきます そのほうがいいでしょう。 このやり方はSELECT句の評価順が左から右に行われることが前提になってますので、もしかするとバージョンアップの際に動作しなくなる危険がありますので。
お礼
なるほどバージョンアップのことを考えると、SQLはあまりトリッキーな記述にせず、単純にした方がよさそうですね。 今後の考慮点に加えます^^;
- nora1962
- ベストアンサー率60% (431/717)
例示されたデータではエラーにはなりませんよ。 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(n)のオーダーなので件数が増えてきた場合、有利になります。 とりあえず、使っているMySQLのバージョンを教えてください。
お礼
失礼しました 私の置き換え方が間違っていました 再度、注意深く置き換えて実行したら通りました 速度も申し分ありません! しかしながら、SQLの内容を理解できていないことと、 すでに独自の代替案で実装が済んでいることの2点を考慮し、 このSQLは今度必要になったときに役立たせていただきます
- nora1962
- ベストアンサー率60% (431/717)
ちなみに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を複数回実行する方向で考えます
- nora1962
- ベストアンサー率60% (431/717)
ユーザ変数を使ったやり方。 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文で取りたいと考えています;;
- nora1962
- ベストアンサー率60% (431/717)
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 ); と書きたいのですが、現在のバージョンではエラーになります。
お礼
こんばんは 試しにやってみたのですが、 やはりパフォーマンス的に厳しいと感じました
関連するQ&A
- MYSQLでgroup by を教えてください。
まだ初心者なんです。よろしくお願いいたします。 テーブル$tbl_nameに日付date コラムに (2009-11-3 14:25:06).(2009-11-3 11:25:06). (2009-11-5 12:25:06).(... ....と年齢 ageコラムに (18)(19)(20)... 性別コラムage に (danshi)(jyoshi) が入っています。 これを日付別 に内容が存在するだけ出力したいのです。 出力内容は(18)→25、(19)→13、(20)→7、の数だけ、 同様に(danshi)→20、(jyoshi)→25、というように 〇月〇日18才は25人、19才は13人、20才は7人、 男子は20人、女子は25人と出したいのです。 $sql = "SELECT 'age',count(age) FROM $tbl_name group by 'date'"; $result = mysql_query($sql, $db) or die("クエリの送信に失敗しました。<br />SQL:".$sql); while ($row = mysql_fetch_assoc($result)){ $age .=$row;} 等記入しているのですが、何とも動きませんのです。 どうしたらよろしいものでしょう。よろしくお願いいたします。
- 締切済み
- MySQL
- GROUP BY について
上のデータを左の2列で分類し、下のように足したいのですが、 実際は列が多いので、なるべく簡単なsqlで記述したいです。 よろしくお願いします。 データは0と1のみですのでカウントしても問題ないです。 116,1126,0,0,0,0,1,0,0,0,1,0 116,1126,1,0,0,0,0,0,1,0,1,0 116,1127,0,0,0,1,0,0,0,0,1,0 117,1127,0,0,0,0,1,0,1,0,0,1 117,1127,0,0,0,0,1,0,1,0,0,1 117,1128,0,0,1,0,0,0,0,0,1,0 ↓ 116,1126,1,0,0,0,1,0,1,0,2,0 116,1127,0,0,0,1,0,0,0,0,1,0 117,1127,0,0,0,0,2,0,2,0,0,2 117,1128,0,0,1,0,0,0,0,0,1,0
- ベストアンサー
- MySQL
- mysqlでカンマデータのgroup by
初めての質問になります。 以下のようなテーブルで、dataカラムにカンマ区切りのデータを保存しているのですが、こちらはgroup byやcount等を使ってSQL一発で集計結果を取得できるのでしょうか? SELECT count(*) FROM TEST WHERE FIND_IN_SET('1', data)で1から順番に取得は出来るのですが、数が多いとちょっといかがなものかと思い、質問させていただきました。 環境:mysql5.5 ------------ |data ------------ |1,2,3 ------------ |1,2,10 ------------ |1,2,3,10 ------------ 期待する結果は以下のような感じです。 1|3 --------- 2|3 --------- 3|2 --------- 10|2
- ベストアンサー
- MySQL
- MySQLのLIMIT 1指定について。
JAVAで、MySQLを書いています。 (理由は不明ですが、たまに、言語がわからないと、SQL文を提示できないという方がいるので、「JAVAで」と書いてあります。MySQLは、言語に依存しないと思っているのですが。) よくここで、どのように、SQL文を書いたらよいか質問しております。 それは、書き方がわからないときもあるし、 わかってはいるが、もっと簡潔、早い、見やすい、メモリの消費が少ない などのSQLを求めているからです。 質問が的を得ていないときもあり、質問が難しいのですが、 テーブル例などを書くようにして、質問しています。 ご回答は、みなさん、それぞれ、色々なSQL文を書いてくれます。 難しいのやパッとみて、ああっ、それ簡単で早そう!! というのとか、あります。 ところで、1行だけのレコードが欲しいと質問したときに、 SQL文に、LIMIT1 をつける方とつけない方がいます。 LIMIT 1 の有無による、速度や、メモリ使用量など、 メリット、デメリットを教えてください。
- ベストアンサー
- MySQL
- MySQLのgroup by同士の結合について
下記のようなSQLについてご指南頂きたく思います。 下記のようなテーブルがあり、 商品毎の売上を販売店毎に 売れた順に格納しています。 <販売テーブル> No | 商品コード | 販売個数 | 販売店コード --------------------------------------------- 1 | 商品A | 1 | 1 2 | 商品A | 1 | 2 3 | 商品A | 1 | 3 4 | 商品B | 2 | 1 5 | 商品B | 2 | 2 6 | 商品B | 1 | 3 7 | 商品A | 3 | 1 8 | 商品A | 4 | 2 9 | 商品A | 1 | 3 上記のテーブルを使って下記のようなデータを取ってきたく考えております。 |商品コード|販売個数 |販売店コード |商品コード毎販売個数合計 | |※ある商品の店毎の販売総数| |※ある商品の販売総数 -------------------------------------------------------------------------------- | 商品A | 4 | 1 | 11 | 商品A | 5 | 2 | 11 | 商品A | 2 | 3 | 11 | 商品B | 2 | 1 | 5 | 商品B | 2 | 2 | 5 | 商品B | 1 | 3 | 5 ----------------------------------------------------------------------------- やり方としては下記の(1)と(2)の結合ができれば良いと考えております。 一回のSQLで上記の結果を得られるようにしたいと考えております。 どなたかアドバイス頂ければと思います。 宜しくお願い致します。 (1)下記のようなSELECT文で商品コード毎の集計はできました。 SELECT 商品コード,SUM(販売個数) FROM 販売テーブル group by 販売テーブル.商品コード; | 商品コード |販売個数 | |※ある商品の店毎の販売総数 --------------------------------------- | 商品A | 11 | 商品B | 5 (2)また、下記のようなSELECT文で販売店、商品コード毎の集計もできました。 SELECT 商品コード, SUM(販売個数), 販売店コード FROM 販売テーブル GROUP BY CONCAT(商品コード,'and',販売店コード) ; | 商品コード |販売個数 |販売店コード | |※ある商品の店毎の販売総数 | ---------------------------------------------------- | 商品A | 4 | 1 | 商品A | 5 | 2 | 商品A | 2 | 3 | 商品B | 2 | 1 | 商品B | 2 | 2 | 商品B | 1 | 3 ----------------------------------------------------
- ベストアンサー
- MySQL
- GROUP BYについて
初歩的な質問になってしまいますが、GROUP BYの使い方について解らない点があるので、宜しくお願いします。 次のようなボーリング大会のレコードがあったとして、 ========bowling TBL (以下、フィールド名)===================== 氏 名|ゲーム回数|1投目|2投目|3投目|・・10投目| ============================================================ 注※ゲーム回数はintで1からインクリメントしていく。 氏名別に1番最後に行ったゲームを参照したい場合 ●SELECT 氏名 MAX(ゲーム回数) GROUP BY 氏名 で、 ----------------------------------------------------- Aさん|3| Bさん|5| ----------------------------------------------------- が得られますが、更にこの最終ゲームでの1投目と2投目の結果のみを付け足し、 ----------------------------------------------------- Aさん|3|12本|19本| Bさん|5|03本|15本| ----------------------------------------------------- のように参照したい場合、どのようなSQL文になるのでしょうか? どなたかご教示宜しくお願いします。
- ベストアンサー
- MySQL
- 10歳単位での GROUP BY
ACCESS2000を使用しています。 従業員データというテーブルの情報を元に、年代別(10歳ごと)の円グラフを作成したいと思っています。 (・20~29歳・30~39歳・40~49歳・50歳以上) どのようにSQL文を書けばいいのでしょうか。 よろしくお願いいたします。 ※今回のテーブルには生年月日ではなく、年齢の数字が直接入っています。 もし生年月日から作成するにはBETWEEN関数と組合せればいいのでしょうか? 2つも質問してすいません。
- ベストアンサー
- オフィス系ソフト
- group by とorder by
失礼致します。 質問させてください。 今テーブルに下のように値が入っています。 No 名前 1 そのこ 2 さちえ 3 あやこ 4 ともよ 5 ひろみ 6 うめこ このテーブルにおいて 名前があ行、か行、さ行の人をNoの昇順で表示し、続けて名前がそれ以外の人のレコードをNoの降順で表示したいです。結果を次のようにしたいです。 2 さちえ 3 あやこ 6 うめこ 5 ひろみ 4 ともよ 1 ののこ sqlを2回発行するのは避けたいです。 ヒントだけでもいいので教えてください。 よろしくお願いします。m(__)m
- ベストアンサー
- MySQL
- group by がうまくいきません。
以下のようなデータがあります。 管理番号 ステータス 受付日 502035 50 2012/09/28 502035 30 2012/09/25 502035 40 2012/09/30 502036 10 2012/09/28 502036 20 2012/09/30 502037 10 2012/09/29 上記のテーブルを管理番号でグループ化し、 ステータスの一番小さい番号の受付日を抽出したいと 考えています。 管理番号 ステータス 受付日 502035 30 2012/09/25 502036 10 2012/09/28 502037 10 2012/09/29 ↑求めたいSQL内容 SELECT 管理番号, Min(ステータス), 受付日 FROM ステータステーブル GROUP BY 管理番号 で行うと、受付日が異なるステータスだと 複数抽出されてしまいます。 First(受付日) Last(受付日)にすると受付日が うまく抽出されません。 受付日をステータスが一番小さな管理番号 でグループ化し、表示することはできますでしょうか? よろしくおねがいいたします。
- ベストアンサー
- SQL Server
- SQL GROUP BY
SQLについてお聞きしたいです。番号カラムの中で最大の数字をもつフィールドと 同じコードを持つデータを、表から全て取得したいと思っているのですが 書き出すとGROUP BYの式が間違っているというエラーが出てしまいます。 SELECT * FROM 表 GROUP BY コード HAVING MAX(番号) = コード どう調べればいいのか分からず四苦八苦しています。どこを直せばよいでしょうか?
- ベストアンサー
- Oracle
お礼
変数に代入した値を即座に使えるということでしょうか 使ったことがない用法が多いので、ひとまずSQLの内容を理解できないまま、 当テーブルの構造に置き換えて実行してみました 最初のt.nenとt.gakuは存在しない旨のエラーになってしまうので、 select x.nen, x.gakuが正解でしょうか うーん、それでもエラーでした ユーザー変数というのは馴染みがないので、 私としてはNo.1のやり方がシンプルで好きです 仮にユーザー変数を使ったやり方だと速度的にはどんなもんでしょう 外部プログラムでループすれば解決ですが念のため