You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@madlib.apache.org by hpandeycodeit <gi...@git.apache.org> on 2018/01/16 17:34:58 UTC
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
GitHub user hpandeycodeit opened a pull request:
https://github.com/apache/madlib/pull/225
Added option for weighted average for both classification and regress…
Added option for weighted average for both classification and regression Models. Jira#1181
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/hpandeycodeit/incubator-madlib knn_dev_1181
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/madlib/pull/225.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #225
----
commit b9d54d56b960f931f7ec4c7ce4525579be9f823c
Author: hpandeycodeit <hp...@...>
Date: 2018-01-16T17:32:41Z
Added option for weighted average for both classification and regression models
----
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r161917948
--- Diff: src/ports/postgres/modules/knn/knn.sql_in ---
@@ -412,7 +451,8 @@ CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.knn(
output_table,
k,
output_neighbors,
- fn_dist
+ fn_dist,
+ weighted_avg
--- End diff --
Two overloaded functions are missing (one seems to be an old issue, and the other is due to this PR):
1.
```
madlib.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
)
```
2.
```
madlib.knn(point_source,
point_column_name,
point_id,
label_column_name,
test_source,
test_column_name,
test_id,
output_table,
k,
output_neighbors
)
```
The first one is a call which does not have the last param specified, and the second function misses both the last two optional params. This should take in default values and work, but it currently fails.
I just ran through the examples in the user docs, and got the following error for one of the examples:
```
greenplum=# SELECT * FROM madlib.knn(
greenplum(# 'knn_train_data', -- Table of training data
greenplum(# 'data', -- Col name of training data
greenplum(# 'id', -- Col name of id in train data
greenplum(# 'label', -- Training labels
greenplum(# 'knn_test_data', -- Table of test data
greenplum(# 'data', -- Col name of test data
greenplum(# 'id', -- Col name of id in test data
greenplum(# 'knn_result_classification', -- Output table
greenplum(# 3, -- Number of nearest neighbors
greenplum(# True, -- True to list nearest-neighbors by id
greenplum(# 'madlib.squared_dist_norm2' -- Distance function
greenplum(# );
ERROR: function madlib.knn(unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer, boolean, unknown) does not exist
LINE 1: SELECT * FROM madlib.knn(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
```
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r163653414
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -212,22 +244,27 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
WHERE {y_temp_table}.r <= {k_val}
""".format(**locals()))
- plpy.execute(
- """
+ plpy.execute("""
CREATE TABLE {output_table} AS
- SELECT {test_id_temp} AS id, {test_column_name}
+ {view_def}
+ SELECT knn_temp.{test_id_temp} AS id ,
+ knn_test.data
{pred_out}
--- End diff --
This `pred_out` doesn't seem right for classification with weighted averaging. Without weighted averaging, we just get the mode as the class predicted. But, with weighted averaging, we must present the class corresponding to the one with the highest weighted sum as the prediction value, and not the highest weighted sum itself.
We should also take multi-class scenario into account while changing this.
---
[GitHub] madlib issue #225: Added option for weighted average for both classification...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/225
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/334/
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/madlib/pull/225
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162371982
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -211,23 +222,43 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
) {y_temp_table}
WHERE {y_temp_table}.r <= {k_val}
""".format(**locals()))
-
- plpy.execute(
- """
- CREATE TABLE {output_table} AS
- SELECT {test_id_temp} AS id, {test_column_name}
- {pred_out}
- {knn_neighbors}
- FROM pg_temp.{interim_table} AS knn_temp
- JOIN
- {test_source} AS knn_test ON
- knn_temp.{test_id_temp} = knn_test.{test_id}
- GROUP BY {test_id_temp} , {test_column_name}
- """.format(**locals()))
-
- plpy.execute("DROP TABLE IF EXISTS {0}".format(interim_table))
+ if weighted_avg and is_classification:
+ plpy.execute(
+ """
+ CREATE TABLE {output_table} AS
+ SELECT id, {test_column_name} ,max(prediction) as prediction
+ {k_neighbours}
+ FROM
+ ( SELECT {test_id_temp} AS id, {test_column_name}
+ {pred_out}
+ {knn_neighbors}
+ FROM pg_temp.{interim_table} AS knn_temp
+ JOIN
+ {test_source} AS knn_test ON
+ knn_temp.{test_id_temp} = knn_test.{test_id}
+ GROUP BY {test_id_temp} ,
+ {test_column_name}, {label_col_temp})
+ a {k_neighbours_unnest}
+ GROUP BY id, {test_column_name}
+ """.format(**locals()))
+ else:
+ plpy.execute(
+ """
+ CREATE TABLE {output_table} AS
+ SELECT {test_id_temp} AS id, {test_column_name}
--- End diff --
This is a subquery in the above query. Let's rewrite to make it a single query and add a wrapper if it's `weighted_avg`. Also we need comments to describe what's happening here. I had to read a couple of times to understand what's the flow for weighted_avg regression.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by hpandeycodeit <gi...@git.apache.org>.
Github user hpandeycodeit commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r164593774
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -212,22 +244,27 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
WHERE {y_temp_table}.r <= {k_val}
""".format(**locals()))
- plpy.execute(
- """
+ plpy.execute("""
CREATE TABLE {output_table} AS
- SELECT {test_id_temp} AS id, {test_column_name}
+ {view_def}
+ SELECT knn_temp.{test_id_temp} AS id ,
+ knn_test.data
{pred_out}
--- End diff --
Updated the pred_out as per our discussion.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162369486
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -167,22 +169,31 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
knn_neighbors = ""
label_out = ""
cast_to_int = ""
+ k_neighbours = ""
+ k_neighbours_unnest = ""
if output_neighbors:
knn_neighbors = (", array_agg(knn_temp.train_id ORDER BY "
"knn_temp.dist ASC) AS k_nearest_neighbours ")
+ k_neighbours = ", array_agg(distinct k_neighbours) AS k_nearest_neighbours"
--- End diff --
Let's update these names to reflect the difference between `knn_neighbors` and `k_neighbors`.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162369645
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -167,22 +169,31 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
knn_neighbors = ""
label_out = ""
cast_to_int = ""
+ k_neighbours = ""
+ k_neighbours_unnest = ""
if output_neighbors:
knn_neighbors = (", array_agg(knn_temp.train_id ORDER BY "
"knn_temp.dist ASC) AS k_nearest_neighbours ")
+ k_neighbours = ", array_agg(distinct k_neighbours) AS k_nearest_neighbours"
+ k_neighbours_unnest = ", unnest(k_nearest_neighbours) as k_neighbours"
if label_column_name:
is_classification = False
label_column_type = get_expr_type(
label_column_name, point_source).lower()
if label_column_type in ['boolean', 'integer', 'text']:
is_classification = True
cast_to_int = '::INTEGER'
-
- pred_out = ", avg({label_col_temp})".format(**locals())
+ if weighted_avg:
+ pred_out = ",sum( {label_col_temp} * 1/dist)/sum(1/dist)".format(**locals())
--- End diff --
We should avoid `**locals()` when the format list is so short.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by hpandeycodeit <gi...@git.apache.org>.
Github user hpandeycodeit commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162685159
--- Diff: src/ports/postgres/modules/knn/knn.sql_in ---
@@ -412,7 +451,8 @@ CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.knn(
output_table,
k,
output_neighbors,
- fn_dist
+ fn_dist,
+ weighted_avg
--- End diff --
@njayaram2,
Thanks for pointing it out! I will add these functions.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by hpandeycodeit <gi...@git.apache.org>.
Github user hpandeycodeit commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162684704
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -211,23 +222,43 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
) {y_temp_table}
WHERE {y_temp_table}.r <= {k_val}
""".format(**locals()))
-
- plpy.execute(
- """
- CREATE TABLE {output_table} AS
- SELECT {test_id_temp} AS id, {test_column_name}
- {pred_out}
- {knn_neighbors}
- FROM pg_temp.{interim_table} AS knn_temp
- JOIN
- {test_source} AS knn_test ON
- knn_temp.{test_id_temp} = knn_test.{test_id}
- GROUP BY {test_id_temp} , {test_column_name}
- """.format(**locals()))
-
- plpy.execute("DROP TABLE IF EXISTS {0}".format(interim_table))
+ if weighted_avg and is_classification:
+ plpy.execute(
+ """
+ CREATE TABLE {output_table} AS
+ SELECT id, {test_column_name} ,max(prediction) as prediction
+ {k_neighbours}
+ FROM
+ ( SELECT {test_id_temp} AS id, {test_column_name}
+ {pred_out}
+ {knn_neighbors}
+ FROM pg_temp.{interim_table} AS knn_temp
+ JOIN
+ {test_source} AS knn_test ON
+ knn_temp.{test_id_temp} = knn_test.{test_id}
+ GROUP BY {test_id_temp} ,
+ {test_column_name}, {label_col_temp})
+ a {k_neighbours_unnest}
+ GROUP BY id, {test_column_name}
+ """.format(**locals()))
+ else:
+ plpy.execute(
+ """
+ CREATE TABLE {output_table} AS
+ SELECT {test_id_temp} AS id, {test_column_name}
--- End diff --
@rahiyer ,
I am changing the query like this:
```
WITH vw
AS (SELECT test_id,
max(data2) data_dist
FROM (SELECT test_id,
Sum(1 / dist) data2
FROM madlib_temp
GROUP BY test_id,
label) a
GROUP BY test_id)
SELECT test_vw.test_id,
knn_test.data,
test_vw.data_dist prediction,
Array_agg(knn_temp.train_id order by dist asc) AS k_nearest_neighbours
FROM madlib_temp AS knn_temp
JOIN knn_test_data AS knn_test
ON knn_temp.test_id = knn_test.id
JOIN vw AS test_vw
ON knn_temp.test_id = test_vw.test_id
GROUP BY knn_temp.test_id,
test_vw.test_id,
test_vw.data_dist,
data
ORDER BY test_vw.test_id;
```
here the max of the distance is calculated seperately in a view and then using it to populate it with the existing data. Let me know your thoughts on this.
Also, can you provide more details on "add a wrapper if it's weighted_avg" comment?
---
[GitHub] madlib issue #225: Added option for weighted average for both classification...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/225
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/324/
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by hpandeycodeit <gi...@git.apache.org>.
Github user hpandeycodeit commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r164594150
--- Diff: src/ports/postgres/modules/knn/test/knn.sql_in ---
@@ -72,43 +72,55 @@ copy knn_test_data (id, data) from stdin delimiter '|';
\.
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,'MADLIB_SCHEMA.squared_dist_norm2');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.squared_dist_norm2',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);
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,'MADLIB_SCHEMA.squared_dist_norm2');
+select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.squared_dist_norm2',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',3,False,NULL);
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,NULL,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,False,'MADLIB_SCHEMA.dist_norm1');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.dist_norm1',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,False,'MADLIB_SCHEMA.dist_angle');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.dist_angle',False);
select assert(array_agg(prediction order by id)='{1,0,0,1,0,1}', '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,False,'MADLIB_SCHEMA.dist_tanimoto');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.dist_tanimoto',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_regression;
-select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.dist_norm1');
+select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.dist_norm1',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',4,False,'MADLIB_SCHEMA.dist_angle');
+select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.dist_angle',False);
select assert(array_agg(prediction order by id)='{0.75,0.25,0.25,0.75,0.25,1}', 'Wrong output in regression') from madlib_knn_result_regression;
--- End diff --
Changed these test cases as suggested.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r163657952
--- Diff: src/ports/postgres/modules/knn/test/knn.sql_in ---
@@ -72,43 +72,55 @@ copy knn_test_data (id, data) from stdin delimiter '|';
\.
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,'MADLIB_SCHEMA.squared_dist_norm2');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.squared_dist_norm2',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);
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,'MADLIB_SCHEMA.squared_dist_norm2');
+select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.squared_dist_norm2',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',3,False,NULL);
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,NULL,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,False,'MADLIB_SCHEMA.dist_norm1');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.dist_norm1',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,False,'MADLIB_SCHEMA.dist_angle');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.dist_angle',False);
select assert(array_agg(prediction order by id)='{1,0,0,1,0,1}', '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,False,'MADLIB_SCHEMA.dist_tanimoto');
+select knn('knn_train_data','data','id','label','knn_test_data','data','id','madlib_knn_result_classification',3,False,'MADLIB_SCHEMA.dist_tanimoto',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_regression;
-select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.dist_norm1');
+select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.dist_norm1',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',4,False,'MADLIB_SCHEMA.dist_angle');
+select knn('knn_train_data_reg','data','id','label','knn_test_data','data','id','madlib_knn_result_regression',4,False,'MADLIB_SCHEMA.dist_angle',False);
select assert(array_agg(prediction order by id)='{0.75,0.25,0.25,0.75,0.25,1}', 'Wrong output in regression') from madlib_knn_result_regression;
--- End diff --
The changes to the above test cases are not necessary since the default value for the new param added is `False` anyway. We could may be specify `False` explicitly for some test cases, and keep the default value for others.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162369682
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -167,22 +169,31 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
knn_neighbors = ""
label_out = ""
cast_to_int = ""
+ k_neighbours = ""
+ k_neighbours_unnest = ""
if output_neighbors:
knn_neighbors = (", array_agg(knn_temp.train_id ORDER BY "
"knn_temp.dist ASC) AS k_nearest_neighbours ")
+ k_neighbours = ", array_agg(distinct k_neighbours) AS k_nearest_neighbours"
+ k_neighbours_unnest = ", unnest(k_nearest_neighbours) as k_neighbours"
if label_column_name:
is_classification = False
label_column_type = get_expr_type(
label_column_name, point_source).lower()
if label_column_type in ['boolean', 'integer', 'text']:
is_classification = True
cast_to_int = '::INTEGER'
-
- pred_out = ", avg({label_col_temp})".format(**locals())
+ if weighted_avg:
+ pred_out = ",sum( {label_col_temp} * 1/dist)/sum(1/dist)".format(**locals())
+ else:
+ pred_out = ", avg({label_col_temp})".format(**locals())
if is_classification:
- pred_out = (", {schema_madlib}.mode({label_col_temp})"
- ).format(**locals())
+ if weighted_avg:
+ pred_out = ",sum(1/dist)".format(**locals())
--- End diff --
Nothing to format here.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by hpandeycodeit <gi...@git.apache.org>.
Github user hpandeycodeit commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r162684994
--- Diff: src/ports/postgres/modules/knn/knn.sql_in ---
@@ -326,6 +331,39 @@ Result, with neighbors sorted from closest to furthest:
(6 rows)
</pre>
+
+-# Run KNN for classification using the
+weighted average:
+<pre class="example">
+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;
+</pre>
+<pre class="result">
+ id | data | prediction | k_nearest_neighbours
+----+---------+---------------------+----------------------
+ 1 | {2,1} | 2.2 | {1,2,3}
+ 2 | {2,6} | 0.425 | {3,4,5}
+ 3 | {15,40} | 0.0174339622641509 | {5,6,7}
+ 4 | {12,1} | 0.0379633360193392 | {3,4,5}
+ 5 | {2,90} | 0.00306428140577315 | {6,7,9}
+ 6 | {50,45} | 0.00214165229166379 | {6,7,8}
+(6 rows)
+</pre>
+
--- End diff --
@njayaram2,
Yeah, I got the same with lower version of postgres. I am changing this query re-writing it, so this error will go away. Thanks!
---
[GitHub] madlib issue #225: Added option for weighted average for both classification...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/225
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/331/
---
[GitHub] madlib issue #225: Added option for weighted average for both classification...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:
https://github.com/apache/madlib/pull/225
Refer to this link for build results (access rights to CI server needed):
https://builds.apache.org/job/madlib-pr-build/329/
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r163653896
--- Diff: src/ports/postgres/modules/knn/knn.sql_in ---
@@ -326,6 +331,39 @@ Result, with neighbors sorted from closest to furthest:
(6 rows)
</pre>
+
+-# Run KNN for classification using the
+weighted average:
+<pre class="example">
+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;
+</pre>
+<pre class="result">
+ id | data | prediction | k_nearest_neighbours
+----+---------+---------------------+----------------------
+ 1 | {2,1} | 2.2 | {2,1,3}
+ 2 | {2,6} | 0.425 | {5,4,3}
+ 3 | {15,40} | 0.0174339622641509 | {7,6,5}
+ 4 | {12,1} | 0.0379633360193392 | {4,5,3}
+ 5 | {2,90} | 0.00306428140577315 | {9,6,7}
+ 6 | {50,45} | 0.00214165229166379 | {6,7,8}
--- End diff --
In continuation to the comment made above regarding `pred_out` for classification with weighted averaging: the prediction in this example should not be those numbers, but rather either `1` or `0`.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r161918108
--- Diff: src/ports/postgres/modules/knn/knn.sql_in ---
@@ -326,6 +331,39 @@ Result, with neighbors sorted from closest to furthest:
(6 rows)
</pre>
+
+-# Run KNN for classification using the
+weighted average:
+<pre class="example">
+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;
+</pre>
+<pre class="result">
+ id | data | prediction | k_nearest_neighbours
+----+---------+---------------------+----------------------
+ 1 | {2,1} | 2.2 | {1,2,3}
+ 2 | {2,6} | 0.425 | {3,4,5}
+ 3 | {15,40} | 0.0174339622641509 | {5,6,7}
+ 4 | {12,1} | 0.0379633360193392 | {3,4,5}
+ 5 | {2,90} | 0.00306428140577315 | {6,7,9}
+ 6 | {50,45} | 0.00214165229166379 | {6,7,8}
+(6 rows)
+</pre>
+
--- End diff --
I got the following error for this example (was running on Greenplum 5):
```
greenplum=# DROP TABLE IF EXISTS knn_result_classification;
NOTICE: table "knn_result_classification" does not exist, skipping
DROP TABLE
greenplum=# SELECT * FROM madlib.knn(
greenplum(# 'knn_train_data', -- Table of training data
greenplum(# 'data', -- Col name of training data
greenplum(# 'id', -- Col name of id in train data
greenplum(# 'label', -- Training labels
greenplum(# 'knn_test_data', -- Table of test data
greenplum(# 'data', -- Col name of test data
greenplum(# 'id', -- Col name of id in test data
greenplum(# 'knn_result_classification', -- Output table
greenplum(# 3, -- Number of nearest neighbors
greenplum(# True, -- True to list nearest-neighbors by id
greenplum(# 'madlib.squared_dist_norm2', -- Distance function
greenplum(# True -- For weighted average
greenplum(# );
ERROR: plpy.SPIError: function expression in FROM cannot refer to other relations of same query level
LINE 15: a , unnest(k_nearest_neighbours)...
^
QUERY:
CREATE TABLE knn_result_classification AS
SELECT id, data ,max(prediction) as prediction
, array_agg(distinct k_neighbours) AS k_nearest_neighbours
FROM
( SELECT __madlib_temp_test_id_temp29900589_1516144312_53639332__ AS id, data
,sum(1/dist) AS prediction
, array_agg(knn_temp.train_id ORDER BY knn_temp.dist ASC) AS k_nearest_neighbours
FROM pg_temp.__madlib_temp_interim_table75130626_1516144312_10216040__ AS knn_temp
JOIN
knn_test_data AS knn_test ON
knn_temp.__madlib_temp_test_id_temp29900589_1516144312_53639332__ = knn_test.id
GROUP BY __madlib_temp_test_id_temp29900589_1516144312_53639332__ ,
data, __madlib_temp_label_col_temp66682446_1516144312_5242078__)
a , unnest(k_nearest_neighbours) as k_neighbours
GROUP BY id, data
CONTEXT: Traceback (most recent call last):
PL/Python function "knn", line 36, in <module>
weighted_avg
PL/Python function "knn", line 242, in knn
PL/Python function "knn"
```
This might be because some functions/features available in Postgres-9.x are not available in Greenplum. So we should use functions that would work on both.
---
[GitHub] madlib pull request #225: Added option for weighted average for both classif...
Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:
https://github.com/apache/madlib/pull/225#discussion_r163652456
--- Diff: src/ports/postgres/modules/knn/knn.py_in ---
@@ -178,11 +183,38 @@ def knn(schema_madlib, point_source, point_column_name, point_id,
if label_column_type in ['boolean', 'integer', 'text']:
is_classification = True
cast_to_int = '::INTEGER'
+ if weighted_avg:
+ pred_out = ",sum( {label_col_temp} * 1/dist)/sum(1/dist)".format(
+ label_col_temp=label_col_temp)
+ else:
+ pred_out = ", avg({label_col_temp})".format(
+ label_col_temp=label_col_temp)
- pred_out = ", avg({label_col_temp})".format(**locals())
if is_classification:
- pred_out = (", {schema_madlib}.mode({label_col_temp})"
- ).format(**locals())
+ if weighted_avg:
+ # This view is to calculate the max value of sum of the 1/distance grouped by label and Id.
+ # And this max value will be the prediction for the
+ # classification model.
+ view_def = (" WITH vw "
+ " AS (SELECT {test_id_temp} ,"
+ " max(data_sum) data_dist "
+ " FROM (SELECT {test_id_temp}, "
+ " sum(1 / dist) data_sum"
+ " FROM pg_temp.{interim_table} "
+ " GROUP BY {test_id_temp}, "
+ " {label_col_temp}) a "
+ " GROUP BY {test_id_temp} )").format(**locals())
+ # This join is needed to get the max value of predicion
+ # calculated above
+ view_join = (" JOIN vw AS knn_vw "
+ "ON knn_temp.{test_id_temp} = knn_vw.{test_id_temp}").format(
+ test_id_temp=test_id_temp)
+ view_grp_by = ", knn_vw.data_dist "
+ pred_out = ", knn_vw.data_dist"
+ else:
+ pred_out = (", {schema_madlib}.mode({label_col_temp})"
+ ).format(**locals())
+
--- End diff --
We can have this string inside a single pair of `"""..."""`, instead of multiple `"..."`
---