16.20 - Example: One Input Matrix, Single SQL Statement - 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 1 billion rows of weather data. It has 1 geometry column representing the point (latitude/longitude) of the observation, and these REAL columns:
  • temperature
  • air_pressure
  • rainfall.

Use an SQL statement that invokes CALCMATRIX twice, once for the LOCAL phase and once for the COMBINE phase, as follows:

select * from CALCMATRIX( ON (
   select 1 as p, X.* from CALCMATRIX( ON (select temperature,       air_pressure, rainfall from T) 
   USING PHASE('LOCAL') )X )
   HASH BY p
   USING PHASE('COMBINE')
   )Y;

The result set would be the SSCP matrix for the weather data. The following table shows the result set. n means the numeric result of the calculation.

rownum rowname temperature air_pressure rainfall
1 temperature n n n
2 air_pressure n n n
3 rainfall n n n