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 matrixes, 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.
The following shows only two of the matrixes.
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 |