効率的なSQL文が思いつきません

Question

いつもお世話になっております。
前回（多分1年ほど前）
質問させて頂いた際は丁寧に回答頂きまして感謝しております。

自分であれこれと思いめぐらせてみたのですが、これだと思うSQL文が思いつきませんでして、早速ですが質問をさせて頂きたいと思います。

テーブル構造
user_id   ques_id  result  (プライマリーは別途)
　001　　1　　　1
　002　　1　　　0
　003　　1　　　1
　004　　1　　　1
　002　　2　　　1
　003　　2　　　0
　001　　3　　　1
…

学習のデータベースの初答の場合の結果を記録するものです。
user_idとques_idでユニークです。
例えば、1レコード目はユーザ001が
問題番号1を正解(result=1)したことを表します。

※任意問題に対してすべてのユーザが
　問題を解いているとは限りません。

ここで、取り出したい情報は

ある任意の問題セット(例: (1,3,4,5))を全て解いているユーザ
のuser_idとその問題セットの正答率です。

理想的な結果としては
ある問題セットに対して

  user_id   正答率　
    001       3/4

のように出力されて欲しいです。

*私が考えたこと
(SELECT * from テーブル WHERE ques_id = 1) as t1 LEFT JOIN (SELECT * from テーブル WHERE ques_id = 3) as t2 
USING(user_id)

って繋げてみて・・・どうするんだろう(？？？)
これでもなんかいびつな感じがとってもします。
って感じです。

アドバイス頂ければ幸いです。

masa6272 · Accepted Answer

SELECT user_id, concat(sum(result), '/', count(*))
FROM テーブル
WHERE ques_id in (1,3,4,5)
GROUP BY user_id
HAVING count(*) = 4;

でどうでしょう?

masa6272 · Answer

chukenkenkou　さんの回答への補足。

sum(result)/count(result)　は　AVG(result) で書けます。
count(*)以外の集約関数は、NULLを最初から取り除きます。

chukenkenkou · Answer

ありゃりゃ。。。

ちょっとテストした結果のSQLをそのまま提示してしまいましたが、今回の提示要件の通りなら、クエリのネストは不要でしたね。
ただ、クエリをネストすることで、内側のクエリで付けた別名を、その側のクエリで利用したり、すべての列の値を表示したりできるので、クエリをネストすることに、まったく意味がない訳ではありません。

「提示されたデータが中途半端」と書いたのは、「表の他の列の値」という意味ではなくて、得たい結果としてuser_id='001'のデータが4件必要なのに、提示されたデータでは2件しかないという意味です。
こういったサイトで質問する場合は、実際に使っている表定義、データ、得たい結果などで、簡略化するのは仕方ないことです。しかし、簡略化した中では、整合性がある内容にしないと、他人がアドバイスする上で、いろいろ誤解釈が生じることが多いです。

さて、本題に戻りますが、例えば、「問題は解いたけれど、合否判定が未実施」といったケースがあり得るなら、「count(*)」でなく、「count(result)」とすることで、nullで入っているデータを除外できます。
そういうケースがないなら、「count(*)」で問題ありません。

select
  user_id,
  sum(result) as 正答数,
  count(result) as 有効回答数,
  count(*) as 総解答数,
   sum(result)/count(result) as 有効正答率
 from t1
 where ques_id in(1,3,4,5)
 group by user_id
  having count(*)=4
;

chukenkenkou · Answer

MySQLの質問をする場合は、バージョンを明記してください。
バージョンにより、SQL周りだけでも大きく機能が違います。

例えば、こんな感じです。
MySQL 4.0以前・・・一部のjoin、union、一時表などは実装済
MySQL 4.1・・・joinの実装範囲の拡張、サブクエリ、unicodeなどの実装
MySQL 5.0・・・ビュー、ストアドプロシジャ、トリガなどの実装

データ例、得たい結果、自分で考えたSQLが提示されている点は好感を持てますが、データ例が中途半端で、回答者側で「得たい結果から、不足分のデータを推測する」ことになってしまいます。

要件が不明な点がいくつかありますが、取りあえずSQLを提示しておきます。
要件に合わせて、変更してみてください。

＜SQL例＞
select
 -- *
  user_id,
  sum(result) as 正答数,
  count(*) as 解答数,
  sum(result)/count(*) as 正答率
 from t1
 where user_id in(
select
  user_id
 from t1
 where ques_id in(1,3,4,5)
 group by user_id
  having count(*)=4
)
group by user_id
;

効率的なSQL文が思いつきません

SELECT user_id, concat(sum(result), '/', count(*))

chukenkenkou さんの回答への補足。

ありゃりゃ。

MySQLの質問をする場合は、バージョンを明記してください。

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

chukenkenkou　さんの回答への補足。