知っていれば広がるグラフの見方とクエリの書き方のちょっとしたコツ

こんにちはサービス開発部のエンジニアの鈴木達矢です。

最近サービス開発においてエンジニアだけでなくディレクタがSQLを書く機会やBI Tool*1を元に意思決定する機会が増えてきました。クックパッドでもモバイルアプリに新機能を足すなどの各施策前後のKPI比較や施策後の経過を見るためにディレクタやエンジニアがクエリを書いてRedashを用いてグラフ化する光景が日常化しました。

モチベーション

この記事に書かれている内容は統計学を学んでいればごく入り口にあたる初歩的なことです。しかしながら、その初歩的な統計を学んでいない人にとっては、ビジュアライズされたデータからその特性をひと目で即座に把握できないことがあったりします。そのような状態ではグラフ付きの資料をゆっくり読む分には良いのですが、例えばミーティングやカンファレンスなどでグラフの意図をすぐに理解して意見を述べるのは難しいです。また、自分がグラフを起こす側である場合でも、特定のデータに対してどのような集計をしてグラフ化すれば良いのか検討するのに時間を要してしまいます。

そこで今日は既にウィンドウ関数を使ったことがある方を対象にクエリを書いてRedashでグラフ化するまでの間に知っていると便利なコツをご紹介しようと思います。数式は用いずそれぞれの基礎知識の用途や特性について説明しますが、あくまで導入として捉えていただき、実用に際しては数式を理解していることをお薦めします。

クエリのちょっとしたコツ

突然ですが、ウィンドウ関数は使っていますか?ウィンドウ関数を利用すると行動ログを利用して新機能開発前の意思決定や開発後の評価が簡単に行なえますね。ここでは背景にある統計学的な基礎知識を紹介しつつRedshiftのウィンドウ関数を例にとってコツを紹介しようと思います。

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の求人で平均年収700万円で、もう一方の会社Bの求人では平均年収が600万円だったとします。あなたがそれらの求人を見ているとして、平均年収だけでAを選ぶべきでしょうか?

蓋を開けて個別のお給料を見てみます

  • 会社A 500 + 500 + 550 + 550 + 1400 = 7500

  • 会社B 600 + 600 + 500 + 700 + 600 = 7000

会社Aは一人の年収が1400万円で残りの人は500万円台です。Aに入社したとしてお給料が500万円台になる確率が高いです。平均値には次ような特徴があります。

  • データの中に大きく外れた値があると信頼度が下がる
  • 平均 x データ個数 = 合計 であることが保証されている

平均を読み解く側も、利用する側もこれらの特徴に注意する必要があります。では、代表値の他の選択肢にはどのようなものがあるのでしょうか?

MEDIAN() - 中央値を取得する関数

中央値:データを小さい順に並べた時にデータ数の二等分する(中央に位置する)データの値

先の給与の例で言うと中央値はそれぞれ会社Aで550万円、会社Bで600万円になります。

中央値にはこのような特徴があります。 - データの中に大きく外れた値があっても信頼度が下がりづらい

使用例

Redshiftのリファレンスの例では販売者ID(sellerid)ごとに販売数の中央値を算出しています。

