KNN Example - Teradata Vantage

This example uses the following user-defined distance metric, defined in the Java class com.example.MyDistance:

package com.example;
import com.asterdata.ncluster.sqlmr.data.RowView;
import com.asterdata.sqlmr.analytics.classification.knn.distance.Distance;
public class MyDistance implements Distance {
    /**
    * calculate the distance between the test row and the training row.
    * note: 1.don't reverse the sequence of parameters
    *       2. the columns of trainingRowView is 'responseColumn, f1,f2,...,fn'
    *       3. the columns of testRowView is the same as TEST_TABLE
    *       4. all the trainingRowView and testRowView is zero-based
    *          (0 <= index && index < getColumnCount())
    *
    * @param testRowView
    *           stands for a point in the test data set
    * @param trainingRowView
    *           stands for a point in the training data set, the columns is the
    * columns in distanceFeatures argument
    * @return the double value of distance
    */
    @Override
    public double calculate(RowView testRowView, RowView trainingRowView) {
        return Math.abs(testRowView.getIntAt(1) - trainingRowView.getIntAt(1));
    }
}

Input

The TrainingTable has as dimensions five attributes of personal computers—price, speed, hard disk size, RAM, and screen size. The table has 5008 rows, categorized into eight price categories.

The test table has more than 1000 rows.

SQL Call

SELECT * FROM KNN (
  ON computers_train1_clustered AS TrainingTable
  ON computers_test1 AS TestTable
  OUT TABLE OutputTable (knn_output)
  USING
  K (50)
  ResponseColumn ('computer_category')
  DistanceFeatures ('price', 'speed', 'hd', 'ram', 'screen')
  VotingWeight (1)
  IDColumn ('id')
) AS dt;

Output

Successful!
 The final result is successfully stored in the table specified in OutputTable argument.

This query returns the following table:

SELECT * FROM knn_output ORDER BY id;