Sign inTry Free

VECTOR_SUM

On this page

Returns a vector, which is a sum of all vectors in a column.

It is an aggregate function.

Syntax

VECTOR_SUM(vector_expression)

Arguments

  • vector_expression: An expression that evaluates to a vector. The vector must be encoded as a blob containing packed 8, 16 or 32 bit integer numbers; 32 or 64 bit floating-point numbers; single-precision; or double-precision floating-point numbers in little-endian byte order.

Return Type

A blob containing packed single-precision floating-point numbers in little-endian byte order.

Remarks

A vector can be of any length, but all input vectors must be the same length and the blob lengths must be divisible by the packed vector element size (1, 2, 4 or 8 bytes depending on the data type of the vector element).

You can specify the data type of the vector elements in which this operation is performed on the vector by adding a suffix to the function. Omitting the suffix from the function is equivalent to suffixing it with _F32. All operations are done using the specified data type. The following table lists the suffixes and their data type.

Suffix

Data Type

_I8

8-bit signed integer

_I16

16-bit signed integer

_I32

32-bit signed integer

_I64

64-bit signed integer

_F32

32-bit floating-point number (IEEE standard format)

_F64

64-bit floating-point number (IEEE standard format)

Example

The following examples use the JSON_ARRAY_UNPACK() function to view the output in JSON format.

Adding Two Vectors

The following example uses the t table:

CREATE TABLE v_t (x BLOB, y BLOB);


INSERT INTO v_t VALUES (JSON_ARRAY_PACK('[12.1, 6.4]'), JSON_ARRAY_PACK('[8.6,9.2]'));
INSERT INTO v_t VALUES (JSON_ARRAY_PACK('[5.2, 11.7]'), JSON_ARRAY_PACK('[4.6,7.3]'));

This example sums the vector blobs for each column using the VECTOR_SUM() aggregate function:

SELECT JSON_ARRAY_UNPACK(VECTOR_SUM(x)), JSON_ARRAY_UNPACK(VECTOR_SUM(y)) FROM v_t;
+----------------------------------+----------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUM(x)) | JSON_ARRAY_UNPACK(VECTOR_SUM(y)) |
+----------------------------------+----------------------------------+
| [17.2999992,18.1000004]          | [13.2000008,16.5]                |
+----------------------------------+----------------------------------+
1 row in set (0.01 sec)

Typecasting the Data type Using Suffixes

To perform this operation on vectors specified in a format other than the default 32-bit floating point number, specify a suffix with the VECTOR_SUM() function. 

 -- _i8 suffix
SELECT JSON_ARRAY_UNPACK(VECTOR_SUM_i8(x)), JSON_ARRAY_UNPACK(VECTOR_SUM_i8(y)) FROM v_t;
+-------------------------------------+-------------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUM_i8(x)) | JSON_ARRAY_UNPACK(VECTOR_SUM_i8(y)) |
+-------------------------------------+-------------------------------------+
| [-8.52219055e-38,-2.49785375e-38]   | [-5.75992243e-38,-9.28640548e-38]   |
+-------------------------------------+-------------------------------------+
1 row in set (0.01 sec)
-- _i16 suffix
SELECT JSON_ARRAY_UNPACK(VECTOR_SUM_i16(x)), JSON_ARRAY_UNPACK(VECTOR_SUM_i16(y)) FROM v_t;
+--------------------------------------+--------------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUM_i16(x)) | JSON_ARRAY_UNPACK(VECTOR_SUM_i16(y)) |
+--------------------------------------+--------------------------------------+
| [-8.48559984e-38,-9.91823359e-38]    | [-5.75992243e-38,-9.28640548e-38]    |
+--------------------------------------+--------------------------------------+
1 row in set (0.03 sec)
-- _i32 suffix
SELECT JSON_ARRAY_UNPACK(VECTOR_SUM_i32(x)), JSON_ARRAY_UNPACK(VECTOR_SUM_i32(y)) FROM v_t;
+--------------------------------------+--------------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUM_i32(x)) | JSON_ARRAY_UNPACK(VECTOR_SUM_i32(y)) |
+--------------------------------------+--------------------------------------+
| [-8.52233404e-38,-9.99170198e-38]    | [-5.75992243e-38,-9.28640548e-38]    |
+--------------------------------------+--------------------------------------+
1 row in set (0.02 sec)
-- _i64 suffix
SELECT JSON_ARRAY_UNPACK(VECTOR_SUM_i64(x)), JSON_ARRAY_UNPACK(VECTOR_SUM_i64(y)) FROM v_t;
+--------------------------------------+--------------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUM_i64(x)) | JSON_ARRAY_UNPACK(VECTOR_SUM_i64(y)) |
+--------------------------------------+--------------------------------------+
| [-8.52233404e-38,-9.99170198e-38]    | [-5.75992243e-38,-9.28640548e-38]    |
+--------------------------------------+--------------------------------------+
1 row in set (0.03 sec)
-- _f64 suffix
SELECT JSON_ARRAY_UNPACK(VECTOR_SUM_f64(x)), JSON_ARRAY_UNPACK(VECTOR_SUM_f64(y)) FROM v_t;
+--------------------------------------+--------------------------------------+
| JSON_ARRAY_UNPACK(VECTOR_SUM_f64(x)) | JSON_ARRAY_UNPACK(VECTOR_SUM_f64(y)) |
+--------------------------------------+--------------------------------------+
| [-4.75446394e+16,11.7140617]         | [-1.8777817e-33,9.39999962]          |
+--------------------------------------+--------------------------------------+
1 row in set (0.03 sec)

How to Use the Vector Data Type

Currently, VECTOR_SUM is not supported with the vector data type. A workaround is provided until support for the vector data type is implemented.

Using VECTOR_SUM with the vector data type will result in an error.

SELECT vector_sum(position) FROM vector_exps;
ERROR 1706 (HY000): Feature 'VECTOR_SUM aggregate function with VECTOR datatype' 
is not supported by SingleStore.

There is a way to use VECTOR_SUM with a vector data type. First cast to a blob data type and then cast back to a vector data type. The example below shows how to do this:

Example

Create a table with a vector data type:

CREATE TABLE vector_exps(id INT, position vector(4));

Insert some vector values:

INSERT INTO vector_exps VALUES(1,'[1,2,3,4]'),(2,'[5,6,7,8]');

Cast to a blob data type:

SELECT vector_sum(position:>blob) FROM vector_exps;
+----------------------------+
| vector_sum(position:>blob) |
+----------------------------+
|   ?A   B  B   B            |
+----------------------------+

Cast to a vector data type:

SELECT vector_sum(position:>blob):>vector(4) FROM vector_exps;
+---------------------------------------+
| vector_sum(position:>blob):>vector(4) |
+---------------------------------------+
| [6,8,10,12]                           |
+---------------------------------------+

Last modified: March 8, 2024

Was this article helpful?

On this page

Was this article helpful?