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

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

新規登録して質問してみよう
ただいま回答率
85.51%
MySQL

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

Q&A

解決済

3回答

2338閲覧

mysql カラム別平均値 抽出

kuroniyusha

総合スコア8

MySQL

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

0グッド

0クリップ

投稿2015/05/20 02:19

お世話になっております。
毎度つたない説明、対応で申し訳ありませんが、
ご教授いただける方いましたらよろしくお願い致します。

下記の3つのテーブルで、

m_kyouka

kyouka_id name
1 国語
2 数学
3 理科
4 社会
5 英語

t_score

id name comment
11 赤井 あかい
22 青葉 あおば
33 白木 しらき
44 田中 たなか
55 佐藤 さとう
66 生田 いくた
77 長門 ながと
88 浅井 あさい
99 沢村 さわむら
100 近藤 こんどう
101 松本 まつもと
102 清水 しみず
103 大竹 おおたけ
104 内藤 ないとう
105 水木 みずき
106 橋本 はしもと
107 陸奥 むつ
108 如月 きさらぎ
109 睦月 むつき
110 加賀 かが

t_score

id seito_id kyouka_id score
1 11 1 50
2 22 1 80
3 33 1 23
4 44 1 32
5 55 1 90
6 11 1 45
7 77 1 66
8 88 1 57
9 99 1 70
:
:

※t_scoreは長くなってしまうので割愛させていただきます。
いくつかseito_idがダブってる箇所があります。

例 seito_id 11(赤井) が kyouka_id 1(国語)の
テストを二回受けているといった感じです。

上記条件で、全生徒の全教科表示させる。
ただし、ダブってるものがあればスコアの平均値を出し
データがない科目があれば0と表示させたいのですが・・・

例)名前 国語 数学 理科 社会 英語
赤井 64 47 25 74 27
青葉 82 37 56 0 65

例として↑のような形に表示させたいのです。

コードとしては↓のように作り,

lang

1select se.name, 2avg(score) "国語", 3avg(score) "数学", 4avg(score) "理科", 5avg(score) "社会", 6avg(score) "英語" 7from t_score sc,t_seito se 8where sc.seito_id=se.id 9group by sc.seito_id

抽出データはこちら↓

name 国語 数学 理科 社会 英語
青葉 64.5000 64.5000 64.5000 64.5000 64.5000
陸奥 52.2500 52.2500 52.2500 52.2500 52.2500
:
:
:

ただ、全教科の生徒別平均値を表示させるだけで
教科別平均値やデータのない箇所を0として表示させることができず、
うまくできません。

どのようにすれば各教科別で表示させることができるのか
ご教授いただける方いましたらよろしくお願い致します。

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

SaintKnowledge

2015/05/20 02:42

データの例と、期待したい結果がわかりづらいです。例えば「青葉」さんはデータ例では80点なのに、期待したい結果では82点になっていたり64.5点になっていたり。。 また、「教科別平均値」の期待したい例も書いてもらえると。 データ例に則した期待したい結果と、用語を統一して、具体的にわかりやすくしてもらえれば、ありがたいです。
SaintKnowledge

2015/05/20 02:46

あ、ひょっとしたら、平均値ではなく、scoreの実値を教科別に出したい、ということですかね??
kuroniyusha

2015/05/20 03:03

わかりづらくてすいません・・。 修正したのですが修正内容が反映されないのでこちらで説明させていただきます。 表示させたい形としては例のとおりです。 各生徒別で各教科のscore値を抽出したいのですが、 それぞれの教科でseito_idがダブってる箇所があるのでダブってる箇所に関しては平均値を表示させたいのです。 例えば国語だと赤井さんがダブっていているので(50,45)点数の平均値を表示,国語の中には生田さん(seito_id66)はいないので生田さんは0と表示させたいのです。
SaintKnowledge

2015/05/20 03:08

であれば、下記の私の書き込み(group化されたcase文のavg)で上手くいくと思います。動作保証ないですが、、^^;。
guest

回答3

0

こういう変換はPIVOTと検索すると情報がよく見つかるのですが、mysqlは今調べてみたところcase式を使う方が多いようですね

SaintKnowledgeさんの答えが少し変な値になってしまう理由について説明します。

case式をつかっていると平均値に0が混じってしまうので
例えば5教科を2回ずつ受けていると2教科の点数を10で割ってしまうんですね
seito_idが11のkyouka_idが1の人の集計はきっと以下のようになってしまいます。
例 0, 0, 0, 0, 0, 0, 0, 0, 45, 50
この平均をとると結果が9.5のようになってしまいます

case式をちょっと直した回答をあげておきます…が、以下の問題があります。
・mysqlで動作確認していません
・受けてない教科のある生徒がいるとエラーになる

lang

1select se.name, 2sum(case when sc.kyoka_id = 1 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end) as "国語", 3sum(case when sc.kyoka_id = 2 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end) as "数学", 4sum(case when sc.kyoka_id = 3 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end) as "理科", 5sum(case when sc.kyoka_id = 4 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end) as "社会", 6sum(case when sc.kyoka_id = 5 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end) as "英語" 7from t_score sc,t_seito se 8where sc.seito_id=se.id 9group by sc.seito_id

投稿2015/05/20 04:28

haru666

総合スコア1591

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

SaintKnowledge

2015/05/20 04:40

フォローありがとうございました!その通りです。私もうっかりしてました^^;。同じものになりましたが^^;、コメントに訂正版を載せています。 0除算は、NULLIFで回避出来るかな。。
kuroniyusha

