PAGE TOP

取り組み

印刷する

gawkでクロス集計に挑戦

さぼ郎
分析

gawk出クロス集計をしてみます。データ量が少なければExcelのピボットテーブルなどを駆使するとか、あるいは、売っているスマートなクロス集計ソフトもたくさんあります。

ここでは、gawkを使ってクロス集計をしてみることにします。ただし、連想配列を使うので、出力された結果に対しては、加工が必要になります。

その加工はExcelのVBAでやろうと思っていますが、それは後日、ご報告いたします。

まず、復習。データの構造。
CSVデータです。全部で「14,606,877」件あります。

1.支店情報
2.月次情報
3.商品情報
4.金額情報
5.性別情報
6.年齢情報
7.県別情報

金額や年齢などは、ある程度範囲を指定してクラス分けしたほうがいいと思いますが、とりあえずは、汎用的にあるがままを単純に集計します。

クラス分けは集計結果を踏まえて、次のステップで行えばいいように思いますし、件数が扱いやすいレベルであればExcelで整形するタイミングでクラス分けをすることも考えられます。
function dataGetCC(pos,str){
title[pos]=str;
buff[pos,$pos]+=1;
}
buff[pos,$pos]+=1;

としているのは、「buff」という名前の配列に[pos,$pos]としています。「pos」は、CSVデータのポジョン番号で「$pos」は、その値になります。

つまり、「データ位置+セパレータ+支店名」で、文字列を作り、その値を配列の添字にしてカウントを取るというわけです。
    dataGetCC(1, "支店集計");

このように使います。データのポジションは1番目で出力するときの文字列は「支店集計」ということになります。

「$pos」としていますので、

福島支店,04M,L,2900,60,男,福島県
福島支店,04M,L,36000,50,男,福島県
福島支店,04M,L,6000,80,男,福島県
「福島支店」という文字列に「+=1」として加算していくことで、合計を出していきます。

# ■posは、csvのフィールド位置
function dataOutCC(pos)
{
print "n" pos "" title[pos] "n";

for (i in buff){
reg = "^" pos;
if (i~reg){
str = i;
asc = sprintf("%c", 28);
sub(asc, "¥t", str);
print str, buff[i];
}
}
printf "n";
}

for(i in buff){
    ・・・
}

この書き方は「連想配列」を出力する定型的な書き方です。出力は、どういう順番になるかは出力次第になってしまいます。

「reg」という文字列の変数の頭に「^(キャレット)」を付けているのは、「行データの先頭」を意味しています。

この文字列変数を使って正規表現でパターンを合わせています。

if(i~reg){
    ・・・
}

「i」は、連想配列から出てくる文字列になります。フィールドの一番だと支店名を受け取ることになります。

str=i;
asc=sprintf("%c",28);
sub(asc,"¥t",str);
print str, buff[i];

asc」は、セパレータを変数に入れています。「28」というのはASCIIコードで「0x1c」のことです。gawkは、このコードを内部的なセパレータとして使っているようですので、このセパレータを「sub」を使って「タブ(¥t)」に置換しています。

【1】支店集計

1 神奈川東支店 58347
1 愛媛支店 32758
1 三重支店 22635
1 大分支店 8471
1 新潟支店 48782
1 長崎支店 50151
1 大阪支店 38746

合計で「14,606,877」件で1件の狂いもありません。所要時間「12秒26」でした。

件数を知ることができれば、売上も知りたいところです。
# ■pos は金額フィールドを指定
function dataGetVal(pos,val,str){
pos="v" pos;
title[pos]=str;
buff[pos,$val]+=$val;
}
これは、金額を取得するための関数です。

引数は「pos、val、str」の3つ。
1つ目の「pos」は、集計対象とするデータフィールドの項目番号です。
2つ目の「val」は、金額のあるフィールド番号になります。
3つ目の「str」は、出力時に表示する文字列です。
    dataGetVal(1,4,"支店金額集計")
このような使い方になります。4番目のフィールドに金額が入っています。
# ■posは、csvのフィールド位置
function dataOutVal(pos){
pos="v" pos;
print "n" pos "" title[pos] "n";
for (i in buff) {
reg="^" pos ;
if(i~reg){
str=i;
asc=sprintf("%c",28);
sub(asc,"¥t",str);
print str,buff[i];
}
}
printf "n";
}
と、このような出力用の関数を用意します。

pos="v" pos;

としているのは、連想配列において識別を付けるために必要な処理です。 

