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

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Administration
Programming Reference
Publication ID
B035-1210-162K
Language
English (United States)

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;