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

Question

###前提・実現したいこと
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
・ゲーム種別
を指定することで、そのゲーム種別のスコアからランキングを取得したく思っております。

Comments

[sazi]

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

[yambejp]

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

[yambejp]

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

Answer 1

そもそも
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がユニークではないので、１つだけランク抜き出すことは無理
ランクの羅列がほしいのでしょうか？

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毎のランクをとってもいい

Comments

[orange377]

回答ありがとうございます。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をかけたいが、
それをまとめて書く方法があるか？という点です。

[yambejp]

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

[orange377]

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

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度で済ませることは不可能なのでしょうか。

[yambejp]

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

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

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

[orange377]

どの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にしかかからず、想定とは違った動きになります。

[orange377]

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

[yambejp]

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")

[orange377]

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

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