17.10 - Example: Multiple Input Matrices, One SQL Statement - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Operators and User-Defined Functions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Administration
Programming Reference
Publication ID
B035-1210-171K
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 and Example: One Input Matrix, Multiple SQL Statements. In this example, in addition to the point location of the observation, there is an additional VARCHAR column for continent, which is the matrix id. Our aim is to produce 7 SSCP matrices, one for each continent’s data.

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

select * from calcmatrix( ON (
   select * from calcmatrix( ON (select continent, temperature,    air_pressure, rainfall from T) LOCAL ORDER BY continent USING    PHASE('LOCAL') )X )
   HASH BY continent
   LOCAL ORDER BY continent
   USING PHASE('COMBINE')
   )Y
   order by continent, rownum;

The result set in the following tables is the SSCP matrix for weather data by continent.

Only 2 of the matrices are shown below.
continent rownum rowname temperature air_pressure rainfall
Europe 1 temperature n n n
Europe 2 air_pressure n n n
Europe 3 rainfall n n n
continent rownum rowname temperature air_pressure rainfall
Asia 1 temperature n n n
Asia 2 air_pressure n n n
Asia 3 rainfall n n n