MySQL8 多段的なCTE表でアクセスログを集計するSQL

多段的にする必要は実はそんなにないのですが、apacheおよびnginx等のアクセスログ（テーブル）をカテゴリに分けて集計するSQLを作ってみました。

nginxのログをテーブルに格納する方法については、以前、ここにまとめました。（ちょっと古いか。。）

hit.hateblo.jp

以下、今回のログの形式ですが、上記の内容とは別のものです。

CREATE TABLE `accesslogs` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `remote_address` varchar(128) DEFAULT NULL,
  `User_Agent` varchar(512) DEFAULT NULL,
  `Url` varchar(512) DEFAULT NULL,
  `CreatedAt` datetime NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=159193 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

やりたいことは、

googlebotからのクロールが？
どのディレクトリをクロールしているか？
最終のクロール日時を出しつつ
botがPC用クロールなのかモバイル用のクロールなのかを判別しながら

のアクセス回数をカウントするSQLです。

結果からいうとこんな感じになります

+------------------+-----------------------------------------------------+-------------+---------------------+
| BotType          | UriType                                             | AccessCount | LastAccess          |
+------------------+-----------------------------------------------------+-------------+---------------------+
| Googlebot_Mobile | zzzz                                                |        1934 | 2018-10-30 08:25:37 |
| Googlebot_PC     | 12345678                                            |        1226 | 2018-10-30 08:21:02 |
| Googlebot_Mobile | 12345678                                            |         462 | 2018-10-30 08:25:12 |
| Googlebot_PC     | abcdef                                              |       10106 | 2018-10-30 08:25:58 |
| Googlebot_PC     | zzzz                                                |        5306 | 2018-10-30 08:23:46 |
| Googlebot_Mobile | abcdef                                              |         633 | 2018-10-30 08:15:30 |

集計SQL

以下がそのSQLです

WITH tmp_googlebot
AS
(
SELECT * 
FROM
 accesslogs 
WHERE
  User_Agent LIKE '%Googlebot%'
)
,tmp_googlebot2
AS
(
SELECT 
  bot.Id AS Id,
  CASE 
    WHEN bot.User_Agent LIKE '%Android%' THEN 'Googlebot_Mobile' 
    ELSE 'Googlebot_PC'
  END AS UA ,
  REPLACE(bot.Url,'https://hit.hateblo.jp','') AS uri,
  bot.CreatedAt
FROM
  tmp_googlebot bot
)
, 
tmp_googlebot3 AS
(
SELECT
  bot.Id,
  bot.UA,
  bot.CreatedAt,
  CASE 
    WHEN bot.uri LIKE '%/abcdef%' THEN 'abcdef' 
    WHEN bot.uri LIKE '%/zzzz%' THEN 'zzzz'
    WHEN bot.uri LIKE '%/12345678%' THEN '12345678'
    ELSE bot.uri
  END AS UriType 
FROM
  tmp_googlebot2 bot 
)
SELECT
  bot.UA AS BotType,
  bot.UriType AS UriType,
  COUNT(bot.Id) AS AccessCount,
  MAX(bot.CreatedAt) AS LastAccess
FROM
  tmp_googlebot3 bot 
GROUP BY
  bot.UA,
  bot.UriType
;

簡単に説明しますと。。

1.tmp_googlebot

User_Agentにgooglebotを含むレコードのみフィルタする

WITH tmp_googlebot
AS
(
SELECT * 
FROM
 accesslogs 
WHERE
  User_Agent LIKE '%Googlebot%'
)

2. tmp_googlebot2

tmp_googlebot2
AS
(
SELECT 
  bot.Id AS Id,
  CASE 
    WHEN bot.User_Agent LIKE '%Android%' THEN 'Googlebot_Mobile' 
    ELSE 'Googlebot_PC'
  END AS UA ,
  REPLACE(bot.Url,'https://hit.hateblo.jp','') AS uri,
  bot.CreatedAt
FROM
  tmp_googlebot bot
)

tmp_googlebot 内の User_Agent

  CASE 
    WHEN bot.User_Agent LIKE '%Android%' THEN 'Googlebot_Mobile' 
    ELSE 'Googlebot_PC'

Androidを含んでいれば、Googlebot_Mobile それ以外だったらいれば、Googlebot_PC と値変換

tmp_googlebot 内の Url

REPLACE(bot.Url,'https://hit.hateblo.jp','') AS uri,

ドメインは必要ないので、文字を置き換え、ディレクトリのみの値に変換

3. tmp_googlebot3

tmp_googlebot3 AS
(
SELECT
  bot.Id,
  bot.UA,
  bot.CreatedAt,
  CASE 
    WHEN bot.uri LIKE '%/abcdef%' THEN 'abcdef' 
    WHEN bot.uri LIKE '%/zzz%' THEN 'zzzz'
    WHEN bot.uri LIKE '%/12345678%' THEN '12345678'
    ELSE bot.uri
  END AS UriType 
FROM
  tmp_googlebot2 bot 
)

tmp_googlebot2 内の Uri

  CASE 
    WHEN bot.uri LIKE '%/abcdef%' THEN 'abcdef' 
    WHEN bot.uri LIKE '%/zzz%' THEN 'zzzz'
    WHEN bot.uri LIKE '%/12345678%' THEN '12345678'
    ELSE bot.uri

サブディレクトリが上記のように合致したら、後のパラメータは必要ないので、ディレクトリ名だけで丸める

4.集計

ここまでデータを補正した上で、集計を実行、参照テーブルは、tmp_googlebot3

SELECT
  bot.UA AS BotType,
  bot.UriType AS UriType,
  COUNT(bot.Id) AS AccessCount,
  MAX(bot.CreatedAt) AS LastAccess
FROM
  tmp_googlebot3 bot 
GROUP BY
  bot.UA,
  bot.UriType
;

全体のSQLを見ると、うわ、まじでキモい！って思いますが、CTEを活用してパーツごと(WITH句ごと）に見ると非常にわかりやすいSQLになるという例です。MySQL8未満のバージョンでこれを実現しようとすると、まじでキモいSQLが作れます（笑）

f:id:hit10231023:20180309104332j:plain

でも、これはとは別に、CASE式でLIKEを使う場合どうすればいいのかってのがわからなくて色々調べちゃいました。

Database JUNKY

MySQL,MariaDBを中心としたブログです