Example: One Input Matrix, Multiple SQL Statements - 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 matrixes. To avoid two 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 three times 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;