WassUpで人気記事を取得するSQL

Pocket

WordPressにはWassUpという、アクセス状況を調べることのできるプラグインがあります。
WassUpはアクセスのログをMySQLのテーブルに保存しているのでSQLを使えば簡単にアクセス状況を集計をすることができます。

そこで「30日間にアクセスの多かった記事」を取得するSQLを考えてみました。

SELECT post_title, count( urlrequested ) AS ct
FROM wp_wassup
JOIN wp_posts ON replace( urlrequested, "/wp/archives/", "" ) = wp_posts.id
WHERE instr( urlrequested, "archives" ) >0
AND instr( urlrequested, "category" ) =0
AND UNIX_TIMESTAMP( ) - timestamp <30 *24 *60 *60
GROUP BY urlrequested
ORDER BY count( urlrequested ) DESC
LIMIT 0 , 10

URLを「/wp/archives/3815」としている場合の例です。「wp」はWordPressのフォルダです。
ログは「wp_wassup」テーブルに保存されています。
記事は「wp_posts」テーブルに保存されているので、結合して記事のタイトルを取得します。
「30 *24 *60 *60」の「30」が日数です。

次が実行結果です。

post_title ct
ワンピースARカードダスのレアカード 7226
ワンピースARカードダス一覧 第3弾 6166
ワンピースARカードダス一覧 第2弾 4436
ワンピースARカードダス一覧 4196
伝説ポケモンのARマーカー 2243
GoogleカレンダーにCSVファイルから予定をインポートする方法 1936
ワンピースARカードダス、40枚中34枚を発見 1668
「ショットノート」を自作 1633
ポケモン立体図鑑ARマーカーリスト 1310
番号と日付がセットできる自作SHOT NOTE 1241

ワンピースとポケモンばかりだ。

[ 2012年1月28日 | カテゴリー: WordPress | タグ: , , , ]

« | »

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

送信してください。


タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報