16.20 - Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_AVG - Teradata Vantage NewSQL Engine

Teradata Vantage™ Data Types and Literals

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

Consider the following 2-D ARRAY data type and table.

CREATE TYPE shot_ary AS INTEGER ARRAY[1:50][1:50];
CREATE TABLE seismic_data (
   id INTEGER,
   shot1 shot_ary,
   shot2 shot_ary);

When evaluating the following query, each element value in the shot1 array is added. The total sum of all the element values is then divided by the number of elements in the shot1 array.

SELECT ARRAY_AVG(shot1) FROM seismic_data;

In the following query, ARRAY_AVG adds each element within the specified scope reference of the shot1 array. The result is a scalar value representing the total sum of adding the affected element values of shot1 divided by the total number of elements composing the specified scope reference.

SELECT ARRAY_AVG(shot1, NEW arrayVec(5,5), NEW arrayVec(10,10)) 
FROM seismic_data;

The following is the same query using method-style syntax.

SELECT shot1.ARRAY_AVG(NEW arrayVec(5,5), NEW arrayVec(10,10)) 
FROM seismic_data;