1.1 - 8.10 - WSRecommender Example - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

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.

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

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