2015/05/20 11:04

ご連絡遅れましてすいません。 select se.name, ifnull(round(sum(case when sc.kyouka_id = 1 then sc.score else 0 end ) / sum(case when sc.kyouka_id = 1 then 1 else 0 end)),0) "国語", ifnull(round(sum(case when sc.kyouka_id = 2 then sc.score else 0 end ) / sum(case when sc.kyouka_id = 2 then 1 else 0 end)),0) "数学", ifnull(round(sum(case when sc.kyouka_id = 3 then sc.score else 0 end ) / sum(case when sc.kyouka_id = 3 then 1 else 0 end)),0) "理科", ifnull(round(sum(case when sc.kyouka_id = 4 then sc.score else 0 end ) / sum(case when sc.kyouka_id = 4 then 1 else 0 end)),0) "社会", ifnull(round(sum(case when sc.kyouka_id = 5 then sc.score else 0 end ) / sum(case when sc.kyouka_id = 5 then 1 else 0 end)),0) "英語" from t_score sc,t_seito se where sc.seito_id=se.id group by sc.seito_id ↑のような形で無事に抽出することができました。 何度もご教授くださりありがとうございました!
guest

0

ベストアンサー

検証していないので、バグがあったらごめんなさい。こんな感じでいけると思います。
期待されている結果と同じかどうかは定かではないですが、、。

select se.name, avg(case when sc.kyoka_id = 1 then sc.score else 0 end ) as "国語", avg(case when sc.kyoka_id = 2 then sc.score else 0 end ) as "数学", avg(case when sc.kyoka_id = 3 then sc.score else 0 end ) as "理科", avg(case when sc.kyoka_id = 4 then sc.score else 0 end ) as "社会", avg(case when sc.kyoka_id = 5 then sc.score else 0 end ) as "英語" from t_score sc,t_seito se where sc.seito_id=se.id group by sc.seito_id

縦持ちレコードを横持ちに変換、みたいな感じです。該当が無ければ0と出るかと。教科が動的に増える場合は、プログラム側で列を増やす処理を施すしかないと思います。
自信ないので、ちゃんと検証してくださいね。

投稿2015/05/20 02:54

編集2015/05/20 02:58
SaintKnowledge

総合スコア368

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kuroniyusha

2015/05/20 03:38

わかり辛い説明にも関わらず答えていただきありがとうございます! 上記構文を試させていただいたところ、無事に表示させたい形に抽出はされたのですが、 name 国語 数学 理科 社会 英語 赤井 15.8333 15.6667 2.1667 13.0000 10.6667 青葉 25.8333 19.3333 12.6667 6.6667 0.0000 白木 4.6000 13.4000 11.8000 15.6000 6.0000 と言ったような形になりましてscoreの値がよくわからない表記になっていました・・。 ですが抽出したい形としてはまさにこのとおりなのでベストアンサーにさせていただき、また少し自分でも調べてみようと思います。 お手数おかけしてすいません、ご教授いただきありがとうございました!
SaintKnowledge

2015/05/20 03:56

ありがとうございます。ヒントになって幸いです。 他にもgroup by や加味しないといけない項目がありそうですね。
SaintKnowledge

2015/05/20 04:22

追記です。解決済みかもしれませんが、ちょっと私のSQLには漏れがあるみたいです。失礼しました。 ``` select se.name, sum(case when sc.kyoka_id = 1 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end ) as "国語", avg(case when sc.kyoka_id = 2 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 2 then 1 else 0 end ) as "数学", avg(case when sc.kyoka_id = 3 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 3 then 1 else 0 end ) as "理科", avg(case when sc.kyoka_id = 4 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 4 then 1 else 0 end ) as "社会", avg(case when sc.kyoka_id = 5 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 5 then 1 else 0 end ) as "英語" from t_score sc,t_seito se where sc.seito_id=se.id group by sc.seito_id ``` 合計値を件数で割らないといけませんです。ただし、分母が0になって例外を起こす場合があるので、回避策をお願いします。
SaintKnowledge

2015/05/20 04:25

sum(case when sc.kyoka_id = 1 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 1 then 1 else 0 end ) as "国語", sum(case when sc.kyoka_id = 2 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 2 then 1 else 0 end ) as "数学", sum(case when sc.kyoka_id = 3 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 3 then 1 else 0 end ) as "理科", sum(case when sc.kyoka_id = 4 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 4 then 1 else 0 end ) as "社会", sum(case when sc.kyoka_id = 5 then sc.score else 0 end ) / sum(case when sc.kyoka_id = 5 then 1 else 0 end ) as "英語" でした。。
guest

0

表にするやり方はあると思うんですが、RDBMSで違ってしまうのでちょっと私ではアドバイスできません。

ただ、それよりも先に今のクエリの修正方法です。
上のやり方だと生徒の全教科の平均が複数出る形になってしまっていますね。
各生徒の科目別の平均の出し方は、group by句にkyouka_idも含める必要があります。

lang

1select se.name, 2sc.name, 3avg(sc.score) 4from t_score sc,t_seito se, m_kyouka ky 5where sc.seito_id=se.id and sc.kyouka_id=ky.kyouka_id 6group by sc.kyouka_id, sc.seito_id 7

投稿2015/05/20 02:41

haru666

総合スコア1591

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

kuroniyusha

2015/05/20 03:41

前回に引き続き今回もコメントいただきありがとうございます! group by句に複数指定できることを知りませんでした・・。勉強になります! 今後ともご縁があればご教授のほどよろしくお願い致します。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問