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

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

ただいまの
回答率

87.37%

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

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 1,025

score 69

表題の通りなのですが、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でソートしたいとも考えています。
お詳しい方がおられましたら、御教示頂けると幸いでございます。
どうぞ宜しくお願い致します。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

checkベストアンサー

+2

正解は 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件以上回答のある質問全ての、回答数と正答数と正答率」が一回で得られるかと。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/10/17 17:26 編集

    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出来ていない気がしています。
    すみませんが、方法を調べつつもう少しだけ回答を募りたいと思います。
    ありがとうございました。

    キャンセル

  • 2019/10/17 17:47

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

    キャンセル

  • 2019/10/17 17:48

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

    キャンセル

  • 2019/10/17 22:00

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

    キャンセル

+1

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/10/17 22:07 編集

    ありがとうございます!
    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);

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

    キャンセル

0

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

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2019/10/17 22:12

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

    キャンセル

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

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

関連した質問

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