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

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?