16.20 - WITH句 - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQLデータ操作言語

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
2019年3月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-162K-JPN
Language
日本語 (日本)

目的

サマリー行と選択された結果を返す方法を決めるブレーク(グループ化の条件とも呼ばれる)を指定します。

WITH句の一般的な用途は小計です。

WITH句とWITH文修飾子は機能が異なります。WITH修飾子を参照してください。

構文



構文要素

WITH expression_1
SELECT文によって満たされる条件が続くことを指定します。選択の結果の列の値にサマリー行(合計など)を指定します。expression_1には、列の値に適用する1つまたは複数の集約式を組み込むことができます。
WITH expression_1リストにLOB列を含めることはできません。
NORMALIZEにWITH句を含めることはできません。
BY expression_2
expression_1を適用する1つまたは複数の結果の式を指定します。BYは、WITHとともに使用する場合だけ有効です。
expression_2では、名前によって、または式リストの式の位置を指定する定数によって、選択式リスト内の式を参照できます。また、スカラーsubqueryも参照できます。
BYexpression_2リストにLOB列を含めることはできません。
ASC
結果が昇順でソートされます。
ソート フィールドが文字列の場合、システムは現在のセッションの照合順序の定義に従って、昇順で整列します。
デフォルトの順序はASCです。
DESC
結果が降順でソートされます。
ソート フィールドが文字列の場合、システムは現在のセッションの照合順序の定義に従って、降順でソートします。

ANSI準拠

WITH句は、ANSI SQL:2011規格に対するTeradata拡張機能です。

UDT列およびWITH句

WITH句にUDT列を含めることはできません。

WITH … BY句のBY指定内のスカラー サブクエリー

問合わせに含まれるWITH ... BY句のBY指定に、列式またはパラメータ化された値として、スカラー サブクエリーを指定することができます。

WITH句の引数としてスカラーsubqueryを指定することはできません。これは、集約式のみがWITH句の引数として有効なためです。ただし、スカラーsubqueryを集約式の引数としてWITH句に指定することはできます。

LOB列およびWITH句

WITH句にLOB列を含めることはできません。

TOP演算子およびWITH文修飾子

TOP n演算子を指定したSELECT文に、WITH文修飾子を指定することはできません。

SELECT文内の複数のWITH句

複数のWITH句をSELECT文で指定して、異なる種類のサマリーを指定することができます。連続する各WITH句は、レベル別の行のグループ化を以下のとおりに示します。
  • 最初のWITH句のBY節は、最下位のソート キーを定義します。
  • 次のWITH句のBY節は、下から2番目のソート キーを定義します。
  • 最後のWITH句は、メジャー ソート キーを定義します。

    例: 単一のSELECT文での複数のWITH句の指定を参照してください。

ORDER BY句およびWITH句

ORDER BY句は、WITH句の前後に指定することができます。ORDER BY句も参照してください。
同じSELECT文にWITH句とORDER BY句を指定すると、以下のように機能します。
  • WITH句は、メジャー ソート キーを定義します。
  • ORDER BY句は、マイナー ソート キーを定義します。

    このことは、問合わせの構造、またはWITH句の数にかかわらず適用されます。

    例: WITH句とORDER BY句の組み合わせを参照してください。

SELECT文内のWITH句とGROUP BY句

WITH句とGROUP BY句をSELECT文内で組み合わせることはできません。

同じSELECT文にWITH句とGROUP BY句とを指定すると、意図しない結果になることがあります。

例: WITH句とORDER BY句の組み合わせを参照してください。

GROUP BY句も参照してください。

式およびWITH句

指定するexpression_2で、サマリー行が生成される場所が決まります。例えば、BY dept_noは、dept_no列の各値に対してサマリーを生成することを示します。サマリー行は、部門番号の各値が列挙された後に続けて生成されます。

BY句を指定しない場合、サマリー行は、SELECTの式リストによって指定された全体の結果に適用されます。

ORDER BY句と同じように、expression_2で指定したすべての式の値は、昇順または降順でソートできます。たとえば、次のようになります。

     WITH SUM(salary) BY divno ASC, dept_no DESC

同様に、expression_2には、SELECTの式リスト内の位置によって式を参照する定数を指定することもできます。たとえば、次のようになります。

     WITH SUM(salary) BY 2 ASC, 3 DESC

ただし、expression_1またはexpression_2で指定する式を、SELECTの式リスト内に指定する必要はありません。

以下の式をWITH句で指定できます。
  • SUM、AVERAGE、COUNT、MIN、またはMAXなどの集約演算子によって操作される式。

    集約演算子は、その演算子が適用される各列の直前に、例えばWITH SUM(salary)またはMAX(yrsexp)のように指定しなければなりません。

  • BY句に入っている列値に関連する式。例えば、WITH dept_no, SUM(salary) BY dept_no

    WITH句にLOBまたはUDT列が入っている式を指定することはできません。

TITLE句およびWITH句

TITLE句を使用して、expression_1およびSELECTの式リストに載っている任意の有効な式の表題を指定することができます。TITLE節は括弧で囲み、それを適用する式の後に続けなければなりません。

表題は、FieldModeでの出力を行なうレポート生成にのみ適切であり、通常は、BTEQを通じてのみ実行されます。

