Example: One Input Matrix, Multiple SQL Statements - Advanced SQL Engine - Teradata Database

SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2023-04-27
dita:mapPath
qqu1556127655717.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1210
lifecycle
previous
Product Category
Teradata Vantage™

In the following example, table T has the same weather data as table T in “Example: One Input Matrix, Single SQL Statement.” However, the aim this time is to produce both the covariance and correlation matrices. In order to avoid 2 passes over billion rows of raw data, insert the results of CALCMATRIX using the LOCAL phase into an intermediate table. Then run CALCMATRIX using the COMBINE phase twice, but against the intermediate table, which only has 3 * (the number of amps in the system) rows.

CREATE TABLE T_INTERMEDIATE as (
   select 1 as p, X.* from CALCMATRIX( ON (select temperature,       air_pressure, rainfall from T) USING PHASE('LOCAL') )X
   ) WITH DATA;
select * from CALCMATRIX ( ON T_INTERMEDIATE
   HASH BY p
   USING PHASE('COMBINE') CALCTYPE('COV')
   )Y;
select * from CALCMATRIX( ON T_INTERMEDIATE
   HASH BY p
   USING PHASE('COMBINE') CALCTYPE('COR')
   )Y;