MySQL AI Release Notes
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_seentofalseto repeat existing interactions from the training table.
Review and complete the following tasks:
When you run
ML_PREDICT_TABLE
to generate user recommendations, a default value of three
users 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_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 and sets thetopkparameter 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, '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.user_recommendationsis 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.'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 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_resultscolumn next touser_id. For example, for item 2, users 6 and 5 are the top users predicted to like it. Review the ratings in theml_resultscolumn 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.
-
Learn how to generate different types of recommendations:
Learn how to Score a Recommendation Model.