【MySQLでデータ分析】JOINで2つのテーブルを使う #初心者

前回は久しぶりにSQLを書いて、分析に必要そうなデータを取ってきたりしました。前回の最後のほうで出したふたつの問題点のうち、今回はひとつめの「URLを含むtweetでは対象として不十分」というところを解決します。

含まれているURLがブログのものか違うかを判別する

ここは、hogelogがプログラムをちょちょいっと書いてくれました。私はgithubに公開されてるリポジトリをクローンして、READMEに書かれているとおりにポチポチやっただけ。hogelogが作ってくれたプログラムでは、まずtweetに含まれている全てのURLとそれを展開したURLを「twitter_links」というテーブルにインサートしています。次に、各tweetに含まれるURL（ひとつのtweetに複数のURLが含まれている場合は、ひとつめのURL）が、www.switch-science.comなのか、mag.switch-science.comなのか、それ以外のURLなのかを判別し、「twitter_attributes」というテーブルにインサートしています。

これで、ブログのURLが含まれているtweetを取り出すことができそうです。

2つのテーブルを使う

さて。一歩一歩行きます。

まずは、hogelogがくれた「JOINを使うのだ」というヒントをもとに、適当に書いてみたのですが、実際のデータベースでは絶対にやってはいけないことをさっそくやってしまいました。

SELECT * FROM twitter JOIN twitter_attributes

なんだか実行に時間がかかるなーと思っていたら、完全に失敗。適当にスクロールしてみても、ひとつ目のtweetのデータしか表示されない……

ちなみにデータの後ろの方にはtwitter_attributesのデータがちゃんと入ってます。twitterのひとつのデータに対して、twitter_attributesのデータがひとつひとつ別々にくっついて並んでいる…今はデータの数が500もないくらいだから「時間がかかるなー」とか言ってられましたが、実際のデータベースでこんなことやっちゃったら大変なことになります。JOINは集合の積を取るようです。そりゃ時間かかるわ…

というわけで、仕切り直しで

SELECT * FROM twitter JOIN twitter_attributes on twitter.tweet_id=twitter_attributes.tweet_id;

今度はちゃんとtweet_idごとに1対1で取り出せました。

リンク先がブログであるtweetという条件を追加し、いくつかの要素を取り出します。

SELECT tweet_text, impression, click_url FROM twitter JOIN twitter_attributes on twitter.tweet_id=twitter_attributes.tweet_id WHERE twitter_attributes.link_status=2;

おおー。間違いなく取り出せているようです。

おまけ？で分析っぽいことをしてみる。

私にはまだ統計の知識が足りません。なので現時点で分析っぽいことをしてみても、それが正しいのかどうか分からない。ですがせっかくなので、そもそも知りたかった「クリック率が高いのは何曜日なのか」をざっくり調べたいと思います。まずは曜日ごとにインプレッションとURLのクリック数を並べてみます。

SELECT DAYNAME(tweet_datetime + INTERVAL 9 HOUR), impression, click_URL FROM twitter
JOIN twitter_attributes on twitter.tweet_id=twitter_attributes.tweet_id 
WHERE twitter_attributes.link_status=2 ORDER BY 1;

うまく取り出せました。このデータをもとに、前回同様スプレッドシートなどで中央値求めたりするといいのかも。
※前回は全然気づいてなかったのですが、Twitter Analyticsからエクスポートしたデータに含まれる投稿時間データは、日本時間−9時間で表示されています。なので、DAYNAME(tweet_datetime + INTERVAL 9 HOUR)しないと正しい曜日が出せないのでした。

さらにおまけ。SQLでは割り算とか掛け算とかも普通にかけるようです。そこで、ブログへのリンクを含むtweetごとのクリック率を、クリック率順にざざっと並べてみたいと思います。

SELECT tweet_text, impression, click_URL, click_url/impression*100 FROM twitter
JOIN twitter_attributes on twitter.tweet_id=twitter_attributes.tweet_id 
WHERE twitter_attributes.link_status=2
ORDER BY 4;

内容とクリック率を並べてみていると、フォロワーさんがどんな内容に興味を持っているのかなんとなく見えてきます。分析っぽいぞ！

次はもうちょっと統計学っぽいことをやりたいと思います。