データ分析の誤りを未然に防ぐ！ SQL4つの検算テクニック
この記事は Eureka Advent Calendar 2016 17日目 の記事です。
16日目は 海藤さん の「pairsでKotlinを採用した5つの理由」でした。
こんにちは、pairsのAnalyzeチームメンバーの大久保です！
Analyzeチームでは、施策検討時のデータ抽出やサービス上の問題検知のために、分析用途のMySQLクエリを作成しています。
分析用途のクエリを作成していると、WHERE条件忘れやDISTINCT忘れ、「＞」「＜」の取り違えなどのミスにより、誤った分析結果を導き出してしまうことがあります（僕はこれまで何度もミスをしてきました…）。
それを防ぐためには自分で作ったクエリを注意深く眺めたり、複数人でチェックしたりすることで防ぐことができますが、できれば人的リソースや時間をかけすぎずに正確な分析結果を導き出したいものです。
そこで本日はクエリ作成時のミスを防ぐために、弊社Analyzeチームが普段実践することの多い、簡易な検算方法を4つ紹介させていただきます。
※本記事内では、分析結果に誤りがないかを確認するアクションを全て検算と呼びます。
目次
- 検算用クエリの作成
- ローデータの確認
- 既存のクエリ実行結果との比較
- 実行結果の妥当性確認
1.検算用クエリの作成
クエリの実行結果を、別のクエリ（検算用クエリ）の実行結果と比較する検算方法です。
多くの分析の場合1つの結果を導くために複数のクエリの作り方があるので、Aの方法で作成したクエリを、Bの方法で作成したクエリの結果と一致するかを確認することができます。これにより多角的な視点でクエリの結果について確認できるので、重複や条件の漏れなどを防げる確率が上がります。
検算用クエリは、重複や条件漏れを含まないようにできるだけ簡易なものにしましょう。
「検算用クエリの作成」が役立つ事例
次のような2つのテーブルを用意しました。
Table 1-1. users
id
name
email
age
100
田中
tanaka@mail.com
30
200
鈴木
suzuki@mail.com
25
300
佐藤
sato@mail.com
27
Table 1-2. purchases
id
user_id
name
amount
1
1
商品A
3000円
2
1
商品B
2000円
3
3
商品C
4000円
この2つのテーブルから全ユーザー数・課金ユーザー数等を一緒に取得する時、DISTINCTを忘れると誤った結果が導かれてしまいます。
Query 1-1 正しくないクエリ
SELECT
COUNT(u.id) AS UserCnt -- 全ユーザー数
, COUNT(p.user_id) AS PurchaseUU -- 課金ユーザー数
, COUNT(p.id) AS PurchaseCnt -- 全課金数
, SUM(p.amount) AS TotalSales -- 売上合計
FROM users AS u
LEFT JOIN purchases AS p ON p.user_id = u.id
;
Result 1-1
UserCnt
PurchaseUU
PurchaseCnt
TotalSales
4
3
3
9000
Query 1-2 正しいクエリ
SELECT
COUNT(DISTINCT(u.id)) AS UserCnt -- 全ユーザー数
, COUNT(DISTINCT(p.user_id)) AS PurchaseUU -- 課金ユーザー数
, COUNT(p.id) AS PurchaseCnt -- 全課金数
, SUM(p.amount) AS TotalSales -- 売上合計
FROM users AS u
LEFT JOIN purchases AS p ON p.user_id = u.id
;
Result 1-2
UserCnt
PurchaseUU
PurchaseCnt
TotalSales
3
2
3
9000
DISTINCTが必要な理由は、usersとpurchasesの結合により以下のようにusers.id=100のユーザーが2行生成されるためなのは言うまでもないですね。
Table 1-3. users (left join purchases)
users.id
users.name
users.email
users.age
purchases.id
purchases.user_id
purchases.name
purchases.amount
100
田中
tanaka@mail.com
30
1
100
商品A
3000
100
田中
tanaka@mail.com
30
2
100
商品B
2000
200
鈴木
suzuki@mail.com
25
null
null
null
null
300
佐藤
sato@mail.com
27
3
300
商品C
4000
仮に誤ってQuery 1-1のクエリを書いてしまったとしても、以下の検算用クエリの結果とResult 1-1.UserCntの値を比較すればDISTINCT忘れに気付くことができます。
TestQuery 1-3 全ユーザー数確認用
SELECT
COUNT(*)
FROM users
; -- => 3
Query 1-1は簡易なものなので間違える余地がないと思われるかもしれませんが、クエリが複雑になるほど検算用クエリはより大きな意味を持つようになります。
2.ローデータの確認
WHERE句で絞り込んだデータが想定通りのものになっているかを、ローデータ（テーブルに保存されている生のデータ）を目視することで確認する検算方法です。「＞」「＜」の取り違えなどを防ぐために有効です。
「ローデータの確認」が役立つ事例
例えばTable 1-1. usersから年齢29歳以下のユーザーのメールアドレスを取得する以下のクエリを作成したとします。
Query 2-1
SELECT
email
FROM users
WHERE age <= 29 -- [ア]
;
誤って[ア]の「＜」を「＞」に取り違えてしまうと全く違う結果が取得されてしまいます。取得したメールアドレスに対して、20代向けのメールを送るようなケースではこのミスは致命的ですね…
そこで上記クエリを一部修正し、ageの閾値を確認する検算クエリを作成します。
TestQuery 2-2
SELECT
MAX(age) -- 変更箇所
FROM users
WHERE age <= 29
;
実行結果が29以下であれば、Query2-1が正常であることが確認できます。
3.既存のクエリ実行結果との比較
過去に作成したクエリの実行結果と比較する検算方法です。比較対象とするクエリが誤っていては意味がないので、信頼性が担保されたものと比較しましょう。
「既存のクエリ実行結果との比較」が役立つ事例
例えばTable 1-1. usersから年代別のユーザー数を取得する以下のクエリを作成したとします。
Query 3-1
SELECT
FLOOR(age / 10) * 10 AS Generation -- 年代
, COUNT(*) AS UserCnt
FROM users
GROUP BY Generation
;
Result 3-1
Generation
UserCnt
20
2
30
1
全年代のユーザー数の合計値はQuery 1-2で「3」であることが既にわかっているので、年代ごとのユーザー数の合計値と比較することでQuery 3-1の正誤を確認することができます。
比較対象クエリが多ければ多いほど検算の材料が増えるので、クエリは積極的にストックしましょう。弊社ではre:dashというBIツールにクエリをストックしており、検算時にはよく参照しています。
※re:dashの利用に関しては「pairsでの活用例から学ぶre:dash導入のすゝめ」で紹介しています。
4.実行結果の妥当性確認
テーブルの仕様や普段のデータの傾向と比べて、クエリの実行結果に不審な点がないかを確認する検算方法です。検算方法というより心がけに近いかもしれません。
「実行結果の妥当性確認」が役立つ事例
「検算用クエリの作成が役立つ事例」の項で「DISTINCT」をつけ忘れたQuery 1-1により以下の誤った結果が導かれることは先に説明しました。
Result 4-1 (Result 1-1と同じ)
全ユーザー数
課金ユーザー数
全課金数
売上合計
4
3
3
9000
上記の結果の妥当性を確認すると以下の事実に気づけます。
課金ユーザー数 = 全課金数
purchasesテーブルは1人につき複数のデータが存在しうるテーブルなので、この時点で誤っているかもしれないと気付けるかと思います。今回はサンプルデータ数が少ないですが、何千・何万とデータ数があればより明確ではないでしょうか。
実行結果の妥当性確認をすると、ほとんどの場合どの検算方法よりも素早くクエリの不正に気付くことができます。そういう意味ではクエリを作成した際まず最初に行うべきでしょう。
ただしテーブルの仕様や、普段のデータの傾向の習熟度によって妥当性の確認時に得られる情報量は大きく変わります。今回の事例では「purchasesテーブルは1人につき複数のデータが存在しうる」という仕様を把握していなければ、クエリの誤りの可能性に気づけません。あるいはテーブルの仕様を知らなくても普段から課金ユーザー数の数字を把握していれば、上記の誤りに気づけると思います。データ分析の精度を高めるために、ぜひ日頃からテーブルの仕様やデータの傾向をキャッチアップしてみてください。
最後に
本記事では4つの検算方法を紹介しました。どれを用いるのが正解ということはなく、データ分析の性質に合わせて検算方法は使い分けたり組み合わせたりすることが重要だと考えています。
例えば、メール配信のために特定のユーザーのメールアドレスを取得したい場合は「2.ローデータの確認」をする手法を取り、施策検討時のデータ抽出やサービス上の問題検知のための分析の場合には「4.実行結果の妥当性確認」をした上で、「1.検算用クエリの作成」をすることで誤りを未然に防ぐことができます。
適切な検算方法が選択できているかを確認し合う意味で、弊社Analyzeチームではクエリのレビューはもちろん、検算方法のレビューをデータ分析者同士で行っています。データ分析の精度が高まるのでオススメです。
明日の記事は 三津澤さん の「text/template概論」になります！
エウレカでは、一緒に働いていただける方を絶賛募集中です。募集中の職種はこちらからご確認ください！皆様のエントリーをお待ちしております！
この記事は Eureka Advent Calendar 2016 17日目 の記事です。
16日目は 海藤さん の「pairsでKotlinを採用した5つの理由」でした。
こんにちは、pairsのAnalyzeチームメンバーの大久保です！
Analyzeチームでは、施策検討時のデータ抽出やサービス上の問題検知のために、分析用途のMySQLクエリを作成しています。
分析用途のクエリを作成していると、WHERE条件忘れやDISTINCT忘れ、「＞」「＜」の取り違えなどのミスにより、誤った分析結果を導き出してしまうことがあります（僕はこれまで何度もミスをしてきました…）。
それを防ぐためには自分で作ったクエリを注意深く眺めたり、複数人でチェックしたりすることで防ぐことができますが、できれば人的リソースや時間をかけすぎずに正確な分析結果を導き出したいものです。
そこで本日はクエリ作成時のミスを防ぐために、弊社Analyzeチームが普段実践することの多い、簡易な検算方法を4つ紹介させていただきます。
※本記事内では、分析結果に誤りがないかを確認するアクションを全て検算と呼びます。
目次
- 検算用クエリの作成
- ローデータの確認
- 既存のクエリ実行結果との比較
- 実行結果の妥当性確認
1.検算用クエリの作成
クエリの実行結果を、別のクエリ（検算用クエリ）の実行結果と比較する検算方法です。
多くの分析の場合1つの結果を導くために複数のクエリの作り方があるので、Aの方法で作成したクエリを、Bの方法で作成したクエリの結果と一致するかを確認することができます。これにより多角的な視点でクエリの結果について確認できるので、重複や条件の漏れなどを防げる確率が上がります。
検算用クエリは、重複や条件漏れを含まないようにできるだけ簡易なものにしましょう。
「検算用クエリの作成」が役立つ事例
次のような2つのテーブルを用意しました。
Table 1-1. users
| id | name | age | |
|---|---|---|---|
| 100 | 田中 | tanaka@mail.com | 30 |
| 200 | 鈴木 | suzuki@mail.com | 25 |
| 300 | 佐藤 | sato@mail.com | 27 |
Table 1-2. purchases
| id | user_id | name | amount |
|---|---|---|---|
| 1 | 1 | 商品A | 3000円 |
| 2 | 1 | 商品B | 2000円 |
| 3 | 3 | 商品C | 4000円 |
この2つのテーブルから全ユーザー数・課金ユーザー数等を一緒に取得する時、DISTINCTを忘れると誤った結果が導かれてしまいます。
Query 1-1 正しくないクエリ
SELECT
COUNT(u.id) AS UserCnt -- 全ユーザー数
, COUNT(p.user_id) AS PurchaseUU -- 課金ユーザー数
, COUNT(p.id) AS PurchaseCnt -- 全課金数
, SUM(p.amount) AS TotalSales -- 売上合計
FROM users AS u
LEFT JOIN purchases AS p ON p.user_id = u.id
;
Result 1-1
| UserCnt | PurchaseUU | PurchaseCnt | TotalSales |
|---|---|---|---|
| 4 | 3 | 3 | 9000 |
Query 1-2 正しいクエリ
SELECT
COUNT(DISTINCT(u.id)) AS UserCnt -- 全ユーザー数
, COUNT(DISTINCT(p.user_id)) AS PurchaseUU -- 課金ユーザー数
, COUNT(p.id) AS PurchaseCnt -- 全課金数
, SUM(p.amount) AS TotalSales -- 売上合計
FROM users AS u
LEFT JOIN purchases AS p ON p.user_id = u.id
;
Result 1-2
| UserCnt | PurchaseUU | PurchaseCnt | TotalSales |
|---|---|---|---|
| 3 | 2 | 3 | 9000 |
DISTINCTが必要な理由は、usersとpurchasesの結合により以下のようにusers.id=100のユーザーが2行生成されるためなのは言うまでもないですね。
Table 1-3. users (left join purchases)
| users.id | users.name | users.email | users.age | purchases.id | purchases.user_id | purchases.name | purchases.amount |
|---|---|---|---|---|---|---|---|
| 100 | 田中 | tanaka@mail.com | 30 | 1 | 100 | 商品A | 3000 |
| 100 | 田中 | tanaka@mail.com | 30 | 2 | 100 | 商品B | 2000 |
| 200 | 鈴木 | suzuki@mail.com | 25 | null | null | null | null |
| 300 | 佐藤 | sato@mail.com | 27 | 3 | 300 | 商品C | 4000 |
仮に誤ってQuery 1-1のクエリを書いてしまったとしても、以下の検算用クエリの結果とResult 1-1.UserCntの値を比較すればDISTINCT忘れに気付くことができます。
TestQuery 1-3 全ユーザー数確認用
SELECT
COUNT(*)
FROM users
; -- => 3
Query 1-1は簡易なものなので間違える余地がないと思われるかもしれませんが、クエリが複雑になるほど検算用クエリはより大きな意味を持つようになります。
2.ローデータの確認
WHERE句で絞り込んだデータが想定通りのものになっているかを、ローデータ（テーブルに保存されている生のデータ）を目視することで確認する検算方法です。「＞」「＜」の取り違えなどを防ぐために有効です。
「ローデータの確認」が役立つ事例
例えばTable 1-1. usersから年齢29歳以下のユーザーのメールアドレスを取得する以下のクエリを作成したとします。
Query 2-1
SELECT
email
FROM users
WHERE age <= 29 -- [ア]
;
誤って[ア]の「＜」を「＞」に取り違えてしまうと全く違う結果が取得されてしまいます。取得したメールアドレスに対して、20代向けのメールを送るようなケースではこのミスは致命的ですね…
そこで上記クエリを一部修正し、ageの閾値を確認する検算クエリを作成します。
TestQuery 2-2
SELECT
MAX(age) -- 変更箇所
FROM users
WHERE age <= 29
;
実行結果が29以下であれば、Query2-1が正常であることが確認できます。
3.既存のクエリ実行結果との比較
過去に作成したクエリの実行結果と比較する検算方法です。比較対象とするクエリが誤っていては意味がないので、信頼性が担保されたものと比較しましょう。
「既存のクエリ実行結果との比較」が役立つ事例
例えばTable 1-1. usersから年代別のユーザー数を取得する以下のクエリを作成したとします。
Query 3-1
SELECT
FLOOR(age / 10) * 10 AS Generation -- 年代
, COUNT(*) AS UserCnt
FROM users
GROUP BY Generation
;
Result 3-1
| Generation | UserCnt |
|---|---|
| 20 | 2 |
| 30 | 1 |
全年代のユーザー数の合計値はQuery 1-2で「3」であることが既にわかっているので、年代ごとのユーザー数の合計値と比較することでQuery 3-1の正誤を確認することができます。
比較対象クエリが多ければ多いほど検算の材料が増えるので、クエリは積極的にストックしましょう。弊社ではre:dashというBIツールにクエリをストックしており、検算時にはよく参照しています。
※re:dashの利用に関しては「pairsでの活用例から学ぶre:dash導入のすゝめ」で紹介しています。
4.実行結果の妥当性確認
テーブルの仕様や普段のデータの傾向と比べて、クエリの実行結果に不審な点がないかを確認する検算方法です。検算方法というより心がけに近いかもしれません。
「実行結果の妥当性確認」が役立つ事例
「検算用クエリの作成が役立つ事例」の項で「DISTINCT」をつけ忘れたQuery 1-1により以下の誤った結果が導かれることは先に説明しました。
Result 4-1 (Result 1-1と同じ)
| 全ユーザー数 | 課金ユーザー数 | 全課金数 | 売上合計 |
|---|---|---|---|
| 4 | 3 | 3 | 9000 |
上記の結果の妥当性を確認すると以下の事実に気づけます。
課金ユーザー数 = 全課金数
purchasesテーブルは1人につき複数のデータが存在しうるテーブルなので、この時点で誤っているかもしれないと気付けるかと思います。今回はサンプルデータ数が少ないですが、何千・何万とデータ数があればより明確ではないでしょうか。
実行結果の妥当性確認をすると、ほとんどの場合どの検算方法よりも素早くクエリの不正に気付くことができます。そういう意味ではクエリを作成した際まず最初に行うべきでしょう。
ただしテーブルの仕様や、普段のデータの傾向の習熟度によって妥当性の確認時に得られる情報量は大きく変わります。今回の事例では「purchasesテーブルは1人につき複数のデータが存在しうる」という仕様を把握していなければ、クエリの誤りの可能性に気づけません。あるいはテーブルの仕様を知らなくても普段から課金ユーザー数の数字を把握していれば、上記の誤りに気づけると思います。データ分析の精度を高めるために、ぜひ日頃からテーブルの仕様やデータの傾向をキャッチアップしてみてください。
最後に
本記事では4つの検算方法を紹介しました。どれを用いるのが正解ということはなく、データ分析の性質に合わせて検算方法は使い分けたり組み合わせたりすることが重要だと考えています。
例えば、メール配信のために特定のユーザーのメールアドレスを取得したい場合は「2.ローデータの確認」をする手法を取り、施策検討時のデータ抽出やサービス上の問題検知のための分析の場合には「4.実行結果の妥当性確認」をした上で、「1.検算用クエリの作成」をすることで誤りを未然に防ぐことができます。
適切な検算方法が選択できているかを確認し合う意味で、弊社Analyzeチームではクエリのレビューはもちろん、検算方法のレビューをデータ分析者同士で行っています。データ分析の精度が高まるのでオススメです。
明日の記事は 三津澤さん の「text/template概論」になります！
エウレカでは、一緒に働いていただける方を絶賛募集中です。募集中の職種はこちらからご確認ください！皆様のエントリーをお待ちしております！
Recommend
2年間オンプレを運用してきた人が、2ヶ月AWSの運用をした所感
2016年はAndroidにチャレンジ！これから始めるAndroid開発ファーストステップ
- 2015.12.23
- Tech
- by Koichi Kukino
