Contents

Warning: This MADlib method is still in early stage development. There may be some issues that will be addressed in a future version. Interface and implementation are subject to change.

K-nearest neighbors is a method for finding the k closest points to a given data point in terms of a given metric. Its input consists of data points as features from testing examples, and it looks for k closest points in the training set for each of the data points in test set. The output of KNN depends on the type of task. For classification, the output is the majority vote of the classes of the k nearest data points. That is, the testing example gets assigned the most popular class from the nearest neighbors. For regression, the output is the average of the values of k nearest neighbors of the given test point.

Usage

knn( point_source,
     point_column_name,
     point_id,
     label_column_name,
     test_source,
     test_column_name,
     test_id,
     output_table,
     k,
     output_neighbors,
     fn_dist,
     weighted_avg
   )

Arguments

point_source

TEXT. Name of the table containing the training data points. Training data points are expected to be stored row-wise in a column of type DOUBLE PRECISION[].

point_column_name

TEXT. Name of the column with training data points.

point_id

TEXT. Name of the column in 'point_source’ containing source data ids. The ids are of type INTEGER with no duplicates. They do not need to be contiguous. This parameter must be used if the list of nearest neighbors are to be output, i.e., if the parameter 'output_neighbors' below is TRUE or if 'label_column_name' is NULL.

label_column_name

TEXT. Name of the column with labels/values of training data points. If this column is a Boolean, integer or text, it will run KNN classification, else if it is double precision values will run KNN regression. If you set this to NULL, it will only return the set of neighbors without actually doing classification or regression.

test_source

TEXT. Name of the table containing the test data points. Testing data points are expected to be stored row-wise in a column of type DOUBLE PRECISION[].

test_column_name

TEXT. Name of the column with testing data points.

test_id

TEXT. Name of the column having ids of data points in test data table.

output_table

TEXT. Name of the table to store final results.

k (optional)

INTEGER. default: 1. Number of nearest neighbors to consider. For classification, should be an odd number to break ties, otherwise the result may depend on ordering of the input data.

output_neighbors (optional)

BOOLEAN default: TRUE. Outputs the list of k-nearest neighbors that were used in the voting/averaging, sorted from closest to furthest.

fn_dist (optional)

TEXT, default: 'squared_dist_norm2'. The name of the function used to calculate the distance between data points.

The following distance functions can be used:

dist_norm1: 1-norm/Manhattan
dist_norm2: 2-norm/Euclidean
squared_dist_norm2: squared Euclidean distance
dist_angle: angle
dist_tanimoto: tanimoto
user defined function with signature DOUBLE PRECISION[] x, DOUBLE PRECISION[] y -> DOUBLE PRECISION

weighted_avg (optional)

BOOLEAN, default: FALSE. Calculates classification or regression values using a weighted average. The idea is to weigh the contribution of each of the k neighbors according to their distance to the test point, giving greater influence to closer neighbors. The distance function 'fn_dist' specified above is used.

For classification, majority voting weighs a neighbor according to inverse distance.

For regression, the inverse distance weighting approach is used from Shepard [4].

Output Format

The output of the KNN module is a table with the following columns:

id	INTEGER. The ids of test data points.
test_column_name	DOUBLE PRECISION[]. The test data points.
prediction	INTEGER. Label in case of classification, average value in case of regression.
k_nearest_neighbours	INTEGER[]. List of nearest neighbors, sorted closest to furthest from the corresponding test point.

Examples

Prepare some training data for classification:

DROP TABLE IF EXISTS knn_train_data;
CREATE TABLE knn_train_data (
                    id integer,
                    data integer[],
                    label integer  -- Integer label means for classification
                    );
INSERT INTO knn_train_data VALUES
(1, '{1,1}', 1),
(2, '{2,2}', 1),
(3, '{3,3}', 1),
(4, '{4,4}', 1),
(5, '{4,5}', 1),
(6, '{20,50}', 0),
(7, '{10,31}', 0),
(8, '{81,13}', 0),
(9, '{1,111}', 0);

Prepare some training data for regression:

DROP TABLE IF EXISTS knn_train_data_reg;
CREATE TABLE knn_train_data_reg (
                    id integer,
                    data integer[],
                    label float  -- Float label means for regression
                    );
INSERT INTO knn_train_data_reg VALUES
(1, '{1,1}', 1.0),
(2, '{2,2}', 1.0),
(3, '{3,3}', 1.0),
(4, '{4,4}', 1.0),
(5, '{4,5}', 1.0),
(6, '{20,50}', 0.0),
(7, '{10,31}', 0.0),
(8, '{81,13}', 0.0),
(9, '{1,111}', 0.0);

Prepare some testing data:

DROP TABLE IF EXISTS knn_test_data;
CREATE TABLE knn_test_data (
                    id integer,
                    data integer[]
                    );
INSERT INTO knn_test_data VALUES
(1, '{2,1}'),
(2, '{2,6}'),
(3, '{15,40}'),
(4, '{12,1}'),
(5, '{2,90}'),
(6, '{50,45}');

Run KNN for classification:

DROP TABLE IF EXISTS knn_result_classification;
SELECT * FROM madlib.knn(
                'knn_train_data',      -- Table of training data
                'data',                -- Col name of training data
                'id',                  -- Col name of id in train data
                'label',               -- Training labels
                'knn_test_data',       -- Table of test data
                'data',                -- Col name of test data
                'id',                  -- Col name of id in test data
                'knn_result_classification',  -- Output table
                 3,                    -- Number of nearest neighbors
                 True,                 -- True to list nearest-neighbors by id
                 'madlib.squared_dist_norm2' -- Distance function
                );
