Examples | CALCMATRIX | Teradata Vantage - Example: One Input Matrix, Single SQL Statement - 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 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