WordPressのデータを直接集計してブログ傾向を知る方法【Cocoon向けSQLあり】

wordpress - SQL ブログ運営
wordpress - SQL

検証環境

mariadb:10.4.7 Dockerコンテナ環境
⇒参考:docker-compose.ymlのひな形

Dockerコンテナ環境の場合の補足

docker ps -a で該当DockerコンテナIDを確定し
docker exec -it [コンテナID] /bin/bash でコマンドラインに入れます。

コマンドライン前提

コマンドラインを前提としました。リモートから使える場合はGUI(グラフィカルインターフェース)のツールの活用も考えてみてください。

コマンドラインのメリット

どの環境でも共通的に使えるやり方で解説できます。

GUIはクライアント環境やネットワーク環境によっては設定が難しかったり、無理なことがあります。コマンドラインならば、サーバアクセスの権限があればどの環境でも同じになるのでまずは覚えておいて損はありません。

コマンドラインのデメリット

Excel表に貼り付けたりするような作業には向いていません。GUIはその点有利です。あと、私の環境では日本語が文字化けしました。悲しい…

MySQL(mariadb)に接続する

コマンドが打てる状況にて

mysql -u root -p

なぜ mariadb なのに mysql というコマンドなのか

簡単に言えば、mariadbさんは、もともとmysqlさんでした。mysqlがOracle社の参加に入った時に、mysqlのバージョンアップの系譜が、mysqlのままの系譜(Oracle社)と、mariadbという系譜(オープンソース)に分かれたという経緯があります。

つまりmariadbとmysqlは親戚同士なので、素人的には「名前は違うけど同じもの」と考えてよく、mariadbは、昔ながらのmysqlコマンドのまま使えるということです。

WordPressのデータベース構造

ここから先は、SQLと呼ばれる文法に従います。厳密にはshowなどはSQLではありませんが、細かいことは気にしない。

データベース名の一覧を表示する

show databases; でデータベースの一覧が見れます。私のサイトではwordpressという名前のデータベース名を使っています。wordpressインストール時にデータベース名を指定したはずなので各自の環境に合わせてください。

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| wordpress |
+--------------------+
4 rows in set (0.06 sec)

データベースを切り替える

use [DB名] でデータベースを切替できます。コマンドの後ろのカッコ内にデータベース名称が表示されるので切り替わったことがわかります。

MariaDB [(none)]> use wordpress
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [wordpress]>

wordpressのデータベース構造(ER図)

wp_で始まるテーブルの関係図は、こちらを参照ください。

テーブル一覧を表示する

どのようなテーブルを持っているか、一覧表示します。show tables;で確認できます。

MariaDB [wordpress]> show tables;
+--------------------------------+
| Tables_in_wordpress |
+--------------------------------+
| wp_cocoon_accesses |
| wp_cocoon_affiliate_tags |
| wp_cocoon_function_texts |
| wp_cocoon_item_rankings |
| wp_cocoon_speech_balloons |
| wp_commentmeta |
| wp_comments |
| wp_links |
| wp_options |
| wp_postmeta |
| wp_posts |
| wp_site_cache |
| wp_sitemanager_device |
| wp_sitemanager_device_group |
| wp_sitemanager_device_relation |
| wp_term_relationships |
| wp_term_taxonomy |
| wp_termmeta |
| wp_terms |
| wp_usermeta |
| wp_users |
+--------------------------------+
21 rows in set (0.00 sec)

テーブル一覧補足

上記は私のデータベースでの一覧となっています。テーマにcocoonを利用していることがよくわかりますね。

wp_ と wp_cocoon_ の2種類があり、wp_の方がどのwordpressでも共通のテーブルということになりますね。

テーブルの列名を表示する

いきなりselect文打ちたくなりますが、列名見ておきましょう。describeまたはdescで見れます。desc [テーブル名]

desc wp_posts;
スポンサーリンク

wordpress共通で使えるSQL文

順次追加していきますね。

ちなみに、idさえわかれば?page_id=番号をURLに付加することで具体的なページにアクセスできまることを知っていると便利です

例えば当記事のIDは4051なので下記URLでアクセスできます。

https://www.taka-output-blog.com/?page_id=4051

設定されているサイトURLの確認

select * from wp_options where option_name in ("siteurl","home");

サイトURLの設定がおかしくなった場合は、ここをメンテナンスすることになります。詳しくは別ページにまとめたので良ければ見てください。

WordPressの一般設定でサイトURLを誤って変更した時の戻し方

カテゴリのIDとslugの一覧を取得する

WordPressの管理画面だと、下記のようになっていてIDが見えない。でもSQLを駆使したい場合、何かとIDがわかってた方が便利です。以下のSQLでIDの一覧を取得できます

select wp_terms.term_id
      ,wp_terms.slug
 from wp_terms
      inner join wp_term_taxonomy
              on wp_terms.term_id = wp_term_taxonomy.term_id
where wp_term_taxonomy.taxonomy = 'category';

10記事達成、などxx記事達成日を確認する(2020/4/19追加)

limitの部分を知りたい数字に変更してご利用ください。
(OracleやSQLServerでいうtop n解析。Mysql系ではlimitを使います)

select ID
      ,post_name
      ,post_date
      ,post_modified
      ,post_type
      ,post_status
 from wp_posts
where post_type='post'
  and post_status='publish'
order by post_date limit 10;
スポンサーリンク

cocoonテーマで使えるSQL文

こちらも思いついたら順次追記します。

今までの合計PV

select sum(count) from wp_cocoon_accesses;

カテゴリごとのPV-記事数-平均PVを確認

カテゴリごとにPVをカウントするSQLです。なお、複数のカテゴリに所属する記事もあるので合計しても上記とは一致しません。

select TER.term_id
      ,TER.slug
      ,sum(ACC.count) PV
      ,count(distinct ACC.post_id) post_count
      ,sum(ACC.count)/count(distinct ACC.post_id) avg_count
  from wp_cocoon_accesses ACC
       inner join
           wp_term_relationships REL
       on ACC.post_id = REL.object_id
       inner join
          wp_term_taxonomy TAX
       on REL.term_taxonomy_id = TAX.term_taxonomy_id
       inner join
          wp_terms TER
       on TAX.term_id = TER.term_id
 where TAX.taxonomy='category'
 group by TER.term_id;
スポンサーリンク

今日アクセスのあったページ一覧

Cocoonだと初心者ブロガーの場合、

select acc.post_id
,pos.post_name
,sum(count)
from wp_cocoon_accesses acc
inner join wp_posts pos
on acc.post_id = pos.id
where date=curdate()
group by acc.post_id,pos.post_name
order by sum(count) desc;

最後に

wordpressのデータの裏側はデータベース、特にRDBMS(リレーショナルデータベース)と呼ばれるものです。そして、データベースと会話するにはSQLという言葉を用います。

SQL文に関しては、IPAの主催する情報処理技術者試験にもよく出題されます。実はwordpressのような身近なアプリケーションで使われてるんですよね。

今後もこういう記事も増やしていきたいと思ってます。良ければ他の記事も見てってください。Twitterもやってますのよければフォローお願いします。

ブログ運営
スポンサーリンク
スポンサーリンク
takaをフォローする

コメント

  1. […] WordPressのデータを直接集計してブログ傾向を知る方法【Cocoon向けSQLあり】w… ブログ・サイト管理 kusanagi WordPress ブログ 動作環境 スポンサーリンク スポンサーリンク シェアする Twitter Facebook はてブ Pocket LINE コピー takaをフォローする taka アフターファイブ改革 […]

タイトルとURLをコピーしました