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

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

データサイエンティストのためのHiveQL分析クエリテンプレート その1

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

データサイエンティストのためのHiveQL分析クエリテンプレートシリーズ:

Treasure Dataでは,カラムナデータベースであるTreasure Cloud Storageに対してSQL ライクなHiveQLでデータの集計・分析が可能になっています。

本シリーズではHiveQLで完結できる集計・分析クエリのテンプレートを示すことによって大規模データの集計・分析を誰にでも実行できることを目指しています。どのような集計・分析を行ったら良いかわからない人でも,このテンプレートを使うだけである程度の集計結果を得ることが可能です。

ディメンジョン・メジャー

f:id:treasure-data:20140430125312p:plain

まずはじめにカラムタイプとして「ディメンジョン」と「メジャー」を,上記の Car Sensor オープンデータ項目をサンプルとして紹介します。

メジャー

メジャーは「中古車価格」や「オドメーター」など,その数値自身が集計の対象となるカラムです。これらのメジャーはセグメント毎に合計,平均,ユニーク数などが求められます。

ディメンジョン

ディメンジョンは「年式」や「メーカー」など,集計時にセグメント軸となるようなカラムを指します。一般には非数値型か順序付け不可能な数値型である事が多いです。もちろん,「中古車価格」のようなメジャーでも10万円〜20万円,20万円〜30万円…といったように区間をとればその区間をディメンジョンとして扱うことが可能です。このような例は後述する「分布」というテンプレートをご参照下さい。

A. < m | dim1, dim2 > パターン

定義

< m | dim1, dim2 > を「ディメンジョン dim1, dim2 をセグメントとしたメジャー m に関する集計」と呼ぶ。

パターン A-1:< m | dim1>

SELECT SUM(#m) AS sum [, COUNT(1) AS cnt, etc...]

FROM table

WHERE condition

GROUP BY %dim1

ORDER BY  cnt

もっとも基本的なパターンは上記のクエリテンプレートで記述される,1ディメンジョン,1メジャーパターンです。

<m | dim1>パターンの出力例は「dim1 = メーカー名」,「m = 中古車相場価格」として「中古車平均相場 = AVG(m)」を求めたものです。

f:id:treasure-data:20140430132412p:plain

パターン A-2:< m | time>

特に「dim1 = time ディメンジョンにした集計」は利用頻度の高い時系列集計となります。

SELECT %year, %month, %day, SUM(#m) AS sum [, COUNT(1) AS cnt, etc...]

FROM table

WHERE condition

GROUP BY %year, %month, %day

ORDER BY  %year, %month, %day

上記テンプレートは,BI 等からのドリルダウン操作を想定しておりますので %year, %month, %day の3つを指定していますが,'yyyy-MM-dd' といった文字列としての時間表記も可能です。

SELECT TD_TIME_FORMAT(time, 'yyyy-MM-dd','JST') AS d, SUM(#m) AS sum [, COUNT(1) AS cnt, etc...]

FROM table

WHERE condition

GROUP BY  TD_TIME_FORMAT(time, 'yyyy-MM-dd','JST') AS d

ORDER BY  date

デイリーのPV集計は「m=アクセス数」として「PV=SUM (m)」を求めたものです。

f:id:treasure-data:20140430134130p:plain

パターン A-3:< m | dim1, dim2 >

さらにディメンジョンを複数にすることによって多軸の集計が可能になります。

SELECT SUM(#m) AS sum, [COUNT( 1 ) AS cnt, etc...]

FROM table

WHERE condition

GROUP BY %dim1, %dim2

ORDER BY  cnt

例 

「dim1=モデル」,「dim2=年式」,「m=中古車価格」として「中古車平均相場=AVG(m)」とした集計です。

f:id:treasure-data:20140430135638p:plain

dim1, dim2は「x-軸: dim1」「y-軸: dim2」としたピボットテーブルで利用される事が多いです。

f:id:treasure-data:20140430140707p:plain

パターン A-4:< m | time, dim2 >

SELECT %year, %month, %day, %dim2, SUM(#m) [, COUNT(1) AS cnt, etc...]

FROM table

WHERE condition

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

ORDER BY  %year, %month, %day

こちらのパターンも自明ですので例は省略します。

本日はここまで。次回はメジャーをセグメントに据えたモデルなどを紹介していきます。

 

トレジャーデータに関するお問い合わせは  support@treasure-data.com まで。