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 `"..."`


---