4.6.5.7 Generating User Recommendations for Items

This topic 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

Review and complete the following tasks:

• Prepare Data for a Recommendation Model

• Train a Recommendation Model

• Generate Predictions for a Recommendation Model

Recommend Users to Items

When you run ML_PREDICT_TABLE or ML_PREDICT_ROW to generate user recommendations, a default value of three users are recommended. To change this value, set the topk parameter.

1. If not already done, load the model. You can use the session variable for the model that is valid for the duration of the connection. Alternatively, you can use the model handle previously set. For the option to set the user name, you can set it to NULL.

   The following example uses the session variable.

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

   The following example uses the model handle.

   mysql> CALL sys.ML_MODEL_LOAD('recommendation_use_case', NULL);

2. Make predictions for the test dataset by using the ML_PREDICT_TABLE routine.

   mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);

   Replace table_name, model_handle, and output_table_name with your own values. Add options as needed.

   The following example runs ML_PREDICT_TABLE on the testing dataset previously created and sets the topk parameter to 2, so only two users are recommended.

   mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.user_recommendations', JSON_OBJECT('recommend', 'users', 'topk', 2));

   Where:

   • recommendation_data.testing_dataset is the fully qualified name of the input table that contains the data to generate predictions for (database_name.table_name).

   • @model is the session variable for the model handle.

   • recommendation_data.user_recommendations is the fully qualified name of the output table with recommendations (database_name.table_name).

   • JSON_OBJECT('recommend', 'users', 'topk', 2) sets the recommendation task to recommend users to items. A maximum of two users to recommend is set.

3. Query the output table to review the recommended top two users for each item in the output table.

   mysql> SELECT * from user_recommendations;
   +---------+---------+--------+-------------------------------------------------------------------+
   | user_id | item_id | rating | ml_results                                                        |
   +---------+---------+--------+-------------------------------------------------------------------+
   | 1       | 2       |    4.0 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
   | 1       | 4       |    7.0 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
   | 1       | 6       |    1.5 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
   | 1       | 8       |    3.5 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
   | 10      | 18      |    1.5 | {"predictions": {"user_id": ["5", "10"], "rating": [3.74, 3.63]}} |
   | 10      | 2       |    6.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
   | 10      | 5       |    3.0 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
   | 10      | 6       |    5.5 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
   | 2       | 1       |    5.0 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
   | 2       | 3       |    8.0 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
   | 2       | 5       |    2.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
   | 2       | 7       |    6.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
   | 3       | 18      |    7.0 | {"predictions": {"user_id": ["5", "10"], "rating": [3.74, 3.63]}} |
   | 3       | 2       |    3.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
   | 3       | 5       |    6.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
   | 3       | 8       |    2.5 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
   | 4       | 1       |    5.5 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
   | 4       | 3       |    8.5 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
   | 4       | 6       |    2.0 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
   | 4       | 7       |    5.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
   | 5       | 12      |    5.0 | {"predictions": {"user_id": ["5", "10"], "rating": [3.29, 3.2]}}  |
   | 5       | 2       |    7.0 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
   | 5       | 4       |    1.5 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
   | 5       | 6       |    4.0 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
   | 6       | 3       |    6.0 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
   | 6       | 5       |    1.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
   | 6       | 7       |    4.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
   | 6       | 8       |    7.0 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
   | 7       | 1       |    6.5 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
   | 7       | 4       |    3.0 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
   | 7       | 5       |    5.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.31, 3.19]}}  |
   | 7       | 9       |    8.0 | {"predictions": {"user_id": ["4", "7"], "rating": [3.34, 3.17]}}  |
   | 8       | 2       |    8.5 | {"predictions": {"user_id": ["6", "5"], "rating": [3.02, 2.9]}}   |
   | 8       | 4       |    2.5 | {"predictions": {"user_id": ["4", "7"], "rating": [4.16, 3.95]}}  |
   | 8       | 6       |    5.0 | {"predictions": {"user_id": ["4", "7"], "rating": [1.94, 1.84]}}  |
   | 8       | 9       |    3.5 | {"predictions": {"user_id": ["4", "7"], "rating": [3.34, 3.17]}}  |
   | 9       | 1       |    5.0 | {"predictions": {"user_id": ["4", "7"], "rating": [3.36, 3.18]}}  |
   | 9       | 3       |    8.0 | {"predictions": {"user_id": ["4", "7"], "rating": [5.42, 5.13]}}  |
   | 9       | 7       |    2.5 | {"predictions": {"user_id": ["4", "6"], "rating": [2.61, 2.4]}}   |
   | 9       | 8       |    5.5 | {"predictions": {"user_id": ["7", "6"], "rating": [3.12, 3.03]}}  |
   +---------+---------+--------+-------------------------------------------------------------------+
   40 rows in set (0.0476 sec)

   Review the recommended users in the ml_results column next to user_id. For example, for item 2, users 6 and 5 are the top users predicted to like it. Review the ratings in the ml_results column to review the expected ratings for each recommended item. For example, user 6 is expected to rate item 2 with a value of 3.02, and user 5 with a value of 2.9.

4. Alternatively, if you do not want to generate an entire table of recommended users, you can run ML_PREDICT_ROW to specify an item to recommend items for.

   mysql> SELECT sys.ML_PREDICT_ROW(input_data, model_handle), [options]);

   Replace input_data and model_handle with your own values. Add options as needed.

   The following example runs ML_PREDICT_ROW and specifies item 2 with a limit of two recommended users.

   mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "2"}', @model,  JSON_OBJECT('recommend', 'items_to_users', 'topk', 2));
   +--------------------------------------------------------------------------------------------------------+
   | sys.ML_PREDICT_ROW('{"item_id": "2"}', @model,  JSON_OBJECT('recommend', 'items_to_users', 'topk', 2)) |
   +--------------------------------------------------------------------------------------------------------+
   | {"item_id": "2", "ml_results": {"predictions": {"rating": [3.02, 2.9], "user_id": ["6", "5"]}}}        |
   +--------------------------------------------------------------------------------------------------------+
   1 row in set (0.8488 sec)

   The predicted users of 5 and 6 and predicted ratings are the same as the one in the output table previously created.

What's Next

• Learn how to generate different types of recommendations:

  • Generate Predictions for Ratings and Rankings

  • Generate Item Recommendations for Users

  • Generate Recommendations for Similar Items

  • Generate Recommendations for Similar Users

• Learn how to Score a Recommendation Model.