DWH系の大量データの計算

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

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

2)会員マスタ一覧抽出→1人ずつ平均を求めて更新
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;
/

3)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倍もの差が出た。1)2)が30時間程度かかった。3)は5分未満。集計関数を実施する際は、一気に集計値を計算してから処理する方が劇的に高速な事がわかる。
- スポンサーリンク -