ITコーディネータのシュウです。

知人の方から頂いた、千葉港から富士山の方角を見た夕日の写真です。右下のほうに富士山が写ってます。晴れた日には、埼玉から富士山が見えるのですが、千葉からも見えるんですね。はじめて知りました。埼玉からのほうが少し大きく見えるような気がしますが、さすが日本一の高さを誇る富士山です。

久しぶりの投稿になります。また、開発の仕事が忙しくなってきました。へたり気味ですが、頑張って行くぞ！

そういえば、dbSheetClientのユーザー事例にウシオ電機様が載りました。様々なランプをはじめとする光製品の開発・販売などで世界的なトップシェア商品を多数持っている企業ですね。
詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/usrcase7.html

＜本日の題材＞
相関副問合せを利用したデータの更新

前回、EXISTS句について取り上げましたが、そのときに、相関副問合せでの例を示しました。今回は、更新の際に、相関副問合せを利用する場合を取り上げてみたいと思います。
(SQL Serverで検証)

通販システムなどで顧客管理をする際に、顧客の受注から顧客のランクを判別して、その顧客のランクによってサービスなどを区別する場合があると思います。
そのための項目「顧客ランク」を顧客マスタに追加したとします。

そして、例えば以下のように顧客のランクを定めて、あるタイミング毎に夜間バッチ処理にて更新を行うとします。
直近3ヶ月以内に
・1万円以上購入した方を「A」ランク
・5千円以上1万円未満の方を「B」ランク
・2千円以上5千円未満の方を「C」ランク
・それ以外（受注はあるが2千円未満）の方を「D」ランク
直近3ヶ月以内に受注がない方は NULL

直近3ヶ月以内の顧客の購入金額を検索してランクを区別する場合のSELECT文は、例えば以下のようにできます。

--①SQL文
SELECT顧客番号
      , SUM(決済金額) AS 購入金額
      , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
             WHEN SUM(決済金額) >= 5000 THEN 'B'
             WHEN SUM(決済金額) >= 2000 THEN 'C'
             ELSE 'D' END AS ランク
  FROM dbo.受注テーブル
 WHERE 受注日 >= DATEADD(MONTH, -3, GETDATE())
 GROUP BY 顧客番号;
--(10 行処理されました)

この抽出結果を元に顧客マスタの顧客ランクを更新するSQLとしては、例えば以下のようにできます。

--②SQL文
UPDATE顧客マスタ SET
   顧客ランク =
          (SELECT A.ランク
              FROM (SELECT 顧客番号
                                , SUM(決済金額) AS 購入金額
                                , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
                                                WHEN SUM(決済金額) >= 5000 THEN 'B'
                                                WHEN SUM(決済金額) >= 2000 THEN 'C'
                                                ELSE 'D' END AS ランク
                                  FROM dbo.受注テーブル
                                WHERE 受注日 >= DATEADD(month, -3, GETDATE())
                                GROUP BY 顧客番号) A
            WHERE A.顧客番号 =顧客マスタ.顧客番号
            );

相関副問合せを使用し、副問合せで抽出した顧客番号と顧客マスタの顧客番号が一致する行については、副問合せで抽出したランクの値で顧客ランクのデータを更新するようにしています。
また、一致する顧客番号が副問合せの抽出にないレコードについては、顧客ランクはNULLに更新されます。

例えば、現在の顧客ランクが以下のような場合：
--③SQL文(顧客ランクがNULL以外のデータを抽出)

SELECT顧客番号,顧客ランク
  FROM dbo.顧客マスタ
 WHERE 顧客ランク IS NOT NULL
 ORDER BY 顧客番号;
--(8行処理されました)

上記の②SQLを実行すると

UPDATE顧客マスタ SET
   顧客ランク =
          (SELECT A.ランク
              FROM (SELECT 顧客番号
                                , SUM(決済金額) AS 購入金額
                                , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
                                                WHEN SUM(決済金額) >= 5000 THEN 'B'
                                                WHEN SUM(決済金額) >= 2000 THEN 'C'
                                                ELSE 'D' END AS ランク
                                  FROM dbo.受注テーブル
                                WHERE 受注日 >= DATEADD(month, -3, GETDATE())
                                GROUP BY 顧客番号) A
            WHERE A.顧客番号 =顧客マスタ.顧客番号
            );

現在の顧客ランクを確認する（NULL以外のレコード抽出）
--上記③SQL文を実行した結果

--(10 行処理されました)

更新前は8件ありましたが、6行目～8行目のレコードは、顧客ランク「A」～「D」の抽出条件からはずれたためにNULLに更新されています。結果として、①SQL文で実行した10件が顧客ランクが設定されたかたちになります。

今回のケースは上記の方法でよいですが、②SQLで、副問合せで抽出される顧客番号のレコードのみを更新したいような場合には、WHEREの条件として、下記のようにEXISTS句を使用して条件を絞る必要があります。

WHERE EXISTS
   (SELECT 'x'
       FROM (SELECT 顧客番号
                         , SUM(決済金額) AS 購入金額
                         , CASE WHEN SUM(決済金額) >= 10000 THEN 'A'
                                          WHEN SUM(決済金額) >= 5000 THEN 'B'
                                          WHEN SUM(決済金額) >= 2000 THEN 'C'
                                          ELSE 'D' END AS ランク
                          FROM dbo.受注テーブル
                        WHERE 受注日 >= DATEADD(month, -3, GETDATE())
                         GROUP BY 顧客番号) A
    WHERE A.顧客番号 =顧客マスタ.顧客番号
 );

上記の条件を設定したかたちでUPDATEを行うと、10件のみが更新されることになります。

ですので、今回の処理についても、一旦全レコードの顧客ランク項目を全て「NULL」に更新してから、上記のWHERE EXISTS句を付けたUPDATE文で顧客ランクを設定するというかたちでもいいかと思います。

上記の内容は、ORACLEでも同様になります。

今日は以上まで

にほんブログ村