SQLで分析を始めた人に贈る、中級者に上がるための10のTips -後編-
こんにちは！pairsのAnalyzeチームでエンジニアをしている鉄本です。
Analyzeチームでは、施策検討時のデータ抽出やサービス上の問題検知のために、分析用途のSQLクエリ (MySQL) を作成しています。
このシリーズでは、日々の業務を通して学んだTipsやよく使うクエリの一部を簡単な活用例と一緒に、2回に分けて紹介しています。今回はその後編です。
※前編はこちら
前回に引き続き、クエリの紹介の中で出てくる関数の詳細は、公式リファレンスを参考にしてください。
目次
– 前編 –
1. クエリ内の複数箇所で使われる固定値を「ユーザ定義変数」でまとめる
2. 誕生日(DATE)から現在の年齢を計算する
3. データのない日を補完して日別レポートを作成する
4. 7日移動平均でデータの増減傾向を割り出す
※前編でも、分析でよく使う便利なクエリを紹介しているので、ぜひこちらも参考にしてみてくださいね！
– 後編 –
5. メールアドレスのドメインを集計する
6. 乱数で検証データを生成する
7. 複数行の結果をカンマ区切りで1行に集約する
8. グルーピングした1番古い(新しい)レコードの情報を取り出す
5. メールアドレスのドメインを集計する
メールアドレスのドメインの分布を分析したいことってありますよね？
そんな時に役に立つのが SUBSTRING_INDEX() という関数です。
SET @EMAIL = 'hoge@hoge.com';
SELECT SUBSTRING_INDEX(@EMAIL, '@', -1); // hoge.com
SUBSTRING_INDEX()は区切り文字に対して、N個目までの文字列を切り出すための関数です。
“www.pairs.lv” のURLのドメインを例にクエリを実行してみます。
正の値は先頭から、負の値は末尾から個数を指定できることがわかります。
SET @URL_DOMAIN = 'www.pairs.lv';
SELECT
num,
SUBSTRING_INDEX(@URL_DOMAIN, '.', num) AS plus,
SUBSTRING_INDEX(@URL_DOMAIN, '.', -1 * num) AS minus
FROM
( -- 0〜3までの連続する数字を生成。詳しくは、前編を参照ください。
SELECT @num := 0 AS num
UNION ALL
SELECT @num := @num+1 FROM information_schema.COLUMNS
LIMIT 4
) AS n
;
num
plus
minus
0
1
www
lv
2
www.pairs
pairs.lv
3
www.pairs.lv
www.pairs.lv
さて、本題に戻ります。
例えば以下のようなデータを集計する場合、メールアドレスのドメインでグルーピングして集計することができます。
user
user_id
email
1
hoge@hoge.com
2
hogehoge@hoge.com
3
fuga@fuga.com
SELECT
SUBSTRING_INDEX(email, '@', -1) AS domain,
COUNT(user_id) AS cnt
FROM user
GROUP BY domain
;
domain
cnt
hoge.com
2
fuga.com
1
メールアドレス以外にも、URLのサブドメインを知りたい、小数の小数部だけを抜き出したい…など様々なシーンで利用できます。
6. 乱数で検証データを生成する
検証用に自前でデータを用意するとき、本番データをマスキングして使うことがあります。
そのときに役に立つのが、「ランダムな文字列を生成する方法」です。
例えば、先ほどのドメインを取得するクエリを例にマスキングしたメールアドレスを作ってみます。
ランダムな文字列は、MD5()やUUID(), ENCRYPT()..などの暗号化関数で生成することができます。
SET @EMAIL = 'hoge@hoge.com';
SELECT CONCAT(MD5(@EMAIL), '@', SUBSTRING_INDEX(@EMAIL, '@', -1));
// => 4712f9b0e63f56ad952ad387eaa23b9c@hoge.com
さらに、SUBSTRING()関数で必要な長さに調整することもできるので、
emailの重複を許容している場合は以下のクエリでも大丈夫です。
以下の例では、生成した文字列の1文字目から6文字分を切り取っています。
SET @EMAIL = 'hoge@hoge.com';
SELECT CONCAT(SUBSTRING(MD5(@EMAIL), 1, 6), '@', SUBSTRING_INDEX(@EMAIL, '@', -1));
// => 4712f9@hoge.com
また、数字を適当に用意したいときにはRAND()関数を使います。
RAND()関数自体は0以上1未満の小数の値を返すので、必要に応じて四捨五入や桁の調整が必要になります。
例えば、 1〜31の数字が欲しい場合は以下で取得することができます。
SET @FROM = 1, @TO = 31;
SELECT FLOOR(@FROM + RAND() * (@TO - @FROM + 1));
// => 23
これらを応用すると、特定の日から1年以内の日付をランダムで必要な件数分生成する、といったことも可能です。
SET @START_DATE = '2000-01-01', @FROM = 1, @TO = 365;
SELECT
@START_DATE + INTERVAL FLOOR(@FROM + RAND() * (@TO - @FROM + 1)) DAY AS random_date
FROM
(
SELECT @num := 0 AS num
UNION ALL
SELECT @num := @num+1 FROM information_schema.COLUMNS
LIMIT 100
) AS n
;
random_date
2000-06-28
2000-08-10
2000-07-27
…
7. 複数行の結果をカンマ区切りで1行に集約する
以下のような、ユーザーの趣味のテーブルがあるとします。
user_hobby
uid
hobby_type
hobby
1
インドア
読書
1
インドア
料理
2
アウトドア
旅行
ここで、ユーザー毎に横並びで趣味を表示したい場合、GROUP_CONCAT()という便利な関数があります。
SELECT
user_id AS uid,
GROUP_CONCAT(hobby) AS hobby_list
FROM user_hobby
GROUP BY uid
;
uid
hobby_list
1
読書,料理
2
旅行
さらに、並びや区切り文字の指定も可能です。
仮にデータが重複していた場合、DISTINCTをつけて弾くこともできます。便利ですね。
SELECT
user_id AS uid,
GROUP_CONCAT(hobby
ORDER BY hobby DESC -- 降順に並べる
SEPARATOR "\t" -- 区切り文字をタブに
) AS hobby_list,
GROUP_CONCAT(DISTINCT hobby_type) AS type_list -- データの重複を弾く
FROM user_hobby
GROUP BY uid
;
uid
hobby_list
type_list
1
料理 読書
インドア
2
旅行
アウトドア
また、テーブル結合をしてデータがない場合は”NULL”になります。
先ほどの例のuserテーブルと結合して集計してみます。
SELECT u.id AS uid, GROUP_CONCAT(hobby)
FROM user AS u
LEFT JOIN user_hobby AS h
ON u.id = h.user_id
GROUP BY uid
;
uid
hobby_list
1
読書,料理
2
旅行
3
NULL
余談ですが、この結果を用いてExcelで区切り位置を「,」に指定すれば、セルを分割することもできます。
Excelのアウトプット例
uid
hobby_list
1
読書
料理
2
旅行
3
8. グルーピングした1番古い(新しい)レコードの情報を取り出す
データをグルーピングするとき、1番古いレコードの情報をとりたいことってありますよね。
例えば以下のようなポイント取得テーブルがあるとします。
ここで、日別でユーザー毎に、最初のレコード日時と獲得したポイント数や、そのとき利用していたデバイス情報を取得したいとき、どうしますか？
user_point
id
user_id
created_at
point
device
1
1
2016-10-01 00:00:00
100
pc
2
1
2016-10-01 13:00:00
200
sp
3
1
2016-10-02 01:00:00
300
sp
4
2
2016-10-01 12:00:00
200
ios
5
2
2016-10-01 14:00:00
100
sp
6
2
2016-10-02 02:00:00
500
sp
7
2
2016-10-02 15:00:00
50
ios
よく例にあがる方法としては、
目的のグルーピングをしたSELECT文でレコードを特定し、クエリを入れ子にすることで目的のデータを取得できます。
SELECT
user_id,
DATE(created_at) AS dt,
point AS first_point,
device AS first_device
FROM user_login
WHERE
id IN -- 入れ子にしたSELECT文でグルーピングした最古のレコードのidを取得する
(
SELECT
MIN(id)
FROM user_login
GROUP BY user_id, DATE(created_at)
)
GROUP BY user_id, dt
;
user_id
dt
first_point
first_device
1
2016-10-01
100
pc
1
2016-10-02
300
sp
2
2016-10-01
200
ios
2
2016-10-02
500
sp
ただこのIN句とサブクエリの組み合わせは、データ量が増えたり、複雑なクエリになると結果が返ってこない可能性も考えられます。
そこで、普段分析クエリを作る時に工夫している方法を紹介したいと思います。
SELECT
user_id,
DATE(created_at) AS dt,
MIN(id * 1000 + point) % 1000 AS first_point,
(CASE
MIN(id * 10 +
CASE device
WHEN 'sp' THEN 1
WHEN 'pc' THEN 2
WHEN 'ios' THEN 3
WHEN 'android' THEN 4
ELSE 5
END
) % 10
WHEN 1 THEN 'sp'
WHEN 2 THEN 'pc'
WHEN 3 THEN 'ios'
WHEN 4 THEN 'android'
ELSE 5 THEN 'other'
END) AS first_device
FROM user_login
GROUP BY user_id, dt
;
一見複雑そうに見えますが、これでサブクエリを使ったり重いクエリになることなく集計が可能になりました。
ここでは、MIN()関数を使って1番古いレコードを集計する際に、桁を上げて必要な情報を付与しています。
今回の例ではポイントは最大3桁なので、idに1000をかけてポイントを加算する (id*10000+point) ことで、idと同じ大小関係のデータを用意しています。
id
point
id*1000+point
1
100
1100
2
200
2200
3
300
3300
4
200
4200
5
100
5100
6
500
6500
7
50
7050
クエリの複雑度が増すので今回は説明を割愛しますが、仮に必要な桁数がわからない場合は、小数点やLENGTH()関数を使ってidに桁の情報も含めることで対応可能です。
また、pointのように数字ではない場合でも、種類が決まっていればCASE文を使って一時的にidを振り分けてあげることで対応が可能になります。
deviceの例を参考に見てみると、CASE文を2度使って工夫しています。
① 1度目のCASE文：device情報をidに置き換える
② idの桁を1つ上げて、1の位にidに変換したデバイス情報を埋め込む
③ デバイス情報を埋め込んだidが1番若いレコードを取得する
④ 2度目のCASE文：device情報を復元する
(CASE
-- ③ デバイス情報を埋め込んだidが1番若いレコードを取得する
MIN(
-- ② idの桁を1つ上げて、1の位にidに変換したデバイス情報を埋め込む
id * 10 +
-- ① 1度目のCASE文：device情報をidに置き換える
CASE device
WHEN 'sp' THEN 1
WHEN 'pc' THEN 2
WHEN 'ios' THEN 3
WHEN 'android' THEN 4
ELSE 5
END
)
-- ④ 2度目のCASE文：device情報を復元する
% 10
WHEN 1 THEN 'sp'
WHEN 2 THEN 'pc'
WHEN 3 THEN 'ios'
WHEN 4 THEN 'android'
ELSE 5 THEN 'other'
END) AS first_device
もしidのような連番で特定できるカラムがない場合でも、例えばUNIX_TIMESTAMP(created_at)で作成順に置き換えることが可能です。
テーブルの構成や目的に合わせて、工夫してみてください！
最後に
本記事を最後までご覧いただきありがとうございます。
今回も、前編に続いて分析や検証で役に立つTipsを4つ紹介しました。
前回に比べて「使わなくてもなんとかなるけど使ってみたら結構便利！」な内容を紹介していますので、是非使ってみてください。
番外編について
実は10のTipsがまだ8つしかないことにお気付きの方もいると思います。
本当は「私がはまったMySQLの罠」についてもお話したかったのですが、なかなかのボリュームになってしまったため、次回 番外編にてお話したいと思います。
どうぞお楽しみに！！
エウレカでは、一緒に働いていただける方を絶賛募集中です。募集中の職種はこちらからご確認ください！皆様のエントリーをお待ちしております！
こんにちは！pairsのAnalyzeチームでエンジニアをしている鉄本です。
Analyzeチームでは、施策検討時のデータ抽出やサービス上の問題検知のために、分析用途のSQLクエリ (MySQL) を作成しています。
このシリーズでは、日々の業務を通して学んだTipsやよく使うクエリの一部を簡単な活用例と一緒に、2回に分けて紹介しています。今回はその後編です。
※前編はこちら
前回に引き続き、クエリの紹介の中で出てくる関数の詳細は、公式リファレンスを参考にしてください。
目次
– 前編 –
1. クエリ内の複数箇所で使われる固定値を「ユーザ定義変数」でまとめる
2. 誕生日(DATE)から現在の年齢を計算する
3. データのない日を補完して日別レポートを作成する
4. 7日移動平均でデータの増減傾向を割り出す
※前編でも、分析でよく使う便利なクエリを紹介しているので、ぜひこちらも参考にしてみてくださいね！
– 後編 –
5. メールアドレスのドメインを集計する
6. 乱数で検証データを生成する
7. 複数行の結果をカンマ区切りで1行に集約する
8. グルーピングした1番古い(新しい)レコードの情報を取り出す
5. メールアドレスのドメインを集計する
メールアドレスのドメインの分布を分析したいことってありますよね？
そんな時に役に立つのが SUBSTRING_INDEX() という関数です。
SET @EMAIL = 'hoge@hoge.com'; SELECT SUBSTRING_INDEX(@EMAIL, '@', -1); // hoge.com
SUBSTRING_INDEX()は区切り文字に対して、N個目までの文字列を切り出すための関数です。
“www.pairs.lv” のURLのドメインを例にクエリを実行してみます。
正の値は先頭から、負の値は末尾から個数を指定できることがわかります。
SET @URL_DOMAIN = 'www.pairs.lv';
SELECT
num,
SUBSTRING_INDEX(@URL_DOMAIN, '.', num) AS plus,
SUBSTRING_INDEX(@URL_DOMAIN, '.', -1 * num) AS minus
FROM
( -- 0〜3までの連続する数字を生成。詳しくは、前編を参照ください。
SELECT @num := 0 AS num
UNION ALL
SELECT @num := @num+1 FROM information_schema.COLUMNS
LIMIT 4
) AS n
;
| num | plus | minus |
|---|---|---|
| 0 | ||
| 1 | www | lv |
| 2 | www.pairs | pairs.lv |
| 3 | www.pairs.lv | www.pairs.lv |
さて、本題に戻ります。
例えば以下のようなデータを集計する場合、メールアドレスのドメインでグルーピングして集計することができます。
user
| user_id | |
|---|---|
| 1 | hoge@hoge.com |
| 2 | hogehoge@hoge.com |
| 3 | fuga@fuga.com |
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(user_id) AS cnt FROM user GROUP BY domain ;
| domain | cnt |
|---|---|
| hoge.com | 2 |
| fuga.com | 1 |
メールアドレス以外にも、URLのサブドメインを知りたい、小数の小数部だけを抜き出したい…など様々なシーンで利用できます。
6. 乱数で検証データを生成する
検証用に自前でデータを用意するとき、本番データをマスキングして使うことがあります。
そのときに役に立つのが、「ランダムな文字列を生成する方法」です。
例えば、先ほどのドメインを取得するクエリを例にマスキングしたメールアドレスを作ってみます。
ランダムな文字列は、MD5()やUUID(), ENCRYPT()..などの暗号化関数で生成することができます。
SET @EMAIL = 'hoge@hoge.com'; SELECT CONCAT(MD5(@EMAIL), '@', SUBSTRING_INDEX(@EMAIL, '@', -1)); // => 4712f9b0e63f56ad952ad387eaa23b9c@hoge.com
さらに、SUBSTRING()関数で必要な長さに調整することもできるので、
emailの重複を許容している場合は以下のクエリでも大丈夫です。
以下の例では、生成した文字列の1文字目から6文字分を切り取っています。
SET @EMAIL = 'hoge@hoge.com'; SELECT CONCAT(SUBSTRING(MD5(@EMAIL), 1, 6), '@', SUBSTRING_INDEX(@EMAIL, '@', -1)); // => 4712f9@hoge.com
また、数字を適当に用意したいときにはRAND()関数を使います。
RAND()関数自体は0以上1未満の小数の値を返すので、必要に応じて四捨五入や桁の調整が必要になります。
例えば、 1〜31の数字が欲しい場合は以下で取得することができます。
SET @FROM = 1, @TO = 31; SELECT FLOOR(@FROM + RAND() * (@TO - @FROM + 1)); // => 23
これらを応用すると、特定の日から1年以内の日付をランダムで必要な件数分生成する、といったことも可能です。
SET @START_DATE = '2000-01-01', @FROM = 1, @TO = 365;
SELECT
@START_DATE + INTERVAL FLOOR(@FROM + RAND() * (@TO - @FROM + 1)) DAY AS random_date
FROM
(
SELECT @num := 0 AS num
UNION ALL
SELECT @num := @num+1 FROM information_schema.COLUMNS
LIMIT 100
) AS n
;
| random_date |
|---|
| 2000-06-28 |
| 2000-08-10 |
| 2000-07-27 |
| … |
7. 複数行の結果をカンマ区切りで1行に集約する
以下のような、ユーザーの趣味のテーブルがあるとします。
user_hobby
| uid | hobby_type | hobby |
|---|---|---|
| 1 | インドア | 読書 |
| 1 | インドア | 料理 |
| 2 | アウトドア | 旅行 |
ここで、ユーザー毎に横並びで趣味を表示したい場合、GROUP_CONCAT()という便利な関数があります。
SELECT user_id AS uid, GROUP_CONCAT(hobby) AS hobby_list FROM user_hobby GROUP BY uid ;
| uid | hobby_list |
|---|---|
| 1 | 読書,料理 |
| 2 | 旅行 |
さらに、並びや区切り文字の指定も可能です。
仮にデータが重複していた場合、DISTINCTをつけて弾くこともできます。便利ですね。
SELECT
user_id AS uid,
GROUP_CONCAT(hobby
ORDER BY hobby DESC -- 降順に並べる
SEPARATOR "\t" -- 区切り文字をタブに
) AS hobby_list,
GROUP_CONCAT(DISTINCT hobby_type) AS type_list -- データの重複を弾く
FROM user_hobby
GROUP BY uid
;
| uid | hobby_list | type_list |
|---|---|---|
| 1 | 料理 読書 | インドア |
| 2 | 旅行 | アウトドア |
また、テーブル結合をしてデータがない場合は”NULL”になります。
先ほどの例のuserテーブルと結合して集計してみます。
SELECT u.id AS uid, GROUP_CONCAT(hobby) FROM user AS u LEFT JOIN user_hobby AS h ON u.id = h.user_id GROUP BY uid ;
| uid | hobby_list |
|---|---|
| 1 | 読書,料理 |
| 2 | 旅行 |
| 3 | NULL |
余談ですが、この結果を用いてExcelで区切り位置を「,」に指定すれば、セルを分割することもできます。
Excelのアウトプット例
| uid | hobby_list | |
|---|---|---|
| 1 | 読書 | 料理 |
| 2 | 旅行 | |
| 3 |
8. グルーピングした1番古い(新しい)レコードの情報を取り出す
データをグルーピングするとき、1番古いレコードの情報をとりたいことってありますよね。
例えば以下のようなポイント取得テーブルがあるとします。
ここで、日別でユーザー毎に、最初のレコード日時と獲得したポイント数や、そのとき利用していたデバイス情報を取得したいとき、どうしますか？
user_point
| id | user_id | created_at | point | device |
|---|---|---|---|---|
| 1 | 1 | 2016-10-01 00:00:00 | 100 | pc |
| 2 | 1 | 2016-10-01 13:00:00 | 200 | sp |
| 3 | 1 | 2016-10-02 01:00:00 | 300 | sp |
| 4 | 2 | 2016-10-01 12:00:00 | 200 | ios |
| 5 | 2 | 2016-10-01 14:00:00 | 100 | sp |
| 6 | 2 | 2016-10-02 02:00:00 | 500 | sp |
| 7 | 2 | 2016-10-02 15:00:00 | 50 | ios |
よく例にあがる方法としては、
目的のグルーピングをしたSELECT文でレコードを特定し、クエリを入れ子にすることで目的のデータを取得できます。
SELECT
user_id,
DATE(created_at) AS dt,
point AS first_point,
device AS first_device
FROM user_login
WHERE
id IN -- 入れ子にしたSELECT文でグルーピングした最古のレコードのidを取得する
(
SELECT
MIN(id)
FROM user_login
GROUP BY user_id, DATE(created_at)
)
GROUP BY user_id, dt
;
| user_id | dt | first_point | first_device |
|---|---|---|---|
| 1 | 2016-10-01 | 100 | pc |
| 1 | 2016-10-02 | 300 | sp |
| 2 | 2016-10-01 | 200 | ios |
| 2 | 2016-10-02 | 500 | sp |
ただこのIN句とサブクエリの組み合わせは、データ量が増えたり、複雑なクエリになると結果が返ってこない可能性も考えられます。
そこで、普段分析クエリを作る時に工夫している方法を紹介したいと思います。
SELECT
user_id,
DATE(created_at) AS dt,
MIN(id * 1000 + point) % 1000 AS first_point,
(CASE
MIN(id * 10 +
CASE device
WHEN 'sp' THEN 1
WHEN 'pc' THEN 2
WHEN 'ios' THEN 3
WHEN 'android' THEN 4
ELSE 5
END
) % 10
WHEN 1 THEN 'sp'
WHEN 2 THEN 'pc'
WHEN 3 THEN 'ios'
WHEN 4 THEN 'android'
ELSE 5 THEN 'other'
END) AS first_device
FROM user_login
GROUP BY user_id, dt
;
一見複雑そうに見えますが、これでサブクエリを使ったり重いクエリになることなく集計が可能になりました。
ここでは、MIN()関数を使って1番古いレコードを集計する際に、桁を上げて必要な情報を付与しています。
今回の例ではポイントは最大3桁なので、idに1000をかけてポイントを加算する (id*10000+point) ことで、idと同じ大小関係のデータを用意しています。
| id | point | id*1000+point |
|---|---|---|
| 1 | 100 | 1100 |
| 2 | 200 | 2200 |
| 3 | 300 | 3300 |
| 4 | 200 | 4200 |
| 5 | 100 | 5100 |
| 6 | 500 | 6500 |
| 7 | 50 | 7050 |
クエリの複雑度が増すので今回は説明を割愛しますが、仮に必要な桁数がわからない場合は、小数点やLENGTH()関数を使ってidに桁の情報も含めることで対応可能です。
また、pointのように数字ではない場合でも、種類が決まっていればCASE文を使って一時的にidを振り分けてあげることで対応が可能になります。
deviceの例を参考に見てみると、CASE文を2度使って工夫しています。
① 1度目のCASE文：device情報をidに置き換える
② idの桁を1つ上げて、1の位にidに変換したデバイス情報を埋め込む
③ デバイス情報を埋め込んだidが1番若いレコードを取得する
④ 2度目のCASE文：device情報を復元する
(CASE
-- ③ デバイス情報を埋め込んだidが1番若いレコードを取得する
MIN(
-- ② idの桁を1つ上げて、1の位にidに変換したデバイス情報を埋め込む
id * 10 +
-- ① 1度目のCASE文：device情報をidに置き換える
CASE device
WHEN 'sp' THEN 1
WHEN 'pc' THEN 2
WHEN 'ios' THEN 3
WHEN 'android' THEN 4
ELSE 5
END
)
-- ④ 2度目のCASE文：device情報を復元する
% 10
WHEN 1 THEN 'sp'
WHEN 2 THEN 'pc'
WHEN 3 THEN 'ios'
WHEN 4 THEN 'android'
ELSE 5 THEN 'other'
END) AS first_device
もしidのような連番で特定できるカラムがない場合でも、例えばUNIX_TIMESTAMP(created_at)で作成順に置き換えることが可能です。
テーブルの構成や目的に合わせて、工夫してみてください！
最後に
本記事を最後までご覧いただきありがとうございます。
今回も、前編に続いて分析や検証で役に立つTipsを4つ紹介しました。
前回に比べて「使わなくてもなんとかなるけど使ってみたら結構便利！」な内容を紹介していますので、是非使ってみてください。
番外編について
実は10のTipsがまだ8つしかないことにお気付きの方もいると思います。
本当は「私がはまったMySQLの罠」についてもお話したかったのですが、なかなかのボリュームになってしまったため、次回 番外編にてお話したいと思います。
どうぞお楽しみに！！
エウレカでは、一緒に働いていただける方を絶賛募集中です。募集中の職種はこちらからご確認ください！皆様のエントリーをお待ちしております！
