本投稿においては実際にアンケートを行うわけではありません。アンケートの回答集計を通じてSQLを語る回です。
今回のネタはSQL＋PHPでのコーディングについてお話しをしようと思います。アンケートの得票数カウントはSQLによる集計の基礎でありながら奥が深いです。

1.question（質問） テーブル?id:2を題材にします。
2.answer（回答） テーブル
3.vote_history（投票履歴）テーブル
この３枚のテーブル（実際には「2.回答」と「3.投票履歴」の2枚のテーブル）を使って、集計SQL＋PHPの書き方を考えてみましょう。

↓

「2.回答」と「3.投票履歴」のテーブルから「4.集計結果の表」を出力したいわけですね。インデックスは？同一人物の二重投稿対策は？という声もあるかと思いますが、また次回以降に

0.まずはPHPのループで集計を頑張るパターン

<?php
$sql = "select * AS cnt from vote_history where question_id = 2"; // 1.質問id:2の履歴を全取得
$stmt = $dbh->query($sql);
$logs = array();
while ($row = $stmt->fetchRow()) {
　$logs[] = $row;
}
$sql = "select * from answer where question_id = 2 order by align asc"; // 2.回答テーブルのデータを全取得
$stmt = $dbh->query($sql);
$answer_count = array();
while ($row = $stmt->fetchRow()) {
　 $row['c_cnt'] = 0;
　 foreach ($logs AS $log) {
　 　 if ($log['qeustion_id'] == $row['qeustion_id'] && $log['qeustion_id'] == $row['id']) {
　 　 　 ++ $row['v_cnt'];
　 　 }
　 }
　 $answer_count[] = $row;
}
?>

PHPでのSQL関数は個々で違う（間違っている？）と思われるのでスルーお願いします。select句とfrom句とwhere句とorder句を駆使して集計しました。あまり見かけないですね。PHP側でがんばって集計する方法として有りかと思います。

1.ループ内で集合関数を使ったSQLを投げるパターン

<?php
// 1.回答テーブルを取得
$sql = "select * from answer where question_id = 2 order by align asc";
$stmt = $dbh->query($sql);
$answer_count = array();
while ($row = $stmt->fetchRow()) {
　 // 2.ループ内で集計
　 $sql = "select count(*) AS cnt from vote_history where question_id=? and answer_id=?";
　 $stmt2 = $dbh->query($sql, array($row['question_id'], $row['id']));
　 $row['v_cnt'] = $stmt2->fetchfield('cnt');
　 $answer_count[] = $row;
}
?>

まぁ、こちらのフローはよく見かけるパターンです。集計関数countの知識があれば問題なく集計出来ますが、ループの中でクエリーを投げるのでRDBへのストレスが懸念されます。実はクエリー回数が多いという点で個人的にはパターン「0」よりお勧めしません。「画面のレスポンスがヤケに遅いなぁ」というときにはこのパターンで書かれているケースが多いです。

2.相関副問い合わせを使うパターン

SQL文が一個で投げるクエリーが一回なのでコード部分は省略し、SQL文のみを掲載します。

select A.*,(
　 select count(*) AS v_cnt from vote_history
　 where question_id = A. question_id and answer_id = A.id -- 相関副問い合わせ
) AS v_cnt
from answer AS A
where question_id = 2
order by align asc

PHPのコードから見るとこSQL１文のみで集計までしてくれます。PHPのコードは整然としますが回答１レコードから一回ずつ集計用のクエリーをRDB内部で連発している点はパターン「1」と変わらないです。回答テーブルの出力レコード１行につき内部でクエリを投げていますので、内部的には外部表で1回、相関副問い合わせで5回の計6回で、あまりパフォーマンスはよくありません。

3.group byを使ってみる。

0と同様にループ内で切り貼りして接合する必要がありますが、PHPのコードは省略します。

1.投票結果を集計（1回目のクエリー）
select question_id , answer_id ,count(*) AS v_cnt
from vote_history
where question_id = 2
group by question_id , answer_id
2.回答テーブルの一覧（2回目のクエリー）
select *
from answer
where question_id = 2
order by align asc

２回のクエリーを投げ、PHP側で質問id、回答idを元に切り貼り処理をして「4.集計表」のような表が出来上がります。一回目のクエリーではキレンジャーの得票数が0で当該行が出力されないため工夫が必要です。SQLの書き換えにより、画面のレスポンス的にはパターン「2」より改善されるケースが多々あります。

4.副問い合わせの結果を元にleft joinをしてみる。

肝となるのはSQL文なので引き続きPHPのコードは省略します。

select A.*,if_null(V.CNT,0) AS v_cnt
from answer AS A
left join (
　 select question_id , answer_id ,count(*) AS CNT
　 from vote_history
　 where question_id = 2
　 group by question_id, answer_id
) AS V
ON V.question_id = A. question_id AND V.answer_id = A.id
where question_id = 2
order by align asc

見づらいので、一行一句で副問い合わせにはインデントを組み合わせました。「left join」のカッコ内のクエリーが副問い合わせです。サブクエリーとも言います。クエリーの結果をテーブルに見立てて利用することが出来ます。「if_null(V.CNT,0)」はid:3「キレンジャー」の投票数が「NULL」になってしまうので「0」にするよう関数を挟んでいます。「left join」のカッコ内の『where question_id = 2』は要らないんじゃないか？とも思いますが、 パターン「0」の一個目で『 where question_id = 2 』を書かないのと同じです。全レコードから集計をかけることになり時間がかかります。

