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

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
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', 'income', 'investment')
  TimeColumn ('period')
  OutputType ('characters') 
  WindowType ('global')
  Accumulate ('id')
) AS dt;

Output

 id value_column period_start period_end saxchar 
 -- ------------ ------------ ---------- ------- 
  1 expenditure  1960q1       1960q1     a      
  1 expenditure  1960q2       1960q2     a      
  1 expenditure  1960q3       1960q3     a      
  1 expenditure  1960q4       1960q4     a      
  1 expenditure  1961q1       1961q1     b      
  1 expenditure  1961q2       1961q2     b      
  1 expenditure  1961q3       1961q3     b      
  1 expenditure  1961q4       1961q4     b      
  1 expenditure  1962q1       1962q1     b      
  1 expenditure  1962q2       1962q2     b      
  1 expenditure  1962q3       1962q3     b      
  1 expenditure  1962q4       1962q4     b      
  1 expenditure  1963q1       1963q1     b      
  1 expenditure  1963q2       1963q2     b      
  1 expenditure  1963q3       1963q3     b      
  1 expenditure  1963q4       1963q4     b      
  1 expenditure  1964q1       1964q1     b      
  1 expenditure  1964q2       1964q2     b      
  1 expenditure  1964q3       1964q3     b      
  1 expenditure  1964q4       1964q4     b      
  1 expenditure  1965q1       1965q1     b      
  1 expenditure  1965q2       1965q2     b      
  1 expenditure  1965q3       1965q3     b      
  1 expenditure  1965q4       1965q4     b      
  1 expenditure  1966q1       1966q1     b      
  1 expenditure  1966q2       1966q2     b      
  1 expenditure  1966q3       1966q3     b      
  1 expenditure  1966q4       1966q4     b      
  1 expenditure  1967q1       1967q1     b      
  1 expenditure  1967q2       1967q2     b      
  1 expenditure  1967q3       1967q3     b      
  1 expenditure  1967q4       1967q4     c      
  1 expenditure  1968q1       1968q1     c      
  1 expenditure  1968q2       1968q2     c      
  1 expenditure  1968q3       1968q3     c      
  1 expenditure  1968q4       1968q4     c      
  1 expenditure  1969q1       1969q1     d      
  1 expenditure  1969q2       1969q2     d      
  1 expenditure  1969q3       1969q3     d      
  1 expenditure  1969q4       1969q4     d      
  1 income       1960q1       1960q1     a      
  1 income       1960q2       1960q2     a      
  1 income       1960q3       1960q3     a      
  1 income       1960q4       1960q4     a      
  1 income       1961q1       1961q1     b      
  1 income       1961q2       1961q2     b      
  1 income       1961q3       1961q3     b      
  1 income       1961q4       1961q4     b      
  1 income       1962q1       1962q1     b      
  1 income       1962q2       1962q2     b      
  1 income       1962q3       1962q3     b      
  1 income       1962q4       1962q4     b      
  1 income       1963q1       1963q1     b      
  1 income       1963q2       1963q2     b      
  1 income       1963q3       1963q3     b      
  1 income       1963q4       1963q4     b      
  1 income       1964q1       1964q1     b      
  1 income       1964q2       1964q2     b      
  1 income       1964q3       1964q3     b      
  1 income       1964q4       1964q4     b      
  1 income       1965q1       1965q1     b      
  1 income       1965q2       1965q2     b      
  1 income       1965q3       1965q3     b      
  1 income       1965q4       1965q4     b      
  1 income       1966q1       1966q1     b      
  1 income       1966q2       1966q2     b      
  1 income       1966q3       1966q3     b      
  1 income       1966q4       1966q4     b      
  1 income       1967q1       1967q1     b      
  1 income       1967q2       1967q2     b      
  1 income       1967q3       1967q3     b      
  1 income       1967q4       1967q4     c      
  1 income       1968q1       1968q1     c      
  1 income       1968q2       1968q2     c      
  1 income       1968q3       1968q3     c      
  1 income       1968q4       1968q4     c      
  1 income       1969q1       1969q1     d      
  1 income       1969q2       1969q2     d      
  1 income       1969q3       1969q3     d      
  1 income       1969q4       1969q4     d      
  1 investment   1960q1       1960q1     a      
  1 investment   1960q2       1960q2     a      
  1 investment   1960q3       1960q3     a      
  1 investment   1960q4       1960q4     a      
  1 investment   1961q1       1961q1     a      
  1 investment   1961q2       1961q2     a      
  1 investment   1961q3       1961q3     a      
  1 investment   1961q4       1961q4     b      
  1 investment   1962q1       1962q1     b      
  1 investment   1962q2       1962q2     b      
  1 investment   1962q3       1962q3     b      
  1 investment   1962q4       1962q4     b      
  1 investment   1963q1       1963q1     a      
  1 investment   1963q2       1963q2     b      
  1 investment   1963q3       1963q3     b      
  1 investment   1963q4       1963q4     b      
  1 investment   1964q1       1964q1     b      
  1 investment   1964q2       1964q2     b      
  1 investment   1964q3       1964q3     b      
  1 investment   1964q4       1964q4     b      
  1 investment   1965q1       1965q1     b      
  1 investment   1965q2       1965q2     c      
  1 investment   1965q3       1965q3     c      
  1 investment   1965q4       1965q4     c      
  1 investment   1966q1       1966q1     c      
  1 investment   1966q2       1966q2     c      
  1 investment   1966q3       1966q3     c      
  1 investment   1966q4       1966q4     c      
  1 investment   1967q1       1967q1     b      
  1 investment   1967q2       1967q2     b      
  1 investment   1967q3       1967q3     b      
  1 investment   1967q4       1967q4     c      
  1 investment   1968q1       1968q1     b      
  1 investment   1968q2       1968q2     b      
  1 investment   1968q3       1968q3     c      
  1 investment   1968q4       1968q4     c      
  1 investment   1969q1       1969q1     d      
  1 investment   1969q2       1969q2     d      
  1 investment   1969q3       1969q3     d      
  1 investment   1969q4       1969q4     d      
  2 expenditure  1970q1       1970q1     d      
  2 expenditure  1970q2       1970q2     d      
  2 expenditure  1970q3       1970q3     d      
  2 expenditure  1970q4       1970q4     d      
  2 expenditure  1971q1       1971q1     d      
  2 expenditure  1971q2       1971q2     d      
  2 expenditure  1971q3       1971q3     d      
  2 expenditure  1971q4       1971q4     d      
  2 expenditure  1972q1       1972q1     a      
  2 expenditure  1972q2       1972q2     a      
  2 expenditure  1972q3       1972q3     a      
  2 expenditure  1972q4       1972q4     a      
  2 expenditure  1973q1       1973q1     a      
  2 expenditure  1973q2       1973q2     a      
  2 expenditure  1973q3       1973q3     a      
  2 expenditure  1973q4       1973q4     a      
  2 expenditure  1974q1       1974q1     a      
  2 expenditure  1974q2       1974q2     a      
  2 expenditure  1974q3       1974q3     a      
  2 expenditure  1974q4       1974q4     a      
  2 expenditure  1975q1       1975q1     b      
  2 expenditure  1975q2       1975q2     b      
  2 expenditure  1975q3       1975q3     b      
  2 expenditure  1975q4       1975q4     b      
  2 expenditure  1976q1       1976q1     b      
  2 expenditure  1976q2       1976q2     b      
  2 expenditure  1976q3       1976q3     b      
  2 expenditure  1976q4       1976q4     b      
  2 expenditure  1977q1       1977q1     b      
  2 expenditure  1977q2       1977q2     b      
  2 expenditure  1977q3       1977q3     b      
  2 expenditure  1977q4       1977q4     c      
  2 expenditure  1978q1       1978q1     c      
  2 expenditure  1978q2       1978q2     c      
  2 expenditure  1978q3       1978q3     c      
  2 expenditure  1978q4       1978q4     c      
  2 expenditure  1979q1       1979q1     c      
  2 expenditure  1979q2       1979q2     c      
  2 expenditure  1979q3       1979q3     c      
  2 expenditure  1979q4       1979q4     d      
  2 income       1970q1       1970q1     d      
  2 income       1970q2       1970q2     d      
  2 income       1970q3       1970q3     d      
  2 income       1970q4       1970q4     d      
  2 income       1971q1       1971q1     d      
  2 income       1971q2       1971q2     d      
  2 income       1971q3       1971q3     d      
  2 income       1971q4       1971q4     d      
  2 income       1972q1       1972q1     a      
  2 income       1972q2       1972q2     a      
  2 income       1972q3       1972q3     a      
  2 income       1972q4       1972q4     a      
  2 income       1973q1       1973q1     a      
  2 income       1973q2       1973q2     a      
  2 income       1973q3       1973q3     a      
  2 income       1973q4       1973q4     a      
  2 income       1974q1       1974q1     a      
  2 income       1974q2       1974q2     a      
  2 income       1974q3       1974q3     a      
  2 income       1974q4       1974q4     a      
  2 income       1975q1       1975q1     b      
  2 income       1975q2       1975q2     b      
  2 income       1975q3       1975q3     b      
  2 income       1975q4       1975q4     b      
  2 income       1976q1       1976q1     b      
  2 income       1976q2       1976q2     b      
  2 income       1976q3       1976q3     b      
  2 income       1976q4       1976q4     b      
  2 income       1977q1       1977q1     b      
  2 income       1977q2       1977q2     b      
  2 income       1977q3       1977q3     c      
  2 income       1977q4       1977q4     c      
  2 income       1978q1       1978q1     c      
  2 income       1978q2       1978q2     c      
  2 income       1978q3       1978q3     c      
  2 income       1978q4       1978q4     c      
  2 income       1979q1       1979q1     c      
  2 income       1979q2       1979q2     c      
  2 income       1979q3       1979q3     c      
  2 income       1979q4       1979q4     c      
  2 investment   1970q1       1970q1     d      
  2 investment   1970q2       1970q2     d      
  2 investment   1970q3       1970q3     d      
  2 investment   1970q4       1970q4     d      
  2 investment   1971q1       1971q1     d      
  2 investment   1971q2       1971q2     d      
  2 investment   1971q3       1971q3     d      
  2 investment   1971q4       1971q4     d      
  2 investment   1972q1       1972q1     a      
  2 investment   1972q2       1972q2     a      
  2 investment   1972q3       1972q3     a      
  2 investment   1972q4       1972q4     a      
  2 investment   1973q1       1973q1     a      
  2 investment   1973q2       1973q2     a      
  2 investment   1973q3       1973q3     a      
  2 investment   1973q4       1973q4     a      
  2 investment   1974q1       1974q1     b      
  2 investment   1974q2       1974q2     b      
  2 investment   1974q3       1974q3     b      
  2 investment   1974q4       1974q4     b      
  2 investment   1975q1       1975q1     b      
  2 investment   1975q2       1975q2     b      
  2 investment   1975q3       1975q3     b      
  2 investment   1975q4       1975q4     b      
  2 investment   1976q1       1976q1     c      
  2 investment   1976q2       1976q2     b      
  2 investment   1976q3       1976q3     b      
  2 investment   1976q4       1976q4     b      
  2 investment   1977q1       1977q1     c      
  2 investment   1977q2       1977q2     b      
  2 investment   1977q3       1977q3     b      
  2 investment   1977q4       1977q4     b      
  2 investment   1978q1       1978q1     b      
  2 investment   1978q2       1978q2     b      
  2 investment   1978q3       1978q3     b      
  2 investment   1978q4       1978q4     b      
  2 investment   1979q1       1979q1     b      
  2 investment   1979q2       1979q2     c      
  2 investment   1979q3       1979q3     c      
  2 investment   1979q4       1979q4     c      
  3 expenditure  1980q1       1980q1     a      
  3 expenditure  1980q2       1980q2     a      
  3 expenditure  1980q3       1980q3     a      
  3 expenditure  1980q4       1980q4     a      
  3 expenditure  1981q1       1981q1     c      
  3 expenditure  1981q2       1981q2     c      
  3 expenditure  1981q3       1981q3     c      
  3 expenditure  1981q4       1981q4     d      
  3 expenditure  1982q1       1982q1     d      
  3 expenditure  1982q2       1982q2     d      
  3 expenditure  1982q3       1982q3     d      
  3 expenditure  1982q4       1982q4     d      
  3 income       1980q1       1980q1     a      
  3 income       1980q2       1980q2     a      
  3 income       1980q3       1980q3     a      
  3 income       1980q4       1980q4     a      
  3 income       1981q1       1981q1     c      
  3 income       1981q2       1981q2     c      
  3 income       1981q3       1981q3     c      
  3 income       1981q4       1981q4     d      
  3 income       1982q1       1982q1     d      
  3 income       1982q2       1982q2     d      
  3 income       1982q3       1982q3     d      
  3 income       1982q4       1982q4     d      
  3 investment   1980q1       1980q1     a      
  3 investment   1980q2       1980q2     a      
  3 investment   1980q3       1980q3     a      
  3 investment   1980q4       1980q4     a      
  3 investment   1981q1       1981q1     c      
  3 investment   1981q2       1981q2     d      
  3 investment   1981q3       1981q3     d      
  3 investment   1981q4       1981q4     c      
  3 investment   1982q1       1982q1     c      
  3 investment   1982q2       1982q2     c      
  3 investment   1982q3       1982q3     c      
  3 investment   1982q4       1982q4     c

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