• 締切済み

【MySQL】SITEN_CODE毎の高速ランキン

MySQL5.7でランキングの集計をしたいのですが、遅すぎて困っています。 URIAGEテーブルのSITEN_CODE毎にKINGAKU_RANKとKENSU_RANKを集計したい。 SELECT URIAGE.SITEN_CODE, URIAGE.SHAIN_BANGO, (SELECT COUNT(URIAGE2.KINGAKU) FROM URIAGE AS URIAGE2 WHERE URIAGE2.KINGAKU > URIAGE.KINGAKU AND URIAGE2.SITEN_CODE = URIAGE.SITEN_CODE)+1 AS KINGAKU_RANK, URIAGE.KINGAKU, (SELECT COUNT(URIAGE2.KENSU) FROM URIAGE AS URIAGE2 WHERE URIAGE2.KENSU > URIAGE.KENSU AND URIAGE2.SITEN_CODE = URIAGE.SITEN_CODE)+1 AS KENSU_RANK, URIAGE.KENSU FROM URIAGE ・件数が少ない内は動いたので、データ登録(約13万件) ・データ登録後、次のSQLで12時間動かしても終了しない。 という状態で困っています。 高速にランキング集計する方法があればご教授くださいm(_ _)m

  • MySQL
  • 回答数1
  • ありがとう数0

みんなの回答

  • mpro-gram
  • ベストアンサー率74% (170/228)
回答No.1

URIAGE テーブルのカラム構成、index はどのようになってるのでしょう??? まずは(SITEN_CODE,SHAIN_BANGO) でuniqueでないと、select文に期間指定がないので、集計が不正確に思うけど。 KINGAKU と KENSU とそれぞれで毎回比較が行われるので、こちらにもindexがないと、各行ごとに全件比較をおこなってようやくランクがでますから、件数の二乗回比較が行われます。十数万件の二乗ですから飛躍的に時間がかかるでしょう。 よって、(SITEN_CODE , KINGAKU) のindexと、(SITEN_CODE , KENSU) のindexが必要でしょう。 indexが多くなると今度はinsertやupdateでもいちいち時間がかかりますので、複数行編集するなら、その前にindexを無効化して、編集後index有効とするのがよいと思われます。indexの無効化は、MyIsamかinnodbかで異なるのでマニュアルご参照ください。 どこに出力してるのか知らないが全行出力するのにもメモリを喰うし時間取られるので、SITEN_CODEごとの出力にしてしまう手もありじゃあないのかな?

