トレジャーデータ(Treasure Data)ブログ

トレジャーデータ(Treasure Data)ブログです。

本サイトは移転しました。新サイトにリダイレクトします。

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

本記事は移転しました。新サイトにリダイレクトします。

*トレジャーデータはデータ収集、保存、分析のためのエンドツーエンドでサポートされたクラウドサービスです。

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

  1. Introduction
  2. Data Collection
  3. Data Storage
  4. Data Management
  5. Data Processing
  6. Data Processing Design Part.1 Part.2 Part.3 Part.4 Part.5 Part.6
  7. Data Visualization Treasure ViewerMetricInsightsTableau
  8. Data Visualization Patterns Part.1 Part.2 Part.3

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

1. Making Mini Cubes 

 

f:id:doryokujin:20140201153106p:plain

Car Sensor データを元に上記のテーブル "usedcar_cube_without_catalog" を作成しました。

Mini Cube 作成ルール
  • メジャーカラムは 1 つだけ選択する。
  • ディメンジョンカラムは最大 2 つまで選択する。
  • ディメンジョンカラムの中で階層構造を持つものは,その上位階層を全部含めたものを1つのディメンジョンカラムとみなす。
パターン3:< m | dim1, dim2 >
< m | dim1, dim2 > クエリパターン

SELECT SUM( #measure ) AS sum, COUNT( 1 ) AS cnt

FROM table

WHERE condition

GROUP BY %dim1, %dim2

ORDER BY  cnt

パターン1 にディメンジョンが 1 つ増えたケースです。

基本例:トヨタ車のモデル (dim1),年式 (dim2) ごとの平均車両価格が安い TOP20

SELECT name, model, year,

  SUM(CAST(used_price AS INT)) AS sum_price,

  COUNT(1) AS cnt_price,

  AVG(CAST(used_price AS INT) )AS avg_price

FROM usedcar_cube_without_catalog

WHERE name='トヨタ' AND 0 < used_price

GROUP BY name, model, year

ORDER BY avg_price asc

LIMIT 20

モデル "model" カラムはメーカー名を上位階層に持ちます。生産国もさらにその上の上位階層ですが,メーカー名をトヨタに絞り込んでいるので生産国のカラムは必要無いことになります。

Result     :

+------+-----------+------+-----------+-----------+--------------------+

| name | model     | year | sum_price | cnt_price | avg_price          |

+------+-----------+------+-----------+-----------+--------------------+

| トヨタ  | マークIIセダン  | 1997 | 50000     | 1         | 50000.0            |

| トヨタ  | カローラII    | 1997 | 50000     | 1         | 50000.0            |

| トヨタ  | エスティマエミーナ | 1995 | 70000     | 1         | 70000.0            |

| トヨタ  | カリーナED    | 1994 | 79000     | 1         | 79000.0            |

| トヨタ  | カルディナ     | 1995 | 80000     | 1         | 80000.0            |

| トヨタ  | ビスタ       | 1994 | 80000     | 1         | 80000.0            |

| トヨタ  | カローラII    | 1994 | 84000     | 1         | 84000.00 |

上記の結果は,トヨタ車の中で安価なモデルと年式を特定しています。できるだけ年式の高いモデルで安い車を見つける場合にはこのリスト20から探せば良いことになります。

f:id:doryokujin:20140201111859p:plain

ここで1つ注意点,セグメントを2つ設定するということはそれだけ出力のバリエーションが爆発的に増える一方で,ここの集計値は非常に少ない個数での値になってしまいます。上記の例では,該当するほとんどが個数1ですので,平均を求めたつもりが値を取り出しただけになってしまっています。

基本例:人気モデルの年式ごとの平均価格

先ほど,バリエーションが爆発的に増えると書きましたが,上位 n 件を必要とする場合はそれでもかまいませんが,他に有効な手段として条件文によってバリエーションを特定することです。次は

新車乗用車販売台数月別ランキング

の楽しいページから人気モデルをピックアップして,どのモデルのどの年式がお買い得かを特定しています。結果をcsvにはき出してみました。

モデル,年式,sum_price,cnt_price,avg_price

フィット,2001,30666000,162,189296.2962962963

フィット,2002,121997000,555,219814.4144144144

フィット,2003,111691000,414,270438.25665859564

フィット,2004,166223000,489,340620.9016393443

ノート,2005,177072000,466,380800.0

フィット,2005,158280000,406,389852.21674876846

フィット,2006,134833000,307,440630.71895424835

アクセラ,2004,9390000,21,447142.85714285716

アクセラ,2003,450000,1,450000.0

ノート,2006,113910000,251,453824.7011952191

...

 

今回は次々回で説明するクロス集計を行ってみる事にします。

 

f:id:doryokujin:20140201115434j:plain

 

今まで平均を求める際に SUM と COUNT を一緒に取ってきましたが,クロス集計した結果に対して平均の平均は適用できないからです。

f:id:doryokujin:20140201115828p:plain

ディメンジョンを2つ設定したことによって x-軸,y-軸 に各々を配置し,z-軸(セル)の値をバブルの大きさと色で表現することが可能になりました。どうやらアクセラが年式の割にはノートやフィットよりも値段が高いようです。

パターン4:< m | time, dim2 >
< m | time, dim2 > クエリパターン

SELECT %year, %month, %day, %dim2, SUM( #measure ), COUNT( 1 )

FROM table

WHERE condition

GROUP BY %year, %month, %day, %dim2

ORDER BY  %year, %month, %day

上記のパターンは説明をする必要も無いので省略しますね。

次回はこれら4パターン以外の特殊パターンを紹介します。分布とか,そこらへんです。