MySQL :: HeatWave User Guide :: 6.7.5.5 Generate Predictions for Ratings and Rankings

HeatWave User Guide / ... / Generate Predictions for Ratings and Rankings

6.7.5.5 Generate Predictions for Ratings and Rankings

This topic describes how to generate recommendations for either ratings (recommendation model with explicit feedback) or rankings (recommendation model with implicit feedback). 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

Review and complete the following tasks:

Generate Rating Recommendations

Since the model you previously trained used explicit feedback, you generate ratings that the user is predicted to give an item. A higher rating means a a better rating. If you train a recommendation model using implicit feedback, you generate rankings. A lower ranking means a better ranking. The steps below are the same for both types of recommendation models. See Recommendation Task Types to learn more.

As of MySQL 9.5.0, 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.

As of MySQL 9.4.1, 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.
```
mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.recommendations', 
                                  JSON_OBJECT ('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.recommendations is the fully qualified name of the output table with predictions (database_name.table_name).
- '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 predicted ratings that users give for each user-item pair.

mysql> SELECT * from recommendations;
+---------+---------+--------+-----------------------------------+
| user_id | item_id | rating | ml_results                        |
+---------+---------+--------+-----------------------------------+
| 1       | 2       |    4.0 | {"predictions": {"rating": 2.71}} |
| 1       | 4       |    7.0 | {"predictions": {"rating": 3.43}} |
| 1       | 6       |    1.5 | {"predictions": {"rating": 1.6}}  |
| 1       | 8       |    3.5 | {"predictions": {"rating": 2.71}} |
| 10      | 18      |    1.5 | {"predictions": {"rating": 3.63}} |
| 10      | 2       |    6.5 | {"predictions": {"rating": 2.82}} |
| 10      | 5       |    3.0 | {"predictions": {"rating": 3.09}} |
| 10      | 6       |    5.5 | {"predictions": {"rating": 1.67}} |
| 2       | 1       |    5.0 | {"predictions": {"rating": 2.88}} |
| 2       | 3       |    8.0 | {"predictions": {"rating": 4.65}} |
| 2       | 5       |    2.5 | {"predictions": {"rating": 3.09}} |
| 2       | 7       |    6.5 | {"predictions": {"rating": 2.23}} |
| 3       | 18      |    7.0 | {"predictions": {"rating": 3.25}} |
| 3       | 2       |    3.5 | {"predictions": {"rating": 2.53}} |
| 3       | 5       |    6.5 | {"predictions": {"rating": 2.77}} |
| 3       | 8       |    2.5 | {"predictions": {"rating": 2.53}} |
| 4       | 1       |    5.5 | {"predictions": {"rating": 3.36}} |
| 4       | 3       |    8.5 | {"predictions": {"rating": 5.42}} |
| 4       | 6       |    2.0 | {"predictions": {"rating": 1.94}} |
| 4       | 7       |    5.5 | {"predictions": {"rating": 2.61}} |
| 5       | 12      |    5.0 | {"predictions": {"rating": 3.29}} |
| 5       | 2       |    7.0 | {"predictions": {"rating": 2.9}}  |
| 5       | 4       |    1.5 | {"predictions": {"rating": 3.68}} |
| 5       | 6       |    4.0 | {"predictions": {"rating": 1.72}} |
| 6       | 3       |    6.0 | {"predictions": {"rating": 4.98}} |
| 6       | 5       |    1.5 | {"predictions": {"rating": 3.31}} |
| 6       | 7       |    4.5 | {"predictions": {"rating": 2.4}}  |
| 6       | 8       |    7.0 | {"predictions": {"rating": 3.03}} |
| 7       | 1       |    6.5 | {"predictions": {"rating": 3.18}} |
| 7       | 4       |    3.0 | {"predictions": {"rating": 3.95}} |
| 7       | 5       |    5.5 | {"predictions": {"rating": 3.41}} |
| 7       | 9       |    8.0 | {"predictions": {"rating": 3.17}} |
| 8       | 2       |    8.5 | {"predictions": {"rating": 2.6}}  |
| 8       | 4       |    2.5 | {"predictions": {"rating": 3.3}}  |
| 8       | 6       |    5.0 | {"predictions": {"rating": 1.54}} |
| 8       | 9       |    3.5 | {"predictions": {"rating": 2.65}} |
| 9       | 1       |    5.0 | {"predictions": {"rating": 2.99}} |
| 9       | 3       |    8.0 | {"predictions": {"rating": 4.83}} |
| 9       | 7       |    2.5 | {"predictions": {"rating": 2.32}} |
| 9       | 8       |    5.5 | {"predictions": {"rating": 2.93}} |
+---------+---------+--------+-----------------------------------+
40 rows in set (0.0459 sec)

Review each user_id and item_id pair and the respective rating value in the ml_results column. For example, in the first row, user 1 is expected to give item 2 a rating of 2.71.

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.

What's Next

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

PREV HOME UP NEXT