v1 北海道支店 5023993700
v1 愛知西支店 3313058300
v1 千葉中支店 2346052000
v1 静岡東支店 4933752200

あとは、件数と金額で1件あたりの売上金額の平均を見て、平均より上回っているか、下回っているか や、最低価格帯の多い支店、逆に高額商品の売上が多い支店などを抽出して、その要因を分析するなど、いかようにも加工ができます。

1,400万件のデータをCSVというテキストデータにすることで、こんなに安く、早く分析ができるということを伝えるのが、この記事の趣旨です。

なぜ、テキストデータに注目しだしたかと言うと、「ブロックチェーン」です。ハッシュと暗号化を組み合わせてP2Pで分散して格納することで、今まで大掛かりなコストをかけていたシステム費用を軽減できる可能性が出てきています。

ここからは、やっとクロス集計です。かつては、クロス集計と言うと条件式を書いていたのですが、ある時、連想配列でできると気が付きました。
# ■pos1pos2はクロスするフィールドの件数
function dataGetCRScc(pos1,pos2,str){
cpos="c" pos1 "×" pos2;
title[cpos]=str;
buff[cpos,$pos1,$pos2]+=1;
}
クロス集計となると、1軸と2軸とで文字列を作り、先頭に「"c"」を付けたポジションの識別フラグを付けて、カウントします。
dataGetCRScc(1, 2, "支店×月度");
dataGetCRScc(1, 3, "支店×商品");
使い方はこんな感じ。
# ■クロス集計した結果を表示する
function dataOutCRScc(pos1,pos2){
cpos="c" pos1 "×" pos2;
print "n" cpos "" title[cpos] "n";

for(i in buff){
reg="^" cpos;
if(i~reg){
str=i;
asc=sprintf("%c",28);
gsub(asc,"¥t",str);
print str,buff[i];
}
}
printf "n";
}
ほとんど同じ構造ですが、集計がpos1+pos2で一致する文字列をカウントしているだけです。

つまり、「"c"+データ位置1+"✕"+データ位置2」で識別子を作り、「識別子+セパレータ+データ位置1+セパレータ+データ位置2」で連想配列用の文字列を作り、それを配列の添字として加算するわけです。

出力の使い方は、
dataOutCRScc(1, 2);
dataOutCRScc(1, 3);
フィールドを指定するだけです。

c1×2 埼玉支店 04M 19641
c1×2 東京中支店 08M 30499
c1×2 佐賀支店 12M 57797
c1×2 東京中支店 04M 36483
c1×2 東京北支店 03M 15332
c1×2 広島支店 11M 26006

クロス集計には見えませんが、Excelのシートに貼り付けてピボットテーブルに「表側」「表頭」「値」を指定すれば一瞬でマトリックスになります。

クロス集計

ちなみに、1400万件のデータを支店名と月度で集計したのに要したのは、
17.38
でした。2軸(支店×月度、支店×商品)にしても、「27.64秒」でした。所詮、IOにかかる時間はCPUの速さとは比例関係にはありません。

分析

たしかに「HADOOP」のような分散処理は、もっとデータ量が多くなり、分散処理としての最適化さえできる技術があるのであれば有効かもしれませんが、1千万件、2千万件のデータ加工なら、「gawk」でもかなり活躍ができそうという気になりませんでしたか?
dataGetCRScc(1, 2, "支店×月度");
dataGetCRScc(1, 3, "支店×商品");
dataGetCRScc(1, 8, "支店×金額");
dataGetCRScc(1, 5, "支店×性別");
dataGetCRScc(1, 6, "支店×年齢");
dataGetCRScc(1, 7, "支店×県別");
表側を「支店」にして「月度」「商品」「金額」「性別」「県別」をクロス集計しています。
dataOutCRScc(1, 2);
dataOutCRScc(1, 3);
dataOutCRScc(1, 8);
dataOutCRScc(1, 5);
dataOutCRScc(1, 6);
dataOutCRScc(1, 7);
出力はこんな感じ。

14,606,877」件のデータに対する6軸のクロス集計に要した時間は1分11秒83でした。72秒。1表約12秒です。

1表->約12秒

なぜ、こんなに早いのかと言うと、「連想配列」を使っているからです。Go言語のような連想配列」と「正規表現」が使える言語であれば、可能だと思うので、いずれGo言語で挑戦してみようと思いますが、awkの便利なところはC言語にも似て、コーディングにうるさいルールを押し付けてこないところが、いまだ、廃れない秘密ではないでしょうか。

キーワード