MySQL AI Release Notes
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_idanditem_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.
Review and complete the following tasks:
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 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.
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_TABLEroutine.mysql> CALL sys.ML_PREDICT_TABLE(table_name, model_handle, output_table_name), [options]);Replace
table_name,model_handle, andoutput_table_namewith your own values. Addoptionsas 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_TABLEon 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_datasetis the fully qualified name of the input table that contains the data to generate predictions for (database_name.table_name).@modelis the session variable for the model handle.recommendation_data.recommendationsis 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_idanditem_idpair and the respectiveratingvalue in theml_resultscolumn. For example, in the first row, user 1 is expected to give item 2 a rating of 2.71.The values in the
ratingcolumn refer to the past rating theuser_idgave to theitem_id. They are not relevant to the values inml_results.
-
Learn how to generate different types of recommendations:
Learn how to Score a Recommendation Model.