次の句では、Subtotalという表題が各サマリー行に指定されます。

     WITH SUM(salary)(TITLE 'Subtotal')

次の句では、空白の表題が指定されています。

     WITH SUM(salary)(TITLE ' ')

例: 各従業員の明細行および各部門の小計行の指定を参照してください。

例: 各従業員の明細行および各部門の小計行の指定

次の文では、各従業員の明細行と部門の小計行を示した部門別の給与報告書を作成します。

     SELECT name, dept_no, salary
     FROM employee
     WITH SUM(salary) BY dept_no;

返される結果は次のとおりです。

     name         dept_no       salary
     ----         -------       ----------
     Peterson J     100         25,000.00
     Moffit H       100         35,000.00
     Jones M        100         50,000.00
     Chin M         100         38,000.00
     Greene W       100         32,500.00
                                ----------
                   Sum(Salary) 180,500.00
     Leidner P       300        34,000.00
     Phan A          300        55,000.00
     Russell S       300        65,000.00
                               ----------
                   Sum(Salary)  154,000.00

例: 単一のSELECT文での複数のWITH句の指定

次の文では、部門別に並んだ従業員の給与報告書を作成します。各部門の合計給与のサマリー行と、組織全体の合計給与のための最後のサマリー行を備えています。

     SELECT name, dep_tno, salary
     FROM employee
     WITH SUM(salary) BY dept_no
     WITH SUM(salary);

返される結果は次のとおりです。

     name        dept_no        salary
     ----------  -------   -----------
     Peterson J      100     25,000.00
     Moffit H        100     35,000.00
     Jones M         100     50,000.00
     Chin M          100     38,000.00
     Greene W        100     32,000.00
                            ----------
                Sum(Salary) 180,000.00
     Leidner P       300     34,000.00
     Phan A          300     55,000.00
     Russell S       300     65,000.00
                           ----------
                Sum(Salary) 154,000.00
     Smith T         500       42,000.00
     Clements D      700     38,000.00
                            ----------
                Sum(Salary) 113,000.00
                            ----------
                Sum(Salary) 851,000.00

例: WITH句とORDER BY句の組み合わせ

次の文では両方ともORDER BY句を使用して、各dept_noグループ内の従業員の名前を昇順でソートします。

     SELECT name, dept_no, salary
     FROM employee
     ORDER BY name
     WITH SUM(salary) BY dept_no
     WITH SUM(salary);
     SELECT name, dept_no, salary
     FROM employee
     WITH SUM(salary) BY dept_no
     WITH SUM(salary)
     ORDER BY name;

返される結果は次のとおりです。

     name        dept_no        salary
     ----------  -------   -----------
     Chin M          100     38,000.00
     Greene W        100     32,500.00
     Jones M         100     50,000.00
     Moffit H        100     35,000.00
     Peterson J      100     25,000.00
                            ----------
                 Sum(Salary)   180,500.00
        .             .        .
        .             .        .
        .             .        .
     Brangle B       700     30,000.00
     Clements D      700     38,000.00
     Smith T         700     45,000.00
                            ----------
                 Sum(Salary)   113,000.00
                            ----------
                 Sum(Salary)   851,100.00

いずれかのソート キー列に英大文字/英小文字が混在して入力された文字データが入っていた場合、WITH...BYまたはORDER BYから作成された結果は、CASESPECIFICオプションがその列に定義されていたかどうかと、そのセッションで作用中の照合によって異なり、結果は予測できません。ORDER BY句と<Teradata Vantage™ SQLデータ定義言語 - 詳細トピック、B035-1184>を参照してください。

GROUP BY句とWITH句の組み合わせにより、意図しない結果セットが返されないように注意して文を記述します。次の例では、GROUP BY句とWITH句を組み合わせた記述にミスがあった場合に起こりうる問題について説明します。

次の文は、従業員の給与の総合計およびdept_no別の給与合計の報告書を作成します。

     SELECT dept_no, SUM(salary)
     FROM employee
     GROUP BY dept_no
     WITH SUM(salary);

返される結果は次のとおりです。

     dept_no    Sum(Salary)
     -------   -----------
         100    180,500.00
         300    143,000.00
         500    268,000.00
         600    146,600.00
         700    113,000.00
               -----------
      Sum(Sa    851,100.00

予想したとおりに、WITH句はすべての部門の合計給与のサマリー行を作成します。サマリーの表題は、dept_no列の幅が狭いため切り捨てられます。

必要のないBY句がWITH句内に入っていると、以下のクエリー例の後のレポートに示されているとおり、各部門の給与合計の後に冗長なサマリー行が生成されます。

     SELECT dept_no, SUM(salary)
     FROM employee
     GROUP BY dept_no
     WITH SUM(salary) BY dept_no;
     dept_no   Sum(Salary)
     -------   ----------
     100       180,500.00
               ----------
     Sum(Sa    180,500.00
               .
               .
               .
     700        113,000.00
               -----------
     Sum(Sa     113,000.00

例: SELECT文のWITH ... BY句にあるスカラー サブクエリー

次に示す例では、BY句にスカラー サブクエリー(SELECT prod_name ..。)を指定しています。

     SELECT SUM(amount)
     FROM sales_table AS s
     WITH AVG(amount) BY (SELECT prod_name
                          FROM prod_table AS p
                          WHERE p.prod_no = s.prod_no);