SQLで分析を始めた人に贈る、中級者に上がるための10のTips — 前編 -

Published in

Eureka Engineering

14 min readAug 31, 2016

こんにちは、PairsのAnalyzeチームでエンジニアをしている鉄本です。

Analyzeチームでは、施策検討時のデータ抽出やサービス上の問題検知のために、分析用途のSQLクエリ (MySQL) を作成しています。
今回は、日々の業務を通して学んだTipsやよく使うクエリの一部を、簡単な活用例と一緒に前編・後編の2回に分けてお話ししたいと思います。
前編では分析でよく使うクエリを中心にご紹介します。

この記事を参考に、分析用SQLの高度な運用に役立てていただけたら幸いです！

本記事で紹介するTipsで用いる関数の詳細については、公式リファレンスを参考にしてください。

- 前編 -

1. クエリ内の複数箇所で使われる固定値を「ユーザ定義変数」でまとめる

2. 誕生日(DATE)から現在の年齢を計算する

3. データのない日を補完して日別レポートを作成する

4. 7日移動平均でデータの増減傾向を割り出す

- 後編 -

5. メールアドレスのドメインを集計する

6. 乱数で検証データを生成する

7. 複数行の結果をカンマ区切りで1行に集約する

8. グルーピングした1番古い(新しい)レコードの情報を取り出す

※後編も、公開しています。ぜひこちらも参考にしてみてくださいね！

1. 固定値を「ユーザ定義変数」でまとめる

以下のようなuserテーブルがあるとします。

id registration_date last_login_date 1 2016–07–31 2016–08–07 2 2016–08–01 2016–08–07 3 2016–08–01 2016–08–16 4 2016–08–02 2016–08–06

ここで、「2016/08/01以前に登録していて、最後のログインが2016/08/01から1週間以内のユーザーを抽出したい」と依頼を受けたとします。

まずは何も考えず、クエリを作ってみます。

SELECT id, registration_date, last_login_date
FROM user
WHERE '2016-08-01' <= registration_date 
  AND last_login_date BETWEEN '2016-08-01' AND '2016-08-01' + INTERVAL 6 DAY ;

ここで「日付を変更したい」と言われたら、3箇所書き換える必要がありますよね。
集計期間を変更するたびに全て書き換えていたら面倒ですし、変更漏れで間違った分析をしかねません。

そんなとき「ユーザー定義変数」を使うと、書き換えるのは1箇所だけで済みます。

ユーザー定義変数の使い方

ユーザー定義変数は「@」をつけて以下のようにSET文で定義することで、クエリの中で使えるようになります。
ただし、ユーザ定義変数はクライアントセッションに固有の値のため、他のセッションから参照することはできず、クライアントセッションが終了すると自動的にリセットされますのでご注意ください。

SET @name1 = 'hoge', @name2 = 'fuga', @name3 = 'piyo'; 
SELECT @name1, @name2, @name3; // => hoge fuga piyo

また、割り当て演算子「:=」を使うとクエリの中で定義することもできます。

SELECT @name1 := 'hoge', @name2 := 'fuga', @name3 := 'piyo'; // => hoge fuga piyo

利用例：複数箇所で固定の日付を指定したクエリを作成する

先ほどの例に戻り、ユーザー定義関数で置き換えてみます。

SET @target_date = '2016-08-01';
SELECT id, registration_date, last_login_date
FROM user
WHERE @target_date <= registration_date
AND last_login_date BETWEEN @target_date AND @target_date + INTERVAL 6 DAY
;

さらに割り当て演算子を使えば、クエリを1つにまとめることができます。

SELECT id, registration_date, last_login_date
FROM user
WHERE @target_date := '2016-08-01' <= registration_date 
  AND last_login_date BETWEEN @target_date AND @target_date + INTERVAL 6 DAY ;

id registration_date last_login_date 1 2016–07–31 2016–08–07 2 2016–08–01 2016–08–07

日付の変更が発生しても、1箇所の書き換えで対応が可能になりました。

2. 誕生日(DATE)から現在の年齢を計算する

生年月日から、ある時点での年齢を算出したいケースは度々見られると思います。
シンプルな解決方法では、「年齢を算出したい日付と生年月日との差分の日数を取り、365日で割る」という方法が考えられます。

SET @now = '2016-08-30 13:00:00', @birthday = '2000-08-31'; 
SELECT TRUNCATE(DATEDIFF(@now, @birthday) / 365, 0) AS age; // => 16

