VARBYTE Examples - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2025-01-20
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

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