目的

以下の入力データのようなアクセス履歴データから、ユーザー毎にどのタイプのお店に何回訪問したか、というクロス集計表を作成します。これはその後、Rで分析する入力データになります。

ファイル群の容量が巨大すぎてローカルPCでのSQLソフトの利用が非現実的な場合、Perlを使うことで至って高速にクロス集計が可能と知り、試作してみました。

私はPerl初心者なので、１）まず、ユーザー毎のショップ毎のアクセス数という中間アウトプットファイルを作成する、２）中間アウトプットファイルから最終的なアウトプットであるクロス集計を作成する、と２つのプロセスにスクリプトを分けています。

入力データ

uid	datetime	shop_name
x00001	20141205050508	サプリメント
x00001	20141205050701	健康グッズ
x00001	20150102170501	収納家具
x00002	20141120160521	書籍
x00002	20150205170530	スキンケア
x00002	20150205175002	ファッション
...	...	...

条件

shop_nameは、1万個以上存在しますが、集計するのは100個のshop_nameに限定します。集計対象のshop_nameリストはshoplistファイルから読み込みます。
YYYYMMDDHHMMSS形式のdatetimeフィールドを読み、YYYYが2015年のレコードのみ集計対象とします。

プロセス１〜クロス表の元になる中間ファイルを作成

中間ファイルは、以下の形式です。

uid	shop_name	count

ここで全てのユーザーについてshop_nameは100個分集計値が入るようにします。
備考）SQLだと、countが０になるカテゴリ名は出力されません。

sample.pl

# !/usr/bin/perl
use 5.014;
use strict;
use warnings;

# アウトプット形式
# userid, カテゴリ名,アクセス数

# 使い方：gzcat ログファイル名*.tsv.gz |./sample.pl >temporary_cross.tsv

# ----------------------------------------------------------------------
# 入力データを標準入力から読み、ハッシュでユーザー毎ショップ毎のアクセス数をカウントする
# ----------------------------------------------------------------------
my $user_cate_data ={};#user_cate_dataという無名ハッシュ（無名配列生成子で宣言された）を生成し、$を付けてリファレンスを宣言する
my @rec =();#読み込んだ１レコードを格納する配列を初期化
my $uids={};#出現したuid全てを格納するハッシュを初期化

while (<>){
    chomp;
    #タブ区切りの文字列を、recという配列に区切って格納する;
    my @rec = split /\t/, $_, -1;
    #uid,date,category_nameという変数を配列から取得
    my $uid = $rec[0];
    my $date = $rec[1];
    #datestrは、YYYYMMDDhhmmssの日付文字列から、YYYYMMの部分を抽出する
    my $datestr = substr($date,0,4);
    #2015年1月以降のみ集計の対象とする。
    if ($datestr ge '2015'){
        my $category = $rec[2];
        #user_cate_dataは、$をつけているのでハッシュのリファレンス
        #ハッシュuser_cate_dataの１つめのキーワード$category_name　と2つめのキーワード $uidとで参照される要素の値を１つインクリメントする
        $user_cate_data->{ $uid }->{ $category }++;
        #uidsハッシュの$uidキーに１を格納する
        $uids->{$uid} = 1;
    }
}

# ---------------------------------------------------
# 集計対象のショップ名ファイルの読み込みとハッシュ格納
# ---------------------------------------------------
my $file="対象１００ショップリスト.tsv";
my %dic;#ハッシュを宣言

open my $fh,'<',$file;
while(my $line =<$fh>){
    chomp($line)    ;
    $dic{$line} = 1; #ハッシュ%dicで、読み込んだショップ名キーワードに対して、値1を割り当てる
}
close $fh;

# ---------------------------------------------------
# 全てのユーザーについて、対象ショップ100個分の集計値を出力
# 対象ショップへのアクセスがない場合も、値0を出力
# ---------------------------------------------------
# $uidsは、ハッシュのリファレンス
# %{$uids｝は、ハッシュのリファレンスをデリファレンス
for my $user_id (keys %{$uids}) {
    #全てのuid名を書き出す。
    # $user_cate_dataは、ハッシュのリファレンス。
    for my $cate (keys %dic){
        #集計対象の100店舗名全てを書き出す
        #カウント結果ハッシュの要素へアクセスするには、$user_cate_data->{$user_id}{$cate}
        my $value = $user_cate_data->{$user_id}{$cate} || 0 ;
        #ハッシュのキーワードは$user_id、$cateで参照
        print "$user_id\t$cate\t$value\n";
    }
}

