【KPI】WITH句【oracle】

WITH句は、特定のSELECT文を、SQL内で何度も使用したい場合に使う。

テーブル「TBL_1」と「TBL_2」がある場合。

・TBL_1

キー	値段
1	10
2	20
3	30
4	40

・TBL_2

キー	値段
1	100
2	200
3	300

下記のようなSELECT文を何度も参照する場合

SELECT TBL_1.キー, TBL_1.値段, TBL_2.値段 AS 値段2
FROM TBL_1, TBL_2
WHERE TBL_1.キー = TBL_2.キー

ちなみに上記結果は下記のようなものとなる。

キー	値段	値段2
1	10	100
2	20	200
3	30	300

実際にSQLに盛り込むとこうなる。

SELECT B.キー, A.値段, A.値段2, B.値段 AS 値段3 
FROM
(
    SELECT TBL_1.キー, TBL_1.値段, TBL_2.値段 AS 値段2
    FROM TBL_1, TBL_2
    WHERE TBL_1.キー = TBL_2.キー
) A
, (
    SELECT TBL_1.キー, NVL(TBL_3.値段, 0), NVL(TBL_3.値段2, 0) + NVL(TBL_1.値段, 0) AS 値段
    FROM 
    (
        SELECT TBL_1.キー, TBL_1.値段, TBL_2.値段 AS 値段2
        FROM TBL_1, TBL_2
        WHERE TBL_1.キー = TBL_2.キー
    ) TBL_3
    , TBL_1
    WHERE TBL_1.キー = TBL_3.キー(+)
) B
WHERE A.キー(+) = B.キー

結果はこう。

キー	値段	値段2	値段3
1	10	100	120
2	20	200	240
3	30	300	360
4			40

上記のSQLも、WITH句を使えばこうなる。

WITH TBL_3 AS
(
    SELECT TBL_1.キー, TBL_1.値段, TBL_2.値段 AS 値段2
    FROM TBL_1, TBL_2
    WHERE TBL_1.キー = TBL_2.キー
)
SELECT B.キー, A.値段, A.値段2, B.値段 AS 値段3 
FROM
  TBL_3 A
, (
    SELECT TBL_1.キー, NVL(TBL_3.値段, 0), NVL(TBL_3.値段2, 0) + NVL(TBL_1.値段, 0) AS 値段
    FROM 
      TBL_3
    , TBL_1
    WHERE TBL_1.キー = TBL_3.キー(+)
) B
WHERE A.キー(+) = B.キー

結果は上記と同じ。

---

※実行計画に注意。

下記は複数のWITH句を宣言する場合

WITH TBL_3 AS
(
    SELECT TBL_1.キー, TBL_1.値段, TBL_2.値段 AS 値段2
    FROM TBL_1, TBL_2
    WHERE TBL_1.キー = TBL_2.キー
)
, TBL_4 AS
(
    SELECT TBL_1.キー, NVL(TBL_1.値段, 0) * (NVL(TBL_3.値段, 0) + NVL(TBL_3.値段2, 0)) AS 値段
    FROM TBL_1, TBL_3
    WHERE TBL_1.キー = TBL_3.キー
)

上記のように複数のWITH句、更にWITH句の中でWITH句を使用すると、実行順がおかしくなる場合がある。
この際、結果が乱れる、乱れた。
可能なら「/*+ MATERIALIZE */」などをWITH句に挿入して、結果が乱れないようにすること。