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

Treasure Data Analytics

本シリーズではデータ分析を以下の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」の第5回目です。

1. Making Mini Cubes 

f:id:doryokujin:20140201153106p:plain

 

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

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

さて，まず先に分布を定義しておきましょう。

定義

パターン5 における < Count(1) | Seg(m1) > ，およびパターン6 における< Count(1) | Seg(m1), Seg(m2) > をそれぞれ「m1 (, m2) における頻度分布（離散）」とよび，それぞれDis<m1>, Dis<m1,m2> と書く。わかりやすく「m1 (,m2) 別の分布」と表現しても良い。また，順序付け可能な dim1, dim2 を含む< Count(1) | Seg(m1), dim1 >，< Count(1) | dim1,  dim2 > も分布と定義する。

--

順序づけ可能な dim1 とは，例えばレベルやアイテム保有数などのディメンジョンにもなりうりますが，順序づけが可能で平均を求めることに意味のあるものを言います。

分布の概念は重要で特に平均を求める集計では，データ数が少なかったりばらつきが大きいことによって意味のある値として使えない事が少なくありません。そこで，分布をみることによってどの領域に多く集中（頻度が高い）しているのかを可視化によって捉える事ができます。

パターン5：< m1 | Seg(m2) >
< Count(1) | Seg(m1) > クエリパターン

こちらのパターンはメジャーの集計方法を頻度集計に限定したものです。

SELECT ceil( #measure1/width )*width AS x, COUNT(1) AS cnt

FROM

(

  SELECT #measure1, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure1 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

GROUP BY ceil( #measure1/width )*width

ORDER BY x

 

基本例：中古車の価格別分布

SELECT ceil(used_price/width)*width AS x, COUNT(1) AS cnt

FROM

(

  SELECT used_price, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( used_price )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t2

ON t1.one = t2.one

GROUP BY ceil(used_price/width)*width

ORDER BY x

f:id:doryokujin:20140201150135p:plain

中古車の価格別分布です。30〜40万台をピークになだらかに減少しています。500万円以上のものは500万円のセグメントに集約しましたが，500万円以上の車も4281件エントリーされています。

< m1 | Seg(m2) > クエリパターン

より一般的なこちらのパターンを見ていきます。

SELECT ceil( #measure2/width )*width AS x,

  SUM( #measure1 ), COUNT(1) AS cnt, AVG( #measure1 ) AS avg

FROM

(

  SELECT #measure1, #measure2, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure2 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

GROUP BY ceil( #measure2/width )*width

ORDER BY x 

基本例：中古車価格別，平均走行距離

SELECT ceil( used_price/width )*width AS x,

  SUM( odd_km ), COUNT(1) AS cnt, AVG( odd_km ) AS avg

FROM

(

  SELECT  odd_km,  used_price, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( used_price )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t2

ON t1.one = t2.one

GROUP BY ceil( used_price/width )*width

ORDER BY x

f:id:doryokujin:20140201154307p:plain今度は走行距離別の「頻度」ではなく「平均走行距離」を計算しました。500万円以上の車両はまとめて計算し直しています（平均の平均は平均ではありませんので）。

価格帯が安いものほど過走行気味ですが，120万円を越える当たりからは平均5万km前後で推移しているようです。

パターン6：< m1 | Seg(m2), Seg(m3) >
< Count(1) | Seg(m1), Seg(m2) > クエリパターン

SELECT ceil( #measure1/t2.width)*t2.width AS x, ceil( #measure2/t3.width)*t3.width AS y, COUNT(1) AS cnt

FROM

(

  SELECT #measure1, #measure2, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure1 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure2 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t3

ON t1.one = t3.one

GROUP BY ceil( #measure1/t2.width)*t2.width, ceil(#measure2/t3.width)*t3.width

ORDER BY x, y

基本例：中古車価格別，平均走行距離別，頻度分布

SELECT ceil(used_price/t2.width)*t2.width AS x, ceil(odd_km/t3.width)*t3.width AS y, COUNT(1) AS cnt

FROM

(

  SELECT used_price, odd_km, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

  AND 0 < odd_km

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( used_price )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < used_price

) t2

ON t1.one = t2.one

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( odd_km )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 < odd_km

) t3

ON t1.one = t3.one

GROUP BY ceil(used_price/t2.width)*t2.width, ceil(odd_km/t3.width)*t3.width

ORDER BY x, y

f:id:doryokujin:20140201161236p:plain

中古車価格300万以上，走行距離3万km以上を無視して描いた2次元分布図。70万〜130万円の価格帯で低走行距離の車が多くあつまっているのがわかります。

< m1 | Seg(m2), Seg(m3) > クエリパターン

自明なので省略します。

パターン7：< m1 | dim1, Seg(m2) >
< m1 | dim1, Seg(m2) > クエリパターン

SELECT ceil( #measure1/width )*width AS x, %dim1,

  SUM( #measure1 ), COUNT(1) AS cnt, AVG( #measure1 ) AS avg

FROM

(

  SELECT #measure1, 1 AS one

  FROM table

  WHERE condition

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( #measure1 )))-1) AS width, 1 AS one

  FROM table

  WHERE condition

) t2

ON t1.one = t2.one

GROUP BY ceil( #measure1/width )*width, %dim1

ORDER BY x, %dim1

基本例：走行距離，年式別平均中古車価格

dim1 として年式，Seg(m2) として走行距離のセグメントで m1: 中古車価格の平均を見ていきましょう。

SELECT ceil(odd_km/width)*width AS x, year,

   SUM( used_price ), COUNT(1) AS cnt, AVG( used_price ) AS avg

FROM

(

  SELECT used_price, odd_km, year, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 <= odd_km AND 0 < year

) t1

JOIN

(

  SELECT POW(10,floor(LOG10(MAX( odd_km )))-1) AS width, 1 AS one

  FROM usedcar_cube_without_catalog

  WHERE 0 <= odd_km

) t2

ON t1.one = t2.one

WHERE 0 <= used_price

GROUP BY ceil(odd_km/width)*width, year

ORDER BY x, year desc

f:id:doryokujin:20140203115320p:plain

パターン8：< m1 | dim1, dim2 >

こちらも省略します。