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;