MovableTypeで運用しているウェブログの記事のアクセス分析。

今までウェブプログラミングをしていて、データをテキストから取り出し、forやwhileで繰り返し処理をしながら、集計や並べ替えをしていたのですが、SQLを使うとそれらが簡単にできてしまうんです。

一応、情報技術者試験の時に勉強したのですが、そのときは具体的な目的はなかったので、詰め込みの勉強をしただけでした。こうして利用してみると、SQLの知識はデータ活用を大きく変えるものだと思いました。

さて、具体的にどのように処理したかを説明します。

やりたいことは、記事ごとのカウントをしているデータから、
カテゴリ別の集計をおこないます。カウントしているデータは、
シンプルにしてあるので、カテゴリ情報は含まれていません。
エントリとカテゴリの関連付けは、mt_placementに保存してありました。

SELECT placement_category_id,

count(*) as count, sum(mt_most_views.views) as sum

FROM mt_placement left join mt_most_views

ON mt_placement.placement_entry_id = mt_most_views.id

WHERE mt_placement.placement_is_primary = 1

AND mt_placement.placement_blog_id = $blogid

GROUP BY mt_placement.placement_category_id

ORDER BY sum DESC

「SELECT」でカテゴリIDとカテゴリIDの数と閲覧数の合計を取り出す。
「FROM」で「mt_placement」というテーブルから取り出すのですが、
そのテーブルは、「mt_most_views」と連結させてあり、
「mt_placement」テーブルの「placement_entry_id」と
「mt_most_views」テーブルの「id」を関連づけてあります。
「placement_is_primary」は「主カテゴリ」かどうかが保存してあって
これが 1 だと「主カテゴリ」ということです。
「mt_placement.placement_blog_id」には、ブログIDが保存してあって、
これが呼び出されるブログIDと一致しているかを調べます。
「GROUP BY」は、どの要素で集計するかということで、
カテゴリIDでまとめるようにしてあります。
最後に「ORDER BY」でソートしています。

説明すると長いのですが、SQLなしのperlやPHPだけで処理しようとすると
何倍の行数になることか...。

エクセルでごちゃごちゃ数式を書いているのと
アクセスでスマートにデータベースで処理しているのと同じです。

完成品は後日公開します。
(動作中のものは「全記事」にあります)