17.05 - 例: ゼロを返すOLAP集約関数の動作 - 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
日本語 (日本)

QUALIFY句の検索条件に順序付き分析集約関数を指定すると、結果に0が返されます。

集約する値がない場合、順序付き分析集約関数はnullでなく0を返します。

この例は、まず、demogrテーブルの行を示します。これは、後続する現在の動作例に対して順序付き分析集約関数の結果を計算するために使用されるものです。

文は、値のない集約行を12行、返します。これは、Remaining Count(inc)列にnullでなく0を報告するものです。

     SELECT line, da, mon, inc, COUNT(inc) OVER(PARTITION BY mon
                                                ORDER BY mon, line
                                                ROWS BETWEEN 1 FOLLOWING
                                                AND UNBOUNDED FOLLOWING)
     FROM demogr
     WHERE yr = 94
     AND   mon < 13
     AND   da < 10;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
  line      da   mon                   inc  Remaining Count(inc)
------  ------  ----  --------------------  --------------------
  4803       3     1             234737.37                     0
  3253       2     1                     ?                     1
   625       4     2              46706.97                     0
  3853       3     4             282747.07                     0
  3687       1     5             172470.52                     0
   547       9     5              31848.56                     1
  2292       6     7             170411.66                     0
  5000       8     9              40548.61                     0
  3213       8     9             257858.55                     1
  3948       6    10             217091.30                     0
  2471       1    10             121299.65                     1
  1496       7    12                     ?                     0

この例は、順序付き分析COUNT関数を使用してdemogrに対する修飾句の現在の動作を示します。クエリーは、再度12行を返します。Remaining Count(inc)列にはnullでなく0が入ります。

     SELECT line, da, mon, inc, COUNT(inc) OVER(PARTITION BY mon
                                                ORDER BY mon, line
                                                ROWS BETWEEN 1 FOLLOWING
                                                AND UNBOUNDED FOLLOWING)
     FROM demogr
     WHERE yr = 94 
     AND mon < 13
     AND da < 10
     QUALIFY COUNT(inc) OVER(PARTITION BY mon ORDER BY mon, line
                             ORDER BY mon, line
                             ROWS BETWEEN 1 FOLLOWING
                             AND UNBOUNDED FOLLOWING) < 3 ;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
  line      da   mon                   inc  Remaining Count(inc)
------  ------  ----  --------------------  --------------------
  4803       3     1             234737.37                     0
  3253       2     1                     ?                     1
   625       4     2              46706.97                     0
  3853       3     4             282747.07                     0
  3687       1     5             172470.52                     0
   547       9     5              31848.56                     1
  2292       6     7             170411.66                     0
  5000       8     9              40548.61                     0
  3213       8     9             257858.55                     1
  3948       6    10             217091.30                     0
  2471       1    10             121299.65                     1
  1496       7    12                     ?                     0

この問合わせは、例に対する順序付き分析集約結果を計算するために使用されるdemogrテーブルから行を返します。この例は、QUALIFY句の検索条件に順序付き分析集約関数の結果を返す旧式のスタイルを示すものです。

このメソッドを使用することにより、Remaining Sum(1)列において値のない集約が0でなくnullとして返されます。

     SELECT line, da, mon, inc, SUM(1) OVER(PARTITION BY mon
                                            ORDER BY mon, line
                                            ROWS BETWEEN 1 FOLLOWING
                                            AND UNBOUNDED FOLLOWING)
     FROM demogr
     WHERE yr = 94
     AND   mon < 13
     AND   da < 10;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
  line      da   mon                   inc  Remaining Sum(1)
------  ------  ----  --------------------  ----------------
  4803       3     1             234737.37                 ?
  3253       2     1                     ?                 1
   625       4     2              46706.97                 ?
  3853       3     4             282747.07                 ?
  3687       1     5             172470.52                 ?
   547       9     5              31848.56                 1
  2292       6     7             170411.66                 ?
  5000       8     9              40548.61                 ?
  3213       8     9             257858.55                 1
  3948       6    10             217091.30                 ?
  2471       1    10             121299.65                 1
  1496       7    12                     ?                 ?

この例は、ゼロでなくnullを結果の行で返すための回避策として順序付き分析SUM(1)関数を使う方法を示します。クエリーは、再度12行を返しますが、Remaining Sum(1)列に0でなくnullとして値のない集約行を報告します。

     SELECT line, da, mon, inc, SUM(1) OVER(PARTITION BY mon
                                            ORDER BY mon, line
                                            ROWS BETWEEN 1 FOLLOWING
                                            AND UNBOUNDED FOLLOWING)
     FROM demogr
     WHERE yr = 94 
     AND   mon < 13
     AND   da < 10
     QUALIFY SUM(1) OVER(PARTITION BY mon ORDER BY mon, line
                                          ROWS BETWEEN 1 FOLLOWING
                                          AND UNBOUNDED FOLLOWING) < 3;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
  line      da   mon                   inc   Remaining Sum(1)
------  ------  ----  --------------------  ----------------
  4803       3     1             234737.37                 ?
  3253       2     1                     ?                 1
   625       4     2              46706.97                 ?
  3853       3     4             282747.07                 ?
  3687       1     5             172470.52                 ?
   547       9     5              31848.56                 1
  2292       6     7             170411.66                 ?
  5000       8     9              40548.61                 ?
  3213       8     9             257858.55                 1
  3948       6    10             217091.30                 ?
  2471       1    10             121299.65                 1
  1496       7    12                     ?                 ?

この例は、ゼロでなくnullを結果の行で返すための回避策として順序付き分析COUNT関数でNULLIFZEROを使う方法を示します。

     SELECT line, da, mon, inc, NULLIFZERO(COUNT(inc)
                                           OVER(PARTITION BY mon
                                                ORDER BY mon, line
                                                ROWS BETWEEN 1 FOLLOWING
                                                AND UNBOUNDED FOLLOWING)
     FROM demogr
     WHERE yr = 94 
     AND mon < 13
     AND da < 10
     QUALIFY NULLIFZERO(COUNT(inc)OVER(PARTITION BY mon
                                       ORDER BY mon, line
                                       ROWS BETWEEN 1 FOLLOWING
                                       AND UNBOUNDED FOLLOWING) < 3;
*** Query completed. 12 rows found. 5 columns returned.
*** Total elapsed time was 1 second.
  line      da   mon                   inc   Remaining Count(inc)
------  ------  ----  --------------------  --------------------
  4803       3     1             234737.37                     ?
  3253       2     1                     ?                     1
   625       4     2              46706.97                     ?
  3853       3     4             282747.07                     ?
  3687       1     5             172470.52                     ?
   547       9     5              31848.56                     1
  2292       6     7             170411.66                     ?
  5000       8     9              40548.61                     ?
  3213       8     9             257858.55                     1
  3948       6    10             217091.30                     ?
  2471       1    10             121299.65                     1
  1496       7    12                     ?                     ?