最近になってようやくなんですが、Googleアナリティクス以外のアクセス集計を自前で作りました。
どういう記事が人気なのかって分析と、純粋にモチベーション維持に使いたいなと思ってます!
で、ふと思ったのが「人気順」がこれで実装できるようになったなぁと思いました。
上の画像の通り、サブコンテンツに「ここ一週間で人気の記事」という項目を追加しました!
今回の記事ではマスタデータ・履歴データから人気順にレコードを取得するSQLを解説します。
純粋にsantmove.がSQL文苦手というだけで、そこまで難しい物ではないように思えるのですが、
同じ悩みを抱えている人が少しでもと思い、ちゃんと記録に残すことにしました。
なお当サイトではアクセス日時と記事IDのみ取得し、個人を特定できる情報は送信いたしません。
詳しくはプライバシーポリシーをお読みください。
記事毎のアクセス数を集計し、ここ一週間でのアクセス回数が多い上位5位の記事IDと表示に必要なデータを取得する事ができます。
なお、SQL文解説においてテーブル名は各々のサイトやブログによって違うかと思うので、
カラム名やテーブル名などは自分の情報に置き換えて書いて頂ければと思います。
以下のようなシンプルなテーブルで管理します。
アクセス時にphpで必要事項を入力して追加すればOKです。
記事マスタ
・ID
・タイトル
(以下省略(必要なら追加))
アクセス履歴
・ID
・記事ID
・アクセス日時
(以下省略(必要なら追加))
上記画像のテーブル名・カラム名に準拠した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;
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.
当サイトはブラウザで遊べるゲームサイトです。
最新のゲームです!春夏秋冬のパネルを1列に揃えて連鎖を狙おう!
ペンギンだって暖を取りたい!氷を避けて登って、アツアツ料理を食べよう!
よかったら遊んでいって頂けると幸いです!面白かったら是非シェアしてください!
また以下のリンクよりお買い物頂けるとサイト運営の助けとなりますので、もしよろしければ是非よろしくお願いいたします!
楽天市場
amazon
この記事にはまだコメントがありません。
はじめてコメントしてみませんか?
ログインするとコメント出来ます!
ログイン
無料アカウント作成