17.05 - WITH句の例 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLデータ操作言語

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
2021年1月
Content Type
プログラミング リファレンス
Publication ID
B035-1146-175K-JPN
Language
日本語 (日本)

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

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

     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);