| # coding=utf-8 |
| |
| """ |
| @file assoc_rules.py_in |
| |
| @brief Association Rules - Apriori Algorithm Implementation. |
| |
| @namespace assoc_rules |
| """ |
| import time |
| import plpy |
| from utilities.validate_args import columns_exist_in_table |
| from utilities.validate_args import table_exists |
| from utilities.validate_args import output_tbl_valid |
| from utilities.utilities import unique_string |
| from utilities.control import MinWarning |
| |
| |
| """ |
| @brief if the given condition is false, then raise an error with the message |
| @param condition the condition to be assert |
| @param msg the error message to be reported |
| """ |
| def __assert(condition, msg): |
| if not condition : |
| plpy.error(msg); |
| |
| """ |
| @brief test if the 1st value is greater than the 2nd value |
| @param val1 a float number |
| @param val2 a float number |
| @return true if the 1st value greater than the 2nd value. |
| Otherwise return false |
| """ |
| def __float_gt(val1, val2): |
| return val1 - val2 > 1E-14; |
| |
| |
| """ |
| @brief test if the 1st value is less than or equal to the 2nd value |
| @param val1 a float number |
| @param val2 a float number |
| @return true if the 1st value is less than or equal to the 2nd value. |
| Otherwise return false |
| """ |
| def __float_le(val1, val2): |
| return (val1 - val2) < -1E-14 or abs(val1 - val2) < 1E-14; |
| |
| |
| """ |
| @brief The entry function for the Apriori. |
| @param support minimum level of support needed for each itemset |
| to be included in result |
| @param confidence minimum level of confidence needed for each rule |
| to be included in result |
| @param tid_col name of the column storing the transaction ids |
| @param item_col name of the column storing the products |
| @param input_table name of the table where the data is stored |
| @param output_schema name of the schema where the final results will be stored |
| @param verbose determining if output contains comments |
| @param max_itemset_size determines the maximum size of frequent itemsets allowed |
| to generate association rules from |
| @param max_lhs_size determines the maximum size of the lhs of the rule |
| @param max_rhs_size determines the maximum size of the rhs of the rule |
| """ |
| |
| @MinWarning("warning") |
| def assoc_rules(madlib_schema, support, confidence, tid_col, |
| item_col, input_table, output_schema, verbose, |
| max_itemset_size, max_lhs_size, max_rhs_size): |
| |
| begin_func_exec = time.time(); |
| begin_step_exec = time.time(); |
| cal_itemsets_time = 0; |
| if max_itemset_size is None: |
| max_itemset_size = 10 |
| elif max_itemset_size <= 1: |
| plpy.error("ERROR: max_itemset_size has to be greater than 1.") |
| |
| #Validate LHS RHS |
| __assert(max_lhs_size is None or max_lhs_size > 0, |
| "max_lhs_size param must be a positive number.") |
| __assert(max_rhs_size is None or max_rhs_size > 0, |
| "max_rhs_size param must be a positive number.") |
| #check parameters |
| __assert( |
| support is not None and |
| confidence is not None and |
| tid_col is not None and |
| item_col is not None and |
| input_table is not None and |
| verbose is not None, |
| "none of the parameters except the output_schema should be null" |
| ); |
| |
| __assert( |
| __float_gt(support, 0) and __float_le(support, 1), |
| "the parameter support must be in range of (0, 1]" |
| ); |
| |
| __assert( |
| __float_gt(confidence, 0) and __float_le(confidence, 1), |
| "the parameter confidence must be in range of (0, 1]" |
| ); |
| |
| __assert(len(input_table) > 0 and table_exists(input_table, madlib_schema) and |
| len(tid_col) > 0 and len(item_col) > 0 and |
| columns_exist_in_table(input_table, [tid_col, item_col], madlib_schema), |
| "the input table must exist and the tid_col and " |
| "item_col must be in the input table") |
| |
| if output_schema is None : |
| rv = plpy.execute("SELECT current_schema() as s"); |
| output_schema = rv[0]["s"]; |
| else : |
| rv = plpy.execute(""" |
| SELECT count(*) as cnt |
| FROM pg_namespace |
| where quote_ident(nspname) = '{0}' |
| """.format(output_schema) |
| ); |
| __assert(rv[0]["cnt"] == 1, "the output schema does not exist"); |
| |
| if verbose: |
| plpy.info("finished checking parameters"); |
| |
| # create the result table for keeping the generated rules |
| output_tbl_valid("{0}.assoc_rules".format(output_schema), 'assoc rules') |
| plpy.execute(""" |
| CREATE TABLE {0}.assoc_rules |
| ( |
| ruleId INT, |
| pre TEXT[], |
| post TEXT[], |
| count INT, |
| support FLOAT8, |
| confidence FLOAT8, |
| lift FLOAT8, |
| conviction FLOAT8 |
| ) |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (ruleId)')""".format(output_schema) |
| ); |
| |
| # the auxiliary table for keeping the association rules |
| assoc_rules_aux_tmp = unique_string('assoc_rules_aux_tmp') |
| plpy.execute(""" |
| CREATE TEMP TABLE {0} |
| ( |
| ruleId SERIAL, |
| pre TEXT, |
| post TEXT, |
| support FLOAT8, |
| confidence FLOAT8, |
| lift FLOAT8, |
| conviction FLOAT8 |
| ) |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (ruleId)') |
| """.format(assoc_rules_aux_tmp)); |
| |
| # if the tid in the input table doesn't start with 1 and the IDs are not |
| # continuous, then we will make the IDs start with 1 and continuous. |
| # note: duplicated records will be removed. |
| rv = plpy.execute(""" |
| SELECT max({0}) as x, min({1}) as i, count({2}) as c |
| FROM {3}""".format(tid_col, tid_col, tid_col, input_table)); |
| if rv[0]["i"] != 1 or rv[0]["x"] - rv[0]["i"] != rv[0]["c"] : |
| assoc_input_unique = unique_string('assoc_input_unique') |
| plpy.execute(""" |
| CREATE TEMP TABLE {5} AS |
| SELECT |
| dense_rank() OVER (ORDER BY tid)::BIGINT as tid, |
| item |
| FROM ( |
| SELECT {0} as tid, |
| {1}::text as item |
| FROM {2} |
| WHERE {3} IS NOT NULL AND |
| {4} IS NOT NULL |
| GROUP BY 1,2 |
| ) t |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (tid)') |
| """.format(tid_col, item_col, input_table, tid_col, item_col, |
| assoc_input_unique) |
| ); |
| |
| if verbose : |
| plpy.info("finished removing duplicates"); |
| |
| rv = plpy.execute(""" |
| SELECT count(DISTINCT tid) as c1, count(DISTINCT item) as c2 |
| FROM {assoc_input_unique} |
| """.format(**locals())); |
| num_tranx = rv[0]["c1"]; |
| num_prod = rv[0]["c2"]; |
| min_supp_tranx = float(num_tranx) * support; |
| # Set default values to the max possible number that an itemset can |
| # have on its LHS or RHS for a given input dataset. |
| max_lhs_size = num_prod if max_lhs_size is None else max_lhs_size |
| max_rhs_size = num_prod if max_rhs_size is None else max_rhs_size |
| |
| # get the items whose counts are greater than the given |
| # support counts. Each item will be given a continuous number. |
| assoc_item_uniq = unique_string('assoc_item_uniq') |
| plpy.execute(""" |
| CREATE TEMP TABLE {assoc_item_uniq} (item_id, item_text, cnt) AS |
| SELECT row_number() OVER (ORDER BY cnt DESC)::BIGINT, |
| item::TEXT, |
| cnt::FLOAT8 |
| FROM ( |
| SELECT item AS item, count(tid) as cnt |
| FROM {assoc_input_unique} |
| GROUP BY item |
| ) t |
| WHERE cnt::FLOAT8 >= {min_supp_tranx} |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (item_id)') |
| """.format(**locals())); |
| |
| if verbose : |
| plpy.info("finished encoding items"); |
| |
| rv = plpy.execute("SELECT count(item_id) as c FROM {assoc_item_uniq}".format(**locals())); |
| num_supp_prod = rv[0]["c"]; |
| |
| assoc_enc_input = unique_string('assoc_enc_input') |
| plpy.execute(""" |
| CREATE TEMP TABLE {assoc_enc_input} (tid, item, cnt) AS |
| SELECT t1.tid, t2.item_id, t2.cnt |
| FROM {assoc_input_unique} t1, {assoc_item_uniq} t2 |
| WHERE t1.item = t2.item_text |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (tid)') |
| """.format(**locals())); |
| |
| if verbose : |
| plpy.info("finished encoding input table: {0}".format( |
| time.time() - begin_step_exec)); |
| plpy.info("Beginning iteration #1"); |
| |
| begin_step_exec = time.time(); |
| |
| # this table keeps the patterns in each iteration. |
| assoc_rule_sets = unique_string('assoc_rule_sets') |
| plpy.execute(""" |
| CREATE TEMP TABLE {assoc_rule_sets} |
| ( |
| text_svec TEXT, |
| set_list {madlib_schema}.svec, |
| support FLOAT8, |
| iteration INT |
| ) |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (text_svec)') |
| """.format(**locals())); |
| |
| # this table keeps the patterns in the current iteration. |
| assoc_rule_sets_loop = unique_string('assoc_rule_sets_loop') |
| plpy.execute(""" |
| CREATE TEMP TABLE {assoc_rule_sets_loop} |
| ( |
| id BIGINT, |
| set_list {madlib_schema}.svec, |
| support FLOAT8, |
| tids {madlib_schema}.svec |
| ) |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (id)') |
| """.format(**locals())); |
| |
| # this table adds a new column to order the table by its setlist |
| assoc_rule_sets_loop_ordered = unique_string('assoc_rule_sets_loop_ordered') |
| plpy.execute(""" |
| CREATE TEMP TABLE {assoc_rule_sets_loop_ordered} |
| ( |
| id BIGINT, |
| set_list {madlib_schema}.svec, |
| support FLOAT8, |
| tids {madlib_schema}.svec, |
| newrownum BIGINT |
| ) |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (newrownum)') |
| """.format(**locals())); |
| |
| plpy.execute(""" |
| INSERT INTO {assoc_rule_sets_loop} (id, set_list, support, tids) |
| SELECT |
| t.item, |
| {madlib_schema}.svec_cast_positions_float8arr |
| (ARRAY[t.item], ARRAY[1], {num_supp_prod}, 0), |
| t.cnt::FLOAT8 / {num_tranx}, |
| {madlib_schema}.svec_cast_positions_float8arr |
| (array_agg(t.tid), array_agg(1), {num_tranx}, 0) |
| FROM {assoc_enc_input} t |
| GROUP BY t.item, t.cnt |
| """.format(**locals())); |
| |
| plpy.execute(""" |
| INSERT INTO {assoc_rule_sets_loop_ordered}(id, set_list, support, |
| tids, newrownum) |
| SELECT *, row_number() over (order by set_list) as newrownum |
| FROM {assoc_rule_sets_loop} |
| """.format(**locals())); |
| rv = plpy.execute(""" |
| SELECT count(id) as c1, max(id) as c2 |
| FROM {assoc_rule_sets_loop} |
| """.format(**locals())); |
| num_item_loop = rv[0]["c1"]; |
| max_item_loop = rv[0]["c2"]; |
| |
| __assert( |
| (num_item_loop == max_item_loop) or |
| (num_item_loop == 0 and max_item_loop is None), |
| "internal error: num_item_loop must be equal to max_item_loop" |
| ); |
| |
| # As two different svecs may have the same hash key, |
| # we use this table to assign a unique ID for each svec. |
| assoc_loop_aux = unique_string('al_aux') |
| plpy.execute(""" |
| CREATE TEMP TABLE {assoc_loop_aux} |
| ( |
| id BIGSERIAL, |
| set_list {madlib_schema}.svec, |
| support FLOAT8, |
| tids {madlib_schema}.svec |
| ) |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (id)') |
| """.format(**locals())); |
| |
| if verbose : |
| plpy.info("{0} Frequent itemsets found in this iteration".format( |
| num_supp_prod)); |
| plpy.info("Completed iteration # 1. Time: {0}".format( |
| time.time() - begin_step_exec)); |
| |
| curr_iter = 0; |
| num_products_threshold = num_supp_prod |
| while num_item_loop > 0 and curr_iter < max_itemset_size: |
| begin_step_exec = time.time(); |
| curr_iter = curr_iter + 1; |
| |
| if verbose : |
| plpy.info("Beginning iteration # {0}".format(curr_iter + 1)); |
| |
| plpy.execute(""" |
| INSERT INTO {assoc_rule_sets} |
| (text_svec, set_list, support, iteration) |
| SELECT array_to_string( |
| {madlib_schema}.svec_nonbase_positions(set_list, 0), ','), |
| set_list, |
| support, {curr_iter} |
| FROM {assoc_rule_sets_loop}""".format(**locals())); |
| |
| if verbose : |
| plpy.info("time of preparing data: {0}".format( |
| time.time() - begin_step_exec)); |
| |
| # generate the patterns for the next iteration |
| |
| # The vector operations are used to calculate the boolean OR operation |
| # efficiently. |
| # Example: |
| # a1 = [1,1,0,0,0,1] a2 = [1,1,0,0,1,1] |
| # svec_a1 = {2,3,1}:{1,0,1} svec_a2 = {2,2,2}:{1,0,1} |
| # x = svec_a1 + svec_a2 = [2,2,0,0,1,2] -> {2,2,1,1}:{2,0,1,2} |
| # y = svec_a1 * svec_a2 = [1,1,0,0,0,1] -> {2,3,1}:{1,0,1} |
| # x - y = [1,1,0,0,1,1] -> {2,2,2}:{1,0,1} |
| |
| # The boolean OR operation is used to combine two itemsets to create a larger one. |
| # (a,b,c) combined with (a,b,d) will give (a,b,c,d) |
| # (a,b,c) combined with (c,d,e) will give (a,b,c,d,e) |
| |
| # The {assoc_rule_sets_loop_ordered} table is ordered by the setlist. |
| # This ensures that the sets (a,b,c) and (a,b,d) are close to each other. |
| # t3.newrownum-t1.newrownum <= {num_products_threshold} check ensures that we are |
| # looking at a subset of pairs of itemsets instead of all pairs. |
| |
| # Let's assume we are trying to get to (a,b,c,d) |
| # If the support for (a,b,c,d) is above the threshold; (a,b,c), (a,b,d), |
| # (b,c,d) should all be already in our list. |
| # In this case it doesn't matter if we skip trying (a,b,c) and (b,c,d) |
| # combination because the result is already covered by (a,b,c) + (a,c,d). |
| |
| # The iterp1 check ensures that the new itemset is of a certain size. |
| # At every iteration, we increase the target itemset size by one. |
| |
| plpy.execute("ALTER SEQUENCE {assoc_loop_aux}_id_seq RESTART WITH 1".format(**locals())); |
| plpy.execute("TRUNCATE TABLE {assoc_loop_aux}".format(**locals())); |
| plpy.execute(""" |
| INSERT INTO {assoc_loop_aux}(set_list, support, tids) |
| SELECT DISTINCT ON({madlib_schema}.svec_to_string(set_list)) set_list, |
| {madlib_schema}.svec_l1norm(tids)::FLOAT8 / {num_tranx}, |
| tids |
| FROM ( |
| SELECT |
| {madlib_schema}.svec_minus( |
| {madlib_schema}.svec_plus(t1.set_list, t3.set_list), |
| {madlib_schema}.svec_mult(t1.set_list, t3.set_list) |
| ) as set_list, |
| {madlib_schema}.svec_mult(t1.tids, t3.tids) as tids |
| FROM {assoc_rule_sets_loop_ordered} t1, |
| {assoc_rule_sets_loop_ordered} t3 |
| WHERE t1.newrownum < t3.newrownum AND |
| t3.newrownum-t1.newrownum <= {num_products_threshold} |
| ) t |
| WHERE {madlib_schema}.svec_l1norm(set_list)::INT = {iterp1} AND |
| {madlib_schema}.svec_l1norm(tids)::FLOAT8 >= {min_supp_tranx} |
| """.format(iterp1=curr_iter + 1, **locals()) |
| ); |
| |
| plpy.execute("TRUNCATE TABLE {assoc_rule_sets_loop}".format(**locals())); |
| plpy.execute(""" |
| INSERT INTO {assoc_rule_sets_loop}(id, set_list, support, tids) |
| SELECT * FROM {assoc_loop_aux} |
| """.format(**locals())); |
| plpy.execute("TRUNCATE TABLE {assoc_rule_sets_loop_ordered}".format(**locals())); |
| plpy.execute(""" |
| INSERT INTO {assoc_rule_sets_loop_ordered}(id, set_list, support, tids, newrownum) |
| SELECT *, row_number() over (order by set_list) as newrownum |
| FROM {assoc_rule_sets_loop} |
| """.format(**locals())); |
| |
| rv = plpy.execute(""" |
| SELECT count(id) as c1, max(id) as c2 |
| FROM {assoc_rule_sets_loop} |
| """.format(**locals())); |
| num_item_loop = rv[0]["c1"]; |
| max_item_loop = rv[0]["c2"]; |
| __assert( |
| (num_item_loop == max_item_loop) or |
| (num_item_loop == 0 and max_item_loop is None), |
| "internal error: num_item_loop must be equal to max_item_loop" |
| ); |
| # num_products_threshold should be equal to the number of distinct |
| # products that are present in the previous iteration's frequent |
| # itemsets. |
| # We can ideally do away with the if condition, but it's a trade-off: |
| # The query in the if condition might be a considerable overhead when |
| # the original number of products is very low compared to the number |
| # of rows in {assoc_rule_sets_loop} in a given iteration. On the other |
| # hand, it can be a considerable improvement if we have a lot of |
| # distinct products and only a small number of them are present in |
| # frequent itemsets. But this is specific to datasets and parameters |
| # (such as support), so the following if statment is a compromise. |
| if num_item_loop < num_supp_prod: |
| # Get number of 1's from all set_lists in {assoc_rule_sets_loop} |
| num_products_threshold = plpy.execute(""" |
| SELECT {madlib_schema}.svec_l1norm( |
| {madlib_schema}.svec_count_nonzero(a)) AS cnt |
| FROM ( |
| SELECT {madlib_schema}.svec_sum(set_list) AS a |
| FROM {assoc_rule_sets_loop} |
| ) t |
| """.format(**locals()))[0]['cnt'] |
| |
| if verbose : |
| plpy.info("{0} Frequent itemsets found in this iteration".format( |
| num_item_loop)); |
| plpy.info("Completed iteration # {0}. Time: {1}".format( |
| curr_iter + 1, time.time() - begin_step_exec)); |
| |
| cal_itemsets_time = cal_itemsets_time + (time.time() - begin_step_exec); |
| |
| if (num_item_loop == 0) and (curr_iter < 2) and verbose : |
| plpy.info("No association rules found that meet given criteria"); |
| plpy.info("finished itemsets finding. Time: {0}".format( |
| cal_itemsets_time)); |
| total_rules = 0; |
| else : |
| if (verbose) : |
| plpy.info("begin to generate the final rules"); |
| |
| begin_step_exec = time.time(); |
| # generate all the final rules |
| plpy.execute(""" |
| INSERT INTO {assoc_rules_aux_tmp} |
| (pre, post, support, confidence, lift, conviction) |
| SELECT |
| t.item[1], |
| t.item[2], |
| t.support_xy, |
| t.support_xy / x.support, |
| t.support_xy / (x.support * y.support), |
| CASE WHEN abs(t.support_xy / x.support - 1) < 1.0E-10 THEN |
| 0 |
| ELSE |
| (1 - y.support) / (1 - (t.support_xy / x.support)) |
| END |
| FROM ( |
| SELECT |
| {madlib_schema}.gen_rules_from_cfp(text_svec, |
| iteration, |
| {max_lhs_size}, |
| {max_rhs_size}) as item, |
| support as support_xy |
| FROM {assoc_rule_sets} |
| WHERE iteration > 1 |
| ) t, {assoc_rule_sets} x, {assoc_rule_sets} y |
| WHERE t.item[1] = x.text_svec AND |
| t.item[2] = y.text_svec AND |
| (t.support_xy / x.support) >= {confidence} |
| """.format(**locals())); |
| |
| # generate the readable rules |
| pre_tmp_table = unique_string('pre_tmp_table') |
| plpy.execute(""" |
| CREATE TEMP TABLE {pre_tmp_table} AS |
| SELECT ruleId, array_agg(item_text) as pre |
| FROM |
| ( |
| SELECT |
| ruleId, |
| unnest(string_to_array(pre, ','))::BIGINT as pre_id |
| FROM {assoc_rules_aux_tmp} |
| ) s1, {assoc_item_uniq} s2 |
| WHERE s1.pre_id = s2.item_id |
| GROUP BY ruleId |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (ruleId)') |
| """.format(**locals())); |
| |
| post_tmp_table = unique_string('post_tmp_table') |
| plpy.execute(""" |
| CREATE TEMP TABLE {post_tmp_table} AS |
| SELECT ruleId, array_agg(item_text) as post |
| FROM |
| ( |
| SELECT |
| ruleId, |
| unnest(string_to_array(post, ','))::BIGINT as post_id |
| FROM {assoc_rules_aux_tmp} |
| ) s1, {assoc_item_uniq} s2 |
| WHERE s1.post_id = s2.item_id |
| GROUP BY ruleId |
| m4_ifdef(`__POSTGRESQL__', `', `DISTRIBUTED BY (ruleId)') |
| """.format(**locals())); |
| |
| plpy.execute(""" |
| INSERT INTO {output_schema}.assoc_rules |
| SELECT t1.ruleId, t2.pre, t3.post, t1.support*{num_tranx}::INT AS count, support, |
| confidence, lift, conviction |
| FROM |
| {assoc_rules_aux_tmp} t1, |
| {pre_tmp_table} t2, |
| {post_tmp_table} t3 |
| WHERE t1.ruleId = t2.ruleId AND t1.ruleId = t3.ruleId |
| """.format(**locals())); |
| |
| |
| # if in verbose mode, we will keep all the intermediate tables |
| if not verbose : |
| plpy.execute(""" |
| DROP TABLE IF EXISTS {post_tmp_table}; |
| DROP TABLE IF EXISTS {pre_tmp_table}; |
| DROP TABLE IF EXISTS {assoc_rules_aux_tmp}; |
| DROP TABLE IF EXISTS {assoc_input_unique}; |
| DROP TABLE IF EXISTS {assoc_item_uniq}; |
| DROP TABLE IF EXISTS {assoc_enc_input}; |
| DROP TABLE IF EXISTS {assoc_rule_sets}; |
| DROP TABLE IF EXISTS {assoc_rule_sets_loop}; |
| DROP TABLE IF EXISTS {assoc_rule_sets_loop_ordered}; |
| DROP TABLE IF EXISTS {assoc_loop_aux}; |
| """.format(**locals())); |
| if verbose : |
| rv = plpy.execute(""" |
| SELECT count(*) as c FROM {0}.assoc_rules |
| """.format(output_schema)); |
| num_item_loop = rv[0]["c"]; |
| plpy.info("{0} Total association rules found. Time: {1}".format( |
| num_item_loop, time.time() - begin_step_exec)); |
| |
| rv = plpy.execute(""" |
| SELECT count(*) as c FROM {0}.assoc_rules |
| """.format(output_schema)); |
| total_rules = rv[0]["c"]; |
| |
| return ( |
| output_schema, |
| 'assoc_rules', |
| total_rules, |
| time.time() - begin_func_exec |
| ); |
| |
| def assoc_rules_help_message(schema_madlib, message=None, **kwargs): |
| """ |
| Given a help string, provide usage information |
| |
| Args: |
| @param schema_madlib Name of the MADlib schema |
| @param message Helper message to print |
| |
| Returns: |
| None |
| """ |
| if message is not None and \ |
| message.lower() in ("usage", "help", "?"): |
| return """ |
| ----------------------------------------------------------------------- |
| USAGE |
| ----------------------------------------------------------------------- |
| SELECT {schema_madlib}.assoc_rules( |
| support, -- FLOAT8, minimum level of support needed for each |
| itemset to be included in result |
| confidence, -- FLOAT8, minimum level of confidence needed for each |
| rule to be included in result |
| tid_col, -- TEXT, name of the column storing the transaction ids |
| item_col, -- TEXT, name of the column storing the products |
| input_table, -- TEXT, name of the table containing the input data |
| output_schema, -- TEXT, name of the schema where the final results |
| will be stored. The schema must be created |
| before calling the function. |
| Alternatively, use <tt>NULL</tt> to output |
| to the current schema. |
| verbose, -- BOOLEAN, (optional, default: False) determines if |
| details are printed for each iteration as |
| the algorithm progresses |
| max_itemset_size, -- INTEGER, (optional, default: itemsets of all sizes) |
| determines the maximum size of frequent |
| itemsets allowed that are used for generating |
| association rules. Value less than 2 throws an error. |
| max_lhs_size, -- INTEGER, (optional, default: NULL) determines the |
| maximum size of the lhs of the rule. |
| NULL means there is no restriction on the |
| size of the left hand side. |
| max_rhs_size -- INTEGER (optional, default: NULL) determines the |
| maximum size of the rhs of the rule. |
| NULL means there is no restriction on the |
| size of the right hand side. |
| ); |
| ------------------------------------------------------------------------- |
| OUTPUT TABLES |
| ------------------------------------------------------------------------- |
| The output table "assoc_rules" in the "output_schema" contains a unique rule of the form "If X, then Y |
| (i.e., X => Y)" in each row. X and Y are non-empty itemsets, called the antecedent and consequent, or |
| the left-hand-side (LHS) and right-hand-side (LHS), of the rule respectively. |
| |
| in each row, with the following columns: |
| ruleid, -- INTEGER, row number |
| pre, -- TEXT, specifies the antecedent, or the LHS of the rule |
| post, -- DOUBLE, specifies the consequent, or the RHS of the rule |
| support, -- DOUBLE, support of the frequent itemset X,Y |
| count, -- INTEGER, number of transactions in the input table that contain X,Y |
| confidence, -- DOUBLE, the ratio of number of transactions that contain X,Y to the number of transactions |
| that contain X |
| lift, -- DOUBLE, the ratio of observed support of X,Y to the expected support of X,Y, assuming X and |
| Y are independent. |
| conviction -- DOUBLE, the ratio of expected support of X occurring without Y assuming X and Y are |
| independent, to the observed support of X occuring without Y |
| """.format(schema_madlib=schema_madlib) |
| else: |
| return """ |
| For an overview on usage, run: SELECT {schema_madlib}.assoc_rules('usage'); |
| """.format(schema_madlib=schema_madlib) |
