MDIFF - Teradata Database

MDIFF

Purpose  

Returns the moving difference between the specified value expression for the current row and the preceding width rows for each row in the partition.

Type

Teradata-specific function.

Syntax  

where:

Syntax element …	Specifies …
value_expression	a numeric column or literal expression for which a moving difference 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 difference. 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, MDIFF(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. The expression cannot contain any ordered analytical or aggregate functions.
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.

Meaning of Moving Difference

A common business metric is to compare activity for some variable in a current time period to the activity for the same variable in another time period a fixed distance in the past. For example, you might want to compare current sales volume against sales volume for preceding quarters. This is a moving difference calculation where value_expression would be the quarterly sales volume, width is 4, and sort_expression might be the quarter_of_calendar column from the SYS_CALENDAR.Calendar system view.

Using SUM Instead of MDIFF

The use of MDIFF is strongly discouraged. It is a Teradata extension to the ANSI SQL:2011 standard, and is retained only for backward compatibility with existing applications. MDIFF(x, w, y) is equivalent to:

   x - SUM(x) OVER (ORDER BY y 

                    ROWS BETWEEN w PRECEDING AND w PRECEDING)

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 MDIFF are as follows:

If operand x is character, the data type is the same as x and the format is the default format for FLOAT.

If operand x is numeric, the data type is the same as x and the format is the same format as x.

If operand is date, the data type is INTEGER and the format is the default format for INTEGER.

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 rows you analyze using MDIFF have no missing data points. Computing a moving difference 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 the Moving Difference When No Preceding Row Exists

When the number of preceding rows to use in a moving difference computation is fewer than the specified width, the result is null.

Example  

Display the difference between each quarter and the same quarter sales for last year for product code 10.

   SELECT year_of_calendar, quarter_of_calendar,

   MDIFF(sumPrice, 4, year_of_calendar, quarter_of_calendar)

   FROM (SELECT a2.year_of_calendar,

   a2.quarter_of_calendar, SUM(a2.Price) AS sumPrice

   FROM Sales a1, SYS_CALENDAR.Calendar a2

   WHERE a1.itemID=10 and a1.calendar_date=a2.calendar_date

   GROUP BY a2.year_of_calendar, a2.quarter_of_calendar) AS T1

   ORDER BY year_of_calendar, quarter_of_year;

Example  

The following example computes the changing market volume week over week for the stock of company Horatio Parker Imports. The ticker name for the company is HPI.

   SELECT MarketWeek, WeekVolume, 

      MDIFF(WeekVolume,1,MarketWeek) AS HPIVolumeDiff

   FROM

   (SELECT MarketWeek, SUM(Volume) AS WeekVolume

   FROM MarketDailyClosing

   WHERE Ticker = 'HPI'

   GROUP BY MarketWeek)

   ORDER BY MarketWeek;

The result might look like the following table. Note that the first row is null for column HPIVolume Diff, indicating no previous row from which to compute a difference.

MarketWeek	WeekVolume	HPIVolumeDiff
11/29/1999	9817671	?
12/06/1999	9945671	128000
12/13/1999	10099459	153788
12/20/1999	10490732	391273
12/27/1999	11045331	554599