質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.75%

  • MySQL

    5512questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2228questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

相関サブクエリと親クエリに共通するJOIN処理をまとめたい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 525

orange377

score 10

前提・実現したいこと

MySQL初学者です。
相関サブクエリと親クエリで共通のJOIN処理を行っていますが、
ベタ書きのため恐らく2回テーブルの読み込み、JOINが走っているかと思います。
ここの読み込みを1度だけで同様の機能を実現できないでしょうか。
※真の目的は処理速度をなるべく早くすることです。レコード数10万件以上を想定しています。

該当のソースコード

ランキングの処理です。テーブルAにスコアを保持しており、テーブルBの種別マスタに合うデータのみでランク付けをする想定です。

SELECT (
    SELECT count(*)+1 FROM テーブルA AS NO1 INNER JOIN テーブルB ON テーブルA.種別 = テーブルB.ID AND 条件
    WHERE スコア > NO2.スコア) AS rank
FROM テーブルA AS NO2
INNER JOIN テーブルB ON テーブルA.種別 = テーブルB.ID AND 条件
WHERE 最終条件

試したこと

「FROM NO1」や「FROM NO2」は相関クエリのためかうまくいきませんでした。
一時テーブルにJOIN後のテーブルを格納する方法も試してみましたが、処理速度が遅くダメでした。

追記 No1

>SQL文少し書き間違ってますね(メインのテーブルB⇒テーブルA)
仰る通りです。書き写しにミスがありましたので、修正しました。

またテーブルのサンプルを連携します。

テーブルA
ID ユーザーID 種別ID スコア
PK   UK    UK  idx

テーブルB
ID 種類ID 外部連携用ID
PK  UK    UK 


情報は絞っていますがおおよそ上記のような感じで、UKは複合UKです。
JOINの条件としてテーブルBのUKを使い、最終条件で特定のユーザーIDのランキングを取得する想定です。
よろしくお願い致します。

追記No2

>丸投げの割に提示する情報が少ないですね。追記されたサンプルではどういったデータ群からどういった結果を導きたいのか類推できません。
失礼しました。まだまだSQLは勉強不足で、どういう情報が必要なのかわかっていないのです。。もう少し情報を追記してみます。
テーブルA:ユーザー毎のスコアを管理
テーブルB:ゲームの種別マスタ(じゃんけん、クイズ、など)
じゃんけんやクイズに関するスコアがテーブルAで管理されており、ランキングを取得する際は
・ユーザーID
・ゲーム種別
を指定することで、そのゲーム種別のスコアからランキングを取得したく思っております。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • sazi

    2017/10/13 18:34

    SQL文少し書き間違ってますね(メインのテーブルB⇒テーブルA) 。インデックスの状況が分からないので実行計画を提示された方が良いかと思います。

    キャンセル

  • yambejp

    2017/10/13 19:10

    テーブルA、Bの簡単なサンプルがあれば記載下さい

    キャンセル

  • yambejp

    2017/10/16 10:40 編集

    丸投げの割に提示する情報が少ないですね。追記されたサンプルではどういったデータ群からどういった結果を導きたいのか類推できません。

    キャンセル

回答 1

checkベストアンサー

+2

そもそも
select (なんたらかんたら) as rank from ・・・
では特定のidが指定されていないのでrankのつけようがありません

select id,(select count(*)+1 from tbl as t2 where t1.id=t2.id and t1.hoge>t2.hoge)
from tbl as t1


のように集約するidの指定と、関連付けと、比較条件が必要です

また命題のテーブルAとテーブルBの紐付けについても本当に必要かどうかわかりませんし
もし必要だとするなら、一度viewに落とし込んで、上記のようなランク処理をしてみると
わかりやすくなります。

繰り返しになりますが、この手の質問は概念的なことを書かれても意味がわかりませんので
具体的なサンプルをつけて、「このサンプルからこういった結果を抽出したい」という
書き方をしないといつまでたっても結論はでないと思いますよ

 追記

tbl_aのsampleです。

create table tbl_a(id int unique key,val int);
insert into tbl_a values(1,100),(2,200),(3,300),(4,50);

select (select count(*)+1 from tbl_a as t2 where t2.val>t1.val) as rank
from tbl_a as t1 where id=1;
/* id=1は結果:3位 */


これにtbl_bがどうからんでくるのかいまいちわかりません

 追記2

tbl_aのcate_idがユニークではないので、1つだけランク抜き出すことは無理
ランクの羅列がほしいのでしょうか?

create table tbl_a(id int unique key,val int,cate_id int);
insert into tbl_a values(1,100,1),(2,200,1),(3,300,2),(4,50,2);

