SAX Example: Multiple-Input Version - Teradata Vantage

In the multiple-input version, the mean and standard deviation statistics are applied globally with MeanStats and the STDevStats tables, respectively.

Input

• InputTable: financial_data3, as in VARMAX Example: No Exogenous Model

SQL Call

The statistics are created from a query on InputTable table finance_data3 and grouped by column id.

SELECT * FROM SAX (
  ON finance_data3 AS InputTable PARTITION BY id ORDER BY period
  ON (SELECT id, AVG (expenditure) AS expenditure,
    AVG (income) AS income,
    AVG (investment) AS investment
  FROM finance_data3 GROUP BY id
  ) AS meanstats PARTITION BY id
  ON (SELECT id, STDDEV_SAMP (expenditure) AS expenditure,
    STDDEV_SAMP (income) AS income,
    STDDEV_SAMP (investment) AS investment
  FROM finance_data3 GROUP BY id
  ) AS stdevstats PARTITION BY id
  USING
  TargetColumns ('expenditure', 'income', 'investment')
  TimeColumn ('period')
  WindowType ('global')
  OutputStats ('true')
  Accumulate ('id')
) AS dt ORDER BY id;

Output

 id period_start period_end expenditure_saxcode                      expenditure_mean   expenditure_stdev  income_saxcode                           income_mean        income_stdev      investment_saxcode                       investment_mean   investment_stdev   
 -- ------------ ---------- ---------------------------------------- ------------------ ------------------ ---------------------------------------- ------------------ ----------------- ---------------------------------------- ----------------- ------------------ 
  1 1960q1       1969q4     aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbcccccdddd              714.2  385.0403375471998 aaaabbbbbbbbbbbbbbbbbbbbbbbbbbbcccccdddd              811.2 449.6825204861417 aaaaaaabbbbbabbbbbbbbcccccccbbbcbbccdddd             301.0 130.70871903116958
  2 1970q1       1979q4     ddddddddaaaaaaaaaaaabbbbbbbbbbbccccccccd             1355.6  404.8422801473085 ddddddddaaaaaaaaaaaabbbbbbbbbbcccccccccc           1589.275 470.7790590456392 ddddddddaaaaaaaabbbbbbbbcbbbcbbbbbbbbccc           556.675 142.19982103562614
  3 1980q1       1982q4     aaaacccddddd                             2045.1666666666667 256.61248936279327 aaaacccddddd                             2387.4166666666665 317.4965879081747 aaaacddccccc                             759.0833333333334 113.35259417437523

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.