ARRAY_AGG - Teradata Database

ARRAY_AGG

Purpose  

An aggregate function for use with ARRAY data types that allows arrays to be created as part of the SELECT list of a query.

Syntax  

where:

Syntax element…	Specifies…
TD_SYSFNLIB	the name of the database where the function is located.
element_value_expr	an expression that evaluates to an ARRAY element type.
value_expression	an expression that evaluates to a Teradata data type that can be compared.
ASC	that the results are to be ordered in ascending sort order. If the sort field is a character string, the system orders it in ascending order according to the definition of the collation sequence for the current session. The default order is ASC.
DESC	that the results are to be ordered in descending sort order. If the sort field is a character string, the system orders it in descending order according to the definition of the collation sequence for the current session.
array_expression	one of the following: A reference to an ARRAY column An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195. A UDF expression A UDM expression

ANSI Compliance

ARRAY_AGG for a one-dimensional (1-D) ARRAY data type is ANSI SQL:2011 compliant. However, ARRAY_AGG includes an additional parameter containing an ARRAY expression of the target ARRAY type, and this is a Teradata extension to the ANSI standard. This was done to eliminate ambiguity since you can define multiple ARRAY types that have the same element data type.

ARRAY_AGG for a multidimensional (n-D) ARRAY data type is a Teradata extension to the ANSI SQL:2011 standard.

Argument Types

ARRAY_AGG accepts two arguments. The first argument is an expression evaluating to a data type that is currently being used as the element value of a defined ARRAY data type. The second argument is an expression evaluating to a defined ARRAY data type whose element type matches that of the first argument.

Result Type

The data type information from the array_expression which resolves to an ARRAY data type is used to set the return type of ARRAY_AGG.

Result Value and Rules

ARRAY_AGG returns an ARRAY type value whose elements are the aggregated set of element expression values that were passed in as the first parameter. For an n-D ARRAY result value, the value_expression values that are aggregated into the array are entered in row-major order.

If you specify the ORDER BY clause in the first argument, then the values input to the resultant array are ordered by the column values specified in this clause. The following rules apply to the ORDER BY clause of ARRAY_AGG:

Only one value may be passed in the ORDER BY clause of ARRAY_AGG. This is different from an ORDER BY in a SELECT statement, where multiple values may be passed for ORDER BY.

An ARRAY column value may not be passed as the sort key in the ORDER BY clause. You cannot make relational comparisons of ARRAY values. Any other data type that cannot be compared, such as a BLOB or CLOB, may not be passed as the sort key in the ORDER BY clause.

For the single value passed to ORDER BY, any expression that may be relationally comparable may be passed. However, if the value passed is not a column value, then sorting of the resultant array elements will not occur and the results will be written to the resultant array in random order. Use a column value from one of the referenced tables in the SQL statement for proper sorting to occur.

Usage Notes  

Because you can pass any array_expression, the ARRAY type value that you pass may or may not contain any initialized elements. However, ARRAY_AGG ignores any initialized elements in the array_expression. The array_expression is used only for providing the data type of the result value.

Recommendation: Pass a blank instance of the desired ARRAY type value for this parameter, such as NEW myArray().

If the array_expression argument passed to ARRAY_AGG is a NULL literal, then an error is returned. If the array_expression argument can be resolved to an ARRAY data type, then the function executes normally, even if the data contains a NULL.

If the value_expression argument for the current row being processed evaluates to NULL, then that expression will not be eliminated from the list of values being aggregated, but will be used to set the appropriate ARRAY element value just as would be done with a non-NULL element. This follows the ANSI SQL:2011 standard and is noted in the standard as a difference between ARRAY_AGG and other aggregate functions.

ARRAY_AGG is created with a CLASS AGGREGATE value of 64000, meaning that the intermediate aggregate storage will be allocated at that size. Since the intermediate aggregate storage is a fixed-length value, it may be possible in cases of processing a very large input set that this buffer may overflow. All aggregate UDFs have this limitation.

Example

This example takes data from a source table which records a phone number associated with an employee and inserts it into a new table which records phone numbers in an ARRAY type. In many cases, an employee may have multiple phone numbers, such as office phone, mobile phone, home phone, etc. A better way to represent this type of data is with a 1-D ARRAY type which records multiple phone numbers. This reduces the number of rows in the table.

Note: the same type of behavior can be illustrated with an n-D ARRAY type. The only difference is that the storage of elements is done in row-major order.

The following statement creates a 1-D ARRAY type that can hold up to 100 phone number values:

   CREATE TYPE emp_phone_array AS VARCHAR(14) ARRAY[100];

The following source table contains one row per employee phone number.

   CREATE SET TABLE employee 

      (emp_id INTEGER, 

       emp_name VARCHAR(30), 

       emp_phone CHAR(14)); 

   SELECT * FROM employee;

     emp_id      emp_name           emp_phone

   --------   -----------           -------------

          1         Beth           (619) 619-6190

          1         Beth           (619) 620-6200

          1         Beth           (619) 720-7200

          2         Greg           (858) 858-8580

          2         Greg           (858) 859-8590

          2         Greg           (858) 860-8600

          3         Louise         (421) 421-4210

          3         Louise         (421) 422-4220

          3         Louise         (421) 423-4230

The following target table contains one row per employee and stores all phone numbers associated with an employee in an ARRAY type.

   CREATE SET TABLE employeePhoneInfo

      (emp_id INTEGER, 

       emp_name VARCHAR(30), 

       emp_phone emp_phone_array);

   INSERT INTO employeePhoneInfo 

      SELECT emp_id, emp_name, 

         ARRAY_AGG(emp_phone, NEW emp_phone_array()) 

      FROM employee GROUP BY emp_id,emp_name 
         WHERE emp_id < 100;

   SELECT * FROM employeePhoneInfo;

   emp_id        emp_name     emp_phone

---------     -----------     ---------

        1            Beth     ( (619) 619-6190, (619) 620-6200, (619) 720-7200 )

        2            Greg     ( (858) 858-8580, (858) 859-8590, (858) 860-8600 )

        3          Louise     ( (421) 421-4210, (421) 422-4220, (421) 423-4230 )