4.6.5.9 Generating Recommendations for Similar Users

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.

Before You Begin

Review and complete the following tasks:

• Prepare Data for a Recommendation Model

• Train a Recommendation Model

• Generate Predictions for a Recommendation Model

Generating Similar Users

When you run ML_PREDICT_TABLE or ML_PREDICT_ROW to generate similar user recommendations, a default value of three similar users are recommended. To change this value, set the topk parameter.

1. 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);

2. 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.

   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));

   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.

3. 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.

4. Alternatively, if you do not want to generate an entire table of similar items, you can run ML_PREDICT_ROW to specify a user to recommend similar users for.

   mysql> SELECT sys.ML_PREDICT_ROW(input_data, model_handle), [options]);

   Replace input_data and model_handle with your own values. Add options as needed.

   The following example runs ML_PREDICT_ROW and specifies user 1 with a limit of two recommended similar users.

   mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "1"}', @model,  JSON_OBJECT('recommend', 'users_to_users', 'topk', 2));
   +----------------------------------------------------------------------------------------------------------+
   | sys.ML_PREDICT_ROW('{"user_id": "1"}', @model,  JSON_OBJECT('recommend', 'users_to_users', 'topk', 2))   |
   +----------------------------------------------------------------------------------------------------------+
   | {"user_id": "1", "ml_results": {"predictions": {"user_id": ["3", "5"], "similarity": [0.7922, 0.7238]}}} |
   +----------------------------------------------------------------------------------------------------------+
   1 row in set (0.7951 sec)

   The similar users of 3 and 5 and similarity values are the same as the one in the output table previously created.

What's Next

• Learn how to generate different types of recommendations:

  • Generate Predictions for Ratings and Rankings

  • Generate Item Recommendations for Users

  • Generate User Recommendations for Items

  • Generate Recommendations for Similar Items

• Learn how to Score a Recommendation Model.