効率の良いデータ照合のさせ方

質問

2つのCSVファイル(どちらもデータ件数が1000件を超える)を照合して
SQL文の発行の有無を処理するプログラムを組んでいます。↓

open(ファイル1, ファイル1の場所) || die("$!");

while(<ファイル1>){

chomp($_);
@data_1 = split(/,/, $_);

open(ファイル2, ファイル2の場所) || die("$!");

while(<ファイル2>){

chomp($_);
@data_2 = split(/,/, $_);

　　#　ここでファイル1の指定した列のデータとファイル2の指定した列のデータ照合しています。↓
　　#　($data_1[列]、$data_2[列])
if(($data_1[0] == $data_2[5]) and ($data_1[1] == $data_2[3]) and
($data_1[3] == $data_2[4]) and ($data_1[4] == $data_2[0])){

#insert文

#update文

}

}

}

上記のような組み方をすると、やはり実行速度がかなり遅くなりました。
8分ぐらい処理にかかってしまいました；
効率的な処理のさせ方ということで、関数などを調べているのですが、
これという関数も見つからず困っています。
プログラムの組み方自体に問題があるとは思うのですが・・
なにぶん、経験が浅いというのもあり・・・；
何か良い方法をご存知の方、ご指導お願いします。

回答No.5

shuyamakawa · ベストアンサー

#1,#4です。
これは、#2さんの回答と趣旨は同じ(ハッシュ変数を使う)なのですが、サンプルを書いてみました。

ただし、条件があって、
ハッシュ変数(連想配列)に入れるキーはユニークになっていないといけません。
下の例では、ファイル１とファイル２が両方ともユニークキーでないといけないようになっています。

2つのファイルのどちらか一方をリスト(配列)で実装すれば、ファイル１かファイル２のいずれかが
ユニークキーであれば良いという条件になります。

open( IN, "ttt1.csv" ) || die;
while( <IN> ) {
chomp( $_ );

$value = $_;

#比較用文字列を求める。(これがハッシュ変数のキーとなる。)
@d = split( /,/, $value );
$key = join( ',', $d[ 0 ], $d[ 1 ], $d[ 3 ], $d[ 4 ] );

#キーがすでに登録されている？
if ( exists( $csv1{ $key } ) ) {
die( "ファイル1のキー「$key」はユニークでは無いのでハッシュ変数に入れる方法は使えません。\n" );
}

# 比較に必要な最低限のカラムだけをキーにしてハッシュ変数に入れる。
# 値は行全体とする。
$csv1{ $key } = $value;
}
close( IN );

open( IN, "ttt2.csv" ) || die;
while( <IN> ) {
chomp( $_ );

$value = $_;

#比較用文字列を求める。(これがハッシュ変数のキーとなる。)
@d = split( /,/, $value );
$key = join( ',', $d[ 5 ], $d[ 3 ], $d[ 4 ], $d[ 0 ] );

#キーがすでに登録されている？
if ( exists( $csv2{ $key } ) ) {
die( "ファイル2のキー「$key」はユニークでは無いのでハッシュ変数に入れる方法は使えません。\n" );
}

# 比較に必要な最低限のカラムだけをキーにしてハッシュ変数に入れる。
# 値は行全体とする。
$csv2{ $key } = $value;
}
close( IN );

print "csv1:\n";
foreach $d ( keys %csv1 ) {
print "key:$d value:$csv1{$d}\n";
}

print "csv2:\n";
foreach $d ( keys %csv2 ) {
print "key:$d value:$csv2{$d}\n";
}

print "result:\n";
foreach $key ( keys %csv1 ) {

#CSV1の該当行が、CSV2のどれかの行と一致する？
if ( exists( $csv2{ $key } ) ) {
#SQLを実行する。
print "exec SQL of key($key) all1($csv1{$key}) all2($csv2{$key})\n";
}
}

補足

レスが遅れてしまい大変申し訳ありませんでした（汗）
経験不足＆勉強不足なためユニークやハッシュについて調べていました。(恥)
とっても難しく思い、どうにかもっと簡単な処理のさせ方がないかと思いまして・・(涙)
二分探索の方法を自分なりに考え無事処理を終えることが出来ました。
処理速度も3秒になりかなり速くなりました。

上記のようなコードをきちんと理解してから使用できるよう、勉強いたします。
こちらの力不足で大変申し訳ありません；
お時間を割いてまでお教えいただき大変ありがとうございました！

回答No.8

microham2

(1) オンメモリ処理の場合
　データ2を読み込み、sortして、2分検索する。

