Moving Difference Using the MDIFF Function Teradata

Compatibility: Teradata Extension

The Moving Difference (MDIFF) function provides a moving difference on a column's value, based on a defined number of rows known as the query width. Since the MDIFF function does subtraction, it works differently than all the other OLAP functions. This difference is based on the fact that addition allows more than two numbers to be added at a time. Where as, subtraction operations can only be performed on two numbers at a time.

The syntax for MDIFF is:

The MDIFF command uses the first parameter as the column containing a numeric value to subtract.

The second parameter used by the MDIFF is the width number. The width determines how many rows back to count for the subtrahend. Valid values are from 1 to 4096. If width is greater than the number of rows in the table, all preceding rows will display a NULL for MDIFF. Therefore, the first rows displayed less than the width will always be represented by a NULL. Another distinction of the MDIFF is that a width of 1 might be a good value.

The third parameter is entered as a sort key that sequences the output rows. This column will be sorted to determine the sequence of the detail row data along with the MDIFF. By default, the sort sequence is ascending (ASC). The DESC can be specified to request a descending (highest to lowest) sequence. Optionally, additional sort keys can be entered to request one or more minor sort sequences.

The following SELECT shows a 2-day moving difference for product 1000:

7 Rows Returned2-day moving difference for product 1000

In the above output, the MDIFF represents the difference between the sales on any two days. Notice the MDIFF for the last row having Daily_Sales of 54553.10. The MDIFF had a width of two so the last rows Daily_Sales is calculated with the row two rows up. Thus, 54,553.10 subtracting 32800.50 gives a difference of 21752,60. Now, comes a different question. Why are there nulls in the first two rows?

Since the MDIFF needs to use a column value <width> rows ago, the first <width> rows will contain a NULL. This is due to the fact that until one more row beyond <width> has been read, there is no value for the subtrahend. Therefore, a missing value is NULL and the first <width> row will always be NULL because the result, of any math operation using a NULL, is a NULL.

The next SELECT is performing a 7-day moving difference for a weekly comparison between the products 1000 and 2000 based on all sales stored in the table:

14 Rows Returned7-day moving difference for a weekly comparison between the products 1000 and 2000 based on all sales stored in the table

Still, this output between products may not be meaningful enough by itself, even when values are present. One of the best uses for MDIFF is graphing the difference between two activity periods.

If the sales data for the previous year is available, the difference might constitute the same day a year ago. The data from previous and current years as well as the difference could then be plotted or graphed to show the increase or decrease in sales.

If the sales data was daily, the <width> might be 365 for the days in a year. The width might also be dependent on issues such as whether or not activity took place on a Sunday. To be as meaningful as possible, the data being compared might represent two different entities or two different time periods. Remember, this is subtraction and it involves two numbers at a time.

The MDIFF function is probably the only OLAP function where using a width of 1 makes sense. Since each row is a month, one day, one week, one month or one year it can easily be compared to the previous.

Moving Difference with Reset Capabilities

Like the other OLAP functions of SUM or AVG, the MDIFF can use the break capability. In order to obtain a break, the data in one or more columns must be monitored and when it changes, it causes the accumulation value to be reset back to zero. Then, the subtraction can start over for the data columns associated with the new value in the column being monitored.


Like all the other OLAP functions, MDIFF may also use the GROUP BY to designate one or more columns to monitor for a change. However, here it must be used carefully because of the subtraction aspect of its operation.

As shown in the next SELECT, care should be taken in attempts to show a 4-day moving difference for all products with only 2 days of data:

12 Rows ReturnedUsing MDIFF and GROUP BY

However, as shown in the following SELECT, care should be taken in attempts to show a 4- day moving difference for all products with only 4 days of data:

12 Rows Returned4- day moving difference for all products with only 4 days of data
4- day moving difference for all products with only 4 days of data

Don't get too detailed so that nothing is seen, as in the above output. Know what the data looks like to pick the best representation in the output. You cannot see 4 days worth of date difference when the data contains only 4 rows.

Also, notice that the above SQL uses two different versions of a date in the WHERE clause. The first is the numeric Teradata native format and the second is the ANSI version. The first could have been written as 1001001(date). However, since it is longer, the only advantage in doing so is the ease of understanding by another person.

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

Teradata Topics