TD_VectorDistance Example | VectorDistance | Teradata Vantage - Example: TD_VectorDistance Using Euclidean, Manhattan, and Cosine - Analytics Database

Database Analytic Functions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-06
dita:mapPath
gjn1627595495337.ditamap
dita:ditavalPath
ayr1485454803741.ditaval
dita:id
jmh1512506877710
Product Category
Teradata Vantageā„¢

Target Table

Userid CallDuration DataCounter SMS
1 0.0000333 0.2 0.1
2 0.5 0.4 0.4
3 1 0.8 0.9
4 0.01 0.4 0.2

Reference Table

Userid CallDuration DataCounter SMS
5 0.93 0.4 0.7
6 0.83 0.3 0.6
7 0.73 0.5 0.7

TD_VectorDistance SQL Call

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','cosine','manhattan')
topk(2)
) AS dt order by 3,1,2,4;

TD_VectorDistance 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