Treasure Data Platform で始めるデータ分析入門 〜6. Data Processing Design 〜 Part.3

本シリーズではデータ分析を以下の7つのレイヤーに分解し,各々について解説していくものとします。(Slide Shareの資料は常時更新されます。)

  1. Data Collection
  2. Data Storage
  3. Data Management
  4. Data Processing
  5. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  6. Data Visualization Treasure Viewer, MetricInsights, Tableau
  7. Data Visualization Patterns Part.1 Part.2 Part.3

本日は「5. Data Processing Design」の第3回目です。

1. Making Big Cube(振り返り)

f:id:doryokujin:20140129131303p:plain

 

f:id:doryokujin:20140131165457p:plain

 

前回の Big Cube 作成ステップによって,上図のような1つの統合テーブル("usedcar_cubu_without_catalog")を作成しました。次のステップではこのテーブルに対して処理を行っていきます。

2. Making Mini Cubes

f:id:doryokujin:20140131115237p:plain

 

Big Cube から Mini Cubes を作成していきます。作成される Mini Cube 達はデータサイズが Source Tables (元データ)と比べて概ね 1/100 以下になっているため,保存先としては Treasure Cloud Storage 以外にもローカルファイル,MySQL,Memory 等の選択肢がありますが,ここでは Treasure Cloud Storage に引き続き保存しておくものとします。

さて,ここでの目標はこのステップをできるだけパターン化し,半自動的に行えるようにすることです。そのために以下のルールを適用します。

Mini Cube 作成ルール

  • メジャーカラムは 1 つだけ選択する。
  • ディメンジョンカラムは最大 2 つまで選択する。
  • ディメンジョンカラムの中で階層構造を持つものは,その上位階層を全部含めたものを1つのディメンジョンカラムとみなす。

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 cnt
FROM 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        |

f:id:doryokujin:20140201103824p:plain

基本例:メーカー名ごとの平均中古車価格

唯一の注意点はメーカー名の上階層に生産国名があることです。以下が具体的なクエリになります。

SELECT country_name, name, SUM(used_price) AS sum_price, COUNT(1) AS cnt_price, AVG(used_price) AS avg_price

FROM usedcar_cube_without_catalog

GROUP BY country_name, name

ORDER BY country_name, avg_price desc

f:id:doryokujin:20140131171748p:plain

 

Treasure Manegement Console からの実行キャプチャ↑,およびリザルト↓。

 

f:id:doryokujin:20140131171902p:plain

 

せっかくなのでいくつか可視化してみましょう。

f:id:doryokujin:20140131172623p:plain

平均車両価格が高いメーカー TOP20。上位には高級車として有名なメーカーが勢揃いですね。

f:id:doryokujin:20140131173409p:plain

↑ そもそもメーカー数がいくらになるのかは,上位階層の「生産国」登場頻度を数えればすぐに見れますね。さらに 「生産国」×「メーカー」のピボットテーブルを作って平均車両価格を見れば面白そうです。こちらは次回のクロス集計章で紹介していきます。

パターン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  |

 

f:id:doryokujin:20140131180751p:plain

上図は11月の ARPU, ARPPU を日単位で表したものです。 

 

次回は < m | d1, d2 >,< m | time, d2 > パターンとその他: < m1 | Seg(m2) > を紹介します。内容は概ね想像が付くと思います。