こんにちは。
ジーズアカデミーでスタッフをさせていただいております木村です。
遅くなりすみません(> <)
今回は、去年書いた記事「 GoogleフォームとGoogle Apps Scriptで課題提出期限を過ぎた人に煽りメールが来るスクリプト」に引き続き、学校で日々行われている身近な業務の課題をテクノロジーの力で解決していきたいと思います。
今回のテーマ
Global Geek Auditionという卒業制作の発表会をテーマにしたいと思います。
動画にある通り毎回100名を超える投資家、採用担当者が集まり、数々の投資、採用実績があります。
ルールはオーディエンスの企業が(約100人)すべての登壇者に５段階でサービス点、テクノロジー点を投票します。
1に近づくにつれて高い評価で、点数の平均が１番低い人が優勝という形です。
毎回の登壇者が20人前後、オーディエンスの企業は毎回約100人程度参加して頂いております。
実際の投票フォーム画面はこちら。
上記の通り、企業No,プレゼンターNo,サービス点、技術点、アドバイス、個別の話希望チェックという形です。
(個別の話にチェックすると追加の項目が表示されて、会社名を入力できるという裏機能もあり)
背景
結果を発表する用で毎回csvデータにダウンロードした後はフィルターをかけて一人一人の平均を集計するのに時間がかかってしまっていました。
「csvをSQLに変換すれば、ランキングを1瞬で表示できれば他の分析に時間をかけられるようになるのでは？」
と思い致り、今回のテーマを選びました。
(LAB5の鵜飼さんに多大なるご協力を頂きました。ありがとうございました！！)
要件
gga_pollsテーブル
id (int)
company_id(int)
presenter_no (int)
service (int)
techlogy (int)
advice (VARCHAR)
is_want_talk (BOOL)
company_name (VARCHAR)
アプローチは以下の３つです。
①それぞれのpresenterのレコードの平均を「total_score」を出す
②GROUP BYを使って個別にすべての登壇者idの合計点の平均値を出す
③合計点が少ないスコアの順番に並べる
①については、
SELECT id, `presenter_no`, (`techlogy` + `service`) / 2 AS avg FROM gga_polls;
こちらでいけそうです。
②については、先程のSQLを使った表示された結果を使って、SQL文を入れ子にするサブクエリを使います。
SELECT `presenter_no`, AVG(avg) AS total_score FROM ( SELECT id, `presenter_no`, (`techlogy` + `service`) / 2 AS avg FROM gga_polls ) AS test_table GROUP BY `presenter_no`
③については
ORDER BY total_score ASC
``
ですね。
これを組み合わせ、、
SELECT `presenter_no`, AVG(avg) AS total_score FROM ( SELECT id, `presenter_no`, (`techlogy` + `service`) / 2 AS avg FROM gga_polls ) AS test_table GROUP BY `presenter_no` ORDER BY total_score ASC
取れました！！！！
途中、「導出表には別名が必須です」のようなエラーが出たのですが、こちらはAS句が抜けていることによるエラーでした。
参考記事はこちら
http://blog.kimuradb.com/?eid=636543
https://teratail.com/questions/1433
ぜひ近くのGGAの集計に困っている方がいらっしゃったらこの記事を共有してみてください(^^)
次はLABの田中さんです！
よろしくお願い致します！