初歩的な表の変換をしたい

質問

趣味のプログラミングでPHP+MYSQLをいじっている大学生です。
PHP側でやりたいこと自体はできるのですが。
SQL文で絶対やれる気がしてなりません。
でも、どのようにSQL文を書けばいいか分かりません。

[元となるデータの格納方式]
職業　 回答番号　 　 　
高校生　 3
社会人　 2
社会人　 3
高校生　 4
中学生　 3
・・・

のような形で格納されたデーターから
　 　 　 　選1　 選2　 選3　 選4 (回答番号)
小学生 　 3　 　2　 3　 　 4
高校生 　10　 　0　 0　 　 2
社会人 　 2　 　2　 3　 　 4 (数字は選択肢を選んだ人数)

のような形で出力したいのですが
SQL文がやり方がわかりませんでした

グループ化をして

SELECT 職業,回答番号,count(*) as num from テーブル
group by `回答番号`

職業　 回答番号　 人数
高校生　 　 2　 　 　5
社会人　 　 1　 　 　3
高校生　 　 1　 　 　2
社会人　 　 2　 　 　5
・・・

のような形からどうにかするのだろうとはと考えたのですが・・・
その後がおもいつきません.

自分は基本的なMYSQLの本しかもってないのですが
調べてみたもののこのような変換をしているサンプルがなくて
困っています(T.T)

初歩的な質問なのだろうと自覚しておるのですが・・・
このような操作をしたい場合のSQL文ご教授くださると大変助かります。

よろしくお願い致します。

回答No.4

chukenkenkou · ベストアンサー

SQLでやった方が良いのか、アプリケーション側でやった方がいいのかは、使い分けが必要です。
SQLの処理は、RDBMS側で最適になるように処理されていますから、本来、アプリケーション側でやれば軽くなる処理を、無理矢理、SQLでやらせようとすると、冗長で性能を出せないSQLになってしまいます。

今回の処理は、SQLとして複雑ではなく、適切なインデクスを定義すれば、性能も出せるでしょう。

select 職業,
count(case when 回答番号=1 then 1 else null end) as `選1`,
count(case when 回答番号=2 then 1 else null end) as `選2`,
count(case when 回答番号=3 then 1 else null end) as `選3`,
count(case when 回答番号=4 then 1 else null end) as `選4`
from `hoge`
group by 職業

お礼

ありがとうございます。
No.3様とは本当にいろんな書き方があるんですね。
SQLでも結構なことができそうですね・・・。

前はSQLはほとんど保存場所みたいなイメージだったんですが
アプリケーションの機能を追加するごとに・・・
データが足りなくなって、最初からDBの構造を見直さないとと
いけなくなって・・
嫌でSQLにあんまり負担をかけないで加工ができればいいなぁ～
と思ってます。
きっとそのバランスのとり方が難しいのですね・・・

SQLとアプリケーションサイドの処理のお話も参考になりました。
ありがとうございます。

回答No.3

yambejp

SELECT `職業`
,SUM(`回答番号`=1) AS `選1`
,SUM(`回答番号`=2) AS `選2`
,SUM(`回答番号`=3) AS `選3`
,SUM(`回答番号`=4) AS `選4`
FROM `hoge` WHERE 1
GROUP BY `職業`

お礼

ご回答ありがとうございます。
この処理だとかなり早くできました！！

欲しいデータはプログラムサイドで
処理しなければならないのでしょうか・・・・

となるとクエリを３０回くらい発行するよりは？
あらかじめこういうテーブルを出しておいたほうがいいですよね？汗
自分がしたい処理は一般的にははどういうふうにするか分からなくなってきました。どういう方法が一番いいのだろう（汗）

と悩んでしまってきました。

とにかくありがとうございます。

回答No.2

noname#45409

追加回答です。

SQLで処理をしない方がいいのは、生産性とメンテナンス性（後で直す楽さ）の問題ですね。SQLが複雑だと、ちょっとの機能修正をする場合も手間がかかります。バグも出やすくなりますし。
どうすべきかですが、その元の生データですとそれを残す必要性が薄そうなので、記録段階でカウントしておくようにして、目的の形の状態でテーブルにしてしまうかな。生データを保存する必要がある（履歴取得）のであれば、トリガーで更新してもいいですし。いずれにせよ、プログラム内のSQLは最低限かつシンプルになるようにします。

そんな感じで、ただ作るだけというより、品質と直しやすさを気にした方がよいと思います。

補足

ご返答ありがとうございます。
実は上の例は説明のために簡略化したものでして本当は別のテーブルを
２つを連結したりしないといけなくて・・・
実際のものは元の生データは残しておきたいです.

実際はこんな感じです
1.ユーザテーブル
( ユーザid 職業 )

2.回答データテーブル
(ユーザid 問題id　選んだ選択肢 正誤 何を入力したか 習熟度)

3.問題テーブル
(問題id 問題type 問題文　選1 選2・・・, 正解選択肢番号, 正解文)

習熟度はプログラムサイドで更新します。
本当は習熟度って項目を消して毎回回答データに挿入すればいいのですがデータ量が無限に膨れ上がるので
問題数×ユーザー数で押さえつけたいので上のような構造にしました。

おっしゃっておられるのは
あらかじめ

　　問題番号　選択肢1 選択肢2 選択肢・・・
高1
浪人

のテーブルをつくりそれでカウントするということだと思いますが
復習させるときに問題とリンクさせておかないといけないので
上のような構造でデータはいれておきたい感じです。

トリガで更新というのはちょっと調べてみたのですが
いわゆる生データのテーブルが更新されたときに
先ほどの教えていただいたクエリからテーブルを？
作成（更新）して

プログラム側ではそのテーブルからいろいろひきだす？
みたいなことをするということでしょうか？

質問ばかりで申し訳ありません。

回答No.1

noname#45409

やって出来なくはないですが、SQLですべき処理ではないでしょう。私の場合ですと、確実にそのような設計はしません。
どうしてもやる場合は、回答番号ごとにwhereで絞ってcountしてから、全部を結合すればいいと思いますよ。

select A.職業, A.num1 as '選1', B.num2 as '選2' from
(SELECT 職業,count(*) as num1 from テーブル
where 回答番号=1 group by 職業) as A,
(SELECT 職業,count(*) as num2 from テーブル
where 回答番号=2 group by 職業) as B
where A.職業=B.職業;

こんな感じですかね。

お礼

おお～なるほど＾＾
ご返答ありがとうございます。
でも・・・これは？SQLで処理させないほうがいいのでしょうか？
なんか専門家の方にそういわれると・・・
プログラムでいじったほうが効率がいいのでしょうか？