*.sellerid:3のデータはデータが偶数なので中央の15と20を足して割った17.5が中央値です

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のリファレンスではハムレットという同じ演目に支払われたティケット料金を4つのグループ(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であればヒストグラムの一つの棒をデバイスの種類として、縦軸を1回の訪問で訪れるページ数にすることで表現できます。もしくは円グラフでデバイスごとに割合を表すと分かりやすいでしょうか。

f:id:tatsuya-suzuki-cookpad:20170317095037p:plain f:id:tatsuya-suzuki-cookpad:20170317095055p:plain

ではBの施策後の時間的経過を把握しやすく表現したいときはどうすればよいでしょうか?例えばヒストグラムの一本の棒をデバイスごとの訪問数で色分けして、その一本を特定の期間として横軸で時間経過を表します。

f:id:tatsuya-suzuki-cookpad:20170317095122p:plain

このように表したいデータの内容や特性に応じてグラフやその先の表現方法(ここでは色分けのこと)を選ぶとより分かりやすいグラフを作ることができるようになります。

以下はRedashのVisualisation Editorでの作業になります。

ヒストグラム

あるグループを一つの棒に表しグループごとの分布を比較するのに適しています。

例. あるレシピサイトを訪れる人をX軸で日にちでY軸で訪問回数で表す

Redash

Visualization Type: Chart、Chart Type: Barを選択します。

f:id:tatsuya-suzuki-cookpad:20170317102242p:plain

レシピサイトを訪れる人数は土曜日と月曜日に多いように見えます。一見なんの規則性も無いように見えます。

Group by

General tabのGroup byでは表示分けをする集団を分けることができます。 この例の場合は性別で分けてみます。

Stacking

General tabのStackingでは1つの棒にグループを上乗せ(スタックする)ことができます この例の場合は性別を上乗せしてみます。

  • enabled 性別を色分けする。棒の高さは訪問者数の合計を表すので曜日によって異なる
  • percent 性別を色分けする。棒の高さは1日の100%を表すのですべての曜日で同じ

この例の場合は日にちごとの合計数が知りたいのでenabledを選択しました。 f:id:tatsuya-suzuki-cookpad:20170317095212p:plain

これでも割合は分からなくはないですががグラフを分けると山が見えてきます。

  • 女性

f:id:tatsuya-suzuki-cookpad:20170317095237p:plain

  • 男性

f:id:tatsuya-suzuki-cookpad:20170317095306p:plain

女性は週末にかけて山があるのに対して、男性はなぜか3月13日月曜日に山があるのがわかります。このレシピサイトの男性の訪問数が平日に多いのは珍しいことです。どうやら3月14日のホワイトデーのお返しを作っている人が多い日だったということがわかりました。

先述のような性質の異なる分布を一つに表している状態を多峰性の分布といいます。このようにビジュアライズ後に規則性が分かりづらかったグラフでも分解することで分かりやすくなることが有ります。クエリにおいてはなるべく集計前の状態をRedashに渡して、後々Redashのグラフ機能に集計や表示軸を設定するのを任せるという方法を取ると編集がしやすいです。

箱ひげ図

与えられたデータを小さい順に並べ、データ数を最小値、上位75%、中央値、上位25%、最大値で区切られる集合に4等分します。そのときの中央値の2つの集合を箱で表し、両端の集合はそれぞれ線(ヒゲ)で表します。データ数で等分するので、ヒゲや箱の大きさが小さいほどその集合のデータ密度が高いという事になります。この様に箱ひげ図では一つの箱ひげでヒストグラムで行っていた様なデータの分布具合を大まかに表現できます。

箱ひげ図について詳しくはこちらを参考にしてください

Redash

Visualization Type: Boxplot

f:id:tatsuya-suzuki-cookpad:20170317095816p:plain

散布図

縦軸、横軸に2つの分布の量や大きさ等をそれぞれ対応させ、データを点で表した図。2つの分布の相関を調べるために使います。

Redash

Visualization Type: Chart, Chart Type: Scatter

f:id:tatsuya-suzuki-cookpad:20170317095859p:plain

疑似相関に注意

ある要因がXの分布の理由でありYの分布の理由でもあるとき、XとYに因果関係がある様に見えること。例えば冷やし中華がよく売れるときにはビールも良く売れるお店があったとして、冷やし中華がよく売れる原因は冷やし中華ではなくて暑かったからといった場合です。散布図で示せるのは相関関係であり因果関係ではないのでデータから意思決定をする際は注意しましょう。

その他

ここでは取り上げられませんでしたがその他にも様々なビジュアライゼイションがRedashには用意されています。

  • Pivot Table

ExcelやGoogle Spread Sheetで使ったことがある人には馴染み深いと思いますが、クエリの結果をクロス集計したものを表にします。

  • Word Cloud

クエリの結果に出現する単語数を頻度に応じて大きくワードクラウドに表示します。検索によく使われるキーワードなどを表示するのに使うと便利そうです。

  • Counter

クエリ結果の特定のカラム値を表示します。単一の数値をKPIとして常にダッシュボードに表示しておきたいときなどに便利そうです。

まとめ

この様にデータの特性やグラフの特性を知っていれば自分が作りたい資料を作る力や資料を読み解く力はずっと良くなります。と同時に、日常生活においてもニュースで提示される資料を疑う力もおまけで付いてきます。いきなり統計学を学ぶとなると初めての人にはステップが大きいですが、知っているとすぐに役に立つことから学習とリターンのサイクルを小さく回してちょっとずつグラフへの理解を深めてみてはいかがでしょうか?

*1:ビジネスインテリジェンス・ツールとは企業活動に伴うビッグデータを収集して分析するためのツールです

/* */ @import "/css/theme/report/report.css"; /* */ /* */ body{ background-image: url('http://cdn-ak.f.st-hatena.com/images/fotolife/c/cookpadtech/20140527/20140527163350.png'); background-repeat: repeat-x; background-color:transparent; background-attachment: scroll; background-position: left top;} /* */ body{ border-top: 3px solid orange; color: #3c3c3c; font-family: 'Helvetica Neue', Helvetica, 'ヒラギノ角ゴ Pro W3', 'Hiragino Kaku Gothic Pro', Meiryo, Osaka, 'MS Pゴシック', sans-serif; line-height: 1.8; font-size: 16px; } a { text-decoration: underline; color: #693e1c; } a:hover { color: #80400e; text-decoration: underline; } .entry-title a{ color: rgb(176, 108, 28); cursor: auto; display: inline; font-family: 'Helvetica Neue', Helvetica, 'ヒラギノ角ゴ Pro W3', 'Hiragino Kaku Gothic Pro', Meiryo, Osaka, 'MS Pゴシック', sans-serif; font-size: 30px; font-weight: bold; height: auto; line-height: 40.5px; text-decoration: underline solid rgb(176, 108, 28); width: auto; line-height: 1.35; } .date a { color: #9b8b6c; font-size: 14px; text-decoration: none; font-weight: normal; } .urllist-title-link { font-size: 14px; } /* Recent Entries */ .recent-entries a{ color: #693e1c; } .recent-entries a:visited { color: #4d2200; text-decoration: none; } .hatena-module-recent-entries li { padding-bottom: 8px; border-bottom-width: 0px; } /*Widget*/ .hatena-module-body li { list-style-type: circle; } .hatena-module-body a{ text-decoration: none; } .hatena-module-body a:hover{ text-decoration: underline; } /* Widget name */ .hatena-module-title, .hatena-module-title a{ color: #b06c1c; margin-top: 20px; margin-bottom: 7px; } /* work frame*/ #container { width: 970px; text-align: center; margin: 0 auto; background: transparent; padding: 0 30px; } #wrapper { float: left; overflow: hidden; width: 660px; } #box2 { width: 240px; float: right; font-size: 14px; word-wrap: break-word; } /*#blog-title-inner{*/ /*margin-top: 3px;*/ /*height: 125px;*/ /*background-position: left 0px;*/ /*}*/ /*.header-image-only #blog-title-inner {*/ /*background-repeat: no-repeat;*/ /*position: relative;*/ /*height: 200px;*/ /*display: none;*/ /*}*/ /*#blog-title {*/ /*margin-top: 3px;*/ /*height: 125px;*/ /*background-image: url('http://cdn-ak.f.st-hatena.com/images/fotolife/c/cookpadtech/20140527/20140527172848.png');*/ /*background-repeat: no-repeat;*/ /*background-position: left 0px;*/ /*}*/