(2) ファイル上での処理の場合
　データ２を固定長ファイルに変換してsortして、seekコマンドで2分検索する。

(3) DB上でファイル１も２も仮テーブルに読み込んで、SQLコマンド一発。

参考URL：

http://ja.wikipedia.org/wiki/二分探索

お礼

回答記入ありがとうございます！

今回は、(1)の方法を取り、無事処理の方完了することが出来ました！
(3)はあまり実行させたくなかったので無理でしたが、
(2)のseekコマンドは初めて知りました・・そんなコマンドがあったんですね・・・勉強不足でした・・・・；
機会があれば、seekコマンドの方活用してみたいと思います。

書き込みありがとうございました！

回答No.7

moon_night

あまりにもファイル２が大きい場合、特定の値でファイルを分けてしまうのも手かと思います。

例えば、$file2[5]で分けるとして、
foreach (@file2) {
(@_) = split(/,/);
@file2_data[$_[5]] = $_;
}
としておけば、
foreach $file1 (@file1) {
(@data1) = split(/,/,$file1);
foreach $file2 (@file2_data[$data[0]]) {
#処理
}
}

とか。
エラー処理とかは入ってないので適当に。
動作確認はしてないので動かないかもしれません。
当然メモリは喰います。

お礼

回答記入ありがとうございます！

そのような方法もあったんですね！！！
私にはそのような発想ありませんでした。
なるほど・・・今回はファイルを2つに分けませんでしたが、
また、なにかある際は上記の方法考えてみます。

ありがとうございました！

回答No.6

神崎 渉瑠（@taloo）

長文失礼。
＊以下のソースは動作検証していません

> my @d1 = split(/,/, $_, 6);
これの第三引数は、最大○個に分割するという意味です。
詳しくはsplitについて調べてください。

$_の内容が'aaa,bbb,ccc,ddd,eee,fff,ggg,hhh,iii, ... zzz'となっているとすると、
6を指定していると
@d1 = ['aaa', 'bbb', 'ccc','ddd','eee','fff,ggg, ... ,zzz']
と、６つだけになり、６つ目以降の分割にかかる手間が省略されます
（文字列が長ければ長いほど、処理速度に差が出ます）

