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

 前回に続いて、アメリカに行ってきた知人の方から頂いた写真で、フーバーダムというラスベガスのそばにある有名なダムの写真です（走っている車の中から撮ったということ）。

ダムによってコロラド川がせき止められたことでできた湖（ダム湖）はミード湖と呼ばれ、貯水量は400億トン、日本にある2,500基のダムの貯水量の合計が250億トンであり、琵琶湖の貯水量が280億トン程度ということなので、如何に大きいかということがわかりますね。
このダムは、1931年世界恐慌のときに、ニューディール政策の一環として着工されて1936年に竣工し、当時は世界一の水力発電だったとのこと。その後、世界にはもっと大きなダムもできているということですが、よくこんな大きなダムを作れるものだと、人間の力は本当にすごいと思います。

それから、先日の父の日は、子供たちから一品ずつ、チョコとかちょっとしたものでしたが、プレゼントを頂きました。一番下の息子はカードを作ってくれて、とても素直な表現がうれしかったです。妻もいつもよりは少しだけ豪華な食事を用意してくれて、うれしい1日となりました。

＜本日の題材＞
UPDATE ～ FROM（その２）

前回、SQL ServerでのUPDATE文で、他のテーブルのデータに基づくデータの更新をUPDATE ～ FROM テーブルというかたちで実現できるということを説明しました。
そのときに、Oracleでの対応の仕方として、例では EXISTS句を使ったものを取り上げましたが、今回はインラインビュー（ビューでもよい）を使ったかたちでの更新を取り上げてみたいと思います。

構文としては、
UPDATE (
    SELECT A.col2 Acol2, B.col2 Bcol2
    FROM tbl_A  A
    JOIN tbl_B  B
    ON A.col1 = B.col1
    )
SET Acol2 = Bcol2;

ただし、UPDATEを行うのは更新可能なビュー（インラインビューも含む）でなければならず、また、テーブルの結合が1：n の場合には、1の親表の列のデータは更新できないということです。
（前回の例である、「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理では、テーブルの結合が１：nの場合に該当するため、このビューの更新の方法ではできません）

ちなみに、以下のような場合は更新できないビューになります。
　・GROUP BY句を使用したビュー
　・式により定義したビュー
　・DISTINCT句を使用したビュー
　・グループ関数を使用したビュー
　・擬似列ROWNUMを使用したビュー

例を以下に示します。
例）
社員マスタの入社日のデータが何らかの問題で消えてしまったとします。対応として、以前バックアップしていたテーブルの日付に戻したいという場合があるとします。当然、現在の社員マスタと、以前バックアップしていた社員マスタはDBのレイアウトは同じなので、データは1対１の関係です。
この場合、Oracleでの上記のやり方で更新するかたちでは以下になります。

 UPDATE (
    SELECT A.入社日, B.入社日 BK_入社日
    FROM 社員マスタ  A
    JOIN 社員マスタ_BAK  B
    ON A.社員番号 = B.社員番号
    )
SET 入社日 = BK_入社日;

 まず、社員マスタのデータを確認します。

SELECT 社員番号, 社員名, 入社日
  FROM 社員マスタ
 ORDER BY 社員番号;

入社日の値がNULLになっています。
次に、同様にして社員マスタ_BAKのデータ（入社日）を確認します。

 上記で示したように、社員マスタ_BAKの入社日のデータで社員マスタの入社日の値を更新します。

 更新後の社員マスタの入社日のデータを確認します。

 

 入社日がセットされているのが確認できます。
念のため、社員マスタの入社日と社員マスタ_BAKの入社日に差がないことを確認します。
上記から、社員マスタと社員マスタ_BAKがともに12件で同じ件数であることを利用して、下記の結果が同じく12件であれば、入社日に差がないことが確認できます。

 上記の入社日の更新を、SQL Serverで実施する場合は、前回説明したように、

UPDATE dbo.社員マスタ SET
  入社日 = dbo.社員マスタ_BAK.入社日
FROM dbo.社員マスタ
INNER JOIN dbo.社員マスタ_BAK
   ON dbo.社員マスタ.社員ID = dbo.社員マスタ_BAK.社員ID;

とするか、JOINを使わずに

UPDATE dbo.社員マスタ SET
  入社日 = SB.入社日
FROM dbo.社員マスタ_BAK SB
WHERE dbo.社員マスタ.社員ID = SB.社員ID;

とすることもできます。
以下は実際に実行したところです。

 念のため結果を確認すると、

 件数が12件で同じため、社員マスタの入社日は社員マスタ_BAKの入社日と同じものに更新されたことがわかります。

※ちなみに、前回の例としての「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理に、今回のやり方で更新する方法で実行してみると、以下のようにエラーが出ますね。

 UPDATE (
   SELECT A.累計購入金額 A_累計購入金額, B.購入金額 B_購入金額
    FROM 顧客購買集計 A
     JOIN 顧客日次購買 B
       ON A.顧客ID = B.顧客ID
    WHERE B.購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD')
       ) C
 SET C.A_累計購入金額 = C.A_累計購入金額 + C.B_購入金額;

 エラーは「ORA-01779：キー保存されていない表にマップする列は変更できません」となります。この「キー保存されていない表」というのが結合の１：ｎとなるときの親表のことです。

 今日は以上まで

