広告配信ログ集計時によく使うMySQLテクニック集 #MySQL

広告配信結果のレポート用に、配信ログをSQLで集計することが多くあるのですが、そこでよく使う簡単なテクニックの一部を載せてみます。

環境

MySQL 5.6

前提

広告（ad_id）・配信日時（delivered_at）ごとの表示回数（imp）・クリック数（click）が保存されている
時刻別データ（hourly_statテーブル）と日別データ（daily_statテーブル）がある

UTCをJSTに直す

convert_tz() でもできるが、面倒なのでいつも + interval 9 hour で足し算してしまう。

select
  id, ad_id,
  delivered_at + interval 9 hour as delivered_at,
  imp, click
from hourly_stat;

日別データを月別にまとめる

date_format と group by を用いる

select
  ad_id,
  date_format(delivered_at + interval 9 hour, "%Y-%m") as delivered_month,
  sum(imp) as imp,
  sum(click) as click
from daily_stat
group by ad_id, delivered_month
order by ad_id, delivered_month;

特定の条件でグループ化する

各レコードを特定の条件で分類して、分類別に合計などを集計したい場合に有効
CASE文を使って特定の条件に応じて変わるカラムを作り出して、そのカラムをGROUP BY句に指定する
下記クエリでは、18:00〜4:59をnight、5:00〜11:59をmorning、12:00〜17:59をafternoonと定義してzoneというカラムを作った後、zoneの値ごとにimpやclickの合計を集計しています。
group by句をコメントアウトして見比べてみると処理の流れがピンと来るかもしれません。

select
  case
    when hour(delivered_at + interval 9 hour) >= 18
      or hour(delivered_at + interval 9 hour) <= 4
    then "night"
    when hour(delivered_at + interval 9 hour) >= 5
     and hour(delivered_at + interval 9 hour) <= 11
    then "morning"
    else "afternoon" end as zone,
  sum(imp) as imp,
  sum(click) as click
from hourly_stat
where ad_id = 1
group by zone;

特定の条件を満たすレコードだけ合計する

特定の条件を満たすレコードだけ合計したいことがあります。Excelで言うとsumifですね。
例えば「表示回数（imp）が1時間に10,000回以上のレコードだけ集計した場合と、1時間に15,000回以上のレコードだけ集計した場合でクリック率を比較する」とかでしょうか。
複数条件ごとに結果を横に並べることができたり、グループ化して一括で集計できるところが利点です。
ちなみにsumの中に単純に条件文だけを入れると、trueの時は1、falseの時は0として集計してくれるので、 条件を満たすレコードの数 を集計できます。

select
  ad_id,
  sum(imp >= 10000 and imp < 15000)
    as "1時間に10,000〜15,000回表示したレコードの数",
  sum(case when imp >= 10000 and imp < 15000 then imp else 0 end)
    as "1時間に10,000〜15,000回表示した時の合計表示回数",
  sum(case when imp >= 10000 and imp < 15000 then click else 0 end)
    as "1時間に10,000〜15,000回表示した時のクリック数",
  sum(imp >= 15000)
    as "1時間に15,000回以上表示したレコードの数",
  sum(case when imp >= 15000 then imp else 0 end)
    as "1時間に15,000回以上表示した時の合計表示回数",
  sum(case when imp >= 15000 then click else 0 end)
    as "1時間に15,000回以上表示した時のクリック数"
from hourly_stat
group by ad_id;

まだまだ他にもあるんですが、複雑で説明が難しいものも多いので一旦ここまで。

サンプルデータ

もし上記クエリを自分で試したい方は、localhost上で下記コマンドを順番に実行すると上記と同じテーブル・レコードの環境を作成できます。

create database qiita;
use qiita;

CREATE TABLE `hourly_stat` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ad_id` bigint(20) NOT NULL,
  `delivered_at` datetime NOT NULL,
  `imp` bigint(20) NOT NULL,
  `click` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8;

INSERT INTO `hourly_stat` (`id`, `ad_id`, `delivered_at`, `imp`, `click`)
VALUES
    (1,1,'2018-03-31 15:00:00',8000,120),
    (2,2,'2018-03-31 15:00:00',12000,130),
    (3,3,'2018-03-31 15:00:00',9000,110),
    (4,1,'2018-03-31 16:00:00',15000,140),
    (5,2,'2018-03-31 16:00:00',9000,150),
    (6,3,'2018-03-31 16:00:00',8000,130),
    (7,1,'2018-03-31 17:00:00',12000,120),
    (8,2,'2018-03-31 17:00:00',9000,130),
    (9,3,'2018-03-31 17:00:00',15000,110),
    (10,1,'2018-03-31 18:00:00',9000,140),
    (11,2,'2018-03-31 18:00:00',10000,150),
    (12,3,'2018-03-31 18:00:00',12000,130),
    (13,1,'2018-03-31 19:00:00',8000,120),
    (14,1,'2018-03-31 20:00:00',12000,130),
    (15,1,'2018-03-31 21:00:00',9000,110),
    (16,1,'2018-03-31 22:00:00',15000,140),
    (17,1,'2018-03-31 23:00:00',9000,150),
    (18,1,'2018-04-01 00:00:00',8000,130),
    (19,1,'2018-04-01 01:00:00',12000,120),
    (20,1,'2018-04-01 02:00:00',9000,130),
    (21,1,'2018-04-01 03:00:00',15000,110),
    (22,1,'2018-04-01 04:00:00',9000,140),
    (23,1,'2018-04-01 05:00:00',10000,150),
    (24,1,'2018-04-01 06:00:00',12000,130),
    (25,1,'2018-04-01 07:00:00',8000,120),
    (26,1,'2018-04-01 08:00:00',12000,130),
    (27,1,'2018-04-01 09:00:00',9000,110),
    (28,1,'2018-04-01 10:00:00',15000,140),
    (29,1,'2018-04-01 11:00:00',9000,150),
    (30,1,'2018-04-01 12:00:00',8000,130),
    (31,1,'2018-04-01 13:00:00',12000,120),
    (32,1,'2018-04-01 14:00:00',9000,130),
    (33,1,'2018-04-01 15:00:00',15000,110),
    (34,1,'2018-04-01 16:00:00',9000,140),
    (35,1,'2018-04-01 17:00:00',10000,150),
    (36,1,'2018-04-01 18:00:00',12000,130);

CREATE TABLE `daily_stat` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ad_id` bigint(20) NOT NULL,
  `delivered_at` datetime NOT NULL,
  `imp` bigint(20) NOT NULL,
  `click` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

INSERT INTO `daily_stat` (`id`, `ad_id`, `delivered_at`, `imp`, `click`)
VALUES
    (1,1,'2018-03-31 15:00:00',8000,120),
    (2,2,'2018-03-31 15:00:00',12000,130),
    (3,3,'2018-03-31 15:00:00',9000,110),
    (4,1,'2018-04-01 15:00:00',15000,140),
    (5,2,'2018-04-01 15:00:00',9000,150),
    (6,3,'2018-04-01 15:00:00',8000,130),
    (7,1,'2018-04-02 15:00:00',12000,120),
    (8,2,'2018-04-02 15:00:00',9000,130),
    (9,3,'2018-04-02 15:00:00',15000,110),
    (10,1,'2018-04-03 15:00:00',9000,140),
    (11,2,'2018-04-03 15:00:00',10000,150),
    (12,3,'2018-04-03 15:00:00',12000,130);