Moving Difference - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
fcf1492702067123.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

Given a column or expression, along with a width and sort expression list, this Ordered Analytical function derives a new column for each expression giving the moving difference of the expression when the rows are sorted by the sort expression list. The moving difference is calculated as the difference between the current value and the nth previous value of the expression, where N equals the width. The moving difference is NULL if there is no Nth-preceding row in the table or group.

In Teradata V2R4.1, this function is implemented using an enhanced version of MDIFF, a non-standard Teradata specific function. MDIFF may not be mixed in the same analysis with aggregation functions such as average, and partitioning is not supported. The SQL generated takes the form MDIFF (expression, width, sort expression list). The enhancement is the ability to compute the moving difference of a date expression, generating MDIFF (expression - DATE '1900-01-01', width, sort expression list).

In Teradata V2R5.0 and later releases, an equivalent version of Moving Difference is generated using the standard ordered analytical function SUM. In this case, Partition Columns may be specified, as with other standard ordered analytical functions. Note that the non-standard expression MDIFF (expression, width, sort expression list) is the same as the following.

expression - 	SUM(expression)
				OVER (ORDER BY sort expression list ROWS BETWEEN width
				PRECEDING AND width PRECEDING)

When dragging a Moving Difference function into a variable, the following tree element is created.

Variable Creation > Input > Variables: SQL Elements pane - Logical > Moving Difference

Sort expressions can be built up in the Sort Expressions folder, and if the system is V2R5.0 or later, Partition Columns can be built up in that folder (with V2R4.1 systems, Partition Columns are ignored). Columns and/or other expressions can be moved into the (empty) branch of the tree. The Width is specified using the Properties panel. Double-click on Moving Difference, or highlight it and click Properties.

Variable Creation > Input > Variables: SQL Elements pane - Logical > Moving Difference Properties

The default Width of 1 can be updated here.