SQL, PigのCUBE - wyukawa's diary

SQLで小計や総合計を求める時にGROUP BYを利用することが多いと思いますがいろんな軸で集計したい場合にROLLUP, CUBE, GROUPING SETSを使うことができるようです。

詳しくはこちら参照
http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj04_4.htm

ROLLUP, CUBE, GROUPING SETSを使うことができますと断定していないのは僕が試してないからです（汗

なぜ試していないかというとこれらの機能を利用できるのがOracle, SQL Server, DB2だからです。Oracle XEをダウンロードしようかと思いましたけどユーザ登録に心が折れましたw　ちなみにMySQLではROLLUPのみサポートしているらしいです。

今回は考えられる全ての組み合わせで集計するCUBEについて書いてみたいと思います。

CUBE演算子を使わないでやろうとすると各集計をUNIONでつなぐことになります。

ちょっとH2で試してみます。

こんなデータがあるとします。

$ cat cube.csv 
dog,miami,12
cat,miami,18
turtle,tampa,4
dog,tampa,14
cat,naples,9
dog,naples,5
turtle,naples,1

テーブルに突っ込みます。

CREATE TABLE a(x VARCHAR(255), y VARCHAR(255), z INT)
        AS SELECT *
                 FROM CSVREAD('cube.csv', 'x,y,z', 'charset=UTF-8 fieldSeparator=,');

x,yで集計、xで集計、yで集計、総合計を求めてUNIONでつなげます。

SELECT x, y, COUNT(*), SUM(z)
FROM a
GROUP BY x, y

UNION ALL

SELECT x, null, COUNT(*), SUM(z)
FROM a
GROUP BY x

UNION ALL

SELECT null, y, COUNT(*), SUM(z)
FROM a
GROUP BY y

UNION ALL

SELECT null, null, COUNT(*), SUM(z)
FROM a
;

今回はxとyの2つの組み合わせなので $2^2$ つまり4つUNIONで組み合わせます。

一般にn個ある場合は
${}_{n}C_{0} + {}_{n}C_{1} + ... + {}_{n}C_{n}$ となるので $2^n$ ですね。

SQLの実行結果はこんなんになります。

X	Y	COUNT(*)	SUM(Z)
cat	naples	1	9
dog	tampa	1	14
dog	naples	1	5
turtle	naples	1	1
cat	miami	1	18
dog	miami	1	12
turtle	tampa	1	4
dog	null	3	31
turtle	null	2	5
cat	null	2	27
null	miami	2	30
null	tampa	2	18
null	naples	3	15
null	null	7	63

で、まあそもそもなんでCUBEのことを書こうかと思ったかというとこのスライドを見たからですね。p16-p20にCUBEがらみのことが書かれています。

Hadoop and the Data Scientist from Takahiro Inoue

このスライドにも書かれていますが、PigがCUBEをサポートするようです。
現行の0.10には入ってないですが0.11では入るようです。

参考
Introducing the CUBE operator for Apache Pig | Arnab Nandi

JIRAでいうと[PIG-2167] CUBE operation in Pig - ASF JIRAですね。
このチケットは5つのサブタスクを持っていて[PIG-2831] MR-Cube implementation (Distributed cubing for holistic measures) - ASF JIRA以外は完了しています。

PIG-2831のholisticというのは $f(x+y)=f(x)+f(y)$ が成り立たないことですね。例えば平均とかメジアンとかね。アクセス解析の例でいうとUUですね。部分和を足しても総和にならないパターンです。こっちのほうが計算が大変ですね。

$f(x+y)=f(x)+f(y)$ が成り立つ場合のことをalgebraicと呼んでいます。例えばcountとか和とかですね。アクセス解析の例でいうとPVですね。部分和を足して総和になるパターンです。

上記のようにまだ正式リリースはされてないですがtrunkからソースとってきてPigのCUBEを動かしてみました。
gitだと遅いのでsvnでソースとってきてます。

$ svn co http://svn.apache.org/repos/asf/pig/trunk/ pig-trunk
$ cd pig-trunk
$ ant
$ export JAVA_HOME=/Library/Java/Home
$ bin/pig -version
Apache Pig version 0.12.0-SNAPSHOT (r1400075) 
compiled 10 19 2012, 21:45:30

実行したPigクエリはこんな感じです。

$ cat cube.pig
a = load 'cube.csv' USING PigStorage(',') as (x:chararray,y:chararray,z:int);
b = cube a BY cube(x, y);
DESCRIBE b;
c = foreach b generate flatten(group) as (type,location), COUNT_STAR(cube) as count, SUM(cube.z) as total;
DUMP c;

実行結果はこんな感じです。

$ bin/pig -x local cube.pig

--ログは省略--

b: {group: (x: chararray,y: chararray),cube: {(x: chararray,y: chararray,z: int)}}

--ログは省略--

(cat,miami,1,18)
(cat,naples,1,9)
(cat,,2,27)
(dog,miami,1,12)
(dog,tampa,1,14)
(dog,naples,1,5)
(dog,,3,31)
(turtle,tampa,1,4)
(turtle,naples,1,1)
(turtle,,2,5)
(,miami,2,30)
(,tampa,2,18)
(,naples,3,15)
(,,7,63)

Hiveでもこんなチケットがあがってるので実装されるかも。
[HIVE-3433] Implement CUBE and ROLLUP operators in Hive - ASF JIRA