Input (Target table and Reference table)
DROP TABLE target_mobile_data_dense;
CREATE TABLE target_mobile_data_dense (userid int, CallDuration double precision, DataCounter double precision, SMS double precision);
INSERT INTO target_mobile_data_dense VALUES(1, 0.0000333, 0.2, 0.1);
INSERT INTO target_mobile_data_dense VALUES(2, 0.5, 0.4, 0.4);
INSERT INTO target_mobile_data_dense VALUES(3, 1, 0.8, 0.9);
INSERT INTO target_mobile_data_dense VALUES(4, 0.01, 0.4, 0.2);
DROP TABLE ref_mobile_data_dense;
CREATE TABLE ref_mobile_data_dense (userid int, CallDuration double precision, DataCounter double precision, SMS double precision);
INSERT INTO ref_mobile_data_dense VALUES(5,0.93, 0.4, 0.7);
INSERT INTO ref_mobile_data_dense VALUES(6,0.83, 0.3, 0.6);
INSERT INTO ref_mobile_data_dense VALUES(7,0.73, 0.5, 0.7);
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','cosine','manhattan')
topk(2)
) 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 |