Cumulative and Moving SUM Using SUM / OVER Teradata

Compatibility: ANSI

An accumulative sum can now be obtained using ANSI standard syntax. This process is requested using the SUM and by requesting an OVER option. This option causes the aggregate to act like an OLAP function. It provides a running or cumulative total for a column's numeric value. This allows users to see what is happening with certain column totals over an ongoing progression. The results will be sorted in ascending or descending order and the sort list can consist of a single or multiple columns as sort keys.

The following ANSI syntax is used with SUM to provide CSUM OLAP functionality:

Unlike the SUM used as an aggregate, the major difference is the OVER specification. Within the OVER is the ORDER BY request. It may specify a single column or multiple columns in a comma-separated list. It is not optional and it is part of the SUM / OVER. The default sequence is ascending (ASC).

Adding a second and final ORDER BY would be as incorrect here as it was with the CSUM. The ORDER BY must be specified in the OVER. Whereas, the CSUM function simply uses one or more columns in the sort list.

The optional ROWS UNBOUNDED portion indicates the width or number of rows to use. The UNBOUNDED specification allows all rows to participate in the addition.

Below, the SELECT shows the cumulative sales using the SUM / OVER:


14 Rows Returned

cumulative sales using the SUM / OVER
cumulative sales using the SUM / OVER

To make the output a bit more organized, it might help to have all of the sales for a particular product together. To accomplish this sequencing, the product ID column must also be used in the sort. The next SELECT adds the product ID to the ORDER BY:


14 Rows Returned

Cumulative and Moving SUM Using SUM / OVER
Cumulative and Moving SUM Using SUM / OVER

Like the CSUM, had a second ORDER BY been used, the output would be wrong and look like the SUM had been reset. Now, with that being said, there are times when it is beneficial to have the accumulation process reset. For instance, maybe product subtotals are needed instead of the total for all products.

To make the output a moving sum, the range of the rows to use must be established. The next SELECT adds ROWS 2 (like width of 3) and the product ID to the ORDER BY:


14 Rows Returnedproduct ID to the ORDER BY
product ID to the ORDER BY

This output is the same as a MSUM with a width of 3. However, here it is required to specify the use of the 2 preceding rows.

Cumulative Sum with Reset Capabilities

As mentioned with the CSUM, this version of the cumulative sum can be reset a to provide the equivalent of a subtotal. This method uses the ANSI Standard SUM with OVER and PARTITION BY designators to specify a data value that, when it changes, causes the accumulation value to be reset back to zero.

SUM Using SUM and OVER / PARTITION BY

Normally aggregate functions and OLAP functions are incompatible. This is because aggregates provide only the final single row answer and eliminate row detail data. Conversely, the OLAP functions provide the row detail data and the answer. Using this ANSI syntax, the SUM aggregate can be made to act more as an OLAP function to provide both the answer and the row detail.

As seen above, CSUM is a Teradata Extension. It may use the GROUP BY designation to reset the accumulation process for the equivalent of a subtotal. The ANSI method does not use GROUP BY. Instead, it uses the OVER to design that a partition or group can be established using the PARTITION BY designator.

One of the major advantages to the PARTITION is that each column that is a SUM can be based on a different value. Whereas, there can only be a single GROUP BY in a SELECT.

The following ANSI syntax used with SUM to provide OLAP functionality:

SELECT SUM(<column-name>) OVER ( PARTITION BY <column-name>ORDER BY <column-name> [ASC | DESC][,<column-name> [ASC | DESC] ] ) FROM <table-name> ;

Below, the previously used SELECT is again modified with SUM and OVER / PARTITION to show the equivalent cumulative sales with a reset on a change in the date as seen with the CSUM:


9 Rows Returnedreset on a change in the date as seen with the CSUM

The PARTITION has the same effect here as the GROUP BY does in the proprietary Teradata extension OLAP functions.

There are two advantages to using this syntax. First, it is the ANSI standard. Second, and the biggest advantage, is that it is compatible with other OLAP functions because the detail data is retained as well as the use of aggregate functions within the formulas for derived data.

Now that the daily total has been seen above, it might be useful to see totals by product. To accomplish this, the product ID needs to be part of the ORDER BY and the PARTITION must be based on the product ID:


9 Rows Returnedproduct ID needs to be part of the ORDER BY and the PARTITION must be based on the product ID

Now that the SUM OVER has been shown, to this point, there has not been demonstrated a compelling reason to use it instead of the MSUM or CSUM extensions.

The following shows the true benefit of these new OLAP functions for mixing them with the original OLAP functions:


2 Rows Returnedtrue benefit of these new OLAP functions for mixing them with the original OLAP functions

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics