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

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

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

WordPressのアイコンとしてGravatarを使う

WordPressではコメントをする際にアイコンが表示されます。 (さらに…)


タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報