しかしこの方法だと、うるう年が考慮されていないため誤差が発生してしまいます。
そこで私は以下のクエリで計算するようにしています。

SET @now = '2016-08-30 13:00:00', @birthday = '2000-08-31';
SELECT (YEAR(DATE(@now)) - YEAR(@birthday)) 
  - (RIGHT(@birthday, 5) > RIGHT(DATE(@now), 5)) AS age; // => 15

ロジックの解説

このクエリは以下の処理を行っています。

ある時点と生年月日の「年の差分」を取る
YEAR(DATE(@now)) — YEAR(@birthday)
ある時点と生年月日の「月日」を比較する
RIGHT(@birthday, 5) > RIGHT(DATE(@now), 5)
生年月日の方が「月日」が大きい場合、「年の差分」から1を引く
※比較がTRUE=1と見なされる

こうすることで、うるう年を考慮しても正確な年齢を算出することができます。

3. データのない日を補完して日別レポートを作成する

以下のようなeventテーブルがあるとします。

id date event_name 1 2016–08–01 lesson1 2 2016–08–03 lesson2 3 2016–08–03 lesson3 4 2016–08–04 lesson4

ここで、「イベントが無い日は0件として、件数を日次で集計して欲しい」と依頼を受けたとします。
件数の集計は以下のクエリでできますが、0件の情報を持つ事はできません。

SELECT date, COUNT(event_name) AS event_count
FROM event
GROUP BY date;

date event_count 2016–08–01 1 2016–08–03 2 2016–08–04 1

このような時は、「カレンダーテーブル」を使って対応することができます。
カレンダーテーブルとは、以下のように日付だけを列挙したテーブルです。

date 2016–08–01 2016–08–02 2016–08–03 2016–08–04 2016–08–05 2016–08–06 2016–08–07

考え方として、まずカレンダーテーブルを作成し、eventテーブルと外部結合することで、eventテーブルに存在しない日付を集計に含めることができます。

クエリ作成手順

一見難しそうに感じますが、シンプルなロジックで解決できます。

連続する数字を生成する
1の数字を指定日に加算して、連続する日付の一覧(カレンダーテーブル)を取得する
2の日付の一覧と目的のテーブルを外部結合して、日別で集計する

それでは、順番に解説していきます。

1. 連続する数字を生成する

以下の構成で、連続する数字を生成します。

(1) @numに初期値を定義する SELECT文
(2) @numを1ずつ加算する SELECT文
(3) SELECT文の結果を結合する UNION ALL
(4) 必要な件数(日数)を取得する LIMIT

(2)のSELECT文で指定するテーブルはなんでも良いですが、LIMITで指定する件数以上のレコード数が必要です。
今回は、MySQLでは必ず存在するテーブルとしてinformation_schema.COLUMNSを指定しています。

SELECT @num := 0 AS number  -- (1)@numに初期値0を定義する
UNION ALL                   -- (3)SELECT結果を合成する
SELECT @num := @num + 1 FROM information_schema.COLUMNS
  -- (2)@numを1ずつ加算する。指定するテーブルは、LIMITの数以上のレコード数が必要
LIMIT 7                     -- (4)必要な件数(日数)を指定する

series_number 0 1 2 3 4 5 6

2. 連続する日付の一覧を取得する

生成した連続する数字を、DATE_ADD()関数で指定日からの経過日数として加算することで
連続する日付を取得して、仮想のカレンダーテーブルを生成することができます。

SELECT
  DATE_ADD('2016-08-01', INTERVAL series_numbers.number DAY) AS date -- 連続する日付の一覧を取得する
FROM (
  SELECT @num := 0 AS number
  UNION ALL
  SELECT @num := @num + 1 FROM information_schema.COLUMNS
  LIMIT 7
) AS series_numbers
;

date 2016–08–01 2016–08–02 2016–08–03 2016–08–04 2016–08–05 2016–08–06 2016–08–07

3. カレンダーテーブルと外部結合し、日別で集計する

生成したカレンダーテーブルとeventテーブルを外部結合します。
カレンダーテーブルの日別ごとに集計をすることで、eventテーブルにレコードがなくても、0件として集計することができます。

SELECT calendar.date, COUNT(e.id) AS event_count
FROM event AS e
RIGHT JOIN ( -- カレンダーテーブルと外部結合する
    SELECT
      DATE_ADD('2016-08-01', INTERVAL series_numbers.number DAY) AS date
    FROM (
      SELECT @num := 0 AS number
      UNION ALL
      SELECT @num := @num + 1 FROM information_schema.COLUMNS
      LIMIT 7
    ) AS series_numbers
) AS calendar ON calendar.date = e.date
GROUP BY calendar.date -- 日別で集計する
;

