【データ分析】月毎に分かれたログが保存してあるテーブルを結合して集計する方法

目的

別記事で紹介したApacheログを月毎にDBのテーブルに保存してある状態で、月毎のアクセス数を一括して取得したい場合に、一括で取得できるクエリを実現する。

環境

MySQL8.0

テーブル構成

月毎のテーブル構成は以下の作りにしている。

CREATE TABLE `access_log_yyyymm` (
  `index` bigint DEFAULT NULL,
  `ip` text COLLATE utf8mb3_bin,
  `time` datetime DEFAULT NULL,
  `request` text COLLATE utf8mb3_bin,
  `status` bigint DEFAULT NULL,
  `size` bigint DEFAULT NULL,
  `referer` text COLLATE utf8mb3_bin,
  `user_agent` text COLLATE utf8mb3_bin,
  `server` text COLLATE utf8mb3_bin,
  KEY `ix_access_log_202407_index` (`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin

実現方法

そのままでは共通カラムがないため結合できないため、ダミーのカラムを用意して結合すれば月毎のテーブルを結合できる。

各月のリクエストに"*****"が含まれる数をカウントしたい場合は以下の1クエリで取得できる。

SELECT
    sub202401.cnt AS PV_202401
    , sub202402.cnt AS PV_202402
    , sub202403.cnt AS PV_202403
    , sub202404.cnt AS PV_202404
    , sub202405.cnt AS PV_202405
    , sub202406.cnt AS PV_202406
    , sub202407.cnt AS PV_202407
FROM
    ( 
        SELECT
            'dummy' AS dummy
            , COUNT(*) AS cnt 
        FROM
            analytics.access_log_202401
        WHERE
            request LIKE '%*****%'
    ) sub202401 JOIN (
        SELECT
            'dummy' AS dummy
            , COUNT(*) AS cnt 
        FROM
            analytics.access_log_202402
        WHERE
            request LIKE '%*****%'
    ) sub202402
        ON sub202401.dummy = sub202402.dummy
     JOIN (
        SELECT
            'dummy' AS dummy
            , COUNT(*) AS cnt 
        FROM
            analytics.access_log_202403
        WHERE
            request LIKE '%*****%'
    ) sub202403
        ON sub202402.dummy = sub202403.dummy
     JOIN (
        SELECT
            'dummy' AS dummy
            , COUNT(*) AS cnt 
        FROM
            analytics.access_log_202404
        WHERE
            request LIKE '%*****%'
    ) sub202404
        ON sub202403.dummy = sub202404.dummy
     JOIN (
        SELECT
            'dummy' AS dummy
            , COUNT(*) AS cnt 
        FROM
            analytics.access_log_202405 
        WHERE
            request LIKE '%*****%'
    ) sub202405
        ON sub202404.dummy = sub202405.dummy
     JOIN ( 
        SELECT
            'dummy' AS dummy
            , COUNT(*) AS cnt 
        FROM
            analytics.access_log_202406 
        WHERE
            request LIKE '%*****%' 
    ) sub202406 
        ON sub202405.dummy = sub202406.dummy;

改善できそうなところ

Apacheログのテーブルを月毎に保存すればデータ数もそれほど多くなく扱いやすいと思ったが、１つのテーブルに保存するようにして月毎にパーティションを区切って取り扱う方が良いかもしれない。