目的
サマリー行と選択された結果を返す方法を決めるブレーク(グループ化の条件とも呼ばれる)を指定します。
WITH句の一般的な用途は小計です。
WITH句とWITH文修飾子は機能が異なります。WITH修飾子を参照してください。
構文
構文要素
- WITH expression_1
- SELECT文によって満たされる条件が続くことを指定します。選択の結果の列の値にサマリー行(合計など)を指定します。expression_1には、列の値に適用する1つまたは複数の集約式を組み込むことができます。
- BY expression_2
- expression_1を適用する1つまたは複数の結果の式を指定します。BYは、WITHとともに使用する場合だけ有効です。
- 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句のBY節は、最下位のソート キーを定義します。
- 次のWITH句のBY節は、下から2番目のソート キーを定義します。
- 最後のWITH句は、メジャー ソート キーを定義します。
例: 単一のSELECT文での複数のWITH句の指定を参照してください。
ORDER BY句およびWITH句
ORDER BY句は、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の式リスト内に指定する必要はありません。
- 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);