Vector calculation functions in Simple Log Service SQL functions - Simple Log Service

You can save text, audio, images, and videos in vector databases as vectors to facilitate search and query. Simple Log Service supports vector calculation functions. This topic describes the syntax of vector calculation functions. This topic also provides examples on how to use the functions.

Vector calculation functions

Important

The vector elements that are used in the following functions are displayed as strings in a specific format. A string is a set of numbers that are enclosed in brackets [] or parentheses () and are separated with commas (,).

Function	Syntax	Description	Data type of the return value
cosine_similarity function	cosine_similarity(array(double) vector1, array(double) vector2) cosine_similarity(varchar vector1, varchar vector2)	Calculates the cosine similarity of two vectors. Valid values of the cosine similarity: [-1, 1]. If the value of the cosine similarity between two vectors is 1, the vectors are in the same direction. If the value of the cosine similarity between two vectors is -1, the vectors are in the opposite directions. If the value of the cosine similarity between two vectors is 0, one vector is vertical to the other vector.	double
inner_product or dot_product function	inner_product(array(double) vector1, array(double) vector2) inner_product(varchar vector1, varchar vector2) dot_product(array(double) vector1, array(double) vector2) dot_product(varchar vector1, varchar vector2)	Calculates the inner product of two vectors.	double
l2_distance or euclidean_distance function	euclidean_distance(array(double) vector1, array(double) vector2) euclidean_distance(varchar vector1, varchar vector2) l2_distance(array(double) vector1, array(double) vector2) l2_distance(varchar vector1, varchar vector2)	Calculates the Euclidean distance between two vectors.	double
vector_norm function	vector_norm(array(double) vector1) vector_norm(array(double) vector1, double p) vector_norm(varchar vector1) vector_norm(varchar vector1, double p)	Calculates the norm of a vector. The norm of a vector is equivalent to the magnitude of the vector.	double
vector_sum function	vector_sum(array(double) vector1) vector_sum(varchar vector1)	Calculates the sum of all elements of a vector.	double
vector_mean function	vector_mean(array(double) vector1) vector_mean(varchar vector1)	Calculates the mean of a vector.	double
vector_variance function	vector_variance(array(double) vector1) vector_variance(varchar vector1)	Calculates the variance of a vector. Variance measures the dispersion of the elements of a vector. Variance is the average of the squared distances from each element of the vector to the mean.	double
vector_std_dev function	vector_std_dev(array(double) vector1) vector_std_dev(varchar vector1)	Calculates the standard deviation of a vector. The standard deviation of a vector is the positive square root of its variance. Variance measures how far each data point in a dataset is from the mean. A high variance value indicates that the data points are significantly spread out from the mean. A low variance value indicates that the data points are close to the mean.	double
vector_add function	vector_add(array(double) vector1, array(double) vector2) vector_add(varchar vector1, varchar vector2)	Calculates the sum of two vectors. You can perform arbitrary linear transformation based on vector addition, vector subtraction, and vector scaling functions.	array(double)
vector_subtract function	vector_subtract(array(double) vector1, array(double) vector2) vector_subtract(varchar vector1, varchar vector2)	Calculates the difference between two vectors. You can perform arbitrary linear transformation based on vector addition, vector subtraction, and vector scaling functions.	array(double)
vector_scale function	vector_scale(array(double) vector1, double alpha) vector_scale(varchar vector1, double alpha)	Calculates the result of vector scaling. You can perform arbitrary linear transformation based on vector addition, vector subtraction, and vector scaling functions.	array(double)
vector_multiply function	vector_multiply(array(double) vector1, array(double) vector2) vector_multiply(varchar vector1, varchar vector2)	Calculates the product of each element at the same position in two vectors and returns the result vector.	array(double)
vector_divide function	vector_divide(array(double) vector1, array(double) vector2) vector_divide(varchar vector1, varchar vector2)	Calculates the division of each element at the same position in two vectors and returns the result vector.	array(double)
vector_add_value function	vector_add_value(array(double) vector1, double value) vector_add_value(varchar vector1, double value)	Adds a constant to each element in a vector and returns the result vector.	array(double)
vector_fill_nan function	vector_fill_nan(varchar vector1, varchar fill_option)	Specifies a value for an element that is left empty in a vector. If `NaN`, `null`, `Inf`, or `-Inf` exists in a vector, the related element is considered empty. You can specify different values for `fill_option`, such as `avg`, `mean`, `median`, `min`, `max`, and `value=0`. If you specify value=0, you can replace 0 with other digits.	array(double)

cosine_similarity function

The cosine_similarity function calculates the cosine similarity of two vectors.

Examples

Query statement

* | SELECT cosine_similarity(array[1, 2, 3, 4], array[10, 20, 30, 40])

* | SELECT cosine_similarity('[1, 2, 3, 4]', '[10, 20, 30, 40]')

Query and analysis results
1

inner_product or dot_product function

The inner_product or dot_product function calculates the inner product of two vectors.

Examples

Query statement

* | SELECT inner_product(array[1, 2, 3, 4], array[10, 20, 30, 40])

* | SELECT inner_product('[1, 2, 3, 4]', '[10, 20, 30, 40]')

Query and analysis results
300.0

