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

検証環境
1. Dockerコンテナ環境の場合の補足
2. コマンドライン前提
  1. コマンドラインのメリット
  2. コマンドラインのデメリット
MySQL（mariadb）に接続する
1. なぜ mariadb なのに mysql というコマンドなのか
WordPressのデータベース構造
wordpress共通で使えるSQL文
cocoonテーマで使える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記事達成、などｘｘ記事達成日を確認する（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;