こんにちはサービス開発部のエンジニアの鈴木達矢です。
最近サービス開発においてエンジニアだけでなくディレクタがSQLを書く機会＊やBI Tool*1を元に意思決定する機会が増えてきました。クックパッドでもモバイルアプリに新機能を足すなどの各施策前後のKPI比較や施策後の経過を見るためにディレクタやエンジニアがクエリを書いてRedashを用いてグラフ化する光景が日常化しました。
モチベーション
この記事に書かれている内容は統計学を学んでいればごく入り口にあたる初歩的なことです。しかしながら、その初歩的な統計を学んでいない人にとっては、ビジュアライズされたデータからその特性をひと目で即座に把握できないことがあったりします。そのような状態ではグラフ付きの資料をゆっくり読む分には良いのですが、例えばミーティングやカンファレンスなどでグラフの意図をすぐに理解して意見を述べるのは難しいです。また、自分がグラフを起こす側である場合でも、特定のデータに対してどのような集計をしてグラフ化すれば良いのか検討するのに時間を要してしまいます。
そこで今日は既にウィンドウ関数を使ったことがある方を対象にクエリを書いてRedashでグラフ化するまでの間に知っていると便利なコツをご紹介しようと思います。数式は用いずそれぞれの基礎知識の用途や特性について説明しますが、あくまで導入として捉えていただき、実用に際しては数式を理解していることをお薦めします。
クエリのちょっとしたコツ
突然ですが、ウィンドウ関数は使っていますか？ウィンドウ関数を利用すると行動ログを利用して新機能開発前の意思決定や開発後の評価が簡単に行なえますね。ここでは背景にある統計学的な基礎知識を紹介しつつRedshiftのウィンドウ関数を例にとってコツを紹介しようと思います。
AVG() - 平均値を取得する関数
代表値（データ値をデータ集合との関係で表した値）の一つ。データ値をデータ数で割った値を取得します。
使用例
Redshiftのリファレンスの例では販売IDごとに販売数(qty)の移動平均（この場合は初日からの販売数(qty)の累計/販売ID数(salesid)）を算出しています。読者がウィンドウ関数の基本的概念や構文を理解している前提としてここでは解説はしません。
select salesid, dateid, sellerid, qty, avg(qty) over (order by dateid, salesid rows unbounded preceding) as avg from winsales order by 2,1; salesid | dateid | sellerid | qty | avg ---------+------------+----------+-----+----- 30001 | 2003-08-02 | 3 | 10 | 10 10001 | 2003-12-24 | 1 | 10 | 10 10005 | 2003-12-24 | 1 | 30 | 16 40001 | 2004-01-09 | 4 | 40 | 22 10006 | 2004-01-18 | 1 | 10 | 20 20001 | 2004-02-12 | 2 | 20 | 20 40005 | 2004-02-12 | 4 | 10 | 18 20002 | 2004-02-16 | 2 | 20 | 18 30003 | 2004-04-18 | 3 | 15 | 18 30004 | 2004-04-18 | 3 | 20 | 18 30007 | 2004-09-07 | 3 | 30 | 19 (11 rows)
このようにソートした状態の以前のレコードに対して平均を出せるのがウィンドウ関数の便利なところですね。
代表値を何にするのか？
私たちは普段何気なく平均を使いますが、いつでも平均を使えるのでしょうか？例えば、ある会社Aの求人で平均年収７００万円で、もう一方の会社Bの求人では平均年収が６００万円だったとします。あなたがそれらの求人を見ているとして、平均年収だけでAを選ぶべきでしょうか？
蓋を開けて個別のお給料を見てみます
会社A 500 + 500 + 550 + 550 + 1400 = 7500
会社B 600 + 600 + 500 + 700 + 600 = 7000
会社Aは一人の年収が１４００万円で残りの人は５００万円台です。Aに入社したとしてお給料が５００万円台になる確率が高いです。平均値には次ような特徴があります。
- データの中に大きく外れた値があると信頼度が下がる
- 平均 x データ個数 = 合計 であることが保証されている
平均を読み解く側も、利用する側もこれらの特徴に注意する必要があります。では、代表値の他の選択肢にはどのようなものがあるのでしょうか？
MEDIAN() - 中央値を取得する関数
中央値：データを小さい順に並べた時にデータ数の二等分する（中央に位置する）データの値
先の給与の例で言うと中央値はそれぞれ会社Aで５５０万円、会社Bで６００万円になります。
中央値にはこのような特徴があります。 - データの中に大きく外れた値があっても信頼度が下がりづらい
使用例
Redshiftのリファレンスの例では販売者ID(sellerid)ごとに販売数の中央値を算出しています。
＊.sellerid:3のデータはデータが偶数なので中央の１５と２０を足して割った１７．５が中央値です
select sellerid, qty, median(qty) over (partition by sellerid) from winsales order by sellerid; sellerid qty median --------------------------- 1 10 10.0 1 10 10.0 1 30 10.0 2 20 20.0 2 20 20.0 3 10 17.5 3 15 17.5 3 20 17.5 3 30 17.5 4 10 25.0 4 40 25.0
NTILE() - データを順位に応じて集団に分ける
さてここまで代表値を扱ってきましたが、NTILEはあるデータが全体のどの分布に入っているのかを大まかに把握するためのものです。データの分布をN個の集団に分けた時に、あるデータの所属集団を見つける時に使います。
例えば自分のマラソンのタイムがトップ集団に入っているか確認したいときなどに使うことができます。
使用例
Redshiftのリファレンスではハムレットという同じ演目に支払われたティケット料金を４つのグループ（ntile(4)）に分類しています。
select eventname, caldate, pricepaid, ntile(4) over(order by pricepaid desc) from sales, event, date where sales.eventid=event.eventid and event.dateid=date.dateid and eventname='Hamlet' and caldate='2008-08-26' order by 4; eventname | caldate | pricepaid | ntile -----------+------------+-----------+------- Hamlet | 2008-08-26 | 1883.00 | 1 Hamlet | 2008-08-26 | 1065.00 | 1 Hamlet | 2008-08-26 | 589.00 | 1 Hamlet | 2008-08-26 | 530.00 | 1 Hamlet | 2008-08-26 | 472.00 | 1 Hamlet | 2008-08-26 | 460.00 | 2 Hamlet | 2008-08-26 | 355.00 | 2 Hamlet | 2008-08-26 | 334.00 | 2 Hamlet | 2008-08-26 | 296.00 | 2 Hamlet | 2008-08-26 | 230.00 | 3 Hamlet | 2008-08-26 | 216.00 | 3 Hamlet | 2008-08-26 | 212.00 | 3 Hamlet | 2008-08-26 | 106.00 | 3 Hamlet | 2008-08-26 | 100.00 | 4 Hamlet | 2008-08-26 | 94.00 | 4 Hamlet | 2008-08-26 | 53.00 | 4 Hamlet | 2008-08-26 | 25.00 | 4 (17 rows)
他にも統計のごく入り口の知識さえあれば有効に使える関数がRedshiftには元から用意されています。 ウィンドウ関数
ここまででSQLの作成に役立つ知識について解説しました。
グラフ
ここからはRedashを例に表現したいグラフを作るのに役立つ知識について解説します。サービス開発の際には線グラフ、棒グラフ（ヒストグラム）、箱ひげ図、散布図などがよく使われます。ここではこれらに絞ってデータの種類に応じてグラフにする方法を解説します。
グラフの選び方
あるデータの集合があるとき、それらをどのように並べるとより直感的に伝わりやすいのでしょうか？
例えばある施策によって利用デバイスごとのページの回遊が増えたかを確認したいとします。
- A：一回の訪問で訪れるページ数をデバイスごとにグラフに表したい
- 施策：ページ間の回遊を増やす新機能を追加した
- B ：一回の訪問で訪れるページ数をデバイスごとに施策前後で比較したいので時間的経過と共にグラフに表したい
Aであればヒストグラムの一つの棒をデバイスの種類として、縦軸を１回の訪問で訪れるページ数にすることで表現できます。もしくは円グラフでデバイスごとに割合を表すと分かりやすいでしょうか。
ではBの施策後の時間的経過を把握しやすく表現したいときはどうすればよいでしょうか？例えばヒストグラムの一本の棒をデバイスごとの訪問数で色分けして、その一本を特定の期間として横軸で時間経過を表します。
このように表したいデータの内容や特性に応じてグラフやその先の表現方法（ここでは色分けのこと）を選ぶとより分かりやすいグラフを作ることができるようになります。
以下はRedashのVisualisation Editorでの作業になります。
ヒストグラム
あるグループを一つの棒に表しグループごとの分布を比較するのに適しています。
例. あるレシピサイトを訪れる人をX軸で日にちでY軸で訪問回数で表す
Redash
Visualization Type: Chart、Chart Type: Barを選択します。
レシピサイトを訪れる人数は土曜日と月曜日に多いように見えます。一見なんの規則性も無いように見えます。
Group by
General tabのGroup byでは表示分けをする集団を分けることができます。 この例の場合は性別で分けてみます。
Stacking
General tabのStackingでは１つの棒にグループを上乗せ（スタックする）ことができます この例の場合は性別を上乗せしてみます。
- enabled 性別を色分けする。棒の高さは訪問者数の合計を表すので曜日によって異なる
- percent 性別を色分けする。棒の高さは１日の１００％を表すのですべての曜日で同じ
この例の場合は日にちごとの合計数が知りたいのでenabledを選択しました。
これでも割合は分からなくはないですががグラフを分けると山が見えてきます。
- 女性
- 男性
女性は週末にかけて山があるのに対して、男性はなぜか３月１３日月曜日に山があるのがわかります。このレシピサイトの男性の訪問数が平日に多いのは珍しいことです。どうやら３月１４日のホワイトデーのお返しを作っている人が多い日だったということがわかりました。
先述のような性質の異なる分布を一つに表している状態を多峰性の分布といいます。このようにビジュアライズ後に規則性が分かりづらかったグラフでも分解することで分かりやすくなることが有ります。クエリにおいてはなるべく集計前の状態をRedashに渡して、後々Redashのグラフ機能に集計や表示軸を設定するのを任せるという方法を取ると編集がしやすいです。
箱ひげ図
与えられたデータを小さい順に並べ、データ数を最小値、上位７５％、中央値、上位２５％、最大値で区切られる集合に４等分します。そのときの中央値の２つの集合を箱で表し、両端の集合はそれぞれ線（ヒゲ）で表します。データ数で等分するので、ヒゲや箱の大きさが小さいほどその集合のデータ密度が高いという事になります。この様に箱ひげ図では一つの箱ひげでヒストグラムで行っていた様なデータの分布具合を大まかに表現できます。
箱ひげ図について詳しくはこちらを参考にしてください
Redash
Visualization Type: Boxplot
散布図
縦軸、横軸に２つの分布の量や大きさ等をそれぞれ対応させ、データを点で表した図。２つの分布の相関を調べるために使います。
Redash
Visualization Type: Chart, Chart Type: Scatter
疑似相関に注意
ある要因がXの分布の理由でありYの分布の理由でもあるとき、XとYに因果関係がある様に見えること。例えば冷やし中華がよく売れるときにはビールも良く売れるお店があったとして、冷やし中華がよく売れる原因は冷やし中華ではなくて暑かったからといった場合です。散布図で示せるのは相関関係であり因果関係ではないのでデータから意思決定をする際は注意しましょう。
その他
ここでは取り上げられませんでしたがその他にも様々なビジュアライゼイションがRedashには用意されています。
- Pivot Table
ExcelやGoogle Spread Sheetで使ったことがある人には馴染み深いと思いますが、クエリの結果をクロス集計したものを表にします。
- Word Cloud
クエリの結果に出現する単語数を頻度に応じて大きくワードクラウドに表示します。検索によく使われるキーワードなどを表示するのに使うと便利そうです。
- Counter
クエリ結果の特定のカラム値を表示します。単一の数値をKPIとして常にダッシュボードに表示しておきたいときなどに便利そうです。
まとめ
この様にデータの特性やグラフの特性を知っていれば自分が作りたい資料を作る力や資料を読み解く力はずっと良くなります。と同時に、日常生活においてもニュースで提示される資料を疑う力もおまけで付いてきます。いきなり統計学を学ぶとなると初めての人にはステップが大きいですが、知っているとすぐに役に立つことから学習とリターンのサイクルを小さく回してちょっとずつグラフへの理解を深めてみてはいかがでしょうか？
*1:ビジネスインテリジェンス・ツールとは企業活動に伴うビッグデータを収集して分析するためのツールです
