Input
DROP TABLE TargetTable_varbyte; CREATE MULTISET TABLE TargetTable_varbyte(id integer, array_col VARBYTE(32000)) NO PRIMARY INDEX; INSERT INTO TargetTable_varbyte values(1, '01B8A3497375013F9A9999999999C93F9A9999999999B93F'XB); INSERT INTO TargetTable_varbyte values(2, '000000000000E03F9A9999999999D93F9A9999999999D93F'XB); INSERT INTO TargetTable_varbyte values(3, '000000000000F03F9A9999999999E93FCDCCCCCCCCCCEC3F'XB); INSERT INTO TargetTable_varbyte values(4, '7B14AE47E17A843F9A9999999999D93F9A9999999999C93F'XB); DROP TABLE RefTable_varbyte; CREATE MULTISET TABLE RefTable_varbyte(id integer, array_col VARBYTE(32000)) NO PRIMARY INDEX; INSERT INTO RefTable_varbyte values(5, 'C3F5285C8FC2ED3F9A9999999999D93F666666666666E63F'XB); INSERT INTO RefTable_varbyte values(6, '8FC2F5285C8FEA3F333333333333D33F333333333333E33F'XB); INSERT INTO RefTable_varbyte values(7, '5C8FC2F5285CE73F000000000000E03F666666666666E63F'XB);
Example 1: Euclidean, Cosine, Manhattan Distance
Query
SELECT target_id, reference_id, distancetype, cast(distance as decimal(36,15)) as distance FROM TD_VECTORDISTANCE ( ON TargetTable_varbyte as TargetTable ON RefTable_varbyte as ReferenceTable Dimension USING TargetIDColumn('id') TargetFeatureColumns('array_col') RefIDColumn('id') RefFeatureColumns('array_col') DistanceMeasure('euclidean','cosine','manhattan') topk(2) EmbeddingSize(3) ) as dt order by 3,1,2,4;
Output
Target_ID | Reference_ID | DistanceType | Distance |
---|---|---|---|
1 | 5 | cosine | 0.45486518 |
1 | 7 | cosine | 0.32604815 |
2 | 5 | cosine | 0.02608923 |
2 | 7 | cosine | 0.00797609 |
3 | 5 | cosine | 0.02415054 |
3 | 7 | cosine | 0.00337338 |
4 | 5 | cosine | 0.43822243 |
4 | 7 | cosine | 0.31184844 |
1 | 6 | euclidean | 0.97408661 |
1 | 7 | euclidean | 0.99138861 |
2 | 6 | euclidean | 0.39862262 |
2 | 7 | euclidean | 0.39102429 |
3 | 5 | euclidean | 0.45265881 |
3 | 7 | euclidean | 0.45044423 |
4 | 6 | euclidean | 0.91782351 |
4 | 7 | euclidean | 0.88226980 |
1 | 6 | manhattan | 1.42996670 |
1 | 7 | manhattan | 1.62996670 |
2 | 6 | manhattan | 0.63000000 |
2 | 7 | manhattan | 0.63000000 |
3 | 5 | manhattan | 0.67000000 |
3 | 7 | manhattan | 0.77000000 |
4 | 6 | manhattan | 1.32000000 |
4 | 7 | manhattan | 1.32000000 |
Example 2: Minkowski Distance
Query
SELECT target_id, reference_id, distancetype, cast(distance as decimal(36,8)) as distance FROM TD_VECTORDISTANCE ( ON target_mobile_data_dense as TargetTable ON ref_mobile_data_dense as ReferenceTable Dimension USING TargetIDColumn('userid') TargetFeatureColumns('CallDuration','DataCounter','SMS') RefIDColumn('userid') RefFeatureColumns('CallDuration','DataCounter','SMS') DistanceMeasure('euclidean','Minkowski') topk(-1) PValue(2) ) as dt order by 3,1,2,4;
Output
Target_ID | Reference_ID | DistanceType | Distance |
---|---|---|---|
1 | 5 | euclidean | 1.12465 |
1 | 6 | euclidean | 0.974087 |
1 | 7 | euclidean | 0.991389 |
2 | 5 | euclidean | 0.524309 |
2 | 6 | euclidean | 0.398623 |
2 | 7 | euclidean | 0.391024 |
3 | 5 | euclidean | 0.452659 |
3 | 6 | euclidean | 0.607371 |
3 | 7 | euclidean | 0.450444 |
4 | 5 | euclidean | 1.047091 |
4 | 6 | euclidean | 0.917824 |
4 | 7 | euclidean | 0.88227 |
1 | 5 | minkowski | 1.12465 |
1 | 6 | minkowski | 0.974087 |
1 | 7 | minkowski | 0.991389 |
2 | 5 | minkowski | 0.524309 |
2 | 6 | minkowski | 0.398623 |
2 | 7 | minkowski | 0.391024 |
3 | 5 | minkowski | 0.452659 |
3 | 6 | minkowski | 0.607371 |
3 | 7 | minkowski | 0.450444 |
4 | 5 | minkowski | 1.047091 |
4 | 6 | minkowski | 0.917824 |
4 | 7 | minkowski | 0.88227 |