You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@madlib.apache.org by iyerr3 <gi...@git.apache.org> on 2018/03/07 07:12:03 UTC

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

GitHub user iyerr3 opened a pull request:

    https://github.com/apache/madlib/pull/239

    Balance Sample: Add support for grouping

    JIRA: MADLIB-1168
    
    This commit adds grouping support for balanced sampling. 
    Grouping is implemented as a loop over the existing logic, 
    with the sampling for each group run independently. 
    
    Closes #239

You can merge this pull request into a Git repository by running:

    $ git pull https://github.com/madlib/madlib feature/balanced-datasets-grouping

Alternatively you can review and apply these changes as the patch at:

    https://github.com/apache/madlib/pull/239.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 #239
    
----
commit 6c5fcfb375eaf7dc68e1ede4aca2a47b8e55309b
Author: Rahul Iyer <ri...@...>
Date:   2018-02-24T02:45:32Z

    Clean code + conform to PEP8

commit a5a0c1e2c851a923b9eb550d42dfc594b4635c64
Author: Rahul Iyer <ri...@...>
Date:   2018-02-26T23:01:34Z

    Add a Collate plpy results function

commit 8e8eca2960207ca0317ded68608c660b8d4ddb55
Author: Rahul Iyer <ri...@...>
Date:   2018-03-02T00:44:54Z

    Add grouping in get_level_frequency_distribution

commit cad4a5be732f89504ff62f4d9e68367d174fc322
Author: Rahul Iyer <ri...@...>
Date:   2018-03-07T07:07:00Z

    Ensure subqueries are filtering groups and using right count

commit 39dd6f436bb9b8d505be5204226dcc3053b1b4df
Author: Rahul Iyer <ri...@...>
Date:   2018-03-07T07:07:14Z

    Update install check to include grouping

commit d61ff28290dad27ead0f1c68d740a8ccb79f4aec
Author: Rahul Iyer <ri...@...>
Date:   2018-03-07T07:07:27Z

    Update documentation with grouping examples