date event_count 2016–08–01 1 2016–08–02 0 2016–08–03 2 2016–08–04 1 2016–08–05 0 2016–08–06 0 2016–08–07 0

これでデータがない日を0件で補完することができました。

次の章では、この仮想のカレンダーテーブルを応用した分析を紹介します。

4. 7日移動平均でデータの増減傾向を割り出す

あるデータの件数を日次で集計して、数値が増加傾向か、減少傾向かを把握したいことってありますよね。

しかし、1日あたりの件数が少なかったり、特定の曜日だけ件数が多い場合など、
日によってばらつきの大きいデータの場合、単純な日次集計グラフでは増減傾向がパッと見で分からないことがあります。

そんなとき、とある日を起点とした過去n日分の結果を合計した結果を使うことで、データをならして傾向を見やすくする方法があります。
FXなどのチャート分析ではよく使われる手法で、この方法で出したグラフは移動平均線と呼ばれます。

では、先ほどのカレンダーテーブルを少し工夫して、実際に移動平均線を出してみましょう！
過去m日間のn日移動平均を分析したいとして、組み合わせて使うためのn*mテーブルを作ってみます。

SET @n = 7;
SELECT
  DATE(NOW() - INTERVAL FLOOR(series_numbers.number/@n) DAY) AS day1,
  DATE(NOW() - INTERVAL FLOOR(series_numbers.number/@n)
                             + series_numbers.number%@n DAY) AS day2
FROM(
  SELECT @num := 0 AS number
  UNION ALL
  SELECT @num := @num + 1 FROM information_schema.COLUMNS
  LIMIT 210 -- 遡りたい日数(m)*平均したい日数(n)を指定する
) AS series_numbers
;

day1 day2 2016–08–27 2016–08–27 2016–08–27 2016–08–26 2016–08–27 2016–08–25 2016–08–27 2016–08–24 2016–08–27 2016–08–23 2016–08–27 2016–08–22 2016–08–27 2016–08–21 2016–08–26 2016–08–26 2016–08–26 2016–08–25 2016–08–26 2016–08–24 2016–08–26 2016–08–23 2016–08–26 2016–08–22 2016–08–26 2016–08–21 2016–08–26 2016–08–20 … …

day1の日付に対応する過去n日分の日付の一覧をday2で取得することができます。
このテーブルを使い、実際にデータを出してみましょう。

利用例：ユーザーの登録件数のトレンドを出す

先ほどのn*mテーブルを利用して、そのまま日別の件数を出したものと、7日間移動平均で出したものを比べてみましょう。

SET @n = 7;
SELECT
  day1,
  COUNT(CASE WHEN day1 = day2 THEN id ELSE NULL END) AS 対象日件数,
  COUNT(id)/@n AS n日移動平均件数
FROM (
  (
  SELECT
    DATE(NOW() - INTERVAL FLOOR(series_numbers.number/@n) DAY) AS day1,
    DATE(NOW() - INTERVAL FLOOR(series_numbers.number/@n)
                               + series_numbers.number%@n DAY) AS day2
  FROM(
    SELECT @num := 0 AS number
    UNION ALL
    SELECT @num := @num+1 FROM information_schema.COLUMNS
    LIMIT 210
  ) AS series_numbers
) AS date_map
LEFT JOIN 
  (
  SELECT
    id,
    DATE(created_at) AS date
  FROM user
  ) AS user
  ON date_map.day2 = user.date
)
GROUP BY day1
;

グラフにしてみると、移動平均線が滑らかになって傾向が見やすくなったことがわかりますね！
Pairsではこれを応用して、DailyMAUという「過去30日間のログインのユニークユーザー数」を指標においてサービスの状況を監視しています。

なお、このクエリはテーブルの構成やデータ量によっては、かなり重い処理になりますので、開発環境で検証してから使うようにしてください。

最後に

本記事を最後までご覧いただきありがとうございます。

今回は分析で役に立つTipsを4つ紹介しました。
皆さんも、是非業務で活用して、分析作業の幅を広げてください。

後編について

後編では、テストデータ作成のためのマスキング処理、メールアドレスの便利な集計方法、
マイナーだけど便利な関数、そしてMySQLでハマりがちな罠とその対処法などについて紹介いたします。

どうぞお楽しみに！

追記

後編を公開しました。こちらからチェック！！