Skip to content

cosine_similarity()

Function Description

cosine_similarity() is the cosine similarity, which measures the cosine value of the angle between two vectors, indicating their similarity by their proximity in multidimensional space, where 1 means exactly similar and -1 means completely dissimilar. The calculation of cosine similarity is achieved by dividing the inner product of two vectors by the product of their l2 norms.

Function Syntax

> SELECT cosine_similarity(vector1, vector2) AS similarity FROM table_name;

Example

drop table if exists vec_table;
create table vec_table(a int, b vecf32(3), c vecf64(3));
insert into vec_table values(1, "[1,2,3]", "[4,5,6]");
mysql> select * from vec_table;
+-------+--------------------------+
| a | b | c |
+-------+--------------------------+
| 1 | [1, 2, 3] | [4, 5, 6] |
+-------+--------------------------+
1 row in set (0.00 sec)

mysql> select cosine_similarity(b,"[1,2,3]") from vec_table;
+------------------------------------+
| cosine_similarity(b, [1,2,3]) |
+------------------------------------+
| 1 |
+------------------------------------+
1 row in set (0.00 sec)

limit

  • Both parameter vectors must have the same dimension.
  • The value of cosine similarity lies between -1 and 1.
  • The input vector is not allowed to be a 0 vector because this will result in a divided by zero, which is mathematically undefined. In practical applications, we generally think that the cosine similarity between zero vectors and any other vectors is 0, because there is no similarity in any direction between them.