l2_distance or euclidean_distance function

Note

l2_distance starts with the lowercase letter l.

The l2_distance or euclidean_distance function calculates the Euclidean distance between two vectors.

Examples

Query statement

* | SELECT l2_distance(array[1, 3], array[4, 2])

* | SELECT l2_distance('[1, 3]', '[4, 2]')

Query and analysis results
3.16

vector_norm function

The vector_norm function calculates the norm of a vector based on the type of the norm. The type of a norm is specified by p.

Examples

Query statement

* | SELECT vector_norm(array[1, 3], 1)

* | SELECT vector_norm(array[1, 3], 2)

* | SELECT vector_norm(array[1, 3], 3)

Query and analysis results
The function calculates the norm of the [1,3] vector when the norm type is 1, 2, and 3. When the norm type is 1, the function calculates the sum for the absolute values of vector elements. The result is 4. When the norm type is 2, the function calculates the square root of the sum for squares of vector elements. The result is 3.16. When the norm type is 3, the function calculates the cube root of the sum for cubes of vector elements. The result is 3.04.

vector_sum function

The vector_sum function calculates the sum of all elements of a vector.

Examples

Query statement

* | SELECT vector_sum(array[1, -3])

* | SELECT vector_sum('[1, -3]')

Query and analysis results
-2

vector_mean function

The vector_mean function calculates the mean of a vector.

Examples

Query statement

* | SELECT vector_mean(array[1, -3])

* | SELECT vector_mean('[1, -3]')

Query and analysis results
-1

vector_variance function

The vector_variance function calculates the variance of a vector. Variance measures the dispersion of the elements of a vector.

Examples

Query statement

* | SELECT vector_variance(array[1, -3, 8])

* | SELECT vector_variance('[1, -3, 8]')

Query and analysis results
The mean of the vector is 2. The variance is calculated by using the following formula: (1² + 5² + 6² = 62/3) = 20.67.

Return value type

Same as the data type of the parameter value.

vector_std_dev function

The vector_std_dev function calculates the standard deviation of a vector. The standard deviation of a vector is the positive square root of its variance.

Examples

Query statement

* | SELECT vector_std_dev(array[1, -3, 8])

* | SELECT vector_std_dev('[1, -3, 8]')

Query and analysis results
The mean of the vector is 2. The standard deviation is calculated by using the following formula: (1² + 5² + 6² = 62/3)^0.5 = 20.67^0.5 = 4.54.

vector_add function

The vector_add function calculates the sum of two vectors.

Examples

Query statement

* | SELECT vector_add(array[1, 2, 3, 4], array[2, 3, 1, 1.5])

* | SELECT vector_add("[1, 2, 3, 4]", "[2, 3, 1, 1.5]")

Query and analysis results
[3.0,5.0,4.0,5.5]

vector_subtract function

The vector_subtract function calculates the difference between two vectors.

Examples

Query statement

* | SELECT vector_subtract(array[1, 2, 3, 4], array[2, 3, 1, 1.5])

* | SELECT vector_subtract(array[1, 2, 3, 4], array[2, 3, 1, 1.5])

Query and analysis results
[-1.0,-1.0,2.0,2.5]

vector_scale function

The vector_scale function calculates the result of vector scaling.

Examples

Query statement

* | SELECT vector_scale(array[1, 2, 3, 4], 5)

* | SELECT vector_scale('[1, 2, 3, 4]', 5)

Query and analysis results
[5.0,10.0,15.0,20.0]

vector_multiply function

The vector_multiply function calculates the product of each element at the same position in two vectors.

Examples

Query statement

* | SELECT vector_multiply(array[1, 2, 3, 4], array[2, 3, 1, 1.5])

* | SELECT vector_multiply('[1, 2, 3, 4]', '[2, 3, 1, 1.5]')

Query and analysis results
[2.0,6.0,3.0,6.0]

vector_divide function

The vector_divide function calculates the division of each element at the same position in two vectors.

Examples

Query statement

* | SELECT vector_divide(array[1, 2, 3, 4], array[2, 3, 1, 1.5])

* | SELECT vector_divide('[1, 2, 3, 4]', '[2, 3, 1, 1.5]')

Query and analysis results
[0.5,0.6666666666666666,3.0,2.6666666666666667]

vector_add_value function

The vector_add_value function adds a constant to each element in a vector.

Examples

Query statement

* | SELECT vector_add_value(array[1, 2, 3, 4], 2.0)

* | SELECT vector_add_value('[1, 2, 3, 4]', 2.0)

Query and analysis results
[3.0,4.0,5.0,6.0]

vector_fill_nan function

The vector_fill_nan function specifies a value for an element that is left empty in a vector. IfNaN,null,Inf, or-Infexists in a vector, the related element is considered empty.

You can specify different values forfill_option, such as:

value=0. You can replace 0 with other digits.
mean
median
min
max

Examples

Query statement

Replace null with value=4:

* | SELECT vector_fill_nan('[1, null, 3, 4]', 'value=4')

Or replace null with the maximum value of the array:

* | SELECT vector_fill_nan('[1, null, 3, 4]', 'max')

Query and analysis results
[1.0,4.0,3.0,4.0]