forked from StarRocks/starrocks
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtest_math
More file actions
139 lines (139 loc) · 5.37 KB
/
Copy pathtest_math
File metadata and controls
139 lines (139 loc) · 5.37 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
-- name: test_vector_math
create table t1 (id int, data array<float>) engine = olap distributed by hash(id) properties ("replication_num" = "1");
-- result:
-- !result
insert into t1 values(1, array<float>[0.1, 0.2, 0.3]), (2, array<float>[0.2, 0.1, 0.3]), (3, array<float>[0.3, 0.2, 0.1]);
-- result:
-- !result
select round(cosine_similarity(array<float>[0.1, 0.2, 0.3], data), 3) as dist, id from t1 order by dist desc, id;
-- result:
1.0 1
0.929 2
0.714 3
-- !result
select round(cosine_similarity(array<float>[0.1, 0.2, 0.3], array<float>[0.1, 0.2, 0.3]), 3) as dist;
-- result:
1.0
-- !result
select round(cosine_similarity_norm(array<float>[0.1, 0.2, 0.3], array<float>[0.1, 0.2, 0.3]), 3) as dist;
-- result:
0.14
-- !result
select round(l2_distance(array<float>[0.1, 0.2, 0.3], data), 3)as dist, id from t1 order by dist desc, id;
-- result:
0.08 3
0.02 2
0.0 1
-- !result
select round(l2_distance(array<float>[0.1, 0.2, 0.3], array<float>[0.1, 0.2, 0.3]), 3) as dist;
-- result:
0.0
-- !result
create table test_vector (id int, data array<float>) ENGINE=olap DUPLICATE KEY (id) DISTRIBUTED BY HASH(id) properties ("replication_num" = "1");
-- result:
-- !result
insert into test_vector values (1, array<float>[0.1, 0.2, 0.3]), (2, array<float>[0.2, 0.1, 0.3]);
-- result:
-- !result
insert into test_vector values (3, array<float>[0.15, 0.25, 0.32]), (4, array<float>[0.12, 0.11, 0.32]);
-- result:
-- !result
insert into test_vector values (5, array<float>[0.25, 0.12, 0.13]), (6, array<float>[0.22, 0.01, 0.39]);
-- result:
-- !result
select id, data, round(cosine_similarity(array<float>[0.1, 0.2, 0.3], data), 3) as sim from test_vector order by sim desc, id;
-- result:
1 [0.1,0.2,0.3] 1.0
3 [0.15,0.25,0.32] 0.994
4 [0.12,0.11,0.32] 0.968
2 [0.2,0.1,0.3] 0.929
6 [0.22,0.01,0.39] 0.841
5 [0.25,0.12,0.13] 0.768
-- !result
select a.id, b.id, a.data, b.data, round(cosine_similarity(a.data, b.data), 3) as sim from test_vector as a cross join test_vector as b order by sim desc, a.id, b.id;
-- result:
1 1 [0.1,0.2,0.3] [0.1,0.2,0.3] 1.0
2 2 [0.2,0.1,0.3] [0.2,0.1,0.3] 1.0
3 3 [0.15,0.25,0.32] [0.15,0.25,0.32] 1.0
4 4 [0.12,0.11,0.32] [0.12,0.11,0.32] 1.0
5 5 [0.25,0.12,0.13] [0.25,0.12,0.13] 1.0
6 6 [0.22,0.01,0.39] [0.22,0.01,0.39] 1.0
1 3 [0.1,0.2,0.3] [0.15,0.25,0.32] 0.994
3 1 [0.15,0.25,0.32] [0.1,0.2,0.3] 0.994
2 4 [0.2,0.1,0.3] [0.12,0.11,0.32] 0.975
4 2 [0.12,0.11,0.32] [0.2,0.1,0.3] 0.975
1 4 [0.1,0.2,0.3] [0.12,0.11,0.32] 0.968
4 1 [0.12,0.11,0.32] [0.1,0.2,0.3] 0.968
2 6 [0.2,0.1,0.3] [0.22,0.01,0.39] 0.967
6 2 [0.22,0.01,0.39] [0.2,0.1,0.3] 0.967
3 4 [0.15,0.25,0.32] [0.12,0.11,0.32] 0.952
4 3 [0.12,0.11,0.32] [0.15,0.25,0.32] 0.952
4 6 [0.12,0.11,0.32] [0.22,0.01,0.39] 0.947
6 4 [0.22,0.01,0.39] [0.12,0.11,0.32] 0.947
2 3 [0.2,0.1,0.3] [0.15,0.25,0.32] 0.932
3 2 [0.15,0.25,0.32] [0.2,0.1,0.3] 0.932
1 2 [0.1,0.2,0.3] [0.2,0.1,0.3] 0.929
2 1 [0.2,0.1,0.3] [0.1,0.2,0.3] 0.929
2 5 [0.2,0.1,0.3] [0.25,0.12,0.13] 0.881
5 2 [0.25,0.12,0.13] [0.2,0.1,0.3] 0.881
1 6 [0.1,0.2,0.3] [0.22,0.01,0.39] 0.841
6 1 [0.22,0.01,0.39] [0.1,0.2,0.3] 0.841
3 6 [0.15,0.25,0.32] [0.22,0.01,0.39] 0.827
6 3 [0.22,0.01,0.39] [0.15,0.25,0.32] 0.827
3 5 [0.15,0.25,0.32] [0.25,0.12,0.13] 0.823
5 3 [0.25,0.12,0.13] [0.15,0.25,0.32] 0.823
5 6 [0.25,0.12,0.13] [0.22,0.01,0.39] 0.779
6 5 [0.22,0.01,0.39] [0.25,0.12,0.13] 0.779
4 5 [0.12,0.11,0.32] [0.25,0.12,0.13] 0.771
5 4 [0.25,0.12,0.13] [0.12,0.11,0.32] 0.771
1 5 [0.1,0.2,0.3] [0.25,0.12,0.13] 0.768
5 1 [0.25,0.12,0.13] [0.1,0.2,0.3] 0.768
-- !result
select a.id, b.id, a.data, b.data, round(cosine_similarity(a.data, b.data), 3) as sim from test_vector as a cross join test_vector as b order by sim desc, a.id, b.id;
-- result:
1 1 [0.1,0.2,0.3] [0.1,0.2,0.3] 1.0
2 2 [0.2,0.1,0.3] [0.2,0.1,0.3] 1.0
3 3 [0.15,0.25,0.32] [0.15,0.25,0.32] 1.0
4 4 [0.12,0.11,0.32] [0.12,0.11,0.32] 1.0
5 5 [0.25,0.12,0.13] [0.25,0.12,0.13] 1.0
6 6 [0.22,0.01,0.39] [0.22,0.01,0.39] 1.0
1 3 [0.1,0.2,0.3] [0.15,0.25,0.32] 0.994
3 1 [0.15,0.25,0.32] [0.1,0.2,0.3] 0.994
2 4 [0.2,0.1,0.3] [0.12,0.11,0.32] 0.975
4 2 [0.12,0.11,0.32] [0.2,0.1,0.3] 0.975
1 4 [0.1,0.2,0.3] [0.12,0.11,0.32] 0.968
4 1 [0.12,0.11,0.32] [0.1,0.2,0.3] 0.968
2 6 [0.2,0.1,0.3] [0.22,0.01,0.39] 0.967
6 2 [0.22,0.01,0.39] [0.2,0.1,0.3] 0.967
3 4 [0.15,0.25,0.32] [0.12,0.11,0.32] 0.952
4 3 [0.12,0.11,0.32] [0.15,0.25,0.32] 0.952
4 6 [0.12,0.11,0.32] [0.22,0.01,0.39] 0.947
6 4 [0.22,0.01,0.39] [0.12,0.11,0.32] 0.947
2 3 [0.2,0.1,0.3] [0.15,0.25,0.32] 0.932
3 2 [0.15,0.25,0.32] [0.2,0.1,0.3] 0.932
1 2 [0.1,0.2,0.3] [0.2,0.1,0.3] 0.929
2 1 [0.2,0.1,0.3] [0.1,0.2,0.3] 0.929
2 5 [0.2,0.1,0.3] [0.25,0.12,0.13] 0.881
5 2 [0.25,0.12,0.13] [0.2,0.1,0.3] 0.881
1 6 [0.1,0.2,0.3] [0.22,0.01,0.39] 0.841
6 1 [0.22,0.01,0.39] [0.1,0.2,0.3] 0.841
3 6 [0.15,0.25,0.32] [0.22,0.01,0.39] 0.827
6 3 [0.22,0.01,0.39] [0.15,0.25,0.32] 0.827
3 5 [0.15,0.25,0.32] [0.25,0.12,0.13] 0.823
5 3 [0.25,0.12,0.13] [0.15,0.25,0.32] 0.823
5 6 [0.25,0.12,0.13] [0.22,0.01,0.39] 0.779
6 5 [0.22,0.01,0.39] [0.25,0.12,0.13] 0.779
4 5 [0.12,0.11,0.32] [0.25,0.12,0.13] 0.771
5 4 [0.25,0.12,0.13] [0.12,0.11,0.32] 0.771
1 5 [0.1,0.2,0.3] [0.25,0.12,0.13] 0.768
5 1 [0.25,0.12,0.13] [0.1,0.2,0.3] 0.768
-- !result
select id, data, round(l2_distance(array<float>[0.1, 0.2, 0.3], data), 3) as sim from test_vector order by sim desc, id;
-- result:
6 [0.22,0.01,0.39] 0.059
5 [0.25,0.12,0.13] 0.058
2 [0.2,0.1,0.3] 0.02
4 [0.12,0.11,0.32] 0.009
3 [0.15,0.25,0.32] 0.005
1 [0.1,0.2,0.3] 0.0
-- !result