17.05 - Example: Behavior of OLAP Aggregate Functions That Return Zeros - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

When you specify an ordered analytical aggregate function in the search condition of a QUALIFY clause, it can return a result of 0.

When there are no values to aggregate, ordered analytical aggregate functions return a 0 instead of a null.

This example first shows the rows in the demogr table that are used to calculate the ordered analytical aggregate result for the examples of current behavior that follow.

The statement returns 12 rows with valueless aggregate rows reported with zeros rather than nulls in the Remaining Count(inc) column.

     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

This example shows the current behavior of a qualify clause over demogr using the ordered analytical COUNT function. The query again returns 12 rows, with zeros in place of nulls in the Remaining Count(inc) column.

     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

This query returns the rows from the demogr table used to calculate the ordered analytical aggregate result for the examples that show the old style of returning results of an ordered analytical aggregate function specified in the search condition of a QUALIFY clause.

By using this method, valueless aggregates are returned as nulls in the Remaining Sum(1) column rather than as zeros.

     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                     ?                 ?

This example shows how to use a ordered analytical SUM(1) function as a workaround to return nulls in the result instead of zeros. The query again returns 12 rows, but reports valueless aggregate rows as nulls rather than zeros for the Remaining Sum(1) column.

     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                     ?                 ?

This example shows how to use NULLIFZERO with the ordered analytical COUNT function as a workaround to return nulls in the result instead of zeros.

     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                     ?                     ?