SQL 概説

表式

単一の照会のためだけに視点の定義が必要となる場合には、 表式 を使うことができます。

表式は一時的なものであり、それが有効なのはその SQL ステートメントの存続期間だけです。 表式は視点と違って共有できませんが、視点よりも柔軟性があります。

ここでは、照会の中での共通表式およびネストされた表式の使い方について説明します。

ネストされた表式

ネストされた表式は、メインの照会の FROM 文節の中で定義がネストされている (直接定義されている) 一時視点です。

次の照会では、 ネストされた表式を使うことによって、 教育レベル (EDLEVEL) が 16 を超える人について、 所得合計 (TOTAL_PAY) の平均、教育レベル、 および採用年 (HIREYEAR) を調べています。

     SELECT EDLEVEL, HIREYEAR, DECIMAL(AVG(TOTAL_PAY),7,2)
        FROM (SELECT EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
                     SALARY+BONUS+COMM AS TOTAL_PAY
                 FROM EMPLOYEE
                 WHERE EDLEVEL > 16)  AS PAY_LEVEL
        GROUP BY EDLEVEL, HIREYEAR
        ORDER BY EDLEVEL, HIREYEAR	

結果は、次のとおりです。

     EDLEVEL HIREYEAR    3        
     ------- ----------- ---------
          17        1967  28850.00
          17        1973  23547.00
          17        1977  24430.00
          17        1979  25896.50
          18        1965  57970.00
          18        1968  32827.00
          18        1973  45350.00
          18        1976  31294.00
          19        1958  51120.00
          20        1975  42110.00

この照会では、ネストした表式を使うことによって、 まず HIREDATE 列から採用年を抽出し、 次にそれを GROUP BY 文節で使うようにしています。 EDLEVEL の値をいろいろに変えて同様の照会を実行しようとする場合、 これを視点として作成しないほうがよいでしょう。

この例では、スカラー組み込み関数 DECIMAL が使われています。 DECIMAL は、数値または文字ストリングを 10 進数として戻します。 関数については、 SQL 解説書 を参照してください。

共通表式

共通表式 は、 複雑な照会で一貫して使用するために作成する表式です。 照会の最初に、WITH 文節を使って定義し、名前を付けます。 共通表式を繰り返して参照した場合、 同じ結果セットが使われます。 これに対し、もしネストされた表式や視点を使うとすれば、 いちいち結果セットを生成し直すことになり、 生成するたびにその結果が違うという可能性も出てきます。

次の例では、全社員の中で、教育レベルが 16 を超える人のうち、 同じ年に採用された同じ教育レベルの人の平均所得を下回っている人のリストを出力します。 この照会の各部分について、この後さらに詳しく説明します。

(1)

     WITH
           PAYLEVEL AS
              (SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
                      SALARY+BONUS+COMM AS TOTAL_PAY
                  FROM EMPLOYEE
                  WHERE EDLEVEL > 16),

(2)

           PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
              (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
                  FROM PAYLEVEL
                  GROUP BY EDLEVEL, HIREYEAR)

(3)

     SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)
        FROM PAYLEVEL, PAYBYED
        WHERE EDLEVEL = EDUC_LEVEL
          AND HIREYEAR = YEAR_OF_HIRE
          AND TOTAL_PAY < AVG_TOTAL_PAY                        

(1)
これは、PAYLEVEL という名前の共通表式です。 この結果表には、従業員番号、従業員の採用年、その従業員の総所得、 およびその教育レベルが含まれています。 その中に含まれるのは、教育レベルが 16 を超える従業員の行だけです。

(2)
これは、PAYBYED (PAY BY EDucation、教育別所得) という名前の共通表式です。 この中では、その前の共通表式で作成された PAYLEVEL 表を使って、 各教育レベルごと、また同じ年に採用された人ごとに、 従業員の教育レベル、採用年、および平均所得を調べています。 この表で戻される列には、 選択リストで使われている列名とは違う名前が付けられています (たとえば EDUC_LEVEL)。 これによって生成される結果セット (PAYBYED) は、 ネストされた表式の例で生成された結果と同じものです。

(3)
最後は、希望する結果を生成するための実際の照会です。 2 つの表 (PAYLEVEL、PAYBYED) を結合することにより、 総所得が、同じ年に採用された人の中での平均所得を下回っている人を調べます。 PAYBYED は PAYLEVEL に基づいていることに注意してください。 そのため、 ステートメント全体を通じて PAYLEVEL に 2 回アクセスすることにより、 効率のよいものとなっています。 2 回とも、照会の評価には同じ行集合が使われます。

最終的な結果は次のとおりです。

     EMPNO  EDLEVEL YEAR_OF_HIRE TOTAL_PAY     5        
     ------ ------- ------------ ------------- ---------
     000210      17         1979      20132.00  25896.50
     


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]