3.11.5 Generating Item Recommendations for Users

This section describes how to generate recommended items for users.

• For known users and known items, the output includes a list of items that the user will most likely give a high rating and the predicted rating or ranking.

• For a new user, and an explicit feedback model, the prediction is the global top K items that received the average highest ratings.

  For a new user, and an implicit feedback model, the prediction is the global top K items with the highest number of interactions.

• For a user who has tried all known items, the prediction is an empty list because it is not possible to recommend any other items. Set remove_seen to false to repeat existing interactions from the training table.

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

Recommending Items to Users

To generate item recommendations for users:

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. You must set the recommend option to users_to_items or items.

   The following example uses explicit feedback and runs the ML_PREDICT_ROW routine to predict the top 3 items that a particular user will like with the users_to_items option.

   mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "846"}', @model,  
             JSON_OBJECT("recommend", "users_to_items", "topk", 3));
   +----------------------------------------------------------------------------------------------------------------------+
   | sys.ML_PREDICT_ROW('{"user_id": "846"}', @model,  JSON_OBJECT("recommend", "users_to_items", "topk", 3))             |
   +----------------------------------------------------------------------------------------------------------------------+
   | {"user_id": "846", "ml_results": "{"predictions": {"item_id": ["313", "483", "64"], "rating": [4.06, 4.05, 4.04]}}"} |
   +----------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.2811 sec)

   • User 846 is predicted to like items 313, 483, and 64.

   • This user is predicted to rate item 313 with a value of 4.06, item 483 with a value of 4.05, and item 64 with a value of 4.04.

   The following example uses implicit feedback and runs the ML_PREDICT_TABLE routine to recommend the top 3 items that particular users will like.

   mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_sample',  @model, 'mlcorpus.table_predictions_users',  JSON_OBJECT("recommend", "items", "topk", 3));
   Query OK, 0 rows affected (5.0672 sec)
   
   mysql> SELECT * FROM mlcorpus.table_predictions_users LIMIT 3;
   +-------------------+---------+---------+--------+--------------------------------------------------------------------------------+
   | _4aad19ca6e_pk_id | user_id | item_id | rating | ml_results                                                                     |
   +-------------------+---------+---------+--------+--------------------------------------------------------------------------------+
   |                 1 | 1026    | 13763   |      1 | {"predictions": {"item_id": ["10", "14", "11"], "rating": [3.43, 3.37, 3.18]}} |
   |                 2 | 992     | 16114   |      1 | {"predictions": {"item_id": ["10", "14", "11"], "rating": [3.42, 3.38, 3.17]}} |
   |                 3 | 1863    | 4527    |      1 | {"predictions": {"item_id": ["10", "14", "11"], "rating": [3.42, 3.37, 3.18]}} |
   +-------------------+---------+---------+--------+--------------------------------------------------------------------------------+

   • Users 1026, 992, and 1863 are predicted to like items 10, 14, and 11.

   • The respective ranking for each user-item pair is included in ml_results after rating. For example, user 1026 has a predicted ranking of 3.43 for item 10.

   The values in columns item_id and rating can be disregarded as they are part of the data that was trained from the input table.