にほんブログ村

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

知人の方が最近アメリカのラスベガスやグランドキャニオンなどに行ってきたということで、写真をいろいろと見せてもらったのですが、上の写真はグランドキャニオンで撮ってきたものです。

グランドキャニオンは、表層に近いほうで2億5千万年前、深層部では20億年前の地層と言われていて、それだけの長い期間の地層を見れるのは、世界的にもここだけらしいです。
谷までの深さは、1600m というところもあるとのことで、崖から落ちたら一巻の終わりですね。
奇跡とも言えるあまりの自然の壮大なスケールに、人生観も変わってしまうほどだと話に聞きますが、私も一生に一度は是非訪れてみたいものです。
写真は、観光スポットでガードレールのような柵があるところですが、下はまさに断崖絶壁になっていますね。ただ、この景色を直接見たら、本当にすごいでしょうね！

＜本日の題材＞
UPDATE ～ FROM（他のテーブルのデータを基に更新）

SQL Serverでの仕事をしているときに、こういうSQLの書き方もあるんだと気が付いた内容について上げてみたいと思います。
SQL Serverでは他のテーブルのデータに基づくデータの更新をUPDATE ～ FROM テーブルというかたちで実現できるということを、わりと最近になって知りました。Oracleでは上記の構文はUPDATE句にはなかったので、なかなか気が付かなかったのですが、今回はそれについて取り上げてみたいと思います。

構文は、

UPDATE  tbl_A  SET
    tbl_A.col1 = tbl_B.col1
FROM tbl_A
INNER JOIN tbl_B
  ON  tbl_A.key_column = tbl_B.key_column;

例）
仮に、顧客の毎日の購買履歴を記録するテーブルが「顧客日次購買」として存在する場合に、顧客の過去の購入に関する集計用のテーブル「顧客購買集計」に対して、累計購入金額を前日の購入金額をプラスして更新することを、上記の構文を元に実行してみます。

「顧客日次購買」テーブルの定義：
顧客ID            VARCHAR(10)
購入日              DATE
購入金額              DECIMAL(10)
※主キーは、顧客ID, 購入日

「顧客購買集計」テーブルの定義：
顧客ID            VARCHAR(10)
累計購入金額       DECIMAL(12)
※主キーは、顧客ID

一昨日までの顧客毎の累計金額を登録した「顧客購買集計」テーブルに、昨日分の購入金額をプラスするということで、累計購入金額を更新する処理を行うとします。

まず、一昨日までの累計購入金額を確認します。

SELECT * FROM dbo.顧客購買集計
 ORDER BY 顧客ID;

 昨日の購入があった顧客の、「顧客日次購買」テーブルのデータを確認します。

SELECT * FROM dbo.顧客日次購買
 WHERE 購入日 = CONVERT(VARCHAR, getdate()-1, 111)
 ORDER BY 顧客ID;

 「顧客購買集計」テーブルに、昨日分の購入金額をプラスして、累計購入金額を更新する処理を行います。

UPDATE dbo.顧客購買集計 SET
  累計購入金額 =累計購入金額 + dbo.顧客日次購買.購入金額
FROM dbo.顧客購買集計
INNER JOIN dbo.顧客日次購買
   ON dbo.顧客購買集計.顧客ID = dbo.顧客日次購買.顧客ID
  AND dbo.顧客日次購買.購入日 = CONVERT(VARCHAR, getdate()-1, 111);

更新後の「顧客購買集計」テーブルの結果を確認します。

SELECT * FROM dbo.顧客購買集計
 ORDER BY 顧客ID;

 4人の顧客(K0000011,K0000021,K0000043,K0000076)については、昨日の購入金額分が累計購入金額に足されていることが確認できました。

同様の処理をOracleで行う場合、同じ設定のSQL文ではエラーになります。

UPDATE 顧客購買集計 SET
  累計購入金額 = 累計購入金額 + 顧客日次購買.購入金額
FROM 顧客購買集計
INNER JOIN 顧客日次購買
   ON 顧客購買集計.顧客ID = 顧客日次購買.顧客ID
  AND 顧客日次購買.購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD');

 Oracleでは、このケースでは以下のようなかたちになるかと思います。

UPDATE 顧客購買集計 SET
  累計購入金額 = 累計購入金額 +
                  (SELECT 購入金額
                     FROM 顧客日次購買
                    WHERE 顧客ID = 顧客購買集計.顧客ID
                      AND 購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'))
 WHERE EXISTS (SELECT 'x' FROM 顧客日次購買
                WHERE 顧客ID = 顧客購買集計.顧客ID
                  AND 購入日 = TO_CHAR(SYSDATE-1, 'YYYY/MM/DD'));

 ※上記はSQL Serverでも同様の処理が可能です。

一応、更新後の結果を確認すると

SELECT * FROM 顧客購買集計
 ORDER BY 顧客ID;

 先ほどのSQL Serverと同様の結果が得られました。

今日は以上まで

にほんブログ村

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でも同様になります。

今日は以上まで

にほんブログ村