VectorDistance Example: Dense Input | Teradata Vantage - VectorDistance Example: Dense-Format Input, Sparse-Format Output, Default Thresholds - Teradata Vantage

Input

TargetTable:

CREATE MULTISET TABLE target_mobile_data_dense (
  userid INTEGER,
  CallDuration REAL,
  DataCounter REAL,
  SMS REAL
);

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);

ReferenceTable

CREATE MULTISET TABLE ref_mobile_data_dense (
  userid INTEGER,
  CallDuration REAL,
  DataCounter REAL,
  SMS REAL
);

INSERT INTO ref_mobile_data_dense VALUES (5,0.93, 0.4, 0.7);

SQL Call

SELECT * FROM VectorDistance (
  ON target_mobile_data_dense AS TargetTable PARTITION BY ANY
  ON ref_mobile_data_dense AS ReferenceTable DIMENSION
  USING
  TargetIdColumns ('UserID')
  TargetColumns ('CallDuration', 'DataCounter', 'SMS')
  DistanceMeasure ('Euclidean')
) AS dt ORDER BY 1;

Output

 TARGET_USERID  REF_USERID    TYPE         DISTANCE      
-------------------------------------------------------------------
              1           5          euclidean    1.12465019517762
              2           5          euclidean    0.524309064791334
              3           5          euclidean    0.452658810804166
              4           5          euclidean    1.04709120838197