最近になってようやくなんですが、Googleアナリティクス以外のアクセス集計を自前で作りました。
どういう記事が人気なのかって分析と、純粋にモチベーション維持に使いたいなと思ってます！

で、ふと思ったのが「人気順」がこれで実装できるようになったなぁと思いました。
上の画像の通り、サブコンテンツに「ここ一週間で人気の記事」という項目を追加しました！

今回の記事ではマスタデータ・履歴データから人気順にレコードを取得するSQLを解説します。
純粋にsantmove.がSQL文苦手というだけで、そこまで難しい物ではないように思えるのですが、
同じ悩みを抱えている人が少しでもと思い、ちゃんと記録に残すことにしました。

なお当サイトではアクセス日時と記事IDのみ取得し、個人を特定できる情報は送信いたしません。
詳しくはプライバシーポリシーをお読みください。

技術解説

出来ること

記事毎のアクセス数を集計し、ここ一週間でのアクセス回数が多い上位5位の記事IDと表示に必要なデータを取得する事ができます。

なお、SQL文解説においてテーブル名は各々のサイトやブログによって違うかと思うので、
カラム名やテーブル名などは自分の情報に置き換えて書いて頂ければと思います。

アクセス履歴のテーブルを作成する

以下のようなシンプルなテーブルで管理します。
アクセス時にphpで必要事項を入力して追加すればOKです。

記事マスタ
・ID
・タイトル
(以下省略(必要なら追加))

アクセス履歴
・ID
・記事ID
・アクセス日時
(以下省略(必要なら追加))

SQL文で集計する

上記画像のテーブル名・カラム名に準拠したSQL文を下に掲載します。

SELECT info.id AS id,info.title
 FROM access_history access
 LEFT JOIN info_master info ON info.id = access.info_id
 WHERE DATE_SUB(NOW(), INTERVAL 7 DAY) <= access.accessdate
 GROUP BY access.info_id
 ORDER BY count(access.info_id) DESC LIMIT 5;

SQL文のそれぞれの行で何をやっているか

1行目：SELECT info.id AS id,info.title

集計後、記事リンクに必要な情報をSELECTで取得します。

2行目：FROM access_history access

アクセス履歴テーブルをaccessという名前で読み込みます

3行目： LEFT JOIN info_master info ON info.id = access.info_id

記事マスタをinfoという名前で読み込み、記事IDで結合します。

4行目：

7日以内という条件を指定します。7を変えれば別の日にちにすることが出来ます。

5行目：GROUP BY access.info_id

アクセス履歴テーブルを記事IDで集計します

6行目：ORDER BY count(access.info_id) DESC LIMIT 5

アクセス履歴テーブルの各記事IDのカラム数を降順で並び替えます。
LIMIT 5で上位5件のみ取得します。

今日の結び

SQL文に少しづつでも慣れていこうと思って色々実験しながら作ってみました！
厳密にはもっと効率の良い文があるのかもしれませんが、わかりやすさ重視ということで、、、(笑)

人気ランキングはゲームにも必要かと思うので、そのうち作ろうかなぁと思います！
同様の仕組みで「各ゲームのスコアランキング」も作れるかと思いますが、それはいずれ、、、

感想・リクエストなどは公式Twitterアカウント(＠santmove_com)まで是非ご連絡よろしくお願いいたします！
また、ゲームの感想なども励みとなりますのでよろしくお願いいたします！

ではまた！

santmove.

昨日公開したての最新ゲームです！

スタップウォッチ

0秒ジャストで止めるのは簡単？でも同時にやると鬼難易度！時間管理能力の限界を目指せ！

当サイトはブラウザで遊べるゲームサイトです。

四季一閃

春夏秋冬のパネルを1列に揃えて連鎖を狙おう！

避けて食べてペンギンタワー

ペンギンだって暖を取りたい！氷を避けて登って、アツアツ料理を食べよう！

よかったら遊んでいって頂けると幸いです！面白かったら是非シェアしてください！

また以下のリンクよりお買い物頂けるとサイト運営の助けとなりますので、もしよろしければ是非よろしくお願いいたします！

楽天市場

amazon

良ければ是非いいね・シェアしてください！

Tweet

次の記事

親子丼とエビとキノコのチーズ焼き【さわレシピ】

2017/05/02 公開

前の記事

ゲーム「スタップウォッチ」を公開しました！

2017/05/01 公開

この記事のコメント

次の記事

親子丼とエビとキノコのチーズ焼き【さわレシピ】

2017/05/02 公開

前の記事

ゲーム「スタップウォッチ」を公開しました！

2017/05/01 公開