Teradata Sql Extensions



Teradata specific SQL extensions

The article lists some Teradata specific SQL extensions which are not ANSI-compliant and might not be supported by some database engines:

  • Teradata provides a robust query execution plan analyzer. The EXPLAIN keyword in front of any SQL statement displays the execution plan for that statement
  • The functions listed below are Teradata extensions which are non ANSI-compliant. Most values can be obtained in ANSI SQL by using a combination of PARTITION BY and ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING statements.

    * WITH and WITH...BY - Count and Sum are ANSI SQL compliant function, Teradata introduces an extension to this function which is WITH and WITH...BY

    *CSUM – Teradata extension alternative for the calculation of cumulative sum
    usage: CSUM(columnname, sort1 [DESC], sort2 [DESC], ...)
    example: CSUM(sales_amt, invc_dte)

    *MSUM – Teradata extension alternative for the calculation of moving sum based on a Query Width value which indicates how many preceding rows should be used to compute sum
    usage: MSUM(columnname, sort1 [DESC], sort2 [DESC], ...)
    example: MSUM(columnname, query_width, sort1 [DESC], sort2 [DESC], ...)

    *MAVG – Teradata extension for the calculation of moving average based on a Query Width value which indicates how many preceding rows should be used for calculation
    usage: MAVG(columnname, sort1 [DESC], sort2 [DESC], ...)
    example: MAVG(columnname, query_width, sort1 [DESC], sort2 [DESC], ...)

    *MDIFF – Teradata extension for the calculation of moving difference.
    usage: MDIFF(columnname, sort1 [DESC], sort2 [DESC], ...)
    example: MDIFF (columnname, query_width, sort1 [DESC], sort2 [DESC], ...)