WassUpで人気記事を取得するSQL
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
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 | タグ: MySQL , SQL , WassUp , WordPress ]
« GoogleとBingを相互に遷移するブックマークレット | iPhoneのシリアルナンバーの意味 »
コメントを残す