15.00 - MSUM - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

MSUM

Purpose  

Computes the moving sum specified by a value expression for the current row and the preceding n-1 rows. This function is very similar to the MAVG function.

Type

Teradata-specific function.

Syntax  

where:

 

Syntax element …

Specifies …

value_expression

a numeric literal or column expression for which a moving sum is to be computed.

The expression cannot contain any ordered analytical or aggregate functions.

width

the number of previous rows to be used in computing the moving sum.

The value is always a positive integer literal.

The maximum is 4096.

sort_expression

a literal or column expression or comma-separated list of literal or column expressions to be used to sort the values.

For example, MSUM(Sale, 6, Region ASC, Store DESC), where Sale is the value_expression, 6 is the width, and Region ASC, Store DESC is the sort_expression list.

ASC

that the results are to be ordered in ascending sort order.

If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session.

The default order is ASC.

DESC

that the results are to be ordered in descending sort order.

If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Using SUM Instead of MSUM

The use of MSUM is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard, and is equivalent to the ANSI-compliant SUM window function. MSUM is retained only for backward compatibility with existing applications.

For more information on the SUM window function, see “Window Aggregate Functions” on page 984.

Result Type and Attributes

The data type, format, and title for MSUM are as follows:

Data Type: Same as operand x

  • If operand x is character, the format is the default format for FLOAT.
  • If operand x is numeric, the format is the same format as x.
  • For information on the default format of data types, see “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Problems With Missing Data

    Ensure that data you analyze using MSUM has no missing data points. Computing a moving average over data with missing points produces unexpected and incorrect results because the computation considers n physical rows of data rather than n logical data points.

    Computing MSUM When Number of Rows < width

    For data having fewer than width rows, MSUM computes the sum using all the preceding rows. MSUM returns the current sum rather than nulls when the number of rows in the sample is fewer than width.

    Possible Result Overflow with SELECT Sum

    When using this function, the result can create an overflow when the data type and format are not in sync. For a column defined as:

    Salary Decimal(15,2) Format ‘$ZZZ,ZZ9.99’

    The following query:

    SELECT SUM (Salary) FROM Employee;

    causes an overflow because the decimal operand and the format are not in sync.

    To avoid possible overflows, explicitly specify the format for decimal sum to specify a format large enough to accommodate the decimal sum resultant data type.

    SELECT Sum(Salary) (format ‘$Z,ZZZ,ZZZ,ZZ9.99) FROM Employee;