Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.3
本シリーズではデータ分析を以下の7つのレイヤーに分解し，各々について解説していくものとします。（Slide Shareの資料は常時更新されます。）
- Data Collection
- Data Storage
- Data Management
- Data Processing
- Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
- Data Visualization Treasure Viewer, MetricInsights, Tableau
- Data Visualization Patterns Part.1 Part.2 Part.3
本日は「5. Data Processing Design」の第3回目です。
1. Making Big Cube（振り返り）
前回の Big Cube 作成ステップによって，上図のような1つの統合テーブル（"usedcar_cubu_without_catalog"）を作成しました。次のステップではこのテーブルに対して処理を行っていきます。
2. Making Mini Cubes
Big Cube から Mini Cubes を作成していきます。作成される Mini Cube 達はデータサイズが Source Tables （元データ）と比べて概ね 1/100 以下になっているため，保存先としては Treasure Cloud Storage 以外にもローカルファイル，MySQL，Memory 等の選択肢がありますが，ここでは Treasure Cloud Storage に引き続き保存しておくものとします。
さて，ここでの目標はこのステップをできるだけパターン化し，半自動的に行えるようにすることです。そのために以下のルールを適用します。
Mini Cube 作成ルール
- メジャーカラムは 1 つだけ選択する。
- ディメンジョンカラムは最大 2 つまで選択する。
- ディメンジョンカラムの中で階層構造を持つものは，その上位階層を全部含めたものを１つのディメンジョンカラムとみなす。
3番目の意味は，例えば時間カラムであれば（年 > 月 > 日 > 時）の4つのカラムは 1 つのTime ディメンジョンカラムとしてひとくくりし，例えば商品カラムであれば（大カテゴリ > 中カテゴリ > 小カテゴリ > 商品名）を商品ディメンジョンカラムとしてひとくくりにして扱うということです。
定義
< m | dim1, dim2 > を「ディメンジョン dim1, dim2 をセグメントとしたメジャー m に関する集計」と呼ぶ。
上記の定義によって，基本的な集計パターンの表記が可能となります。それではここから Car Sensor のデータと共に具体的に見ていくことにします。
パターン1：< m | dim1 >
< m | dim1 > クエリパターン
SELECT SUM( #measure ) AS sum, COUNT( 1 ) AS cnt
FROM table
WHERE condition
GROUP BY %dim1
ORDER BY cnt
もっとも基本的なパターンは 1 ディメンジョン，1 メジャーパターンです。
基本例：年式ごとの中古車登録数
年式 "year" をディメンジョンに，1 をメジャーにした最もシンプルな集計です。
SELECT year, COUNT(1) AS cntFROM usedcar_cube_without_catalog
GROUP BY year
ORDER BY cnt
Result :
+------+----------+
| year | cnt_year |
+------+----------+
| 0 | 96 |
| 1926 | 1 |
| 1928 | 2 |
| 1929 | 1 |
| 1930 | 1 |
| 1931 | 1 |
基本例：メーカー名ごとの平均中古車価格
唯一の注意点はメーカー名の上階層に生産国名があることです。以下が具体的なクエリになります。
SELECT country_name, name, SUM(used_price) AS sum_price, COUNT(1) AS cnt_price, AVG(used_price) AS avg_priceFROM usedcar_cube_without_catalog
GROUP BY country_name, name
ORDER BY country_name, avg_price desc
Treasure Manegement Console からの実行キャプチャ↑，およびリザルト↓。
せっかくなのでいくつか可視化してみましょう。
平均車両価格が高いメーカー TOP20。上位には高級車として有名なメーカーが勢揃いですね。
↑ そもそもメーカー数がいくらになるのかは，上位階層の「生産国」登場頻度を数えればすぐに見れますね。さらに 「生産国」×「メーカー」のピボットテーブルを作って平均車両価格を見れば面白そうです。こちらは次回のクロス集計章で紹介していきます。
パターン2： < m | time >
特に「dim1 を time ディメンジョンにした集計」は最もシンプルかつメジャーな集計です。残念ながら Car Sensor データでは意味のあるtimeカラムを持っていませんので，他データで集計したものを図示しています。
< m | time > クエリパターン
SELECT %year, %month, %day, SUM( #measure ), COUNT( 1 )
FROM table
WHERE condition
GROUP BY %year, %month, %day
ORDER BY %year, %month, %day
基本例：デイリーの商品売上総額
SELECT
TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS y
TD_TIME_FORMAT(time, 'MM', 'PDT') AS m,
TD_TIME_FORMAT(time, 'dd', 'PDT') AS d,
SUM( price * count ) AS sum
)
FROM pay
WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')
GROUP BY
TD_TIME_FORMAT(time, 'yyyy', 'PDT'),
TD_TIME_FORMAT(time, 'MM', 'PDT'),
TD_TIME_FORMAT(time, 'dd', 'PDT')
ORDER BY y desc, m desc, d asc
結果は以下の通りです。
Result :
+------+----+----+-----------+
| y | m | d | sum |
+------+----+----+-----------+
| 2012 | 12 | 01 | 1647475.0 |
| 2012 | 12 | 02 | 1110545.0 |
| 2012 | 12 | 03 | 1195025.0 |
| 2012 | 12 | 04 | 1916040.0 |
...
課金テーブルを pay としています。出力テーブルはディメンジョンと集計値で構成されます。
応用例：ARPPU
SELECT
TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS year,
TD_TIME_FORMAT(time, 'MM', 'PDT') AS month,
TD_TIME_FORMAT(time, 'dd', 'PDT') AS day,
COUNT(distinct uid ) AS uu,
CAST( SUM( price * count ) AS BIGINT) AS sum
FROM pay
WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')
GROUP BY
TD_TIME_FORMAT(time, 'yyyy', 'PDT'),
TD_TIME_FORMAT(time, 'MM', 'PDT'),
TD_TIME_FORMAT(time, 'dd', 'PDT')
ORDER BY year desc, month desc ,day asc
応用例：ARPU
SELECT
t1.year AS year,
t1.month AS month,
t1.day AS day,
CAST( t2.sum/t1.uu AS BIGINT ) AS arpu,
CAST( t2.sum/t2.uu AS BIGINT ) AS arppu
FROM
(
SELECT
TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS year,
TD_TIME_FORMAT(time, 'MM', 'PDT') AS month,
TD_TIME_FORMAT(time, 'dd', 'PDT') AS day,
COUNT( distinct uid ) AS uu
FROM login
WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')
GROUP BY
TD_TIME_FORMAT(time, 'yyyy', 'PDT'),
TD_TIME_FORMAT(time, 'MM', 'PDT'),
TD_TIME_FORMAT(time, 'dd', 'PDT')
) t1
LEFT OUTER JOIN
(
SELECT
TD_TIME_FORMAT(time, 'yyyy', 'PDT') AS year,
TD_TIME_FORMAT(time, 'MM', 'PDT') AS month,
TD_TIME_FORMAT(time, 'dd', 'PDT') AS day,
COUNT(distinct uid ) AS uu,
CAST( SUM( price * count ) AS BIGINT) AS sum
FROM pay
WHERE TD_TIME_RANGE(time, '2012-01-01 PDT', '2013-01-01 PDT', 'PDT')
GROUP BY
TD_TIME_FORMAT(time, 'yyyy', 'PDT'),
TD_TIME_FORMAT(time, 'MM', 'PDT'),
TD_TIME_FORMAT(time, 'dd', 'PDT')
) t2
ON
(t1.year=t2.year AND t1.month=t2.month AND t1.day=t2.day)
ORDER BY year desc, month desc ,day asc
ユニークログインユーザー数の計算が t1 で，課金総額とユニーク課金ユーザー数の計算が t2 で行われています。
結果は以下の通りです。
+------+-------+-----+------+-------+
| year | month | day | arpu | arppu |
+------+-------+-----+------+-------+
| 2012 | 12 | 01 | 106 | 2294 |
| 2012 | 12 | 02 | 71 | 1891 |
| 2012 | 12 | 03 | 77 | 1890 |
| 2012 | 12 | 04 | 123 | 2717 |
上図は11月の ARPU, ARPPU を日単位で表したものです。
次回は < m | d1, d2 >，< m | time, d2 > パターンとその他： < m1 | Seg(m2) > を紹介します。内容は概ね想像が付くと思います。
