PHP & MySQL


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

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

回答の条件
  • 1人5回まで
  • 登録:2009/03/07 09:37:22
  • 終了:2009/03/14 09:40:02

回答(4件)

id:pahoo No.1

pahoo回答回数5960ベストアンサー獲得回数6332009/03/07 10:17:48

ポイント27pt

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

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


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

id:esecua

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

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

2009/03/07 13:27:07
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542009/03/07 17:17:44

ポイント27pt

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

例えば、表の構成列が、


質問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回程度しか出ません。

id:chuken_kenkou No.3

chuken_kenkou回答回数722ベストアンサー獲得回数542009/03/08 20:44:11

ポイント26pt

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以降なら、ストアドプロシジャで実装し、得たい情報のみクライアントで受け取るといった実装も可能です。

質問者が未読の回答一覧

 回答者回答受取ベストアンサー回答時間
1 chuken_kenkou 722 671 54 2009-03-08 17:20:00
  • id:chuken_kenkou
    「知りたいこと」を勘違いしていたかも知れず、3回連続の回答となってしまいました。

    「はてなで採用されている方法」でなく、「MySQLで同じようなことを実装するとしたら」という質問ですよね?

    「リアルタイム」とありますが、どの程度のリアルタイム性と、どの程度の精度を求めますか?
    精度が厳密でなくてもいいなら、他ユーザが更新中でも読み込む「ダーティーリードする」といった方法もあります。

    #2では、「インデクスを有効利用できるなら、操作対象の行数が増えても、大きな負荷増を避けられる」といったことを書きました。

    #3では、#2のようにすることで、「どのようにRDBMSが処理してくれるのか」といったことを書きました。

    と、ここまでは「考え方」といったものを書いたのですが、質問やリンク先を改めて見て、「内部処理的なものでなく、SQLの書き方を知りたいのか?」と感じました。

    そのため、#4では、表・インデクス定義、実際の検索クエリの例を追記しました。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません