src/ports/postgres/modules/knn/test/knn.sql_in

/* ----------------------------------------------------------------------- *//**
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 *//* ----------------------------------------------------------------------- */

m4_include(`SQLCommon.m4')
/* -----------------------------------------------------------------------------
 * Test knn.
 *
 * FIXME: Verify results
 * -------------------------------------------------------------------------- */

drop table if exists knn_train_data;
create table knn_train_data (
id  integer,
data    integer[],
label   integer);
copy knn_train_data (id, data, label) from stdin delimiter '|';
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
\.
DROP TABLE IF EXISTS knn_train_data_reg;
CREATE TABLE knn_train_data_reg (
                    id integer, 
                    data integer[], 
                    label float
                    );
COPY knn_train_data_reg (id, data, label) from stdin delimiter '|';
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
\.
create table knn_test_data (
id  integer,
data integer[]);
copy knn_test_data (id, data) from stdin delimiter '|';
1|{2,1}
2|{2,6}
3|{15,40}
4|{12,1}
5|{2,90}
6|{50,45}
\.
drop table if exists madlib_knn_result_classification;
select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False);
select assert(array_agg(prediction order by id)='{1,1,0,1,0,0}', 'Wrong output in classification with k=3') from madlib_knn_result_classification;

drop table if exists madlib_knn_result_classification;
select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,True);
select assert(array_agg(x)= '{1,2,3}','Wrong output in classification with k=3') from (select unnest(k_nearest_neighbours) as x from madlib_knn_result_classification where id = 1 order by x asc) y;

drop table if exists madlib_knn_result_regression;
select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False);
select assert(array_agg(prediction order by id)='{1,1,0.5,1,0.25,0.25}', 'Wrong output in regression') from madlib_knn_result_regression;

drop table if exists madlib_knn_result_regression;
select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',3,True);
select assert(array_agg(x)= '{1,2,3}' , 'Wrong output in regression with k=3') from (select unnest(k_nearest_neighbours) as x from madlib_knn_result_regression where id = 1 order by x asc) y;

drop table if exists madlib_knn_result_classification;
select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',False);
select assert(array_agg(prediction order by id)='{1,1,0,1,0,0}', 'Wrong output in classification with k=1') from madlib_knn_result_classification;

select knn();
select knn('help');