Input
The item table, recommender_product, contains product categories and their similarity scores. The similarity scores are from column cntb in CFilter Example: Filter by Customer Segment 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 recommender_product AS ItemTable PARTITION BY product_category_a ON recommender_user AS UserTable PARTITION BY product_category USING ITEM1('product_category_a') ITEM2('product_category_b') ITEMSIMILARITYSCORE('product_similarity ') USERIDCOLUMN('companyname') USERITEMCOLUMN('product_category') USERPREFCOLUMN('prod_preference') ) as dt1 ) PARTITION BY usr, col1_item2 ) as dt 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.515151500701904 0 home office Company1 7.488888740539551 1 corporate Company1 7.266666889190674 0 consumer Company3 7.0 0 small business Company1 6.699999809265137 0 corporate Company3 6.310344696044922 0 small business Company3 5.82608699798584 1 consumer Company2 5.333333492279053 1 home office Company4 5.137930870056152 0 small business Company4 5.137930870056152 0 home office Company2 4.5 0 home office Company3 4.103448390960693 0 corporate Company4 3.9782607555389404 1 small business Company2 3.909090995788574 0 consumer Company4 2.0 0
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.