データサイエンティストのためのHiveQL分析クエリテンプレート その１
本記事は移転しました。新サイトにリダイレクトします。
*トレジャーデータはデータ収集、保存、分析のためのエンドツーエンドでサポートされたクラウドサービスです。
データサイエンティストのためのHiveQL分析クエリテンプレートシリーズ：
Treasure Dataでは，カラムナデータベースであるTreasure Cloud Storageに対してSQL ライクなHiveQLでデータの集計・分析が可能になっています。
本シリーズではHiveQLで完結できる集計・分析クエリのテンプレートを示すことによって大規模データの集計・分析を誰にでも実行できることを目指しています。どのような集計・分析を行ったら良いかわからない人でも，このテンプレートを使うだけである程度の集計結果を得ることが可能です。
ディメンジョン・メジャー
まずはじめにカラムタイプとして「ディメンジョン」と「メジャー」を，上記の 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)」を求めたものです。
パターン 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)」を求めたものです。
パターン 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)」とした集計です。
dim1, dim2は「x-軸: dim1」「y-軸: dim2」としたピボットテーブルで利用される事が多いです。
パターン 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 まで。
