VECTOR_
On this page
Adds all vectors in a column and returns a vector which is the sum of those vectors.
Syntax
VECTOR_SUM(vector_expression)
Arguments
-
vector_: An expression that evaluates to a vector.expression Vectors can be stored in SingleStore using the BLOBtype (Binary String Types).
Return Type
Returns the type of the argument (the vector_).
Remarks
When using VECTOR_ on a column of type BLOB, the vectors in that column may be of any length.
Using VECTOR_ with Vectors as BLOBs
The following examples demonstrate use of VECTOR_ with the BLOB type.
Example 1 - Adding Two Vectors
Create a table with a column of type BLOB to store the vectors and insert data into that table.vec and type BLOB, will store the vectors.
CREATE TABLE vectors_b (id int, vec BLOB not null);INSERT INTO vectors_b VALUES (1, JSON_ARRAY_PACK('[0.1, 0.8, 0.2, 0.555]'));INSERT INTO vectors_b VALUES (2, JSON_ARRAY_PACK('[0.45, 0.55, 0.495, 0.5]'));
The following query sums the vectors in the vec column and uses JSON_ to see the results in JSON format.
SELECT JSON_ARRAY_UNPACK(VECTOR_SUM(vec)) AS vector_sumFROM vectors_b;
+---------------------------------------------------+
| vector_sum |
+---------------------------------------------------+
| [0.550000012,1.35000002,0.694999993,1.05500007] |
+---------------------------------------------------+
1 row in set (0.01 sec)The following query uses HEX to view the results in hexadecimal format.
SELECT HEX(VECTOR_SUM(vec)) AS vector_sumFROM vectors_b;
+---------------------------------------------------+
| vector_sum |
+---------------------------------------------------+
| CDCC0C3FCDCCAC3F85EB313F3E0A873F |
+---------------------------------------------------+Using Suffixes for Other Element Types with BLOBs
The default element type for vector storage and processing is 32-bit floating point (F32).
You can specify the datatype of the vector elements to be used in the operation by adding a suffix to the function._.
When using a suffix, the return type will be the type specified by the suffix.
Note
The functions with suffixes do not work with the VECTOR type.
The following table lists the suffixes and their data type.
|
Suffix |
Data Type |
|---|---|
|
|
8-bit signed integer |
|
|
16-bit signed integer |
|
|
32-bit signed integer |
|
|
64-bit signed integer |
|
|
32-bit floating-point number (IEEE standard format) |
|
|
64-bit floating-point number (IEEE standard format) |
Example 2 - BLOBs with 16-bit Integers
Below is an example of using JSON_ and VECTOR_ with 16-bit signed integers.
Create a table of vectors stored as 16-bit integers.JSON_.
CREATE TABLE vectors_b_i (id int, vec BLOB not null);INSERT INTO vectors_b_i VALUES (1, JSON_ARRAY_PACK_I16('[1, 3, 2, 5]'));INSERT INTO vectors_b_i VALUES(2, JSON_ARRAY_PACK_I16('[23, 4, 1, 8]'));
The following query sums the vectors in the vectors_ table.
SELECT JSON_ARRAY_UNPACK_I16(VECTOR_SUM_I16(vec)) AS vector_sumFROM vectors_b_i;
+--------------+
| vector_sum |
+--------------+
| [24,7,3,13] |
+--------------+Note
Be sure that the suffixes you use to pack the vector data match the suffixes you use to unpack the data and the suffixes you use on functions to process that data.
Formatting Binary Vector Data for BLOBs
When using the BLOB type for vector operations, vector data can be formatted using JSON_.BLOBs.BLOB containing packed numbers in little-endian byte order.BLOBs can be of any length; however, the input blob length must be divisible by the size of the packed vector elements .
Use VECTOR_ with the VECTOR Data Type
The VECTOR_ function is not supported with the VECTOR data type in this version.
To use VECTOR_ with a column of type VECTOR, cast the column to a BLOB type and then cast back to a VECTOR type as shown in the following example.
Create a table with a VECTOR data type and insert data into that table.
CREATE TABLE vector_exps(id INT, position VECTOR(4));INSERT INTO vector_exps VALUES(1,'[1,2,3,4]'),(2,'[5,6,7,8]');
Cast to a BLOB data type and back to a VECTOR data type.
SET vector_type_project_format = JSON; /* to make vector output readable */SELECT VECTOR_SUM(position:>BLOB):>VECTOR(4) as vector_sumFROM vector_exps;
+-----------------+
| vector_sum |
+-----------------+
| [6,8,10,12] |
+-----------------+Last modified: February 12, 2025