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:
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:
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:
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:
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:
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:
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.