4.6.5.6 Generating Item Recommendations for Users

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

Review and complete the following tasks:

Recommend Items to Users

When you run ML_PREDICT_TABLE to generate item recommendations, a default value of three items are recommended. To change this value, set the topk parameter.

You have the option to include item and user metadata when generating predictions. These steps include that metadata in the command to generate predictions.

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);
```
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.
You have the option to specify the input table and output table as the same table if specific conditions are met. See Input Tables and Output Tables to learn more.
The following example runs ML_PREDICT_TABLE on the testing dataset previously created and sets the topk parameter to 2, so only two items are recommended.
```
mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.item_recommendations', 
                                  JSON_OBJECT('recommend', 'items', 
                                              'topk', 2, 
                                              'user_metadata', JSON_OBJECT('table_name', 'recommendation_data.users'), 
                                              'item_metadata', JSON_OBJECT('table_name', 'recommendation_data.items')));
```
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.item_recommendations is the fully qualified name of the output table with recommendations (database_name.table_name).
- JSON_OBJECT('recommend', 'items', 'topk', 2) sets the recommendation task to recommend items to users. A maximum of two items to recommend is set.
- 'user_metadata', JSON_OBJECT('table_name', 'recommendation_data.users') specifies the table that has user metadata to use when generating predictions.
- 'item_metadata', JSON_OBJECT('table_name', 'recommendation_data.items') specifies the table that has item metadata to use when generating predictions.

Query the output table to review the recommended top two items for each user in the output table.

mysql> SELECT * from item_recommendations;
+---------+---------+--------+--------------------------------------------------------------------+
| user_id | item_id | rating | ml_results                                                         |
+---------+---------+--------+--------------------------------------------------------------------+
| 1       | 2       |    4.0 | {"predictions": {"item_id": ["20", "18"], "rating": [4.7, 3.48]}}  |
| 1       | 4       |    7.0 | {"predictions": {"item_id": ["20", "18"], "rating": [4.7, 3.48]}}  |
| 1       | 6       |    1.5 | {"predictions": {"item_id": ["20", "18"], "rating": [4.7, 3.48]}}  |
| 1       | 8       |    3.5 | {"predictions": {"item_id": ["20", "18"], "rating": [4.7, 3.48]}}  |
| 10      | 18      |    1.5 | {"predictions": {"item_id": ["20", "3"], "rating": [4.9, 4.65]}}   |
| 10      | 2       |    6.5 | {"predictions": {"item_id": ["20", "3"], "rating": [4.9, 4.65]}}   |
| 10      | 5       |    3.0 | {"predictions": {"item_id": ["20", "3"], "rating": [4.9, 4.65]}}   |
| 10      | 6       |    5.5 | {"predictions": {"item_id": ["20", "3"], "rating": [4.9, 4.65]}}   |
| 2       | 1       |    5.0 | {"predictions": {"item_id": ["3", "17"], "rating": [4.65, 3.38]}}  |
| 2       | 3       |    8.0 | {"predictions": {"item_id": ["3", "17"], "rating": [4.65, 3.38]}}  |
| 2       | 5       |    2.5 | {"predictions": {"item_id": ["3", "17"], "rating": [4.65, 3.38]}}  |
| 2       | 7       |    6.5 | {"predictions": {"item_id": ["3", "17"], "rating": [4.65, 3.38]}}  |
| 3       | 18      |    7.0 | {"predictions": {"item_id": ["20", "3"], "rating": [4.39, 4.17]}}  |
| 3       | 2       |    3.5 | {"predictions": {"item_id": ["20", "3"], "rating": [4.39, 4.17]}}  |
| 3       | 5       |    6.5 | {"predictions": {"item_id": ["20", "3"], "rating": [4.39, 4.17]}}  |
| 3       | 8       |    2.5 | {"predictions": {"item_id": ["20", "3"], "rating": [4.39, 4.17]}}  |
| 4       | 1       |    5.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.71, 5.42]}}  |
| 4       | 3       |    8.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.71, 5.42]}}  |
| 4       | 6       |    2.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.71, 5.42]}}  |
| 4       | 7       |    5.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.71, 5.42]}}  |
| 5       | 12      |    5.0 | {"predictions": {"item_id": ["20", "18"], "rating": [5.05, 3.74]}} |
| 5       | 2       |    7.0 | {"predictions": {"item_id": ["20", "18"], "rating": [5.05, 3.74]}} |
| 5       | 4       |    1.5 | {"predictions": {"item_id": ["20", "18"], "rating": [5.05, 3.74]}} |
| 5       | 6       |    4.0 | {"predictions": {"item_id": ["20", "18"], "rating": [5.05, 3.74]}} |
| 6       | 3       |    6.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.25, 4.98]}}  |
| 6       | 5       |    1.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.25, 4.98]}}  |
| 6       | 7       |    4.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.25, 4.98]}}  |
| 6       | 8       |    7.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.25, 4.98]}}  |
| 7       | 1       |    6.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.41, 5.13]}}  |
| 7       | 4       |    3.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.41, 5.13]}}  |
| 7       | 5       |    5.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.41, 5.13]}}  |
| 7       | 9       |    8.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.41, 5.13]}}  |
| 8       | 2       |    8.5 | {"predictions": {"item_id": ["20", "18"], "rating": [4.53, 3.35]}} |
| 8       | 4       |    2.5 | {"predictions": {"item_id": ["20", "18"], "rating": [4.53, 3.35]}} |
| 8       | 6       |    5.0 | {"predictions": {"item_id": ["20", "18"], "rating": [4.53, 3.35]}} |
| 8       | 9       |    3.5 | {"predictions": {"item_id": ["20", "18"], "rating": [4.53, 3.35]}} |
| 9       | 1       |    5.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.09, 4.83]}}  |
| 9       | 3       |    8.0 | {"predictions": {"item_id": ["20", "3"], "rating": [5.09, 4.83]}}  |
| 9       | 7       |    2.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.09, 4.83]}}  |
| 9       | 8       |    5.5 | {"predictions": {"item_id": ["20", "3"], "rating": [5.09, 4.83]}}  |
+---------+---------+--------+--------------------------------------------------------------------+
40 rows in set (0.0387 sec)

Review the recommended items in the ml_results column next to item_id. For example, user 1 is predicted to like items 20 and 18. Review the ratings in the ml_results column to review the expected ratings for each recommended item. For example, user 1 is expected to rate item 20 with a value of 4.7, and item 18 with a value of 3.48.

What's Next

Learn how to generate different types of recommendations:
Learn how to Score a Recommendation Model.