1.0 - 8.00 - WSRecommender Example - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Teradata Vantage
Release Number
Release Date
May 2019
Content Type
Programming Reference
Publication ID
English (United States)


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


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