SELECT * from knn_result_classification ORDER BY id;

Result:

  id |  data   | prediction | k_nearest_neighbours
----+---------+------------+----------------------
  1 | {2,1}   |          1 | {2,1,3}
  2 | {2,6}   |          1 | {5,4,3}
  3 | {15,40} |          0 | {7,6,5}
  4 | {12,1}  |          1 | {4,5,3}
  5 | {2,90}  |          0 | {9,6,7}
  6 | {50,45} |          0 | {6,7,8}
(6 rows)

Note that the nearest neighbors are sorted from closest to furthest from the corresponding test point.

Run KNN for regression:

DROP TABLE IF EXISTS knn_result_regression;
SELECT * FROM madlib.knn(
                'knn_train_data_reg',  -- Table of training data
                'data',                -- Col name of training data
                'id',                  -- Col Name of id in train data
                'label',               -- Training labels
                'knn_test_data',       -- Table of test data
                'data',                -- Col name of test data
                'id',                  -- Col name of id in test data
                'knn_result_regression',  -- Output table
                 3,                    -- Number of nearest neighbors
                True,                  -- True to list nearest-neighbors by id
                'madlib.dist_norm2'    -- Distance function
                );
SELECT * FROM knn_result_regression ORDER BY id;

Result:

 id |  data   |    prediction     | k_nearest_neighbours
----+---------+-------------------+----------------------
  1 | {2,1}   |                 1 | {2,1,3}
  2 | {2,6}   |                 1 | {5,4,3}
  3 | {15,40} | 0.333333333333333 | {7,6,5}
  4 | {12,1}  |                 1 | {4,5,3}
  5 | {2,90}  |                 0 | {9,6,7}
  6 | {50,45} |                 0 | {6,7,8}
(6 rows)

List nearest neighbors only, without doing classification or regression:

DROP TABLE IF EXISTS knn_result_list_neighbors;
SELECT * FROM madlib.knn(
                'knn_train_data_reg',  -- Table of training data
                'data',                -- Col name of training data
                'id',                  -- Col Name of id in train data
                NULL,                  -- NULL training labels means just list neighbors
                'knn_test_data',       -- Table of test data
                'data',                -- Col name of test data
                'id',                  -- Col name of id in test data
                'knn_result_list_neighbors', -- Output table
                3                      -- Number of nearest neighbors
                );
SELECT * FROM knn_result_list_neighbors ORDER BY id;

Result, with neighbors sorted from closest to furthest:

 id |  data   | k_nearest_neighbours
----+---------+----------------------
  1 | {2,1}   | {2,1,3}
  2 | {2,6}   | {5,4,3}
  3 | {15,40} | {7,6,5}
  4 | {12,1}  | {4,5,3}
  5 | {2,90}  | {9,6,7}
  6 | {50,45} | {6,7,8}
(6 rows)

Run KNN for classification using the weighted average:

DROP TABLE IF EXISTS knn_result_classification;
SELECT * FROM madlib.knn(
                'knn_train_data',      -- Table of training data
                'data',                -- Col name of training data
                'id',                  -- Col name of id in train data
                'label',               -- Training labels
                'knn_test_data',       -- Table of test data
                'data',                -- Col name of test data
                'id',                  -- Col name of id in test data
                'knn_result_classification',  -- Output table
                 3,                    -- Number of nearest neighbors
                 True,                 -- True to list nearest-neighbors by id
                 'madlib.squared_dist_norm2', -- Distance function
                 True                 -- For weighted average
                );
SELECT * FROM knn_result_classification ORDER BY id;

 id |  data   |     prediction      | k_nearest_neighbours 
----+---------+---------------------+----------------------
  1 | {2,1}   |                 1   | {2,1,3}
  2 | {2,6}   |                 1   | {5,4,3}
  3 | {15,40} |                 0   | {7,6,5}
  4 | {12,1}  |                 1   | {4,5,3}
  5 | {2,90}  |                 0   | {9,6,7}
  6 | {50,45} |                 0   | {6,7,8}
(6 rows)

Technical Background

The training data points are vectors in a multidimensional feature space, each with a class label. The training phase of the algorithm consists only of storing the feature vectors and class labels of the training points.

In the classification phase, k is a user-defined constant, and an unlabeled vector (a test point) is classified by assigning the label which is most frequent among the k training samples nearest to that test point. In case of regression, average of the values of these k training samples is assigned to the test point.

Literature

[1] Wikipedia, k-nearest neighbors algorithm, https://en.wikipedia.org/wiki/K-nearest_neighbors_algorithm

[2] N. S. Altman: An Introduction to Kernel and Nearest-Neighbor Nonparametric Regression http://www.stat.washington.edu/courses/stat527/s13/readings/Altman_AmStat_1992.pdf

[3] Gongde Guo1, Hui Wang, David Bell, Yaxin Bi, Kieran Greer: KNN Model-Based Approach in Classification, https://ai2-s2-pdfs.s3.amazonaws.com/a7e2/814ec5db800d2f8c4313fd436e9cf8273821.pdf

[4] Shepard, Donald (1968). "A two-dimensional interpolation function for irregularly-spaced data". Proceedings of the 1968 ACM National Conference. pp. 517–524.