15.00 - MDIFF - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

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