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