5.left joinをしてみる。その2

次はサブクエリーを使わずに「left join」で書いてみます。PHPのコードは省略します。

SELECT A.question_id, A.id, A.name, A.align, count(*) AS v_cnt
FROM answer AS A
left join vote_history AS V
ON V.question_id = A. question_id AND V.answer_id = A.id AND V.question_id = 2
where A.question_id = 2
GROUP BY A.question_id, A.id, A.name, A.align
order by align asc

group by句に全フィールドを入れているので面倒ですよね。group byに入れるフィールドのバイト数合計のリミットもあるので、group by内でパンクするリスクがあります。

6.left joinをしてみる。その3

パターン「5」ではGROUP BYに全フィールドを入れましたが、集合関数に入れてしまえばGROUP BYに出力用の全カラム入れなくても済みます。

SELECT A.question_id, A.id, MAX(A.name) AS name, MAX(A.align) AS align, count(*) AS v_cnt
FROM answer AS A
left join vote_history AS V
ON V.question_id = A. question_id AND V.answer_id = A.id AND V.question_id = 2
where A.question_id = 2
GROUP BY A.question_id, A.id
order by align asc

なんかalign（表示順）をMAX関数に入れるのはまだしも、name（回答の選択肢名称）をMAX関数に入れるのは違和感がありますが、group by句に無いフィールドを出すにはこうするしかありません。

7.left joinをしてみる。その4

パターン「5」のようにselect句に集合関数を入れず、パターン「6」のようにGROUP BY句にもフィールド指定をせずに書いてみる。

SELECT A.question_id, A.id, A.name, A.align, count(*) AS v_cnt
FROM answer AS A
left join vote_history AS V
ON V.question_id = A. question_id AND V.answer_id = A.id AND V.question_id = 2
where A.question_id = 2
GROUP BY A.question_id, A.id
order by align asc

一意性が保証される場合であれば、このような書き方（パターン「5」からgroup by句への記述を省略）でも実行できますが古い厳格なタイプのSQLではルール違反なのでSQLエラーで終了しますね…。

8.inner joinをしてみる。

パターン「7」では古いバージョンのRDBでは動かないリスクがありますが、下記のようにすればパターン「5」のようにGROUP BY句に全フィールドを入れる必要もありませんし、パターン「6」のように強引に集合関数を使う必要もありません。

SELECT A.*, A2.v_cnt
FROM answer AS A
inner join (
　 select A3.question_id, A3.id count(*) AS v_cnt
　 from answer AS A3
　 left join vote_history AS V
　 ON V.question_id = A3. question_id AND V.answer_id = A3.id AND V.question_id = 2
　 where A3.question_id = 2
　 GROUP BY A3.question_id, A3.id
) AS A2
ON A2.question_id = A.question_id AND A2.id = A.id
order by A.align asc

結果的にはパターン「4」の書き方に似ています（より複雑にした？）が、ifnull関数を挟まずに済む点、のべ3枚（answerを2枚＋vote_historyを1枚）のテーブルを使用している点が違います。あえてinner joinを使うために書いた感が強いですね。

9.ビューオブジェクトを使うパターン

副問い合わせとにていますが、主キー・（ユニーク）インデックスなどの検索インデックスが無効になりません。（副問い合わせの結果にはインデックスが何も効いてません）具体的な書き方は省略します。

10.TEMPORARY TABLE（一時テーブル）を使うパターン

管理画面など、リクエスト数が増えないシステムなら有りかと思います。自分だけのセッションで荒らしても、他のセッションには影響を与えませんし、他のセッションから自分だけのセッションで値を改ざんされるリスクもありません。具体的な書き方は省略します。

最後に、結局パターン0~10でどれがおススメか？

どれも実運用にはおススメではないですね。ライトな集計・表示であればどれでも構いません。お好みに合わせて書くor既存の書き方に準えるなどが宜しいかと思います。特に最近のSNSで流行りの「いいね」「高評価・低評価」など、多数のユーザーがボタンを押すだけでレコードを挿入することになり、ローディング時間が長くなりサイト利用者は閲覧ストレスが蓄積されていく事でしょう。Twitter、YouTubeなどの「いいね」「高評価」はレコード行数が数千億くらい行ってると思われます。それなのに数億行レベルのテーブルから集計SQLを投げるなんて現実的ではないような気がします。

こういった問題はリリース直後は快適に動いていても、年月の経過とともに動きがスローに感じられるようになってきますので日頃の監視or定期的なチューニングも必要です。

普段あまり気にはしていませんが、記事にしてみようと思い起こせば同じ結果を出すためのSQLでも11パターンあるのには自分でも驚きました。普段は頭の中で考えたり、口頭で伝えるレベルなので何件くらいあるかは記事にしてみる事で改めて実感した次第です。

今回はガラケー、ネオヒストリックカー、windows 2000、VHSのビデオテープ、CDラジカセ、ファミコン＆ディスクシステムが大好きなおじさんがSQL（「group by」、「結合（join）」、「副問い合わせ（サブクエリー）」、「集合関数（count、max）」）について語る回でした。次回の投稿ではUNIX/LINUXのコマンドについて語ろうかと思います。