Input
The item table, recommender_product, contains product categories and their similarity scores. The similarity scores are from column cntb in CFilter Example 2 Output Table cfilter_output1, which contains the number of co-occurrences of the items in product_category_a and product_category_b2.
region | product_category_a | product_category_b | product_similarity |
---|---|---|---|
Northern California | Consumer | Corporate | 13 |
Northern California | Consumer | Home Office | 13 |
Northern California | Consumer | Small Business | 13 |
Northern California | Corporate | Corporate | 13 |
Northern California | Corporate | Home Office | 16 |
Northern California | Corporate | Small Business | 17 |
Northern California | Home Office | Corporate | 13 |
Northern California | Home Office | Home Office | 16 |
Northern California | Home Office | Small Business | 16 |
Northern California | Small Business | Corporate | 13 |
Northern California | Small Business | Home Office | 17 |
Northern California | Small Business | Small Business | 16 |
The user table, recommender_user, contains the product preference (business presence) of four companies in four product categories, on a scale of 0 to 10 (10 is highest). For example, the table shows that in the Consumer product category, Company4 has a high business presence, while Company2 has none. The prod_preference 0 means that the company has never viewed or bought a product in that category.
product_category | companyname | prod_preference | month |
---|---|---|---|
Consumer | Company1 | 5 | December |
Consumer | Company3 | 3 | December |
Consumer | Company2 | 0 | December |
Consumer | Company4 | 9 | December |
Corporate | Company1 | 8 | December |
Corporate | Company3 | 5 | December |
Corporate | Company2 | 1 | December |
Corporate | Company4 | 0 | December |
Home Office | Company1 | 0 | December |
Home Office | Company3 | 9 | December |
Home Office | Company2 | 7 | December |
Home Office | Company4 | 2 | December |
SQL Call
SELECT * FROM WSRecommender ( ON ( SELECT * FROM WSRecommenderReduce ( ON ( SELECT * FROM recommender_product ) AS item_table PARTITION BY product_category_a ON ( SELECT * FROM recommender_user ) AS user_table PARTITION BY product_category USING Item1 ('product_category_a') Item2 ('product_category_b') ItemSimilarity ('product_similarity') UserItem ('product_category') UserID ('companyname') UserPref ('prod_preference') ) AS dt1 ) AS temp_input_table PARTITION BY usr, col1_item2 ) AS dt2 ORDER BY recommendation desc, item;
Output
If the company (usr) has ever viewed or bought items in the product category (item), then the recommendation column contains the value in the prod_preference column of the user table; otherwise, the column contains the recommendation score calculated by the function.
If the recommendation value is greater than 0 and the company has never viewed or bought items in the product category (that is, the value in the prod_preference column of the user table is 0), then the new_reco_flag is 1, meaning that the product category is to be recommended to the company.
item | usr | recommendation | new_reco_flag |
---|---|---|---|
Consumer | Company1 | 8.5 | 0 |
Corporate | Company2 | 7.51515 | 0 |
Home Office | Company1 | 7.48889 | 1 |
Corporate | Company1 | 7.26667 | 0 |
Consumer | Company3 | 7 | 0 |
Small Business | Company1 | 6.7 | 0 |
Corporate | Company3 | 6.31034 | 0 |
Small Business | Company3 | 5.82609 | 1 |
Consumer | Company2 | 5.33333 | 1 |
Home Office | Company4 | 5.13793 | 0 |
Small Business | Company4 | 5.13793 | 0 |
Home Office | Company2 | 4.5 | 0 |
Home Office | Company3 | 4.10345 | 0 |
Corporate | Company4 | 3.97826 | 1 |
Small Business | Company2 | 3.90909 | 0 |
Consumer | Company4 | 2 | 0 |