1.1 - 8.10 - SAX Example: Sliding Window, Default Output - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Published
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

SQL Call

SELECT * FROM SAX (
  ON finance_data3 PARTITION BY id ORDER BY period
  USING
  TargetColumns ('expenditure')
  TimeColumn ('period')
  WindowType ('sliding') 
  WindowSize (20)
  OutputStats ('true')
  Accumulate ('id')
) AS dt ORDER BY 1, 2;

Output

 id period_start period_end expenditure_saxcode  expenditure_mean expenditure_stdev  
 -- ------------ ---------- -------------------- ---------------- ------------------ 
  1 1960q1       1964q4     aaaaaabbbcccccdddddd           507.65 56.465429572811686
  1 1960q2       1965q1     aaaaaabbbbcccccddddd           517.75  57.08338775768973
  1 1960q3       1965q2     aaaaaabbbbccccdddddd           528.65  58.43419600848437
  1 1960q4       1965q3     aaaaaabbbbbccccddddd            539.6  60.25507185466199
  1 1961q1       1965q4     aaaaabbbbbbccccddddd            550.6  62.68501037980077
  1 1961q2       1966q1     aaaaaabbbbbccccddddd            561.6  65.02420601915138
  1 1961q3       1966q2     aaaaaabbbbcccccddddd            573.0   65.8858662265366
  1 1961q4       1966q3     aaaaaaabbbbcccdddddd            583.9  67.52847352666971
  1 1962q1       1966q4     aaaaaaabbbbcccdddddd           593.95  67.30486413954085
  1 1962q2       1967q1     aaaaaaabbbbccddddddd            604.3  67.52707059511755
  1 1962q3       1967q2     aaaaaabbbbcccddddddd           613.75  66.85717298052154
  1 1962q4       1967q3     aaaaaaabbbccdddddddd            623.4  65.92771160820578
  1 1963q1       1967q4     aaaaaaabbcccdddddddd            632.9  64.67969664349684
  1 1963q2       1968q1     aaaaaabbbcccdddddddd           642.65  62.86516481620384
  1 1963q3       1968q2     aaaaaabbbcccdcdddddd           653.05  61.83805249878237
  1 1963q4       1968q3     aaaaaabbcccccccddddd           663.65  60.68145906027865
  1 1964q1       1968q4     aaaaabbbccccccccdddd           674.85 60.242732691215465
  1 1964q2       1969q1     bbbbbbbbbbbbbbbbcccd           734.85 250.77926444122951
  1 1964q3       1969q2     bbbbbbbbbbbbbbbbbbdd            796.5 343.55211050374726
  1 1964q4       1969q3     bbbbbbbbbbbbbbbbbddd           858.75  409.8081547312236
  1 1965q1       1969q4     bbbbbbbbbbbbbbbbdddd           920.75 459.68477471552524
  2 1970q1       1974q4     ddddddddaaaaaaaaabbb          1361.05  555.2160769513644
  2 1970q2       1975q1     dddddddaaaaaaabbbbbb           1321.6  543.5625563185705
  2 1970q3       1975q2     ddddddaaaaaabbbbbbbb           1278.8  524.1698800559747
  2 1970q4       1975q3     dddddaaaaabbbbbbbbbb          1238.65 500.42931831736115
  2 1971q1       1975q4     ddddaaaaabbbbbbbbbbb           1197.6  467.8177107873493
  2 1971q2       1976q1     dddaaaabbbbbbbbbbbcc          1155.35 421.61297730451696
  2 1971q3       1976q2     ddaaaabbbbbbbbbbcccc           1114.0  365.4449342924321
  2 1971q4       1976q3     daaaabbbbbbbbccccccc           1071.0 284.76305940202286
  2 1972q1       1976q4     aaaaaabbbbccccdddddd           1028.3 152.20488269365566
  2 1972q2       1977q1     aaaaaabbbbccccdddddd          1053.15 153.19415785205337
  2 1972q3       1977q2     aaaaaabbbbccccdddddd           1078.2 153.33609455942508
  2 1972q4       1977q3     aaaaaabbbbbccddddddd           1104.1 154.19601332821765
  2 1973q1       1977q4     aaaaaabbbbccccdddddd          1129.75 153.77080518198434
  2 1973q2       1978q1     aaaaaabbbbccccdddddd           1155.8  153.5432735163129
  2 1973q3       1978q2     aaaaaabbbbccccdddddd          1183.15 155.22624405075726
  2 1973q4       1978q3     aaaaaaabbcccccdddddd           1210.7 157.55804277187843
  2 1974q1       1978q4     aaaaaabbbbccccdddddd           1238.1 160.74756280382127
  2 1974q2       1979q1     aaaaaabbbbcccccddddd           1266.4 163.27580798662123
  2 1974q3       1979q2     aaaaaabbbbcccccddddd           1294.1 165.00778291054277
  2 1974q4       1979q3     aaaaabbbbbbcccdddddd           1321.8 165.54585944368188
  2 1975q1       1979q4     aaaaaabbbbccccdddddd          1350.15  167.6105369756414

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