> my $str = $d1[0].','.$d1[1].','.$d1[3].','.$d1[4];
と
my $str = join(',', $d1[0],$d1[1],$d1[3],$d1[4]);
は同じ意味ですが、joinよりもべた書きした方が早いです。
join(',', @d1);というような配列の内容全てを含める場合でなければ、（私は速度優先派なので）べた書きの方が多いです(^^;
1000回ほど繰り返せば、多少の差が出ると思います（CPUの処理速度によります）が、
速度に違いがないようなら、どちらでもわかりやすい方を書いてください。

> ファイル１の１行目のデータと同じ値のもの（列の値）がファイル２にあれば、そのファイル１の情報を元にＳＱＬ文の発行をするというものです。・・ちなみにファイル２のデータの方が膨大です・・・(涙)
ということは、ANo.2の方法になりますね。
ファイルサイズの小さなファイル１の方をハッシュにした方がメモリ使用量は小さくなりそうですね。
値を1にせずにデータをそのまま登録して、

while(<ファイル1>){
chomp($_);
my @d1 = split(/,/, $_, 6);
my $str = $d1[0].','.$d1[1].','.$d1[3].','.$d1[4];
$data_1{$str}=$_; # この行を変更
}
close ファイル1;

としたものを、ANo.2の該当部分に置き換えてください。
SQLの生成は
ファイル２をオープンした後、比較している部分を以下のように置き換えてください。

$key=$d2[5].','.$d2[3].','.$d2[4].','.$d2[0];
if(exist $data_1{$key}){
#insert文
# $data_1{$key}を元にSQL文を作成する
（以下略）

SQL文の発行はかなり時間がかかりますのであまりに何度も発行するとそれだけで時間がかかります。
（SQL文によっては使えませんが）プレースホルダーを使うなど、時間短縮をさせるようにしてみてください。

ファイル２をハッシュにすると、メモリ使用量は増えますが、時間短縮になる思います。
書き換えるならファイル１で作ったハッシュ作成方法を、ファイル２に適用し、
比較の部分とSQL生成の元ネタを変更内容にあわせて修正してください。

> ANo.5
そういわれればユニークキーについては考慮していませんから、
キーが存在する場合は、
ハッシュの値を配列（または配列へのリファレンス）にしたり、\nや\0を区切り文字として連結、SQL文発行時は改めて区切り文字で分割するなど、何らかの処理が必要です。

別なやり方として、ファイル２をDBMに作り替えてしまう、ということも出来なくはないですが、、、
メモリ使用量も減りますし、ファイル１のハッシュキーの重複は考えなくて済みます

お礼

レスが大変遅れてしまい申し訳ありませんでした；
下記にも書きましたが、勉強不足のためハッシュなどの意味が分からず調べていました。
すみません・・私にはかなり難しく感じてしまい二分探索という形をとりました・・
色々知識を分かりやすくお教えいただいたのに大変申し訳ありません(泣)
プログラム自体まともに組んだことが無いためtalooさんのお教えいただいたことは大変ためになりました。たとえば・・↓

> SQL文の発行はかなり時間がかかりますのであまりに何度も発行するとそれだけで時間がかかります。
そ、そうだったんですか・・・まだ大量のSQL文を発行したことが無かったため初めて知りました・・

力を付けてからtalooの言っている処理のさせ方を組みたいと思います。というか勉強します・・(泣)
お時間をいただき、大変ありがとうございました。

回答No.4

shuyamakawa

#1です。リファレンスの例です。
@csv_ref1のようなデータの持ち方をすると、使う時にいちいちsplitする必要が無くなります。

open( IN1, "ttt1.csv" ) || die;
while( <IN1> ) {
chomp( $_ );

# CSVの1行まるごとをリスト(配列)に入れる。
push( @csv_all1, $_ );

@d = split( /,/, $_ );

# 比較に必要な最低限のカラムだけをリスト(配列)に入れる。
push( @csv_cmp1, join( ',', $d[ 0 ], $d[ 1 ], $d[ 3 ], $d[ 4 ] ) );

# @dの実体を作り、それをリファレンス変数でポイントする。
$ref_d = [ @d ];
# リファレンス変数をリスト(配列)に入れる。
push( @csv_ref1, $ref_d );

#これは間違いです。リファレンスが全て同じところを指してしまいます。
push( @csv_ref_mistake1, \@d );
}

print "csv_all1:\n";
foreach $d ( @csv_all1 ) {
@a = split( /,/, $d );
print join( '|', @a ). "\n";
}

print "csv_cmp1:\n";
foreach $d ( @csv_cmp1 ) {
@a = split( /,/, $d );
print join( '|', @a ). "\n";
}

print "csv_ref1:\n";
foreach $ref_d ( @csv_ref1 ) {
print join( '|', @{$ref_d} ). "\n";
}

print "csv_ref_mistake1:\n";
foreach $ref_d ( @csv_ref_mistake1 ) {
print join( '|', @{$ref_d} ). "\n";
}

お礼

こんなにたくさん、しかも分かりやすく教えていただき、
大変ありがとうございます！

> @csv_ref1のようなデータの持ち方をすると、使う時にいちいちsplitする必要が無くなります。
splitでデータを取得し配列する速度自体は支障がなかったため、push( @csv_cmp1, join( ',', $d[ 0 ], $d[ 1 ], $d[ 3 ], $d[ 4 ] ) );
の方がコード的にも分かりやすかったのでこちらを採用しました！
でも@csv_ref1も後々使っていくと思います！ありがとうございます！(嬉)

ことは相談なのですが・・・・・問題の処理速度が遅い部分がまだ改善されていませんでした・・；(泣)
どうやら比較の際、つまりループ時にかなり時間がかかっているみたいです・・・；

foreach $d1 ( @file_1 ) {
foreach $d2 ( @file_2 ){
if ($d1 eq $d2){
#　ここでSQL文の発行
}
}
}
とやってみると当初の速度と変わらず・・困っています・・(泣)
なにか良い改善策の方をお持ちであれば是非教えていただきたいのですが・・よろしくお願いいたします。

回答No.3

神崎 渉瑠（@taloo）

質問のスクリプトがやってる内容を読み直してみましたが、
ファイル１の１行目とファイル２の１行目を、
ファイル１の２行目とファイル２の２行目を、、、
という比較ならANo.2は使えません。

open(ファイル1, ファイル1の場所) || die("$!");
open(ファイル2, ファイル2の場所) || die("$!");

while(<ファイル1>){
chomp($_);
@data_1 = split(/,/, $_,6);

$_=<ファイル2>;
chomp($_);
@data_2 = split(/,/, $_, 7);

　　#　ここでファイル1の指定した列のデータとファイル2の指定した列のデータ照合しています。↓
　　#　($data_1[列]、$data_2[列])
if(($data_1[0] == $data_2[5]) and ($data_1[1] == $data_2[3]) and
($data_1[3] == $data_2[4]) and ($data_1[4] == $data_2[0])){
#insert文

#update文

}
}
}

＊動作未検証

補足

参考意見ありがとうございます。

> 質問のスクリプトがやってる内容を読み直してみましたが、
> ファイル１の１行目とファイル２の１行目を、
> ファイル１の２行目とファイル２の２行目を、、、
> という比較ならANo.2は使えません。

紛らわしい質問コードですみません；
ファイル１の１行目とファイル２の１行目・・というように
行を合わせて照合させたいわけではありません。

メインはファイル１です。
ファイル１の１行目のデータと同じ値のもの（列の値）がファイル２にあれば、そのファイル１の情報を元にＳＱＬ文の発行をするというものです。・・ちなみにファイル２のデータの方が膨大です・・・(涙)

上記のコードを参考にプログラムを組んでいたのですが、
@data_1 = split(/,/, $_,6);
@data_2 = split(/,/, $_,7);
の6と7はいったいどういう意味なのでしょうか・・
配列を分けているのでしょうか？？でも何故6と7なんでしょうか・・
すみませんなにぶん経験が浅いものでお教え願えないでしょうか(泣)

上記の6と7をいれずに実行してみるとやはり、
ファイル１とファイル２が一緒になって一行ずつ動いているので、
照合に失敗してしまいました。

回答No.2

神崎 渉瑠（@taloo）

全行照らし合わせはハッシュが早いです。（が、メモリとCPUリソースも食います（負荷がかかる））
splitしたものから必要な項目を取り出してキーにすると、一般的な照らし合わせサンプルと同じ事が出来ます。
（＊リファレンスのサンプルじゃないです）

my %data_1;
open(ファイル1, ファイル1の場所) || die("$!");
while(<ファイル1>){
chomp($_);
my @d1 = split(/,/, $_, 6);
my $str = $d1[0].','.$d1[1].','.$d1[3].','.$d1[4];
$data_1{$str}=1;
}
close ファイル1;

open(ファイル2, ファイル2の場所) || die("$!");
while(<ファイル2>){
chomp($_);
@d2 = split(/,/, $_, 7);
#　ここでファイル1の指定した列のデータとファイル2の指定した列のデータ照合しています。↓
#　($data_1[列]、$data_2[列])
if(exist $data_1{$d2[5].','.$d2[3].','.$d2[4].','.$d2[0]}){
#insert文

#update文

}
}
close ファイル2;

＊動作未検証

回答No.1

shuyamakawa

ファイル2は何回もファイルオープン～読み込み～クローズを繰り返している
ので効率が悪いと思います。
リスト(配列)にあらかじめ読み込んで置くと良いと思います。(@csv_all1)
(当然消費メモリは増えるのでその点は注意する必要があります。)

長い行に対してsplitすると時間がかかるので、
比較で使用する必要最低限のカラムにしぼって
@csv_all1とは別の配列に入れておくのはどうでしょうか？(@csv_cmp1)

リファレンスを使うと全ての行をsplitした状態でリスト(配列)に入れることが
出来ますが、ちょっと複雑に見えるかもしれないので割愛します。
(必要なら補足で言ってください。)

open( IN1, "ttt1.csv" ) || die;
while( <IN1> ) {
chomp( $_ );

# CSVの1行まるごとをリスト(配列)に入れる。
push( @csv_all1, $_ );

# 比較に必要な最低限のカラムだけをリスト(配列)に入れる。
@d = split( /,/, $_ );
push( @csv_cmp1, join( ',', $d[ 0 ], $d[ 1 ], $d[ 3 ], $d[ 4 ] ) );
}

print "csv_all1:\n";
foreach $d ( @csv_all1 ) {
print "$d\n";
}

print "csv_cmp1:\n";
foreach $d ( @csv_cmp1 ) {
print "$d\n";
}

お礼

早速処理の方、自分のデータで試してみました！
処理が早くて感動しました(涙)5秒・・感動・・

> 長い行に対してsplitすると時間がかかるので、
> 比較で使用する必要最低限のカラムにしぼって
> @csv_all1とは別の配列に入れておくのはどうでしょうか？(@csv_cmp1)

必要最低限のカラムにしぼる・・そんな処理方法出来たんですね！！(汗)
新たな提案本当にありがとうございます。早速採用させていただきます！

これらの情報を元にもう一度組みなおしてみます！！
ありがとうございます！！

それから技術向上のため、リファレンスの方お教え願いたいのですが・・
お時間のよろしいときにお願いできませんか？？
よろしくお願いいたします。