関連するQ&A

  • MySQLで対象期間を決め集計しランキングする方法

    MySQLの初心者ですが、対象期間で集計を行いランキング付けし任意の上位何位までを抽出する方法について教えてください。 全データから集計後上位5位を選ぶ場合・・同順有り select x.`品名`,x.kei,(select count(*)+1 from (select `品名`,sum(`金額`) as kei from uriage group by `品名`) y where x.kei<y.kei ) as rank from (select `品名`,sum(`金額`) as kei from uriage group by `品名`) x having rank <= 5 order by rank といろいろ参考にしてで出来ましたが。任意の対象期間(日付)を設定して実行するSQL文が思いつきません。どこに期間を挿入するのか・・・悪あがきで思い付いた方法として CREATE OR REPLACE VIEW uriage_01 AS SELECT `品名`,SUM(`金額`) AS `金額` FROM uriage WHERE `日付` BETWEEN "2001/01/01" AND "2001/01/31" GROUP BY `品名` で任意期間の集計結果のビューを作りそのビューから select r1.`品名` as `品名`, r1.`金額` as p, count(r2.`金額`)+1 as rank from uriage_01 as r1 left join uriage_01 as r2 on r1.`金額` < r2.`金額` group by r1.`品名` having rank <= 5 order by rank と手間の掛かる方法です。単純に5行だけに限定ならorder by `金額`Limit 5 にすれば良いだけですけど・・・同順があるとどうすれば良いのかと思考停止状態です。尚、当方は、MySQLの勉強を始めたばかりにですので宜しくお願いいたします

    • ベストアンサー
    • MySQL
  • SQL文(副問合せについて)

    SELECT URIAGE_NO FROM URIAGE AS U1 WHERE URIAGE_SURYO > (SELECT AVG(URIAGE_SURYO) FROM URIAGE AS U2 WHERE U1.HINMOKU_CODE = U2.HINMOKU_CODE); 上記SQLの構文で (SELECT AVG(URIAGE_SURYO) FROM URIAGE AS U2 WHERE U1.HINMOKU_CODE = U2.HINMOKU_CODE この部分の論理がわかりません。 書籍では「HINMOKU_CODEごとのURIAGE_SURYOの 平均を求める」ということですが、何故このように なるのかがわかりません。副問合せではGROUP BY句 が使えないので、非常に困っております。 下記にデータを記載致します。 以上、宜しくお願いします。 ■主問合せ URIAGE.NO HINMOKU_CODE URIAGE_SURYO 001 BP001 10 002 BP002 20 003 BP002 30 004 BP002 10 005 BP001 20 006 BP003 30 007 BP004 10 008 BP004 20 009 BP003 20 ■副問合せ HINMOKU_CODE AVG(URIAGE_SURYO) BP001 10 BP002 20 BP003 25 BP004 15 ■結果 URIAGE.NO HINMOKU_CODE  003 BP002 005 BP001 006 BP003 008 BP004

  • EXSIST述語を使った副問合わせについて

    SELECT HINMOKU_NAME FROM HINMOKU WHERE HINMOKU_CODE IN (SELECT HINMOKU_CODE FROM URIAGE WHERE URIAGE_DATE = '2004-11-10'); 上記をSQL文をEXISTS述語を使って書き換えると エラーが出てしまい、うまく書き換えがすることが できません。下記にエラーがでるSQL文を記載いたします。 SELECT HINMOKU_NAME FROM HINMOKU WHERE EXISTS (SELECT HINMOKU_CODE FROM URIAGE WHERE URIAGE_DATE ='2004-11-10' WHERE HINMOKU.HINMOKU_CODE=URIAGE.HINMOKU_CODE); 上記SQL文についてどこに問題があるのか、ご教授お願い致します。

  • PHPでMySQLのデータを表示したい

    /* どなたか以下のエラーの原因をご教授ください。よろしくお願いいたします。 金額の多い人順にデータを並べ替えて上位3人を順番に表示するプログラムです*/ <?php mysql_connect("localhost","user","password"); mysql_selectdb("rensyu"); $result = mysql_query("SELECT * FROM uriage ORDER BY kingaku DESCLIMIT 3"); $uriageStr = ""; $count = 1; while(!$data = mysql_fetch_array($result)) { $uriageStr = $uriageStr."namae".$count."=".$data[0]."&kingaku".$count."=".$data[0]."&"; } $uriageStr = substring($uriage,0,length($uriage)-1); print($uriageStr); ?> /* MySQLにはrensyuデータベースを作成、それにuriageテーブルを作成、フィールドとしてnamaeとkingakuを作成、 namaeにakiko,bunta,coco,daisuke,eikoを入力、それぞれにkingakuとして1,2,3,4,5を入力している。 この場合http://localhost/get_uriage.phpと入力した場合次のような結果が表示されるはずです。 namae1=eiko&kingaku1=5&namae2=daisuke&kingaku2=4&namae3=coco&kingaku3=3 しかし、表示結果は Parse error:parse error,unexpected T_STRING in C:\www\get_uriage.php on line 9 でした。 TeraPadの行表示でで9行目は $count = 1;です。(コメントは除く) */

    • 締切済み
    • PHP
  • ASPからのSQL文でエラーが発生

    ASPでoracleのデータを集計しようとしたところ、SQL文でエラーが発生してしまいました。 (ORA-00911: 文字が無効です。) 同じSQL文を「SQLPlus Worksheet」で実行してみたら、問題なく実行できました。 エラーの原因を教えてください。 SELECT CASE WHEN 処理CD = '1' AND 集計F = 'Y' THEN '1Y' WHEN 処理CD = '1' AND 集計F <> 'Y' THEN '1N' END AS kubun, COUNT(連番) AS kensu SUM(金額) AS kingaku FROM aaa WHERE 日付 = '200701' GROUP BY CASE WHEN 処理CD = '1' AND 集計F = 'Y' THEN '1Y' WHEN 処理CD = '1' AND 集計F <> 'Y' THEN '1N' END kubun毎に金額の合計値などを集計したいというような感じです。 よろしくお願い致します。

  • MySQLチューニング

    同順位を考慮したランクの取得について(1~55位まで) 下記の2つのテーブルがあります。 rankingscoreinfo フィールド名 型 長さ missionid integer 4 NOT NULL name varchar 20 NOT NULL skill integer 4 NOT NULL score integer 4 NOT NULL rankinginfo フィールド名 型 長さ NOT NULL missionid integer 4 NOT NULL name varchar 20 NOT NULL kind integer 4 NOT NULL missionidとname2つのテーブルの共通フィールドです。 この二つを組み合わせて同順位を考慮したスコアランキングを作成したいです。 1位のscoreが3人同点の場合、3人のスコアランキングは1位で次の人は4位になるようにしたいです。 SQLを組んだのですがレコード数が増えるにつれてどんどんSQLが返ってくる速度が遅くなって困っています。 この二つのテーブルの最速のSQLを組みたいのですがどなたか教えて下さい。 とりあえずINDEXは考慮なしでお願いします。 参考までに私が組んだSQLは下記のものになります。 SELECT d.name FROM (SELECT *, (SELECT COUNT(a.score)+1 FROM rankingscoreinfo AS a , rankinginfo as b WHERE a.missionid=10 AND a.skill= 2 AND b.kind = 2 AND a.missionid = b.missionid AND a.name = b.name AND a.score > c.score ) AS score_rank FROM rankingscoreinfo AS c WHERE c.missionid = 10 AND c.score AND c.skill=2 ) AS d , rankinginfo as e WHERE e.missionid = 10 AND e.kind = 2 AND d.skill = 2 AND d.missionid = e.missionid AND d.name = e.name AND d.score_rank >=1 AND d.score_rank <= 55 ORDER BY score_rank

    • ベストアンサー
    • MySQL
  • MySQL4でViewの代わりにできますか?

    PostgreSQLで下記のようなビューを作成し、そのビューから日付でgroupbyして日付ごとのユニーク件数を取っていました。 ---------------------------------------------- create view v_uniqcountday as select substring(datetime, 1, 8) as date, uniqid, careercd, count(*) as cnt from accesslog group by date, uniqid, careercd order by date; select date, count(*) as cnt from v_uniqcountday where (date >= xxx) and (date < xxx) group by date; ---------------------------------------------- しかし、MySQLではViewは作成できないようです。 そこでselect文のみで上記のような集計は可能でしょうか? よろしくお願いいたします。

  • MySQL8.0で中央値を求める方法

    Windows10 64bit環境でMySQL8.0を使い中央値を求めるため模索している素人です。 5.6ではユーザー定義変数を使った下記の方法で求めていました。 SELECT avg(t1.`金額`) as `金額` FROM ( SELECT @rownum:=@rownum+1 as `row_number`, d.`金額` FROM uriage d, (SELECT @rownum:=0) r ORDER BY d.`金額` ) as t1, ( SELECT count(*) as total_rows FROM uriage d ) as t2 WHERE 1 AND t1.`row_number` in ( floor((total_rows+1)/2), floor((total_rows+2)/2)); しかし、これを8.0で実行すると結果は求められるのですが、次のような注意が出ます 「式内でのユーザー変数の設定は非推奨であり、将来のリリースで削除される予定です」 その上ODBCで通らなくなりました。そこで何か他にないかと検索したところ下記のサイトにPreparedStatementを使っている別の方法がありました、 *ttps://qiita.com/nkojima/items/2c483d4ddbdb29439c87 この場合。注意はでませんがODBC(デフォルト設定)では通らなかったのでさらに検索したところ *ttps://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql&usg=ALkJrhjuCCaICL0uLguGt2oD4qbVM9cG7g にウインドウ関数を使った単一ステートメントの下記の方法が紹介されていました、 SELECT AVG(t1.`金額`) AS median_val FROM (SELECT `金額`, ROW_NUMBER() OVER(ORDER BY `金額`) AS row_num FROM uriage) t1, (SELECT COUNT(*) AS num_records FROM uriage) t2 WHERE t1.row_num IN (FLOOR((t2.num_records + 1) / 2), FLOOR((t2.num_records + 2) / 2)) MySQLで100万件のレコードを対象に試したところ上記の変数を使った2件の方法より同等か少し遅い結果となりました。それでも標準SQLでの方法よりはるかに速いのですが・・・ また、以前使っていた32bit5.6に比べて上記2件の方法が遅いことがわかりました(平均3.0と1.9secが3.188と2.806secに)。・・・ とあるサイトで8.0ではCOUNT(*)が遅い原因の一つと考えられているとあったのででCOUNT(カラム名)に変更 (1)1.670 (2)1.640 (3)1.919 となりウインドウ関数を使った単一ステートメントの方法が一番遅い結果となりました。・・えっと!思いそこで前々回に質問しました(削除済み)・・平均値は前回のバッテリーモードから変更して測定し直しました。 さら検索していたら単一ステートメントの方法は見つかりませんでしたが、あるサイトにwith句をを使った方法が載っていました(試したところクエリが成立せず?)。1部を改変し下記クエリを試してみました。 SELECT COUNT(*)+1 INTO @rowindex FROM uriage; WITH i AS (SELECT `金額`,ROW_NUMBER() OVER (ORDER BY `金額`) AS rowindex FROM uriage) SELECT AVG(`金額`) AS median_duration FROM i WHERE i.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2)); この方法では、COUNT(カラム名)よりCOUNT(*)の方が速い結果となりました。 (4)COUNT(カラム名)・・1.906   (5)COUNT(*)・・1.547・・・・これでまた混乱を増加 他のDBからのODBC経由でSQLを実行するスクリプトを組んでいるので(3)の単一ステートメント方法以外にも、(2)や(5)の方法を参考に最初にCOUNTするクエリで値を取得し変数使い次に計算式でクエリを整形し直してから発行する2度手間のスクリプトを組んで中央値を取得していましたが、投稿の後調べてみるとODBCのシステムDSNの設定でconnectionのAllow multiple statementsにチェックで複数ステートメントの発行が可能でした。・・知らないことが多すぎる! もし他に別の方法があればと思い再々投稿しました。

  • 集計関数の合計について

    お世話になります。 SQLSever2005を使用しています。 Count(*)で月毎のデータ件数を集計し、またその結果を出力するSQLを考えております。 SELECT  (SELECT Count(*) FROM TBL_TEST T1 WHERE T1.T_DATE>='2005/01/01' AND T1.T_DATE=<'2005/01/31') AS CNT_1,  (SELECT Count(*) FROM TBL_TEST T2 WHERE T2.T_DATE>='2005/02/01' AND T2.T_DATE=<'2005/02/31') AS CNT_2,   (SELECT Count(*) FROM TBL_TEST T3 WHERE T3.T_DATE>='2005/03/01' AND T3.T_DATE=<'2005/03/31') AS CNT_3,   (CNT_1+CNT_2+CNT_3) AS TOTAL_CNT ・・・・・・・・・・・・ (★)  FROM TBL_TEST T  WHERE ・・・・・・・・・ 結果(★)のところで『CNT_1は無効です』というエラーになってしまいます。 ちなみにAccessではエラーにならずに結果を出力していました。 上記のSQL文はどのように修正すればよろしいでしょうか?

  • SELECT文でこんなことはできるでしょうか?

    例えば、日付別の売上のリストを作る場合に SELECT date,SUM(kingaku) AS goukei FROM uriage WHERE date BETWEEN '2007/01/01' AND '2007/01/31' GROUP BY date ORDER BY date を発行して, date goukei 2007/01/01 10000 2007/01/03 20000 2007/01/04 30000 2007/01/06 20000 2007/01/07 40000 2007/01/08 50000 ・・・ を取得したとして、 これを date goukei 2007/01/01 10000 2007/01/02 0 2007/01/03 20000 2007/01/04 30000 2007/01/05 0 2007/01/06 20000 2007/01/07 40000 2007/01/08 50000 ・・・ というように、データが1件もない日付も 間にはさむ方法はあるでしょうか? 以上、よろしくお願いします。