3.11.6 Generating User Recommendations for Items

This section describes how to generate recommended users for items.

• For known users and known items, the output includes a list of users that will most likely give a high rating to an item and will also predict the ratings or rankings.

• For a new item, and an explicit feedback model, the prediction is the global top K users who have provided the average highest ratings.

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

• For an item that has been tried by all known users, the prediction is an empty list because it is not possible to recommend any other users. 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 Users to Items

To generate user recommendations to items:

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 items_to_users or users.

   The following example uses explicit feedback and runs the ML_PREDICT_ROW routine to predict the top three users that will like a particular item.

   mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "524"}', @model,  
             JSON_OBJECT("recommend", "items_to_users", "topk", 3));
   +---------------------------------------------------------------------------------------------------------------------+
   | sys.ML_PREDICT_ROW('{"item_id": "524"}', @model,  JSON_OBJECT("recommend", "items_to_users", "topk", 3))            |
   +---------------------------------------------------------------------------------------------------------------------+
   | {"item_id": "524", "ml_results": "{"predictions": {"user_id": ["7", "164", "894"], "rating": [4.05, 3.94, 3.91]}}"} |
   +---------------------------------------------------------------------------------------------------------------------+
   1 row in set (0.2808 sec)

   • Users 7, 164, and 894 are predicted as the top three users to like item 524.

   • For item 524, user 7 is predicted to give a rating of 4.05, user 164 is predicted to give a rating of 3.94, and user 894 is predicted to give a rating of 3.91.

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

   mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_sample',  @model, 'mlcorpus.table_predictions_items',  JSON_OBJECT("recommend", "users", "topk", 3));
   Query OK, 0 rows affected (4.1777 sec)
    
   mysql>  SELECT * FROM mlcorpus.table_predictions_items LIMIT 3;
   +-------------------+---------+---------+--------+--------------------------------------------------------------------------------------+
   | _4aad19ca6e_pk_id | user_id | item_id | rating | ml_results                                                                           |
   +-------------------+---------+---------+--------+--------------------------------------------------------------------------------------+
   |                 1 | 1026    | 13763   |      1 | {"predictions": {"user_id": ["3929", "5255", "4583"], "rating": [1.26, 1.25, 1.25]}} |
   |                 2 | 992     | 16114   |      1 | {"predictions": {"user_id": ["2671", "69", "429"], "rating": [-0.13, -0.13, -0.13]}} |
   |                 3 | 1863    | 4527    |      1 | {"predictions": {"user_id": ["4732", "4439", "3399"], "rating": [0.43, 0.43, 0.43]}} |
   +-------------------+---------+---------+--------+--------------------------------------------------------------------------------------+
   3 rows in set (0.0003 sec)

   • Users 3929, 5255, and 4583 are predicted as the top three users to like item 13763.

   • Users 2671, 69, and 429 are predicted as the top three users to like item 16114.

   • Users 4732, 4439, and 3399 are predicted as the top three users to like item 4527.

   • The respective rankings for each user-item pair are in ml_results after rating. For example, for item 13763, user 3929 has a ranking of 1.26, and users 5255 and 4583 have a ranking of 1.25.

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