人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

PHP & MySQL

はてなでは質問形式を選ぶことが出来、その中にアンケート形式がありますが、この集計はどのように行われているのでしょうか?

http://d.hatena.ne.jp/esecua/20090307 続き

●質問者: esecua
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:はてな アンケート HP SQ
○ 状態 :終了
└ 回答数 : 3/4件

▽最新の回答へ

1 ● pahoo
●27ポイント

select(アンケート結果の割合を表示する)の方が update(アンケートに回答する)より圧倒的に多いという前提で考えます。

この前提であれば、アンケート結果の割合の値(パーセント)を格納するカラムを用意しておき、update した際に割合を計算して更新していくという方法が考えられます。


さらに、アンケート結果の割合の値(パーセント)はデータベースに持つのではなく、テキストファイルに持たせておけば(updateした際に割合を計算して、そのテキストファイルを更新する)、表示の負荷は更に減ります。

◎質問者からの返答

>テキストファイルに持たせておけば(updateした際に割合を計算して、そのテキストファイルを更新する)、表示の負荷は更に減ります。

かもしれませんが、おおよそ採用されない方法でしょう。


2 ● chuken_kenkou
●27ポイント

適切なインデクスを定義し、絞り込めるようにしておけば、負荷は心配するようなことはありません。

例えば、表の構成列が、


質問id、

設問id、

選択肢id、

集計


とし、各列のデータ型はintegerだとします。

  1. (質問id、設問id、選択肢id)でユニークなインデクスを定義
  2. 「=条件」や「between条件」など、インデクスを有効利用できる検索条件を指定
  3. 「group by」や「order by」の指定は、「質問id」だけ、「質問id、設問id」、「質問id、設問id、選択肢id」と、インデクスの先頭構成列から順に指定することで、作業ファイルや作業メモリを使ったソートを、インデクスを活用することで抑止。

そうした場合、母体データ件数が、

1000(質問数)

×100(設問数)

×10(選択肢)

=100万件

とします。


そうすると、インデクスはB-TREE構造の場合、特定の質問+設問+選択肢の行を見つけるのに、実I/Oは最大でも4?5回程度しか出ません。


3 ● chuken_kenkou
●26ポイント

MySQLのことを考えてなら、バージョンを明記してください。


#2では「インデクスを有効利用するための考え方」、

#3では「#2のようにすることで、RDBMS側がどうやった処理をやってくれるか」

といったことを書きました。

質問やリンク先を改めて読み、具体には「どんなSQLにすればいいかを知りたいのかな?」と感じ、追記することにしました。

表定義例

create table t1
(質問id bigint,
 設問id int,
 選択肢id tinyint,
 回答者数 int,
 primary key(質問id,設問id,選択肢id))

「絞込みを行える検索条件」、「group byで、関連するデータを一度で受け取る」、「group byで余分なソートを発生させない」といったところがポイントです。

select
 質問id,設問id,
 sum(case when 選択肢id=1 then 回答者数 else 0 end)
 / sum(回答者数) as `選1比`,
 sum(case when 選択肢id=2 then 回答者数 else 0 end)
 / sum(回答者数) as `選2比`,
 sum(case when 選択肢id=3 then 回答者数 else 0 end)
 / sum(回答者数) as `選3比`,
 sum(case when 選択肢id not in(1,2,10) then 回答者数 else 0 end)
 / sum(回答者数) as `選etc比`,
 sum(回答者数) as 設問回答数
 from t1
 where 質問id=11111
 group by 質問id,設問id

なお、MySQL 5.0以降なら、ストアドプロシジャで実装し、得たい情報のみクライアントで受け取るといった実装も可能です。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