----


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/362/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172922334
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=2',         -- Specified class value size. Rest of the values are sampled as is.
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             1 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             6 | 14 | Norway      |        3 |    1 |  324 |          4 |        6 |       3 | red
    +             5 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             4 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             3 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             2 |  3 | Austria     |        3 |    1 |   84 |          8 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             1 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 | 11 | Jamaica     |        1 |    4 |   11 |          2 |        1 |       3 | green
    +             5 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             8 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             2 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             7 | 16 | Spain       |        3 |    4 |  505 |         38 |        2 |       2 | red
    +             6 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             9 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             3 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(19 rows)
    --- End diff --
    
    This example asks for `blue=2` for class_size but the output has blue=1 for all groups.  Is this a bug or a cut and paste error in the example?



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r173254469
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +545,95 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the
    +classes (mainhue), with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- Grouping by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group. Further, `blue=8` in the example below will
    +be split between the four groups, resulting in two blue rows for each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=8',         -- Specified class value size. Rest of the values are outputed as is.
    +    NULL,             -- Output table size
    +    'zone'            -- Group by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             2 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             1 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             3 |  3 | Austria     |        3 |    1 |   84 |          8 |        4 |       2 | red
    +             5 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             4 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             8 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             7 | 14 | Norway      |        3 |    1 |  324 |          4 |        6 |       3 | red
    +             6 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             2 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             3 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             2 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             1 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 | 11 | Jamaica     |        1 |    4 |   11 |          2 |        1 |       3 | green
    +             6 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             7 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 16 | Spain       |        3 |    4 |  505 |         38 |        2 |       2 | red
    +             9 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +            10 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             4 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
     @anchor literature
    --- End diff --
    
    That's OK, thanks for checking.


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/363/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172958587
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
    @@ -58,28 +60,64 @@ NOSAMPLE = 'nosample'
     NEW_ID_COLUMN = '__madlib_id__'
     NULL_IDENTIFIER = '__madlib_null_id__'
     
    -def _get_level_frequency_distribution(source_table, class_col):
    -    """ Returns a dict containing the number of rows associated with each class
    +
    +def _get_level_frequency_distribution(source_table, class_col,
    +                                      grp_by_cols=None):
    +    """ Count the number of rows for each class, partitioned by the grp_by_cols
    +
    +        Returns a dict containing the number of rows associated with each class
             level. Each class level count is converted to a string using ::text.
             None is a valid key in this dict, capturing NULL value in the database.
         """
    +    if grp_by_cols and grp_by_cols.lower() != 'null':
    +        is_grouping = True
    +        grp_by_cols_comma = grp_by_cols + ', '
    +        array_grp_by_cols_comma = "array[{0}]".format(grp_by_cols) + " as group_values, "
    +    else:
    +        is_grouping = False
    +        grp_by_cols_comma = array_grp_by_cols_comma = ""
    +
    +    # In below query, the inner query groups the data using grp_by_cols + classes
    +    # and obtains the count for each combination. The outer query then groups
    +    # again by the grp_by_cols to collect the classes and counts in an array.
         query_result = plpy.execute("""
    -                    SELECT {class_col}::text AS classes,
    -                           count(*) AS class_count
    -                    FROM {source_table}
    -                    GROUP BY {class_col}
    -                 """.format(**locals()))
    +        SELECT
    +            -- For each group get the classes and their rows counts
    +            {grp_identifier} as group_values,
    +            array_agg(classes) as classes,
    +            array_agg(class_count) as class_count
    +        FROM(
    +            -- for each group and class combination present in source table
    +            -- get the count of rows for that combination
    +            SELECT
    +                {array_grp_by_cols_comma}
    +                ({class_col})::TEXT AS classes,
    +                count(*) AS class_count
    +            FROM {source_table}
    +            GROUP BY {grp_by_cols_comma} ({class_col})
    +        ) q
    +        GROUP BY {grp_identifier}
    --- End diff --
    
    In Greenplum 5.x, the above query fails on install check, with the following error:
    ```
    SELECT balance_sample('"TEST_s"', 'out_sr2', 'gr1', 'undersample ', NULL, NULL, TRUE, TRUE);
    psql:/tmp/madlib.2N5sjK/sample/test/balance_sample.sql_in.tmp:111: ERROR:  plpy.SPIError: non-integer constant in GROUP BY
    LINE 17:         GROUP BY true
                              ^
    QUERY:
            SELECT
                -- For each group get the classes and their rows counts
                true as group_values,
                array_agg(classes) as classes,
                array_agg(class_count) as class_count
            FROM(
                -- for each group and class combination present in source table
                -- get the count of rows for that combination
                SELECT
    
                    (gr1)::TEXT AS classes,
                    count(*) AS class_count
                FROM "TEST_s"
                GROUP BY  (gr1)
            ) q
            GROUP BY true
    
    CONTEXT:  Traceback (most recent call last):
      PL/Python function "balance_sample", line 23, in <module>
        return balance_sample.balance_sample(**globals())
      PL/Python function "balance_sample", line 575, in balance_sample
      PL/Python function "balance_sample", line 100, in _get_level_frequency_distribution
    PL/Python function "balance_sample"
    ```


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/366/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172920581
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -149,8 +149,10 @@ non-stratified, that is, the whole table is treated as a
     single group.
     
     @note
    -Current implementation does not support grouping_cols.  It
    -will be added in an upcoming release.
    +The 'output_table_size' and the 'class_sizes' are defined for the whole table.
    +When grouping is used, these parameters are split evenly for each group.
    +Further, if a specific class value is specified in the 'class_sizes' parameter,
    +that particular class value should be present in each group.
     </dd>
    --- End diff --
    
    Please change/add:
    "...that particular class value should be present in each group."
    to 
    "...that particular class value should be present in each group.  If not, an error will be thrown."


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r173080410
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
    @@ -58,28 +60,64 @@ NOSAMPLE = 'nosample'
     NEW_ID_COLUMN = '__madlib_id__'
     NULL_IDENTIFIER = '__madlib_null_id__'
     
    -def _get_level_frequency_distribution(source_table, class_col):
    -    """ Returns a dict containing the number of rows associated with each class
    +
    +def _get_level_frequency_distribution(source_table, class_col,
    +                                      grp_by_cols=None):
    +    """ Count the number of rows for each class, partitioned by the grp_by_cols
    +
    +        Returns a dict containing the number of rows associated with each class
             level. Each class level count is converted to a string using ::text.
             None is a valid key in this dict, capturing NULL value in the database.
         """
    +    if grp_by_cols and grp_by_cols.lower() != 'null':
    +        is_grouping = True
    +        grp_by_cols_comma = grp_by_cols + ', '
    +        array_grp_by_cols_comma = "array[{0}]".format(grp_by_cols) + " as group_values, "
    +    else:
    +        is_grouping = False
    +        grp_by_cols_comma = array_grp_by_cols_comma = ""
    +
    +    # In below query, the inner query groups the data using grp_by_cols + classes
    +    # and obtains the count for each combination. The outer query then groups
    +    # again by the grp_by_cols to collect the classes and counts in an array.
         query_result = plpy.execute("""
    -                    SELECT {class_col}::text AS classes,
    -                           count(*) AS class_count
    -                    FROM {source_table}
    -                    GROUP BY {class_col}
    -                 """.format(**locals()))
    +        SELECT
    +            -- For each group get the classes and their rows counts
    +            {grp_identifier} as group_values,
    +            array_agg(classes) as classes,
    +            array_agg(class_count) as class_count
    +        FROM(
    +            -- for each group and class combination present in source table
    +            -- get the count of rows for that combination
    +            SELECT
    +                {array_grp_by_cols_comma}
    +                ({class_col})::TEXT AS classes,
    +                count(*) AS class_count
    +            FROM {source_table}
    +            GROUP BY {grp_by_cols_comma} ({class_col})
    +        ) q
    +        GROUP BY {grp_identifier}
    --- End diff --
    
    Thanks for checking this! I've removed the constant group by clause to avoid this. 


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r173080726
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
    @@ -468,81 +544,107 @@ def balance_sample(schema_madlib, source_table, output_table, class_col,
             parsed_class_sizes = extract_keyvalue_params(class_sizes,
                                                          allow_duplicates=False,
                                                          lower_case_names=False)
    +        distinct_levels = collate_plpy_result(
    +            plpy.execute("SELECT DISTINCT ({0})::TEXT as levels FROM {1} ".
    +                         format(class_col, source_table)))['levels']
             if not parsed_class_sizes:
    -            sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
    -                                        output_table_size)
    +            sampling_strategy_str = _validate_and_get_sampling_strategy(
    +                class_sizes, output_table_size)
             else:
                 sampling_strategy_str = None
                 try:
                     for each_level, each_class_size in parsed_class_sizes.items():
    -                    _assert(each_level in actual_level_counts,
    +                    _assert(each_level in distinct_levels,
                                 "Sample: Invalid class value specified ({0})".
    -                                       format(each_level))
    +                            format(each_level))
                         each_class_size = int(each_class_size)
                         _assert(each_class_size >= 1,
                                 "Sample: Class size has to be greater than zero")
                         parsed_class_sizes[each_level] = each_class_size
    -
    -            except TypeError:
    +            except ValueError:
                     plpy.error("Sample: Invalid value for class_sizes ({0})".
                                format(class_sizes))
     
             # Get the number of rows to be sampled for each class level, based on
             # the input table, class_sizes, and output_table_size params. This also
             # includes info about the resulting sampling strategy, i.e., one of
             # UNDERSAMPLE, OVERSAMPLE, or NOSAMPLE for each level.
    -        target_class_sizes = _get_target_level_counts(sampling_strategy_str,
    -                                                      parsed_class_sizes,
    -                                                      actual_level_counts,
    -                                                      output_table_size)
    -
    -        undersample_level_dict, oversample_level_dict, nosample_level_dict = \
    -            _get_sampling_strategy_specific_dict(target_class_sizes)
    -
    -        # Get subqueries for each sampling strategy, so that they can be used
    -        # together in one big query.
    -
    -        # Subquery that will be used to get rows as is for those class levels
    -        # that need no sampling.
    -        nosample_subquery = _get_nosample_subquery(
    -            new_source_table, class_col, nosample_level_dict.keys())
    -        # Subquery that will be used to sample those class levels that
    -        # have to be oversampled.
    -        oversample_subquery = _get_with_replacement_subquery(
    -            schema_madlib, new_source_table, source_table_columns, class_col,
    -            actual_level_counts, oversample_level_dict)
    -        # Subquery that will be used to sample those class levels that
    -        # have to be undersampled. Undersampling supports both with and without
    -        # replacement, so fetch the appropriate subquery.
    -        if with_replacement:
    -            undersample_subquery = _get_with_replacement_subquery(
    -                schema_madlib, new_source_table, source_table_columns, class_col,
    -                actual_level_counts, undersample_level_dict)
    -        else:
    -            undersample_subquery = _get_without_replacement_subquery(
    -                schema_madlib, new_source_table, source_table_columns, class_col,
    -                actual_level_counts, undersample_level_dict)
    -
    -        # Merge the three subqueries using a UNION ALL clause.
    -        union_all_subquery = ' UNION ALL '.join(
    -            ['({0})'.format(subquery)
    -             for subquery in [undersample_subquery, oversample_subquery, nosample_subquery]
    -             if subquery])
    -
    -        final_query = """
    -                CREATE TABLE {output_table} AS
    -                SELECT row_number() OVER() AS {new_col_name}, *
    +        grp_col_str, grp_cols = get_grouping_col_str(
    +            schema_madlib, 'Balance sample', [NEW_ID_COLUMN, class_col],
    +            source_table, grouping_cols)
    +        actual_grp_level_counts = _get_level_frequency_distribution(
    +            new_source_table, class_col, grp_col_str)
    +
    +        is_output_created = False
    +        n_grp_values = len(actual_grp_level_counts)
    +        grp_cols_list = grp_col_str.split(',') if grp_cols else []
    +
    +        # for each group
    +        for grp_vals, actual_level_counts in actual_grp_level_counts.items():
    +            target_class_sizes = _get_target_level_counts(
    +                sampling_strategy_str, parsed_class_sizes,
    +                actual_level_counts, output_table_size, n_grp_values)
    --- End diff --
    
    I've moved the `_get_target_level_counts` outside the loop to catch the error earlier. Hopefully, all the grouping logic doesn't complicate the code much. 


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172921714
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=2',         -- Specified class value size. Rest of the values are sampled as is.
    --- End diff --
    
    Do you mean 
    “Rest of the values are left as is.”
    since they are not actually sampled. ?


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172943311
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=2',         -- Specified class value size. Rest of the values are sampled as is.
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             1 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             6 | 14 | Norway      |        3 |    1 |  324 |          4 |        6 |       3 | red
    +             5 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             4 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             3 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             2 |  3 | Austria     |        3 |    1 |   84 |          8 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             1 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 | 11 | Jamaica     |        1 |    4 |   11 |          2 |        1 |       3 | green
    +             5 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             8 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             2 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             7 | 16 | Spain       |        3 |    4 |  505 |         38 |        2 |       2 | red
    +             6 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             9 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             3 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(19 rows)
    --- End diff --
    
    This is because the blue=2 is spilt between the groups. I guess I could change the example to use blue=8 to ensure 2 blues for each group. 


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172954235
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
    @@ -468,81 +544,107 @@ def balance_sample(schema_madlib, source_table, output_table, class_col,
             parsed_class_sizes = extract_keyvalue_params(class_sizes,
                                                          allow_duplicates=False,
                                                          lower_case_names=False)
    +        distinct_levels = collate_plpy_result(
    +            plpy.execute("SELECT DISTINCT ({0})::TEXT as levels FROM {1} ".
    +                         format(class_col, source_table)))['levels']
             if not parsed_class_sizes:
    -            sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
    -                                        output_table_size)
    +            sampling_strategy_str = _validate_and_get_sampling_strategy(
    +                class_sizes, output_table_size)
             else:
                 sampling_strategy_str = None
                 try:
                     for each_level, each_class_size in parsed_class_sizes.items():
    -                    _assert(each_level in actual_level_counts,
    +                    _assert(each_level in distinct_levels,
                                 "Sample: Invalid class value specified ({0})".
    -                                       format(each_level))
    +                            format(each_level))
                         each_class_size = int(each_class_size)
                         _assert(each_class_size >= 1,
                                 "Sample: Class size has to be greater than zero")
                         parsed_class_sizes[each_level] = each_class_size
    -
    -            except TypeError:
    +            except ValueError:
                     plpy.error("Sample: Invalid value for class_sizes ({0})".
                                format(class_sizes))
     
             # Get the number of rows to be sampled for each class level, based on
             # the input table, class_sizes, and output_table_size params. This also
             # includes info about the resulting sampling strategy, i.e., one of
             # UNDERSAMPLE, OVERSAMPLE, or NOSAMPLE for each level.
    -        target_class_sizes = _get_target_level_counts(sampling_strategy_str,
    -                                                      parsed_class_sizes,
    -                                                      actual_level_counts,
    -                                                      output_table_size)
    -
    -        undersample_level_dict, oversample_level_dict, nosample_level_dict = \
    -            _get_sampling_strategy_specific_dict(target_class_sizes)
    -
    -        # Get subqueries for each sampling strategy, so that they can be used
    -        # together in one big query.
    -
    -        # Subquery that will be used to get rows as is for those class levels
    -        # that need no sampling.
    -        nosample_subquery = _get_nosample_subquery(
    -            new_source_table, class_col, nosample_level_dict.keys())
    -        # Subquery that will be used to sample those class levels that
    -        # have to be oversampled.
    -        oversample_subquery = _get_with_replacement_subquery(
    -            schema_madlib, new_source_table, source_table_columns, class_col,
    -            actual_level_counts, oversample_level_dict)
    -        # Subquery that will be used to sample those class levels that
    -        # have to be undersampled. Undersampling supports both with and without
    -        # replacement, so fetch the appropriate subquery.
    -        if with_replacement:
    -            undersample_subquery = _get_with_replacement_subquery(
    -                schema_madlib, new_source_table, source_table_columns, class_col,
    -                actual_level_counts, undersample_level_dict)
    -        else:
    -            undersample_subquery = _get_without_replacement_subquery(
    -                schema_madlib, new_source_table, source_table_columns, class_col,
    -                actual_level_counts, undersample_level_dict)
    -
    -        # Merge the three subqueries using a UNION ALL clause.
    -        union_all_subquery = ' UNION ALL '.join(
    -            ['({0})'.format(subquery)
    -             for subquery in [undersample_subquery, oversample_subquery, nosample_subquery]
    -             if subquery])
    -
    -        final_query = """
    -                CREATE TABLE {output_table} AS
    -                SELECT row_number() OVER() AS {new_col_name}, *
    +        grp_col_str, grp_cols = get_grouping_col_str(
    +            schema_madlib, 'Balance sample', [NEW_ID_COLUMN, class_col],
    +            source_table, grouping_cols)
    +        actual_grp_level_counts = _get_level_frequency_distribution(
    +            new_source_table, class_col, grp_col_str)
    +
    +        is_output_created = False
    +        n_grp_values = len(actual_grp_level_counts)
    +        grp_cols_list = grp_col_str.split(',') if grp_cols else []
    +
    +        # for each group
    +        for grp_vals, actual_level_counts in actual_grp_level_counts.items():
    +            target_class_sizes = _get_target_level_counts(
    +                sampling_strategy_str, parsed_class_sizes,
    +                actual_level_counts, output_table_size, n_grp_values)
    +
    --- End diff --
    
    Following the above comment, we can then have a new for loop that starts here, which will be executed only if there is no error condition arising due to `sampling_strategy_str`.


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/361/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172921328
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    --- End diff --
    
    For zone 4 there are 12 output rows sampled from 9 input rows.  I am assuming this is just rounding due to small numbers in the example?  i.e., could have been 8 rows or 12 rows in the output table for uniform sampling for 4 groups (blue, green, red, white) but we went with 12? 
    
    Which is fine, just confirming.



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172920825
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    --- End diff --
    
    I think you mean "Group by zone" or something like that, not "No grouping" .


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/367/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172953687
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.py_in ---
    @@ -468,81 +544,107 @@ def balance_sample(schema_madlib, source_table, output_table, class_col,
             parsed_class_sizes = extract_keyvalue_params(class_sizes,
                                                          allow_duplicates=False,
                                                          lower_case_names=False)
    +        distinct_levels = collate_plpy_result(
    +            plpy.execute("SELECT DISTINCT ({0})::TEXT as levels FROM {1} ".
    +                         format(class_col, source_table)))['levels']
             if not parsed_class_sizes:
    -            sampling_strategy_str = _validate_and_get_sampling_strategy(class_sizes,
    -                                        output_table_size)
    +            sampling_strategy_str = _validate_and_get_sampling_strategy(
    +                class_sizes, output_table_size)
             else:
                 sampling_strategy_str = None
                 try:
                     for each_level, each_class_size in parsed_class_sizes.items():
    -                    _assert(each_level in actual_level_counts,
    +                    _assert(each_level in distinct_levels,
                                 "Sample: Invalid class value specified ({0})".
    -                                       format(each_level))
    +                            format(each_level))
                         each_class_size = int(each_class_size)
                         _assert(each_class_size >= 1,
                                 "Sample: Class size has to be greater than zero")
                         parsed_class_sizes[each_level] = each_class_size
    -
    -            except TypeError:
    +            except ValueError:
                     plpy.error("Sample: Invalid value for class_sizes ({0})".
                                format(class_sizes))
     
             # Get the number of rows to be sampled for each class level, based on
             # the input table, class_sizes, and output_table_size params. This also
             # includes info about the resulting sampling strategy, i.e., one of
             # UNDERSAMPLE, OVERSAMPLE, or NOSAMPLE for each level.
    -        target_class_sizes = _get_target_level_counts(sampling_strategy_str,
    -                                                      parsed_class_sizes,
    -                                                      actual_level_counts,
    -                                                      output_table_size)
    -
    -        undersample_level_dict, oversample_level_dict, nosample_level_dict = \
    -            _get_sampling_strategy_specific_dict(target_class_sizes)
    -
    -        # Get subqueries for each sampling strategy, so that they can be used
    -        # together in one big query.
    -
    -        # Subquery that will be used to get rows as is for those class levels
    -        # that need no sampling.
    -        nosample_subquery = _get_nosample_subquery(
    -            new_source_table, class_col, nosample_level_dict.keys())
    -        # Subquery that will be used to sample those class levels that
    -        # have to be oversampled.
    -        oversample_subquery = _get_with_replacement_subquery(
    -            schema_madlib, new_source_table, source_table_columns, class_col,
    -            actual_level_counts, oversample_level_dict)
    -        # Subquery that will be used to sample those class levels that
    -        # have to be undersampled. Undersampling supports both with and without
    -        # replacement, so fetch the appropriate subquery.
    -        if with_replacement:
    -            undersample_subquery = _get_with_replacement_subquery(
    -                schema_madlib, new_source_table, source_table_columns, class_col,
    -                actual_level_counts, undersample_level_dict)
    -        else:
    -            undersample_subquery = _get_without_replacement_subquery(
    -                schema_madlib, new_source_table, source_table_columns, class_col,
    -                actual_level_counts, undersample_level_dict)
    -
    -        # Merge the three subqueries using a UNION ALL clause.
    -        union_all_subquery = ' UNION ALL '.join(
    -            ['({0})'.format(subquery)
    -             for subquery in [undersample_subquery, oversample_subquery, nosample_subquery]
    -             if subquery])
    -
    -        final_query = """
    -                CREATE TABLE {output_table} AS
    -                SELECT row_number() OVER() AS {new_col_name}, *
    +        grp_col_str, grp_cols = get_grouping_col_str(
    +            schema_madlib, 'Balance sample', [NEW_ID_COLUMN, class_col],
    +            source_table, grouping_cols)
    +        actual_grp_level_counts = _get_level_frequency_distribution(
    +            new_source_table, class_col, grp_col_str)
    +
    +        is_output_created = False
    +        n_grp_values = len(actual_grp_level_counts)
    +        grp_cols_list = grp_col_str.split(',') if grp_cols else []
    +
    +        # for each group
    +        for grp_vals, actual_level_counts in actual_grp_level_counts.items():
    +            target_class_sizes = _get_target_level_counts(
    +                sampling_strategy_str, parsed_class_sizes,
    +                actual_level_counts, output_table_size, n_grp_values)
    --- End diff --
    
    If the `sampling_strategy_str` contains a class level that does not exist in some group, the current implementation fails when this for loop starts processing that particular group.
    Can we change `_get_target_level_counts` to work with `actual_grp_level_counts`, that returns back a group specific `target_class_sizes` instead (say something like `target_grp_class_sizes`)? That way, the failure would happen much earlier, before SQL sampling queries are run for other groups.
    
    If I am not mistaken, we can create a new function that would call `_get_target_level_counts` in a loop, but returns back a dict with `grp_vals` being the key, and `target_class_sizes` being the values.


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/368/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r173239594
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +545,95 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the
    +classes (mainhue), with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- Grouping by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group. Further, `blue=8` in the example below will
    +be split between the four groups, resulting in two blue rows for each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=8',         -- Specified class value size. Rest of the values are outputed as is.
    +    NULL,             -- Output table size
    +    'zone'            -- Group by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             2 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             1 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             3 |  3 | Austria     |        3 |    1 |   84 |          8 |        4 |       2 | red
    +             5 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             4 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             8 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             7 | 14 | Norway      |        3 |    1 |  324 |          4 |        6 |       3 | red
    +             6 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             2 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             3 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             2 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             1 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 | 11 | Jamaica     |        1 |    4 |   11 |          2 |        1 |       3 | green
    +             6 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             7 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 16 | Spain       |        3 |    4 |  505 |         38 |        2 |       2 | red
    +             9 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +            10 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             4 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
     @anchor literature
    --- End diff --
    
    Are we supporting specifying the number of nulls?
    ```
    DROP TABLE IF EXISTS output_table;
    SELECT madlib.balance_sample(
        'flags',          -- Source table
        'output_table',   -- Output table
        'mainhue',        -- Class column
        'null=8',         -- Specified class value size. Rest of the values are outputed as is.
        NULL,             -- Output table size
        'zone' ,           -- Group by zone
        NULL,             -- With replacement
        TRUE              -- Keep null
    );
    SELECT * FROM output_table ORDER BY zone, mainhue;
    ```
    produces
    ```
    /Users/fmcquillan/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
        448 
        449     def do_execute(self, cursor, statement, parameters, context=None):
    --> 450         cursor.execute(statement, parameters)
        451 
        452     def do_execute_no_params(self, cursor, statement, context=None):
    
    InternalError: (psycopg2.InternalError) plpy.Error: Invalid input param name: null (plpython.c:4960)
    CONTEXT:  Traceback (most recent call last):
      PL/Python function "balance_sample", line 23, in <module>
        return balance_sample.balance_sample(**globals())
      PL/Python function "balance_sample", line 559, in balance_sample
      PL/Python function "balance_sample", line 604, in extract_keyvalue_params
    PL/Python function "balance_sample"
     [SQL: "SELECT madlib.balance_sample(\n    'flags',          -- Source table\n    'output_table',   -- Output table\n    'mainhue',        -- Class column\n    'null=8',         -- Specified class value size. Rest of the values are outputed as is.\n    NULL,             -- Output table size\n    'zone' ,           -- Group by zone\n    NULL,\n    TRUE\n);"]
    ```
    
    
    



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172920935
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=2',         -- Specified class value size. Rest of the values are sampled as is.
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    --- End diff --
    
    I think you mean "Group by zone" or something like that, not "No grouping" .


---

[GitHub] madlib issue #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit commented on the issue:

    https://github.com/apache/madlib/pull/239
  
    
    Refer to this link for build results (access rights to CI server needed): 
    https://builds.apache.org/job/madlib-pr-build/365/



---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by njayaram2 <gi...@git.apache.org>.
Github user njayaram2 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r173254181
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +545,95 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the
    +classes (mainhue), with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- Grouping by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group. Further, `blue=8` in the example below will
    +be split between the four groups, resulting in two blue rows for each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=8',         -- Specified class value size. Rest of the values are outputed as is.
    +    NULL,             -- Output table size
    +    'zone'            -- Group by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             2 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             1 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             3 |  3 | Austria     |        3 |    1 |   84 |          8 |        4 |       2 | red
    +             5 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             4 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             8 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             7 | 14 | Norway      |        3 |    1 |  324 |          4 |        6 |       3 | red
    +             6 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             2 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             3 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             2 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             1 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 | 11 | Jamaica     |        1 |    4 |   11 |          2 |        1 |       3 | green
    +             6 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             7 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 16 | Spain       |        3 |    4 |  505 |         38 |        2 |       2 | red
    +             9 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +            10 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             4 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
     @anchor literature
    --- End diff --
    
    This is an expected failure. We explicitly check for such conditions and fail in our utility function that extracts key value params (`extract_keyvalue_params()`):
    `if not param_name or param_name in ('none', 'null'):`


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r173238804
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +545,95 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the
    +classes (mainhue), with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- Grouping by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group. Further, `blue=8` in the example below will
    +be split between the four groups, resulting in two blue rows for each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=8',         -- Specified class value size. Rest of the values are outputed as is.
    +    NULL,             -- Output table size
    +    'zone'            -- Group by zone
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    --- End diff --
    
    For some reason, doxygen builds the above as
    ```
    DROP TABLE IF EXISTS output_table;
    SELECT madlib.balance_sample(
        'flags,          -- Source table
        'output_table',   -- Output table
        'mainhue',        -- Class column
        'blue=8',         -- Specified class value size. Rest of the values are outputed as is.
        NULL,             -- Output table size
        'zone'            -- Group by zone
    );
    SELECT * FROM output_table ORDER BY zone, mainhue;
    ```
    where flags is missing the trailing '


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:

    https://github.com/apache/madlib/pull/239


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by fmcquillan99 <gi...@git.apache.org>.
Github user fmcquillan99 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172949439
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    +</pre>
    +
    +-# Grouping can be used with class size specification as well. Note below that
    +'blue=<Integer>' is the only valid class value since 'blue' is the only class
    +value that is present in each group.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    'blue=2',         -- Specified class value size. Rest of the values are sampled as is.
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             1 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             6 | 14 | Norway      |        3 |    1 |  324 |          4 |        6 |       3 | red
    +             5 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             4 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             3 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             2 |  3 | Austria     |        3 |    1 |   84 |          8 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             1 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 | 11 | Jamaica     |        1 |    4 |   11 |          2 |        1 |       3 | green
    +             5 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             8 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             2 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             7 | 16 | Spain       |        3 |    4 |  505 |         38 |        2 |       2 | red
    +             6 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             9 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             3 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(19 rows)
    --- End diff --
    
    I forgot that blue=2 means for the whole table, so my misunderstanding, but perhaps others may be confused too.
    
    So yes, please put blue=8 in the example and remind the user that this applies to the whole table so that each group will get 2 blues each.


---

[GitHub] madlib pull request #239: Balance Sample: Add support for grouping

Posted by iyerr3 <gi...@git.apache.org>.
Github user iyerr3 commented on a diff in the pull request:

    https://github.com/apache/madlib/pull/239#discussion_r172941914
  
    --- Diff: src/ports/postgres/modules/sample/balance_sample.sql_in ---
    @@ -543,6 +544,90 @@ SELECT * FROM output_table ORDER BY mainhue, name;
     (25 rows)
     </pre>
     
    +-# To perform the balance sampling for independent groups, use the 'grouping_cols'
    +parameter. Note below that each group (zone) has a different count of the classes (mainhue),
    +with some groups not containing some class values.
    +<pre class="syntax">
    +DROP TABLE IF EXISTS output_table;
    +SELECT madlib.balance_sample(
    +    'flags',          -- Source table
    +    'output_table',   -- Output table
    +    'mainhue',        -- Class column
    +    NULL,             -- Uniform
    +    NULL,             -- Output table size
    +    'zone'            -- No grouping
    +);
    +SELECT * FROM output_table ORDER BY zone, mainhue;
    +</pre>
    +<pre class="result">
    + __madlib_id__ | id |    name     | landmass | zone | area | population | language | colours | mainhue
    +---------------+----+-------------+----------+------+------+------------+----------+---------+---------
    +             6 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             5 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             8 | 17 | Sweden      |        3 |    1 |  450 |          8 |        6 |       2 | blue
    +             7 |  8 | Greece      |        3 |    1 |  132 |         10 |        6 |       2 | blue
    +             2 |  7 | Denmark     |        3 |    1 |   43 |          5 |        6 |       2 | red
    +             1 |  6 | China       |        5 |    1 | 9561 |       1008 |        7 |       2 | red
    +             4 | 12 | Luxembourg  |        3 |    1 |    3 |          0 |        4 |       3 | red
    +             3 | 18 | Switzerland |        3 |    1 |   41 |          6 |        4 |       2 | red
    +             1 |  2 | Australia   |        6 |    2 | 7690 |         15 |        1 |       3 | blue
    +             1 |  1 | Argentina   |        2 |    3 | 2777 |         28 |        2 |       2 | blue
    +             2 |  4 | Brazil      |        2 |    3 | 8512 |        119 |        6 |       4 | green
    +             6 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             5 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +             4 |  9 | Guatemala   |        1 |    4 |  109 |          8 |        2 |       2 | blue
    +            12 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            10 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +            11 | 13 | Mexico      |        1 |    4 | 1973 |         77 |        2 |       4 | green
    +             1 | 19 | UK          |        3 |    4 |  245 |         56 |        1 |       3 | red
    +             3 |  5 | Canada      |        1 |    4 | 9976 |         24 |        1 |       2 | red
    +             2 | 15 | Portugal    |        3 |    4 |   92 |         10 |        6 |       5 | red
    +             8 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             7 | 20 | USA         |        1 |    4 | 9363 |        231 |        1 |       3 | white
    +             9 | 10 | Ireland     |        3 |    4 |   70 |          3 |        1 |       3 | white
    +(23 rows)
    --- End diff --
    
    Right. We're always using ceil instead of round and hence will provide more than the expected rows in cases where division is not exact.  


---