3.11.4 Generating Recommendations for Ratings and Rankings

This section describes how to generate recommendations for either ratings or rankings. If generating a rating, the output predicts the rating the user will give to an item. If generating a ranking, the output is a ranking of the user compared to other users.

• For known users and known items, the output includes the predicted rating or ranking for an item for a given pair of user_id and item_id.

• For a known user with a new item, the prediction is the global average rating or ranking. The routines can add a user bias if the model includes it.

• For a new user with a known item, the prediction is the global average rating or ranking. The routines can add an item bias if the model includes it.

• For a new user with a new item, the prediction is the global average rating or ranking.

Before You Begin

1. Connect to your HeatWave DB System.

2. Complete the steps for training the recommendation model. See Section 3.11.2, “Training a Recommendation Model”.

3. Review Section 3.11.3, “Using a Recommendation Model”.

Generating Recommendations

To generate recommendations for ratings or rankings:

1. Use the ML_MODEL_LOAD routine to load the trained model with recommendations. For example:

   mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);

2. Run the ML_PREDICT_TABLE or ML_PREDICT_ROW routine to generate predictions.

   The following example uses implicit feedback and runs the ML_PREDICT_ROW routine to predict the ranking for a particular user and item.

   mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "836", "item_id": "226"}', @model,  NULL);
   +---------------------------------------------------------------------------------------+
   | sys.ML_PREDICT_ROW('{"user_id": "836", "item_id": "226"}', @model,  NULL)             |
   +---------------------------------------------------------------------------------------+
   | {"item_id": "226", "user_id": "836", "ml_results": {"predictions": {"rating": 2.46}}} |
   +---------------------------------------------------------------------------------------+
   1 row in set (0.1390 sec)

   The predicted ranking of item 226 and user 836 is 2.46.

   The following example uses explicit feedback and runs the ML_PREDICT_TABLE routine to predict the ratings for particular users and items. This is the default option for recommend, with options set to NULL.

   mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.retailrocket-transactionto_to_predict', 
             @model, 'mlcorpus.table_predictions', NULL);
   Query OK, 0 rows affected (0.7589 sec)
   
   mysql> SELECT * FROM  table_predictions;
   +-------------------+---------------+---------+---------+--------+-----------------------------------+
   | _4aad19ca6e_pk_id | timestamp     | user_id | item_id | rating | ml_results                        |
   +-------------------+---------------+---------+---------+--------+-----------------------------------+
   |                 1 | 1436670000000 | 836347  | 64154   |      1 | {"predictions": {"rating": 1.0}}  |
   |                 2 | 1441250000000 | 435603  | 335366  |      1 | {"predictions": {"rating": 1.04}} |
   |                 3 | 1439670000000 | 1150086 | 314062  |      1 | {"predictions": {"rating": 1.03}} |
   +-------------------+---------------+---------+---------+--------+-----------------------------------+
   3 rows in set (0.00 sec)

   The output table displays the following recommendations:

   • User 836347 is predicted to give a rating of 1.0 for item 64154.

   • User 435603 is predicted to give a rating of 1.04 for item 335366.

   • User 1150086 is predicted to give a rating of 1.03 for item 314062.

   The values in the rating column refer to the past rating the user_id gave to the item_id. They are not relevant to the values in ml_results.