KNNRecommender Example - Teradata Vantage

Input

The RatingTable has movie ratings from 50 users on approximately 2900 movies, with an average of about 150 ratings for each user. The 10 possible ratings range from 0.5 to 5 in steps of 0.5. A higher number indicates a better rating.

SQL Call

KNNRecommender uses the input data to create three model tables: the weights model ('ml_weights'), the bias model table ('ml_bias') and the optional nearest items or neighbors table ('ml_itemngbrs').

DROP TABLE ml_weights;
DROP TABLE ml_bias;
DROP TABLE ml_itemngbrs;

SELECT * FROM KNNRecommender (
  ON ml_ratings AS RatingTable
  OUT TABLE WeightModelTable (ml_weights)
  OUT TABLE BiasModelTable (ml_bias)
  OUT TABLE NearestItemsTable (ml_itemngbrs)
  USING
  UserIDColumn ('userid')
  ItemIDColumn ('itemid')
  RatingColumn ('rating')
  K (15)
  MaxIterNum (20)
  StopThreshold (0.0002)
  LearningRate (0.001)
) AS dt;

Output

The rmse value is output for each of the 20 iterations. The null iteration or first row of the table shows the rmse of the default initialized model.

This query returns the following table:

SELECT * FROM ml_itemngbrs ORDER BY 1;

The sij value (similarity between itemi and itemj) in ml_itemngbrs is the default Pearson correlation coefficient.

This query returns the following table:

SELECT * FROM ml_bias ORDER BY 1, 2;