DWH系の大量データの計算

≪課題≫
会員マスタ：5万件
　　会員ID(PK),会員名,平均購入金額,...
POSデータ：500万件
　　POSID(PK),会員ID(FK),取引日時,購入金額,...
のテーブルにおいて、POSデータの購入金額から平均購入金額を求めるて会員マスタの平均購入金額へ値を反映する場合、適切なINDEXが構成済みの環境下で、以下のどれが高速か？

１）UPDATE句＋副問い合わせで一発更新
UPDATE 会員マスタ SET 平均購入金額 = (
　SELECT AVG(購入金額) FROM POSデータ
　WHERE POSデータ.会員ID = 会員マスタ.会員ID
);

２）会員マスタ一覧抽出→１人ずつ平均を求めて更新
DECLARE
　CURSOR cur IS
　　SELECT 会員ID FROM 会員マスタ;
BEGIN
　FOR rec IN cur LOOP
　　UPDATE 会員マスタ SET 平均購入金額 = (
　　　SELECT AVG(購入金額) FROM POSデータ
　　　WHERE POSデータ.会員ID = rec.会員ID
　　)
　END LOOP ;
END;
/

３）GROUP句で会員毎の平均計算→会員マスタ更新
DECLARE
　CURSOR cur IS
　　SELECT 会員ID, AVG(購入金額) AS 平均 FROM POSデータ GROUP BY 会員ID;
BEGIN
　FOR rec IN cur LOOP
　　UPDATE 会員マスタ SET 平均購入金額 = rec.平均
　　WHERE 会員マスタ.会員ID = rec.会員ID;
　END LOOP ;
END;
/


結果は実に約360倍もの差が出た。１）２）が30時間程度かかった。３）は5分未満。集計関数を実施する際は、一気に集計値を計算してから処理する方が劇的に高速な事がわかる。
- スポンサーリンク -

関連する記事＆スポンサーリンク