Examples

Teradata® Vantage User Guide

prodname
Teradata Vantage
vrm_release
1.0
category
User Guide
featnum
B700-4002-098K

Function with Multiple ON Clauses

This function, Attribution, has two partitioned inputs and five dimensional inputs. When this SQL statement runs, all seven input tables are transferred to the Teradata ML Engine. The Attribution function is then called using the transferred tables as inputs and with the function arguments in the SQL statement. Finally, the result of the Attribution function is returned to Teradata SQL Engine for use in processing the rest of the SQL statement.

SELECT * FROM Attribution (
  ON attribution_sample_table1 AS input1 PARTITION BY user_id 
                                         ORDER BY time_stamp
  ON attribution_sample_table2 AS input2 PARTITION BY user_id 
                                         ORDER BY time_stamp
  ON conversion_event_table AS conversion DIMENSION
  ON excluding_event_table AS excluding DIMENSION
  ON optional_event_table AS optional DIMENSION
  ON model1_table AS model1 DIMENSION
  ON model2_table AS model2 DIMENSION
  USING EventColumn ('event')
        TimestampColumn ('time_stamp')
        WindowSize ('rows:10&seconds:20')
) AS attrtb ORDER BY user_id, time_stamp;

Function with OUT TABLE Clauses

This function, KMeans, also has multiple ON clauses, but no ON clause has a PARTITION BY or DIMENSION clause. OUT TABLE clauses specify additional (secondary) output tables (those other than the primary output table). When this SQL statement runs, the results of the ON clauses are transferred to the Teradata ML Engine. The KMeans function is then called using the transferred tables as inputs and with the function arguments provided in the SQL statement. The secondary outputs of the KMeans function are returned to the SQL Engine in the kmeanssample_output and kmeanssample_clusteredoutput tables as specified in the function arguments. Finally, the result of the KMeans function is returned to Teradata SQL Engine for use in processing the rest of the SQL statement.

SELECT * FROM KMeans (
  ON computers_train1 AS InputTable
  ON kmeanssample_centroid AS CentroidsTable
  OUT TABLE OutputTable (kmeanssample_output)
  OUT TABLE ClusteredOutput (kmeanssample_clusteredoutput)
  USING
  MaxIterNum ('10')
) AS kmouttb2;

Nested Functions

This example calls nested functions. The output of SentimentExtractor is used as the input to SentimentEvaluator. When this SQL statement is executed, the input table for SentimentExtractor is transferred to Teradata ML Engine. The SentimentExtractor function is then called using the transferred table as input and with the function arguments provided in the SQL statement. The result of the SentimentExtractor function is then returned to Teradata SQL Engine. This intermediate result is transferred to the Teradata ML Engine. Then the SentimentEvaluator function is called using the transferred intermediate result as input and with the function arguments provided in the SQL statement. Finally, the result of the SentimentEvaluator function is returned to Teradata SQL Engine for use in processing the rest of the SQL statement. For more information, see Nested Teradata ML Engine Analytic Functions. SQL identifiers that are reserved keywords must be enclosed in double quotation marks.

SELECT * FROM SentimentEvaluator (
  ON (
    SELECT * FROM SentimentExtractor (
      ON sentiment_extract_input as "input"
      USING TextColumn ('review')
      Accumulate ('category')
      "model" ('dictionary')
    ) AS dt1
  ) AS "input" PARTITION BY 1
  USING ObsColumn ('category')
  SentimentColumn ('out_polarity')
) AS dt;