17.10 - Example: Querying a 2-D ARRAY Data Type and Table using ARRAY_AVG - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

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;