MySQL AI User Guide
This topic describes how to generate recommendations for similar users.
For known users, the output includes a list of predicted users that have similar behavior and taste.
The predictions are expressed in cosine similarity, and range from 0, very dissimilar, to 1, very similar.
For a new user, there is no information to provide a prediction. This generates an error.
Review and complete the following tasks:
When you run
ML_PREDICT_TABLE
to generate similar user recommendations, a default value of
three similar 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_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
similar users are generated.
mysql> CALL sys.ML_PREDICT_TABLE('recommendation_data.testing_dataset', @model, 'recommendation_data.similar_user_recommendations',
JSON_OBJECT('recommend', 'users_to_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_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.similar_user_recommendations
is the fully qualified name of the output table with
recommendations
(database_name.table_name).
JSON_OBJECT('recommend', 'users_to_users',
'topk', 2) sets the recommendation task to
recommend similar users. A maximum of two similar
users 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 top two similar users generated for each user in the output table.
mysql> SELECT * from similar_user_recommendations;
+---------+---------+--------+---------------------------------------------------------------------------+
| user_id | item_id | rating | ml_results |
+---------+---------+--------+---------------------------------------------------------------------------+
| 1 | 2 | 4.0 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.7922, 0.7238]}} |
| 1 | 4 | 7.0 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.7922, 0.7238]}} |
| 1 | 6 | 1.5 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.7922, 0.7238]}} |
| 1 | 8 | 3.5 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.7922, 0.7238]}} |
| 10 | 18 | 1.5 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.6827, 0.5943]}} |
| 10 | 2 | 6.5 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.6827, 0.5943]}} |
| 10 | 5 | 3.0 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.6827, 0.5943]}} |
| 10 | 6 | 5.5 | {"predictions": {"user_id": ["3", "5"], "similarity": [0.6827, 0.5943]}} |
| 2 | 1 | 5.0 | {"predictions": {"user_id": ["7", "9"], "similarity": [0.6473, 0.5746]}} |
| 2 | 3 | 8.0 | {"predictions": {"user_id": ["7", "9"], "similarity": [0.6473, 0.5746]}} |
| 2 | 5 | 2.5 | {"predictions": {"user_id": ["7", "9"], "similarity": [0.6473, 0.5746]}} |
| 2 | 7 | 6.5 | {"predictions": {"user_id": ["7", "9"], "similarity": [0.6473, 0.5746]}} |
| 3 | 18 | 7.0 | {"predictions": {"user_id": ["1", "10"], "similarity": [0.7922, 0.6827]}} |
| 3 | 2 | 3.5 | {"predictions": {"user_id": ["1", "10"], "similarity": [0.7922, 0.6827]}} |
| 3 | 5 | 6.5 | {"predictions": {"user_id": ["1", "10"], "similarity": [0.7922, 0.6827]}} |
| 3 | 8 | 2.5 | {"predictions": {"user_id": ["1", "10"], "similarity": [0.7922, 0.6827]}} |
| 4 | 1 | 5.5 | {"predictions": {"user_id": ["9", "7"], "similarity": [0.9764, 0.9087]}} |
| 4 | 3 | 8.5 | {"predictions": {"user_id": ["9", "7"], "similarity": [0.9764, 0.9087]}} |
| 4 | 6 | 2.0 | {"predictions": {"user_id": ["9", "7"], "similarity": [0.9764, 0.9087]}} |
| 4 | 7 | 5.5 | {"predictions": {"user_id": ["9", "7"], "similarity": [0.9764, 0.9087]}} |
| 5 | 12 | 5.0 | {"predictions": {"user_id": ["8", "1"], "similarity": [0.992, 0.7238]}} |
| 5 | 2 | 7.0 | {"predictions": {"user_id": ["8", "1"], "similarity": [0.992, 0.7238]}} |
| 5 | 4 | 1.5 | {"predictions": {"user_id": ["8", "1"], "similarity": [0.992, 0.7238]}} |
| 5 | 6 | 4.0 | {"predictions": {"user_id": ["8", "1"], "similarity": [0.992, 0.7238]}} |
| 6 | 3 | 6.0 | {"predictions": {"user_id": ["4", "9"], "similarity": [0.5695, 0.4862]}} |
| 6 | 5 | 1.5 | {"predictions": {"user_id": ["4", "9"], "similarity": [0.5695, 0.4862]}} |
| 6 | 7 | 4.5 | {"predictions": {"user_id": ["4", "9"], "similarity": [0.5695, 0.4862]}} |
| 6 | 8 | 7.0 | {"predictions": {"user_id": ["4", "9"], "similarity": [0.5695, 0.4862]}} |
| 7 | 1 | 6.5 | {"predictions": {"user_id": ["9", "4"], "similarity": [0.9738, 0.9087]}} |
| 7 | 4 | 3.0 | {"predictions": {"user_id": ["9", "4"], "similarity": [0.9738, 0.9087]}} |
| 7 | 5 | 5.5 | {"predictions": {"user_id": ["9", "4"], "similarity": [0.9738, 0.9087]}} |
| 7 | 9 | 8.0 | {"predictions": {"user_id": ["9", "4"], "similarity": [0.9738, 0.9087]}} |
| 8 | 2 | 8.5 | {"predictions": {"user_id": ["5", "1"], "similarity": [0.992, 0.6356]}} |
| 8 | 4 | 2.5 | {"predictions": {"user_id": ["5", "1"], "similarity": [0.992, 0.6356]}} |
| 8 | 6 | 5.0 | {"predictions": {"user_id": ["5", "1"], "similarity": [0.992, 0.6356]}} |
| 8 | 9 | 3.5 | {"predictions": {"user_id": ["5", "1"], "similarity": [0.992, 0.6356]}} |
| 9 | 1 | 5.0 | {"predictions": {"user_id": ["4", "7"], "similarity": [0.9764, 0.9738]}} |
| 9 | 3 | 8.0 | {"predictions": {"user_id": ["4", "7"], "similarity": [0.9764, 0.9738]}} |
| 9 | 7 | 2.5 | {"predictions": {"user_id": ["4", "7"], "similarity": [0.9764, 0.9738]}} |
| 9 | 8 | 5.5 | {"predictions": {"user_id": ["4", "7"], "similarity": [0.9764, 0.9738]}} |
+---------+---------+--------+---------------------------------------------------------------------------+
40 rows in set (0.0414 sec)
Review the recommended similar users in the
ml_results column next to
user_id. For example, for user 1,
users 3 and 5 are the top users predicted to be most
similar. Review the similarity values in the
ml_results column next to
similarity to review the how similar
each user is. For example, user 3 has a similarity value
of 0.7922 to user 1, and user 5 has a similarity value
of 0.7238.
Learn how to generate different types of recommendations:
Learn how to Score a Recommendation Model.