中間ファイル　

uid	shop_name	count
x00001	サプリメント	1
x00001	健康グッズ	1
x00001	収納家具	1
x00001	書籍	0
x00001	スキンケア	0
x00001	ファッション	0
x00001	...	0
x00002	サプリメント	0
x00002	健康グッズ	0
x00002	収納家具	0
x00002	書籍	1
x00002	スキンケア	1
x00002	ファッション	1
x00002	...	0

プロセス２〜中間ファイルを読み込んで、クロス表の完成形を作成する

make_uid_shop_cross.pl

# !/usr/bin/perl
use 5.014;
use strict;
use warnings;

# 使い方 ./make_uid_shop_cross.pl temprary_cross.tsv > cross_table.tsv

my %shops ;#ショップ名が全て格納されるハッシュ
my %uids;       #uidが全て格納されるハッシュ
my %cross_org;  #ユーザー毎、店舗名ごとのアクセス数が格納されるハッシュ

# 標準入力から読み込むのは、sample.plで作成したユーザー、店舗名、アクセス数の3列のデータ

while (<>){
    chomp;
    my @rec = split /\t/, $_, -1;
    my $shop_name = $rec[1] ; #店舗名はレコードの２フィールド目から取得
    #ハッシュshopsの$shop_nameキーに値＝１をセットする
    $shops{ $shop_name }=1;
    my $uid = $rec[0];　　#ユーザー名はレコードの１フィールド目から取得
    #ハッシュuidsに$uidのキーワードを登録し、値＝１をセットする
    $uids{$uid} = 1;
    my $value = $rec[2];　#アクセス数はレコードの3フィールド目から取得
    #ハッシュcross_orgの１こめのキーワード$uidと、２個目のキーワード　$shop_nameの値に、$valueをセットする
    $cross_org{$uid}{$shop_name} = $value;
}

# ハッシュshopsのキーワードの数を確認する。
my $val = keys %shops;

# print "ショップ数=\t$val\n";

# ハッシュ%uidsのキーワードの数を確認する。
my $uu = keys %uids;
# print "ユーザー数=\t$uu\n";

# uidをソートしたものを配列にする。
my @uid_list = sort keys %uids;

# カテゴリをソートしたものを配列にする。
my @shop_list = sort keys %shops;

# 最初にヘッダを出力
# join関数は、join(区切り文字、配列）で、配列の中身を１つずつ区切り文字で区切って出力
print join("\t",'uid',@shop_list) ."\n";#ピリオド(.)は、文字列の結合演算子

# -----クロス表の形式に出力する
foreach my $i (@uid_list){
    #１ユーザーについて、１人分ずつcross_tableという配列をつくる
    my @cross_table;
    # @cross_tableの１要素目にユーザー名を格納
    push @cross_table,$i;
    foreach my $j (@shop_list){
        #次に、カテゴリ要素のアルファベット順ごとに、ハッシュ%cross_orgを参照する
        my $nn = $cross_org{$i}{$j};
        push @cross_table,$nn;
    }
    #1ユーザー分の値の出力
    print join("\t",@cross_table)."\n";
}

最終的なアウトプット

uid	サプリメント	健康グッズ	収納家具	書籍	スキンケア	ファッション	...
x00001	1	1	1	0	0	0	0
x00002	0	0	0	1	1	1	0
x00003	...	...	...	...	...	...	...

SQLではなくPerlで集計処理を行うメリット

集計が必要なファイル群の総容量が巨大すぎてローカルPCデータベースソフトに入れるのが現実的ではない場合の処理が可能です。
SQLソフトに比べてかなり高速に結果を得られます。
入力データのフィールド数が可変だとSQLソフトにインポートするためには、いちいち整形処理を行わねばなりませんが、最初からPerlで集計ができればその分速く処理できます。
SQLでクロス集計は可能ですが、データが重たいと遅いです。
（このページの例にある入力データは可変のものを固定フィールドに整形してあります）

参考資料

　 -　木本裕紀『業務に役立つPerl』、技術評論社、２０１5年第2刷版、pp.125〜pp.126. 　
　-　13. ハッシュの活用：柔軟なデータ集計