create table tbl_b(id int unique key,name varchar(10), type varchar(10));
insert into tbl_b values(1,"janken","win_rate"),(2,"quiz", "win_count");


これに対して、name = 'janken' and type = 'win_rate'を元にランク計算

select (select count(*)+1 from tbl_a as t2 where t2.val>t1.val) as rank
from tbl_a as t1 
where cate_id in (select id from tbl_b  where name = 'janken' and type = 'win_rate')


※select id,(・・・) as rank
として、id毎のランクをとってもいい

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/18 11:04

    回答ありがとうございます。IDの指定は一番最後の[最終条件]でしております。
    見返してみると全然伝わらないですね、申し訳ないです。
    参考にした元のSQL文は以下です。
    ```
    select id, highscore, (
    select count(*)+1 from test as t2
    where t2.highscore > t1.highscore) as rank
    from test as t1
    where id = ○○;
    ```
    今回の質問の意図は、上記でいう[test]に同じ内容のJOINをかけたいが、
    それをまとめて書く方法があるか?という点です。

    キャンセル

  • 2017/10/18 11:53 編集

    あっと、失礼しました。ちょっと勘違いしていたようです。
    追記しますが、これにtbl_bはどう絡むのでしょうか?
    tbl_aに対して1対1でつながるだけならすんなり行くともいますが
    1対nやn対1になるとそもそもランクの考え方とあわないと思います
    いずれにしてもサンプルの提示がないのでなんといえませんけどね

    キャンセル

  • 2017/10/18 14:03

    サンプルまで提示いただきありがとうございます。そういう提示の仕方をすればよいのですね。

    create table tbl_a(id int unique key,val int,cate_id int);
    insert into tbl_a values(1,100,1),(2,200,1),(3,300,2),(4,50,2);

    create table tbl_b(id int unique key,name varchar(10), type varchar(10));
    insert into tbl_b values(1,"janken","win_rate"),(2,"quiz", "win_count");

    上記のようにtbl_aにはtbl_bと紐づけるためのcate_idを保持しており、
    ユーザーからはcate_idは見えていないので

    ~ FROM tbl_a INNER JOIN tbl_b on cate_id = tbl_b.id and name = "janken" and type = "win_rate"

    で紐づける想定です。
    より高速化するために、このJOIN処理を1度で済ませることは不可能なのでしょうか。

    キャンセル

  • 2017/10/18 14:30 編集

    前回の回答にかいたとおり、いまのテーブル構成では無理ですね
    1対nで処理されているのでロジックが破綻しています。

    name = "janken" and type = "win_rate"で抽出されるcate_idは「2」
    これがtbl_aの3と4の両方に紐付いているので、rankを出そうにもどちらのidに基づいた
    ランクを出すか判断できません

    一応どこまでできるかだけ追記しておきます

    キャンセル

  • 2017/10/18 14:58

    どのidに紐づいたランクを出すかは親クエリのWHERE句でtbl_aのidを使用します。
    (where id = ○○;の部分)

    SELECT (
    SELECT count(*)+1 FROM tbl_a AS t1 INNER JOIN tbl_b ON cate_id = tbl_b.id AND name = "janken" and type = "win_rate"
    WHERE val > t2.val) AS rank
    FROM tbl_a AS t2
    INNER JOIN tbl_b ON cate_id = tbl_b.id AND name = "janken" and type = "win_rate"
    WHERE t1.id = 〇〇

    動作は確認できております。
    t1とt2のJOINに同じ処理を書いている箇所を効率化できないか、という質問です。
    分かりづらく申し訳ありません。

    ちなみに提示いただいたSELECT文だとIN句がt1にしかかからず、想定とは違った動きになります。

    キャンセル

  • 2017/10/18 15:01 編集

    最後のWHERE句はt2.idの誤記です。

    キャンセル

  • 2017/10/18 15:10

    t1とt2の結合にt2.cate_id=t1.cate_idを条件付けして下さい

    同じカテゴリ内の順位ということなんですかね?

    select id,(select count(*)+1 from tbl_a as t2 where t2.val>t1.val and t2.cate_id=t1.cate_id
    ) as rank
    from tbl_a as t1
    where cate_id in(select id from tbl_b where name = "janken" and type = "win_rate")

    キャンセル

  • 2017/10/18 15:14

    > 同じカテゴリ内の順位ということなんですかね?
    そうです!なかなかうまく伝えられずすみません。

    変に複雑に考えてしまっていましたが、
    > t2.cate_id=t1.cate_idを条件付けしてください
    で視界が開けました。
    今回の質問でいろんな学びがありました。
    本当にありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.75%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • MySQL

    5512questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • SQL

    2228questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。