- 締切済み
GROUP BYによる集計後のソートの高速化にアイデアはないでしょうか?
ウェブサービスはいろいろ作ったことはあるのですが、分析系は作ったことがなく、DB構造について悩んでるところであります。 現在の課題は、商品ごとの売り上げ集計後に、それをランキング表示して表示する、というところなんですが、 select 商品ID,SUM(売り上げ)as`sales` from 売り上げテーブル group by 商品ID order by sales DESC limit 0,30 というものですが、やはり sales にインデックスが使えないのでtemporary table と file sort がでてきて遅くなってしまいます。 order by をはずすとやはり高速にレスポンスは返ってきます。 日別、月別、などのサマリーテーブルは作ってはいるのですが、単月ではなく、複数月指定での集計だとサマリーはないので集計をしないといけなくなります。 基本的なことなんだと思いますが、諸先輩方はどういったやり方で解決されておられるのでしょうか? なお、試験段階の現在は、売り上げテーブルには400万件、商品データは6万件あります。ですので、order by をはずした場合、6万件ほどがヒットし、それを(file sortで)並び替えてる状態だと思います。
- SHlVA
- お礼率92% (60/65)
- MySQL
- 回答数2
- ありがとう数2
- みんなの回答 (2)
- 専門家の回答
みんなの回答
- hrm_mmm
- ベストアンサー率63% (292/459)
>複数月指定での集計 月別集計した物を、unionで複数月連結して、sum をとればよさそうな要件にも思えるのだけど?
- yambejp
- ベストアンサー率51% (3827/7415)
400万件のデータをダミーでつくってテストできる環境ではないため なんともいえませんが、order by している以上テンポラリはしかたないのでは? ヒープにおとしてソート処理すれば若干はやくなるかもしれませんが あまり期待できないかなぁ・・・
お礼
アドバイスありがとうございます。 そうですね、私もHEAPは考えたのですが、じゃあいつの段階で消すのか、というのもありますし、放置しておくとメモリーを食い尽くしそうな気もしますし、いろいろ試行が必要ですね。 結局、テンポラリもメモリ上だとは思いますが、設定でメモリをしっかり割り当てて、最適化することが大切ですよね。
関連するQ&A
- GROUP BYで集約されるときのソートを変えたい
GROUP BYで集約されるときのソートを変えたい MySQLの5.1を使用しています。 テーブル"tbl"には、"no","id","score"の3つのフィールドがあり、 "no"は主キーです。 +---+----+-------+ | no | id | score | +---+----+-------+ | 1 | 10 | 10002 | | 2 | 10 | 10000 | | 3 | 10 | 10008 | | 4 | 11 | 10004 | | 5 | 12 | 10006 | +---+----+-------+ 上記の表から、 scoreの値が高い順にグループ化してソートしたいのですが、 以下のSQL文ではscoreが最初に登録されたものに集約されてからグループ化されてソートしてしまうため、 思い通りの結果が得られずに困っています。 ↓具体例 SELECT no, id, score FROM tbl ORDER BY score DESC GROUP BY id; +---+----+-------+ | no | id | score | +---+----+-------+ | 5 | 12 | 10006 | | 4 | 11 | 10004 | | 1 | 10 | 10002 | +---+----+-------+ ↓欲しいソート順 +---+----+-------+ | no | id | score | +---+----+-------+ | 3 | 10 | 10008 | | 5 | 12 | 10006 | | 4 | 11 | 10004 | +---+----+-------+ テンポラリテーブルはなるべく使用せずソートしたいのですが、 このような事は可能なのでしょうか? ご存知の方がいらっしゃいましたら教えていただけると助かります。 よろしくお願いします。
- ベストアンサー
- MySQL
- group by のソート
mysql Client API version 3.23.49 PHP/4.4.5 で動作させようと思っています。 table a b 1 2 3 4 2 4 7 1 2 6 1 6 上のデータから以下のような結果を得たいのですが table a b idcount1 1 6 2 3 4 1 2 6 2 7 1 1 グループで集計をとり、その上でbに6がある場合は6その他は bの値にNULLもしくは別の値が入るという形でいいのですが・・・ できません。 SELECT a, b, count( * ) AS idcount1 FROM table GROUP BY a するとはじめに読みこんだ値がbには入ってしまいます。 結果 table a b idcount1 1 2 2 3 4 1 2 4 2 7 1 1 SELECT a, b, count( * ) AS idcount1 FROM table where b=6 GROUP BY a するとbの値ははじきますし、カウントしている意味がなくなります。 結果 table a b idcount1 1 6 1 2 6 1 GROUP BY 処理前のソートを考え、from table後に oder by b=6 asc を使ったがエラー 末尾に oder by b=6 asc を使うと最初に試した値をソートするだけでした。 宜しくお願いいたします。
- ベストアンサー
- MySQL
- GROUP BY使用時のソートについて
OracleSqlにて「GROUP BY」使用した日付のデータを取得するSql文を作成したのですが、 「ORDER by」句に「DESC」を付けても降順ソートが行えません。 作成したSql文は以下の通りなのですがなぜ降順ソートが行えないのでしょうか? 「GROUP BY」を使用するとソート出来ないと言うことなのでしょうか? それとも日付型なのでソートが出来ないと言うことなのでしょうか? どなたかご存知の方おりましたらご教授下さい。 ------------------------------------------------- SELECT TO_CHAR(RECORDDATE,'YYYY/MM/DD') ,RECORDYEAR ,RECORDMONTH ,RECORDDAY FROM STOCKTBL WHERE TO_CHAR(RECORDDATE,'YYYY/MM/DD')<='2002/03/07' AND TO_CHAR(RECORDDATE,'YYYY/MM/DD')>='2002/02/22' GROUP BY TO_CHAR(RECORDDATE,'YYYY/MM/DD') ,RECORDYEAR ,RECORDMONTH ,RECORDDAY ORDER by TO_CHAR(RECORDDATE,'YYYY/MM/DD') ,RECORDYEAR ,RECORDMONTH ,RECORDDAY DESC -------------------------------------------------
- ベストアンサー
- その他(データベース)
- group byにより集計した結果での名称取得方法
次のようなテーブルがあったとして、一回のSQLで商品名称とその売上件数の一覧を作成することは可能なのでしょうか? 【売上テーブル】 ID 販売日 商品ID 0001 2011/01/01 1 0002 2011/01/01 2 0003 2011/01/03 1 0004 2011/01/10 1 0005 2011/01/12 2 ・・・ 【商品マスタ】 ID 商品名 1 りんご 2 みかん 3 いちご 【最終的に作成したい一覧】 商品名 件数 りんご 3 みかん 2 いちご 0 ※売上テーブルに存在しない商品についても、件数が0件として一覧に出力出来るようにしたいです。 select 商品ID from 売上テーブル group by 商品ID にて商品ID毎の件数を算出し、それとは別に select ID,商品名 from 商品マスタ として、商品IDと名称のリストを取得後、アプリにてこれらの情報を突き合わせれば実現できるのは分かるのですが、これらの処理を一回のSQLにまとめることは可能なのでしょうか? よろしくお願いします。
- ベストアンサー
- SQL Server
- クロス集計の日付表示について
はじめまして。 クロス集計で悩んでいます。 日別の売上データをクロス集計で表示する際 月別の合計として列軸に表示したいのですが・・・。 例:テーブル:売上一覧 XXX商事 2010/11/5 A商品 2個 XXX商事 2010/11/8 A商品 2個 BBB商会 2010/11/8 B商品 3個 XXX商事 2010/12/5 A商品 2個 のようなでーたーを クエリ:月別売上クロス 2010/11月 2010/12月 ______________________________________________________ XXX商事 A商品 4個 2個 BBB商会 B商品 3個 のように表示したいのです。 この場合テーブルの書式プロパティで "YY/MM"とすればテーブルでは10/11と表示されますが クロス集計にすると、日別で表示されます。(11/5.11/8) どうしたらいいでしょうか
- 締切済み
- オフィス系ソフト
- 売上を集計する際、売れていない日も結果に含めることはできますか?
以下のような売上表から売上数を日別に集計する際、 売れていない日も結果に含めることはできますか? 売上表 年月日,商品コード,売上数 2007-01-01,0001,1 2007-01-01,0002,3 2007-01-02,0001,2 2007-01-03,0003,1 ... 求めたい集計結果 2007-01-01,0 2007-01-02,0 2007-01-03,1 2007-01-04,0 2007-01-05,2 2007-01-06,0 2007-01-07,0 ... 年月日は(休日がないとして)、 select distinct 年月日 from 売上表 order by 年月日; でわかるのですが…。 よろしくお願いします。
- ベストアンサー
- MySQL
- ソートしてからグループ化したいけれど・・・。
SQL構文について質問させて下さい。 ABC │ID │DATE ------------------ XXX │aaa │02/01 YYY │bbb │02/02 YYY │aaa │02/03 と言うデータから、DATEが新しい(大きい)値を抽出し、 次に重複するIDを排除した結果を出したいです。 希望の検索結果は、 ABC │ID │DATE ------------------ YYY │bbb │02/02 YYY │aaa │02/03 となって欲しいのです。 単純にソートした後でIDでグループ化したいのですが、当然できなく、私の考えでは、 SELECT abc, id, max( date ) FROM `test` GROUP BY id ORDER BY date DESC なのですが結果は、 ABC │ID │DATE ------------------ YYY │bbb │02/02 XXX │aaa │02/03 と言う風になり、何故か、2行目の様にDATEに対して、ABCの値が別の行の ID:aaaの値になってしまってます。 どうしたら良いのでしょうか?
- ベストアンサー
- MySQL
- ピポットテーブルのグループ化について
エクセルの初心者です。ピポットテーブルのグループ化により日別になっている売上日を月別集計させようと思ったところ『選択対象をグループ化することはできません』と出てしまってできませんでした。 なぜでしょうか?
- 締切済み
- その他MS Office製品
- ピポットテーブルの集計結果でのソート
エクセル2000を使っています。 顧客名称、商品名、売上という3つの項目のデータで 商品名をページ見出しにして、顧客名ごとの売上を作りました。集計は売上の合計を使用しており、売上ごとの上位10箇所を表示させたいのですが、ピポットテーブルフィールド集計オプションや詳細オプションを色々やってみても”見出し”が青くなるだけでうまくできません。どなたか集計結果のソートでわかる方がいらっしゃったらアドバイスおねがいします。
- 締切済み
- オフィス系ソフト
- Access2007でのクエリの集計
今Access2007を使って月別に商品の売上を集計するものを作っています。 そこで売上を月別で集計することはできたのですが、月で売上の無い物は集計できずに空白が表示されてしまいます。それで空白に0を入れたいのですが、やり方がいまいちわかりません。 どうやったらいいか教えてください。お願いします
- 締切済み
- その他(データベース)
お礼
アドバイスありがとうございます。 なるほど、単月毎に売り上げ順に並び替えたものをUNIONでつなげて、それをSUMする、という逆のアプローチということですかね。 UNIONしたあとにGROUP BYはできましたっけ。その後にでてくる値がちゃんと売り上げ順になってるかもやってみないとわかりませんね。ちょっと試してみます。