SQLで質問テーブルに紐付く回答結果テーブルの正解確率を抽出したい

Question

表題の通りなのですが、Laravel6.3にて開発中のサイトのSQLの組み立てとクエリビルダを構築するのに詰まってしまい、手が止まってしまいました。
抽出したい条件は、質問に対する回答結果を格納しており、回答が10件以上付いている質問の正解率（正解数/回答数）を抽出しし、最終的に正解率順にソートしたいと考えています。

---

簡易ですがテーブル状況は以下です。

・質問テーブル (questions)
id int

・回答結果テーブル (results)
id int
question_id int (外部キー参照制約)
result tinyint (0か1)

---

回答が10件以上付いている質問を抽出するEloquentクエリビルダは下記の通りです。

Question::has('results', '>=', 10)->get();

これで以下のSQLが生成されます。

select *
from `questions`
where (
  select count(*)
  from `results`
  where `questions`.`id` = `results`.`question_id`
) >= 10
and `questions`.`deleted_at` is null

この絞り込まれた質問毎の正解率（正解数/回答数）を求める方法に詰まっております。
まずSQLで考えると、以下のようなサブクエリになるような気がするのですが、どうも構築がうまくいきません。

SELECT
*,(
(select count(*) from `results` where `questions`.`id` = `results`.`question_id` AND `results`.`result` = 1) AS CORRECT
/
(select count(*) from `results` where `questions`.`id` = `results`.`question_id`) AS TOTAL
) AS ACCURACY_RATE
from questions;

クエリビルダのメソッドのみで構築出来るのか、もしくはselectRaw()（DB::raw()）を使う必要があるのか。
最終的には抽出した正解率をascもしくはdescでソートしたいとも考えています。
お詳しい方がおられましたら、御教示頂けると幸いでございます。
どうぞ宜しくお願い致します。

Answer 1

正解は result = 1 なんですよね？

であれば、
全回答数＝results の件数（count(*)）
正解数＝sum(result)
で、一回の SQL で得られませんか？

つまりこんな SQL です。

SELECT
  questions.id,
  count(results.id) as total_answers_count,
  sum(results.result) as correct_answers_count,
  sum(results.result) * 100 / count(results.id) as correct_percent
FROM
  questions
  INNER JOIN results ON (results.question_id = questions.id)
WHERE
  questions.id = {質問ID}
GROUP BY
  questions.id

これをクエリビルダで表現できれば行けそうな気はします。
ソートもできるでしょうね。

余談：
回答数が10件以上ある、という条件があるなら、

SELECT
  questions.id,
  count(results.id) as total_answers_count,
  sum(results.result) as correct_answers_count,
  sum(results.result) * 100 / count(results.id) as correct_percent
FROM
  questions
  INNER JOIN results ON (results.question_id = questions.id)
GROUP BY
  questions.id
HAVING
  count(result.id) >= 10

で、「10件以上回答のある質問全ての、回答数と正答数と正答率」が一回で得られるかと。

Comments

[wonda]

tacsheavenさん、ありがとうございます！
MySQL集計関数を駆使する事で表現できるんですね。。
頂いたSQLのresultテーブルをresultsにして、削除フラグ考慮やlimit句などを入れればほぼ目的のデータが取れる事が判りました！

後はこれをEloquent経由のクエリビルダで表現出来れば良いんですが、これもまた難解で。。

Question::has('results', '>=', 10)
->withCount([
'results AS results_cnt',
'results AS correct_cnt' => function (Builder $query) {
$query->where('result', true);
},
])

->selectRaw('`results`.`correct_cnt` * 100 / `results`.`results_cnt` as correct_percent')
としてみるものの、なんかJOIN出来ていない気がしています。
すみませんが、方法を調べつつもう少しだけ回答を募りたいと思います。
ありがとうございました。

[tacsheaven]

Lalabel だとこんな感じですかね？

DB::Table('questions')
->join('results', 'results.question_id', '=', 'questions.id')
->select('questions.id')
->selectRaw('count(results.id) as results_cnt')
->selectRaw('sum(results.result) as correct_cnt')
->selectRaw('sum(results.result) * 100 / count(results.id) as correct_percent')
->groupBy('questions.id')
->having('count(results.id)', '>=', 10)
->orderBy('correct_percent', 'desc')
->get()

[tacsheaven]

例えば正答率が同点の場合、どちらを上位にするか（回答数の多い方が優先かな？）とか、もう少し考えることはあるかと思いますが、基本的にはこんな感じで行けると思います

[wonda]

ありがとうございます！！
Eloquent経由ではないので後はこれに論理削除カラム考慮などが必要になりますが、DBファサードからのクエリビルダだとこれで問題無さそうです。
Eloquent経由の例を挙げて頂いた方がおられ、tacsheaven様から御教示頂いたSQLのお陰で解決となりました！
お忙しいところ、本当にありがとうございました。

Answer 2

tacsheaven さんのSQLをクエリビルダで書いてみた。

動作確認まではしていません。

$query = Question::query()
    ->selectRaw('questions.id, count(results.id) as total_answers_count, sum(results.result) as correct_answers_count, sum(results.result) * 100 / count(results.id) as correct_percent')
    ->join('results', 'results.question_id', '=', 'questions.id')
    ->groupBy('questions.id')
    ->havingRaw('count(result.id) >= 10')
    ->toSql();
dd($query);

Comments

[wonda]

ありがとうございます！
Eloquentを利用する場合、リレーションクラスで簡単に済ます方法ばかり探ってしまっていましたが、`query()`でビルダインスタンスを生成して、後は普通にjoinすれば良かったのですね。
最終的に以下の形で解決となりました。

$items = Question::query()
->selectRaw('questions.*, count(results.id) as total_answers_count, sum(results.result) as correct_answers_count, sum(results.result) * 100 / count(results.id) as correct_percent')
->leftJoin('results', 'questions.id', '=', 'results.question_id')
->groupBy('questions.id')
->havingRaw('count(results.id) >= 10')
->orderBy('correct_percent', 'ASC')
->limit(3)
->get();
dd($items);

高評価とさせて頂きました！
本当にありがとうございました。

Answer 3

ビルダでどのようにするかは分かりませんが、インラインビューでカウントして結合した方が高速じゃないかと思うので、参考までに。

select questions.*
from questions inner join (
      select question_id, count(*) as result_count from results group by question_id having count(*)>=10
    ) results
    on questions.id = results.question_id
where questions.deleted_at is null
order by results.result_count desc

Comments

[wonda]

参考になります。
まだ試験データが足りず、速度検証まではなかなか難しいですが、実稼働後にクエリチューニングする必要が出てきた場合に検討してみます！
ありがとうございました。