HeatWave Release Notes
Recommendation models can recommend the following for explicit and implicit feedback:
-
The rating or ranking that a user will give to an item.
For known users and known items, the prediction is the model output.
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.
-
The top K users that will like an item.
For known users and known items, the prediction is the model output.
-
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.
-
The top K items that a user will like.
For known users and known items, the prediction is the model output.
-
For a new user, and an explicit feedback model, the prediction is the global top K items that received the average highest ratings.
For a new user, and an implicit feedback model, the prediction is the global top K items with the highest number of interactions.
For a user who has tried all known items, the prediction is an empty list because it is not possible to recommend any other items. Set
remove_seentofalseto repeat existing interactions from the training table.
-
The top K items similar to another item.
For known items, the prediction is the model output.
For a new item, there is no information to provide a prediction. This will produce an error.
The predictions are expressed in cosine similarity, and range from 0, very dissimilar, to 1, very similar.
-
The top K users similar to another user.
For known users, the prediction is the model output.
For a new user, there is no information to provide a prediction. This will produce an error.
The predictions are expressed in cosine similarity, and range from 0, very dissimilar, to 1, very similar.
For recommendations, run the
ML_PREDICT_ROW or
ML_PREDICT_TABLE routines on
data with the same columns as the training model.
The options for
ML_PREDICT_ROW and
ML_PREDICT_TABLE include the
following:
topk: The number of recommendations to provide. The default is3.-
recommend: Specifies what to recommend. Permitted values are:ratings: Use this option to predict ratings. This is the default value.items: Use this option to recommend items for users.users: Use this option to recommend users for items.users_to_items: This is the same asitems.items_to_users: This is the same asusers.items_to_items: Use this option to recommend similar items for items.users_to_users: Use this option to recommend similar users for users.
remove_seen: Iftrue, the model will not repeat existing interactions from the training table.
A table with the same name as the output table for
ML_PREDICT_TABLE must not
already exist.
NULL values for any row in the
users or items columns
will cause an error.
The options for
ML_SCORE include the following:
threshold: The optional threshold that defines positive feedback, and a relevant sample. Only use with ranking metrics.topk: The optional top K rows to recommend. Only use with ranking metrics.remove_seen: Iftrue, the model will not repeat existing interactions from the training table.
ML_SCORE can use any of the
recommendation metrics to score a recommendation model. Always
use the metric parameter to specify a
ratings metric to use with a recommendation model that uses
explicit feedback, or a ranking metric to use with a
recommendation model that uses implicit feedback. See:
Section 3.15.13, “Optimization and Scoring Metrics”.
For instructions to use the
ML_PREDICT_ROW,
ML_PREDICT_TABLE, and
ML_SCORE routines, see
Section 3.7, “Predictions”, and
Section 3.13.6, “Scoring Models”. For the complete
list of option descriptions, see
ML_PREDICT_ROW,
ML_PREDICT_TABLE, and
ML_SCORE.
ML_EXPLAIN,
ML_EXPLAIN_ROW and
ML_EXPLAIN_TABLE do not support
recommendation models. A call to any of these routines with a
recommendation model will produce an error.
-
A
ML_PREDICT_ROWexample that predicts the rating for a particular user and item. This is the default option forrecommend, withoptionsset toNULL.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "836347", "item_id": "64154"}', @model, NULL); +----------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "836347", "item_id": "64154"}', @model, NULL) | +----------------------------------------------------------------------------------------------+ | {"item_id": "64154", "user_id": "836347", "ml_results": "{"predictions": {"rating": 1.0}}"} | +----------------------------------------------------------------------------------------------+ 1 row in set (0.12 sec) -
A
ML_PREDICT_TABLEexample that predicts the ratings for particular users and items. This is the default option forrecommend, withoptionsset toNULL.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.retailrocket-transactionto_to_predict', @model, 'mlcorpus.table_predictions', NULL); Query OK, 0 rows affected (0.7589 sec) mysql> SELECT * FROM table_predictions; +-----+---------------+---------+---------+--------+-----------------------------------+ | _id | timestamp | user_id | item_id | rating | ml_results | +-----+---------------+---------+---------+--------+-----------------------------------+ | 1 | 1436670000000 | 836347 | 64154 | 1 | {"predictions": {"rating": 1.0}} | | 2 | 1441250000000 | 435603 | 335366 | 1 | {"predictions": {"rating": 1.04}} | | 3 | 1439670000000 | 1150086 | 314062 | 1 | {"predictions": {"rating": 1.03}} | +-----+---------------+---------+---------+--------+-----------------------------------+ 3 rows in set (0.00 sec) -
This is the same as setting
recommendtoratings.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.retailrocket-transactionto_to_predict', @model, 'mlcorpus.table_predictions', JSON_OBJECT("recommend", "ratings")); Query OK, 0 rows affected (1.29 sec) mysql> SELECT * FROM table_predictions; +-----+---------------+---------+---------+--------+-----------------------------------+ | _id | timestamp | user_id | item_id | rating | ml_results | +-----+---------------+---------+---------+--------+-----------------------------------+ | 1 | 1436670000000 | 836347 | 64154 | 1 | {"predictions": {"rating": 1.0}} | | 2 | 1441250000000 | 435603 | 335366 | 1 | {"predictions": {"rating": 1.04}} | | 3 | 1439670000000 | 1150086 | 314062 | 1 | {"predictions": {"rating": 1.03}} | +-----+---------------+---------+---------+--------+-----------------------------------+ 3 rows in set (0.00 sec) -
A
ML_PREDICT_ROWexample that recommends the top 3 users that will like a particular item.mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "64154"}', @model, JSON_OBJECT("recommend", "users", "topk", 3)); +-------------------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"item_id": "64154"}', @model, JSON_OBJECT("recommend", "users", "topk", 3)) | +-------------------------------------------------------------------------------------------------------------------------------------+ | {"item_id": "64154", "ml_results": "{"predictions": {"user_id": ["171718", "1167457", "1352334"], "rating": [2.08, 2.03, 1.99]}}"} | +-------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.34 sec) -
A
ML_PREDICT_TABLEexample that recommends the top 3 users that will like particular items.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.retailrocket-transactionto_to_predict', @model, 'mlcorpus.table_predictions_items', JSON_OBJECT("recommend", "users", "topk", 3)); Query OK, 0 rows affected (1.85 sec) mysql> SELECT * FROM table_predictions_items; +-----+---------------+---------+---------+--------+-----------------------------------------------------------------------------------------+ | _id | timestamp | user_id | item_id | rating | ml_results | +-----+---------------+---------+---------+--------+-----------------------------------------------------------------------------------------+ | 1 | 1436670000000 | 836347 | 64154 | 1 | {"predictions": {"user_id": ["user_1", "user_2", "user_3"], "rating": [3.0, 1.0, 0.0]}} | | 2 | 1441250000000 | 435603 | 335366 | 1 | {"predictions": {"user_id": ["user_1", "user_2", "user_3"], "rating": [3.0, 1.0, 0.0]}} | | 3 | 1439670000000 | 1150086 | 314062 | 1 | {"predictions": {"user_id": ["user_1", "user_2", "user_3"], "rating": [3.0, 1.0, 0.0]}} | +-----+---------------+---------+---------+--------+-----------------------------------------------------------------------------------------+ 3 rows in set (0.0003 sec) -
A more complete example for the top 3 users that will like particular items.
mysql> SELECT * FROM train_table; +---------+------------+--------+ | user_id | item_id | rating | +---------+------------+--------+ | user_1 | good_movie | 5 | | user_1 | bad_movie | 1 | | user_2 | bad_movie | 1 | | user_3 | bad_movie | 0 | | user_4 | bad_movie | 0 | +---------+------------+--------+ 5 rows in set (0.00 sec) mysql> CALL sys.ML_TRAIN('mlcorpus.train_table', 'rating', JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id'), @model); Query OK, 0 rows affected (11.39 sec) mysql> CALL sys.ML_MODEL_LOAD(@model, NULL); Query OK, 0 rows affected (0.98 sec) mysql> SELECT * FROM items_table; +------------+ | item_id | +------------+ | good_movie | | bad_movie | | new_movie | +------------+ 3 rows in set (0.00 sec) mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.items_table', @model, 'mlcorpus.user_recommendation', JSON_OBJECT("recommend", "users", "topk", 3)); Query OK, 0 rows affected (1.21 sec) mysql> SELECT * FROM user_recommendation; +-----+------------+-------------------------------------------------------------------------------------------+ | _id | item_id | ml_results | +-----+------------+-------------------------------------------------------------------------------------------+ | 1 | good_movie | {"predictions": {"user_id": ["user_2", "user_3", "user_4"], "rating": [1.84, 1.71, 1.7]}} | | 2 | bad_movie | {"predictions": {"user_id": [], "rating": []}} | | 3 | new_movie | {"predictions": {"user_id": ["user_1", "user_2", "user_3"], "rating": [3.0, 1.0, 0.0]}} | +-----+------------+-------------------------------------------------------------------------------------------+ 3 rows in set (0.0004 sec) -
A
ML_PREDICT_ROWexample for the top 3 users that will like a particular item with theitems_to_usersoption.mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "524"}', @model, JSON_OBJECT("recommend", "items_to_users", "topk", 3)); +---------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"item_id": "524"}', @model, JSON_OBJECT("recommend", "items_to_users", "topk", 3)) | +---------------------------------------------------------------------------------------------------------------------+ | {"item_id": "524", "ml_results": "{"predictions": {"user_id": ["7", "164", "894"], "rating": [4.05, 3.94, 3.91]}}"} | +---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.2808 sec) -
A
ML_PREDICT_TABLEexample for the top 3 users that will like particular items with theitems_to_usersoption.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.ml-100k', @model, 'mlcorpus.item_to_users_recommendation', JSON_OBJECT("recommend", "items_to_users", "topk", 3)); Query OK, 0 rows affected (21.2070 sec) mysql> SELECT * FROM mlcorpus.item_to_users_recommendation LIMIT 5; +-----+---------+---------+--------+-----------+---------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | timestamp | ml_results | +-----+---------+---------+--------+-----------+---------------------------------------------------------------------------------+ | 1 | 846 | 524 | 3 | 883948000 | {"predictions": {"user_id": ["7", "164", "894"], "rating": [4.05, 3.94, 3.91]}} | | 2 | 138 | 474 | 5 | 879024000 | {"predictions": {"user_id": ["7", "164", "894"], "rating": [4.58, 4.47, 4.45]}} | | 3 | 840 | 609 | 4 | 891205000 | {"predictions": {"user_id": ["7", "164", "894"], "rating": [4.25, 4.14, 4.13]}} | | 4 | 660 | 402 | 3 | 891201000 | {"predictions": {"user_id": ["7", "164", "894"], "rating": [4.09, 3.98, 3.96]}} | | 5 | 154 | 89 | 5 | 879139000 | {"predictions": {"user_id": ["7", "164", "151"], "rating": [4.28, 4.18, 4.15]}} | +-----+---------+---------+--------+-----------+---------------------------------------------------------------------------------+ -
A
ML_PREDICT_ROWexample for the top 3 items that a particular user will like.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "836347"}', @model, JSON_OBJECT("recommend", "items", "topk", 3)); +------------------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "836347"}', @model, JSON_OBJECT("recommend", "items", "topk", 3)) | +------------------------------------------------------------------------------------------------------------------------------------+ | {"user_id": "836347", "ml_results": "{"predictions": {"item_id": ["119736", "396042", "224549"], "rating": [2.18, 1.53, 1.53]}}"} | +------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.31 sec) -
A
ML_PREDICT_TABLEexample for the top 3 items that particular users will like.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.retailrocket-transactionto_to_predict', @model, 'mlcorpus.user_recommendations', JSON_OBJECT("recommend", "items", "topk", 3)); Query OK, 0 rows affected (6.0322 sec) mysql> SELECT * FROM user_recommendations; +-----+---------------+---------+---------+--------+--------------------------------------------------------------------------------------------+ | _id | timestamp | user_id | item_id | rating | ml_results | +-----+---------------+---------+---------+--------+--------------------------------------------------------------------------------------------+ | 1 | 1436670000000 | 836347 | 64154 | 1 | {"predictions": {"item_id": ["119736", "224549", "227311"], "rating": [2.23, 1.57, 1.52]}} | | 2 | 1441250000000 | 435603 | 335366 | 1 | {"predictions": {"item_id": ["119736", "224549", "396042"], "rating": [2.31, 1.54, 1.53]}} | | 3 | 1439670000000 | 1150086 | 314062 | 1 | {"predictions": {"item_id": ["396042", "224549", "227311"], "rating": [1.54, 1.53, 1.51]}} | +-----+---------------+---------+---------+--------+--------------------------------------------------------------------------------------------+ 3 rows in set (0.0004 sec) -
A more complete example for the top 3 items that particular users will like.
mysql> SELECT * FROM train_table; +---------+------------+--------+ | user_id | item_id | rating | +---------+------------+--------+ | user_1 | good_movie | 5 | | user_1 | bad_movie | 1 | | user_2 | bad_movie | 1 | | user_3 | bad_movie | 0 | | user_4 | bad_movie | 0 | +---------+------------+--------+ 5 rows in set (0.00 sec) mysql> CALL sys.ML_TRAIN('mlcorpus.train_table', 'rating', JSON_OBJECT('task', 'recommendation', 'users', 'user_id', 'items', 'item_id'), @model); Query OK, 0 rows affected (11.39 sec) mysql> CALL sys.ML_MODEL_LOAD(@model, NULL); Query OK, 0 rows affected (0.98 sec) mysql> SELECT * FROM users_table; +----------+ | user_id | +----------+ | user_1 | | user_3 | | new_user | +----------+ 3 rows in set (0.00 sec) mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.users_table', @model, 'mlcorpus.item_recommendation', JSON_OBJECT("recommend", "items", "topk", 3)); Query OK, 0 rows affected (1.23 sec) mysql> SELECT * FROM item_recommendation; +-----+----------+---------------------------------------------------------------------------------+ | _id | user_id | ml_results | +-----+----------+---------------------------------------------------------------------------------+ | 1 | user_1 | {"predictions": {"item_id": [], "rating": []}} | | 2 | user_2 | {"predictions": {"item_id": ["good_movie"], "rating": [1.84]}} | | 3 | new_user | {"predictions": {"item_id": ["good_movie", "bad_movie"], "rating": [5.0, 0.5]}} | +-----+----------+---------------------------------------------------------------------------------+ 3 rows in set (0.0004 sec) -
A
ML_PREDICT_ROWexample for the top 3 items that a particular user will like with theusers_to_itemsoption.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "846"}', @model, JSON_OBJECT("recommend", "users_to_items", "topk", 3)); +----------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "846"}', @model, JSON_OBJECT("recommend", "users_to_items", "topk", 3)) | +----------------------------------------------------------------------------------------------------------------------+ | {"user_id": "846", "ml_results": "{"predictions": {"item_id": ["313", "483", "64"], "rating": [4.06, 4.05, 4.04]}}"} | +----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.2811 sec) -
A
ML_PREDICT_TABLEexample for the top 3 items that particular users will like with theusers_to_itemsoption.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.ml-100k', @model, 'mlcorpus.user_to_items_recommendation', JSON_OBJECT("recommend", "users_to_items", "topk", 3)); Query OK, 0 rows affected (22.8504 sec) mysql> SELECT * FROM mlcorpus.user_to_items_recommendation LIMIT 5; +-----+---------+---------+--------+-----------+----------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | timestamp | ml_results | +-----+---------+---------+--------+-----------+----------------------------------------------------------------------------------+ | 1 | 846 | 524 | 3 | 883948000 | {"predictions": {"item_id": ["313", "483", "64"], "rating": [4.06, 4.05, 4.04]}} | | 2 | 138 | 474 | 5 | 879024000 | {"predictions": {"item_id": ["313", "64", "483"], "rating": [4.29, 4.29, 4.24]}} | | 3 | 840 | 609 | 4 | 891205000 | {"predictions": {"item_id": ["313", "64", "483"], "rating": [4.24, 4.23, 4.2]}} | | 4 | 660 | 402 | 3 | 891201000 | {"predictions": {"item_id": ["313", "64", "483"], "rating": [3.63, 3.62, 3.6]}} | | 5 | 154 | 89 | 5 | 879139000 | {"predictions": {"item_id": ["313", "64", "483"], "rating": [4.45, 4.45, 4.4]}} | +-----+---------+---------+--------+-----------+----------------------------------------------------------------------------------+ 5 rows in set (0.0004 sec) -
A
ML_PREDICT_ROWexample for the top 3 items similar to another item.mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "524"}', @model, JSON_OBJECT("recommend", "items_to_items", "topk", 3)); +------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"item_id": "524"}', @model, JSON_OBJECT("recommend", "items_to_items", "topk", 3)) | +------------------------------------------------------------------------------------------------------------------------+ | {"item_id": "524", "ml_results": "{"predictions": {"item_id": ["665", "633", "378"], "similarity": [1.0, 1.0, 1.0]}}"} | +------------------------------------------------------------------------------------------------------------------------+ -
A
ML_PREDICT_TABLEexample for the top 3 items similar to other items.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.ml-100k', @model, 'mlcorpus.item_to_items_recommendation', JSON_OBJECT("recommend", "items_to_items", "topk", 3)); Query OK, 0 rows affected (13.4530 sec) mysql> SELECT * FROM mlcorpus.items_to_items_recommendation limit 5; +-----+---------+---------+--------+-----------+----------------------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | timestamp | ml_results | +-----+---------+---------+--------+-----------+----------------------------------------------------------------------------------------------+ | 1 | 846 | 524 | 3 | 883948000 | {"predictions": {"item_id": ["665", "633", "378"], "similarity": [1.0, 1.0, 1.0]}} | | 2 | 138 | 474 | 5 | 879024000 | {"predictions": {"item_id": ["414", "522", "164"], "similarity": [0.9222, 0.6713, 0.639]}} | | 3 | 840 | 609 | 4 | 891205000 | {"predictions": {"item_id": ["208", "1071", "2"], "similarity": [0.9991, 0.9983, 0.9978]}} | | 4 | 660 | 402 | 3 | 891201000 | {"predictions": {"item_id": ["1183", "1092", "589"], "similarity": [1.0, 0.9979, 0.9953]}} | | 5 | 154 | 89 | 5 | 879139000 | {"predictions": {"item_id": ["480", "590", "1047"], "similarity": [0.9438, 0.2715, 0.1976]}} | +-----+---------+---------+--------+-----------+----------------------------------------------------------------------------------------------+ 5 rows in set (0.0003 sec) -
A
ML_PREDICT_ROWexample for the top 3 users similar to another user.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "846"}', @model, JSON_OBJECT("recommend", "users_to_users", "topk", 3)); +--------------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "846"}', @model, JSON_OBJECT("recommend", "users_to_users", "topk", 3)) | +--------------------------------------------------------------------------------------------------------------------------------+ | {"user_id": "846", "ml_results": "{"predictions": {"user_id": ["62", "643", "172"], "similarity": [0.7413, 0.7275, 0.6709]}}"} | +--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.2373 sec) -
A
ML_PREDICT_TABLEexample for the top 3 users similar to other users.mysql> CALL sys.ML_PREDICT_TABLE'mlcorpus.ml-100k', @model, 'mlcorpus.user_to_users_recommendation', JSON_OBJECT("recommend", "users_to_users", "topk", 3)); Query OK, 0 rows affected (11.2847 sec) mysql> SELECT * FROM mlcorpus.user_to_users_recommendation limit 5 ; +-----+---------+---------+--------+-----------+---------------------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | timestamp | ml_results | +-----+---------+---------+--------+-----------+---------------------------------------------------------------------------------------------+ | 1 | 846 | 524 | 3 | 883948000 | {"predictions": {"user_id": ["62", "643", "172"], "similarity": [0.7413, 0.7275, 0.6709]}} | | 2 | 138 | 474 | 5 | 879024000 | {"predictions": {"user_id": ["932", "151", "671"], "similarity": [0.9958, 0.844, 0.1993]}} | | 3 | 840 | 609 | 4 | 891205000 | {"predictions": {"user_id": ["49", "293", "887"], "similarity": [0.9993, 0.9988, 0.9986]}} | | 4 | 660 | 402 | 3 | 891201000 | {"predictions": {"user_id": ["532", "160", "416"], "similarity": [0.9991, 0.9978, 0.9966]}} | | 5 | 154 | 89 | 5 | 879139000 | {"predictions": {"user_id": ["338", "562", "942"], "similarity": [0.993, 0.993, 0.7617]}} | +-----+---------+---------+--------+-----------+---------------------------------------------------------------------------------------------+ 5 rows in set (0.0004 sec) -
A
ML_SCOREexample:mysql> CALL sys.ML_SCORE('mlcorpus.ipinyou-click_test', 'rating', @model, 'neg_mean_squared_error', @score, NULL); Query OK, 0 rows affected (1 min 18.29 sec) mysql> SELECT @score; +----------------------+ | @score | +----------------------+ | -0.23571448028087616 | +----------------------+ 1 row in set (0.00 sec)
-
A
ML_PREDICT_ROWexample that predicts the ranking for a particular user and item.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "836", "item_id": "226"}', @model, NULL); +---------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "836", "item_id": "226"}', @model, NULL) | +---------------------------------------------------------------------------------------+ | {"item_id": "226", "user_id": "836", "ml_results": {"predictions": {"rating": 2.46}}} | +---------------------------------------------------------------------------------------+ 1 row in set (0.1390 sec) -
A
ML_PREDICT_TABLEexample that predicts the rankings for particular users and items.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_table', @model, 'mlcorpus.table_predictions', NULL); mysql> SELECT * FROM mlcorpus.table_predictions LIMIT 10; +-----+---------+---------+--------+------------------------------------+ | _id | user_id | item_id | rating | ml_results | +-----+---------+---------+--------+------------------------------------+ | 1 | 1026 | 13763 | 1 | {"predictions": {"rating": 1.25}} | | 2 | 992 | 16114 | 1 | {"predictions": {"rating": -0.15}} | | 3 | 1863 | 4527 | 1 | {"predictions": {"rating": 0.42}} | | 4 | 3725 | 3981 | 1 | {"predictions": {"rating": 3.11}} | | 5 | 3436 | 5854 | 1 | {"predictions": {"rating": 0.45}} | | 6 | 2236 | 13608 | 1 | {"predictions": {"rating": -0.35}} | | 7 | 5230 | 1181 | 1 | {"predictions": {"rating": 1.96}} | | 8 | 1684 | 10140 | 1 | {"predictions": {"rating": -0.42}} | | 9 | 3438 | 8022 | 1 | {"predictions": {"rating": -0.11}} | | 10 | 1536 | 7578 | 1 | {"predictions": {"rating": 0.1}} | +-----+---------+---------+--------+------------------------------------+ 10 rows in set (0.0004 sec) -
A
ML_PREDICT_ROWexample that recommends the top 3 users that will like a particular item.mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "13763"}', @model, JSON_OBJECT("recommend", "users", "topk", 3)); +--------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"item_id": "13763"}', @model, JSON_OBJECT("recommend", "users", "topk", 3)) | +--------------------------------------------------------------------------------------------------------------------------+ | {"item_id": "13763", "ml_results": {"predictions": {"rating": [1.26, 1.25, 1.25], "user_id": ["3929", "5255", "4583"]}}} | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.3098 sec) -
A
ML_PREDICT_ROWexample that recommends the top 3 users that will like a particular item and includes existing interactions from the training table.mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "13763"}', @model, JSON_OBJECT("recommend", "users", "topk", 3, "remove_seen", false)); +--------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"item_id": "13763"}', @model, JSON_OBJECT("recommend", "users", "topk", 3, "remove_seen", false)) | +--------------------------------------------------------------------------------------------------------------------------+ | {"item_id": "13763", "ml_results": {"predictions": {"rating": [1.26, 1.26, 1.26], "user_id": ["4590", "1822", "3585"]}}} | +--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.3098 sec) -
A
ML_PREDICT_TABLEexample that recommends the top 3 users that will like particular items.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_sample', @model, 'mlcorpus.table_predictions_items', JSON_OBJECT("recommend", "users", "topk", 3)); Query OK, 0 rows affected (4.1777 sec) mysql> SELECT * FROM mlcorpus.table_predictions_items LIMIT 3; +-----+---------+---------+--------+--------------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | ml_results | +-----+---------+---------+--------+--------------------------------------------------------------------------------------+ | 1 | 1026 | 13763 | 1 | {"predictions": {"user_id": ["3929", "5255", "4583"], "rating": [1.26, 1.25, 1.25]}} | | 2 | 992 | 16114 | 1 | {"predictions": {"user_id": ["2671", "69", "429"], "rating": [-0.13, -0.13, -0.13]}} | | 3 | 1863 | 4527 | 1 | {"predictions": {"user_id": ["4732", "4439", "3399"], "rating": [0.43, 0.43, 0.43]}} | +-----+---------+---------+--------+--------------------------------------------------------------------------------------+ 3 rows in set (0.0003 sec) -
A
ML_PREDICT_ROWexample that recommends the top 3 items that a particular user will like.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "1026"}', @model, JSON_OBJECT("recommend", "items", "topk", 3)); +-------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "1026"}', @model, JSON_OBJECT("recommend", "items", "topk", 3)) | +-------------------------------------------------------------------------------------------------------------------+ | {"user_id": "1026", "ml_results": {"predictions": {"rating": [3.43, 3.37, 3.18], "item_id": ["10", "14", "11"]}}} | +-------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.6586 sec) -
A
ML_PREDICT_ROWexample that recommends the top 3 items that a particular user will like and includes existing interactions from the training table.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "1026"}', @model, JSON_OBJECT("recommend", "items", "topk", 3, "remove_seen", false)); +------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "1026"}', @model, JSON_OBJECT("recommend", "items", "topk", 3, "remove_seen", false)) | +------------------------------------------------------------------------------------------------------------------------+ | {"user_id": "1026", "ml_results": {"predictions": {"rating": [3.43, 3.37, 3.18], "item_id": ["10", "14", "11"]}}} | +------------------------------------------------------------------------------------------------------------------------+ -
A
ML_PREDICT_TABLEexample that recommends the top 3 items that particular users will like.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_sample', @model, 'mlcorpus.table_predictions_users', JSON_OBJECT("recommend", "items", "topk", 3)); Query OK, 0 rows affected (5.0672 sec) mysql> SELECT * FROM mlcorpus.table_predictions_users LIMIT 3; +-----+---------+---------+--------+--------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | ml_results | +-----+---------+---------+--------+--------------------------------------------------------------------------------+ | 1 | 1026 | 13763 | 1 | {"predictions": {"item_id": ["10", "14", "11"], "rating": [3.43, 3.37, 3.18]}} | | 2 | 992 | 16114 | 1 | {"predictions": {"item_id": ["10", "14", "11"], "rating": [3.42, 3.38, 3.17]}} | | 3 | 1863 | 4527 | 1 | {"predictions": {"item_id": ["10", "14", "11"], "rating": [3.42, 3.37, 3.18]}} | +-----+---------+---------+--------+--------------------------------------------------------------------------------+ -
A
ML_PREDICT_ROWexample for the top 3 items similar to another item.mysql> SELECT sys.ML_PREDICT_ROW('{"item_id": "13763"}', @model, JSON_OBJECT("recommend", "items_to_items", "topk", 3)); +------------------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"item_id": "13763"}', @model, JSON_OBJECT("recommend", "items_to_items", "topk", 3)) | +------------------------------------------------------------------------------------------------------------------------------------+ | {"item_id": "13763", "ml_results": {"predictions": {"item_id": ["13751", "13711", "13668"], "similarity": [1.0, 0.9999, 0.9999]}}} | +------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.4607 sec) -
A
ML_PREDICT_TABLEexample for the top 3 items similar to other items.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_sample', @model, 'mlcorpus.item_to_items_recommendation', JSON_OBJECT("recommend", "items_to_items", "topk", 3)); mysql> SELECT * FROM mlcorpus.item_to_items_recommendation LIMIT 3; +-----+---------+---------+--------+---------------------------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | ml_results | +-----+---------+---------+--------+---------------------------------------------------------------------------------------------------+ | 1 | 1026 | 13763 | 1 | {"predictions": {"item_id": ["13751", "13711", "13668"], "similarity": [1.0, 0.9999, 0.9999]}} | | 2 | 992 | 16114 | 1 | {"predictions": {"item_id": ["14050", "16413", "16454"], "similarity": [0.8492, 0.8017, 0.7829]}} | | 3 | 1863 | 4527 | 1 | {"predictions": {"item_id": ["6008", "1873", "1650"], "similarity": [0.8169, 0.7471, 0.7442]}} | +-----+---------+---------+--------+---------------------------------------------------------------------------------------------------+ 3 rows in set (0.0004 sec) -
A
ML_PREDICT_ROWexample for the top 3 users similar to another user.mysql> SELECT sys.ML_PREDICT_ROW('{"user_id": "1026"}', @model, JSON_OBJECT("recommend", "users_to_users", "topk", 3)); +--------------------------------------------------------------------------------------------------------------------------+ | sys.ML_PREDICT_ROW('{"user_id": "1026"}', @model, JSON_OBJECT("recommend", "users_to_users", "topk", 3)) | +--------------------------------------------------------------------------------------------------------------------------+ | {"user_id": "1026", "ml_results": {"predictions": {"user_id": ["2215", "1992", "4590"], "similarity": [1.0, 1.0, 1.0]}}} | +--------------------------------------------------------------------------------------------------------------------------+ -
A
ML_PREDICT_TABLEexample for the top 3 users similar to other users.mysql> CALL sys.ML_PREDICT_TABLE('mlcorpus.test_sample', @model, 'mlcorpus.users_to_users_recommendation', JSON_OBJECT("recommend", "users_to_users", "topk", 3)); Query OK, 0 rows affected (3.4959 sec) mysql> SELECT * FROM mlcorpus.users_to_users_recommendation LIMIT 3; +-----+---------+---------+--------+------------------------------------------------------------------------------------------------+ | _id | user_id | item_id | rating | ml_results | +-----+---------+---------+--------+------------------------------------------------------------------------------------------------+ | 1 | 1026 | 13763 | 1 | {"predictions": {"user_id": ["2215", "1992", "4590"], "similarity": [1.0, 1.0, 1.0]}} | | 2 | 992 | 16114 | 1 | {"predictions": {"user_id": ["2092", "1809", "5174"], "similarity": [0.8576, 0.8068, 0.7533]}} | | 3 | 1863 | 4527 | 1 | {"predictions": {"user_id": ["4895", "1405", "2972"], "similarity": [0.9845, 0.8666, 0.8623]}} | +-----+---------+---------+--------+------------------------------------------------------------------------------------------------+ 3 rows in set (0.0004 sec) -
ML_SCOREexamples for the four metrics suitable for a recommendation model with implicit feedback, withthresholdset to3andtopkset to50.mysql> CALL sys.ML_MODEL_LOAD(@model, NULL); mysql> SET @options = JSON_OBJECT('threshold', 3, 'topk', 50); Query OK, 0 rows affected (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'precision_at_k', @score, @options); Query OK, 0 rows affected (21.22 sec) mysql> SELECT @score; +----------------------+ | @score | +----------------------+ | 0.058855220675468445 | +----------------------+ 1 row in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'recall_at_k', @score, @options); Query OK, 0 rows affected (20.93 sec) mysql> SELECT @score; +---------------------+ | @score | +---------------------+ | 0.32194915413856506 | +---------------------+ 1 row in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'hit_ratio_at_k', @score, @options); Query OK, 0 rows affected (21.02 sec) mysql> SELECT @score; +---------------------+ | @score | +---------------------+ | 0.31720301508903503 | +---------------------+ 1 row in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'ndcg_at_k', @score, @options); Query OK, 0 rows affected (20.99 sec) mysql> SELECT @score; +--------------------+ | @score | +--------------------+ | 0.1945730447769165 | +--------------------+ 1 row in set (0.00 sec) -
ML_SCOREexamples for the four metrics suitable for a recommendation model with implicit feedback, withthresholdset to3,topkset to50and including existing interactions from the training table withremove_seenset tofalse.mysql> CALL sys.ML_MODEL_LOAD(@model, NULL); mysql> SET @options = JSON_OBJECT('threshold', 3, 'topk', 50, 'remove_seen', false); Query OK, 0 rows affected (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'precision_at_k', @score, @options); Query OK, 0 rows affected (2.03 sec) mysql> SELECT @score; +---------------------+ | @score1 | +---------------------+ | 0.03488215431571007 | +---------------------+ 1 row in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'recall_at_k', @score, @options); Query OK, 0 rows affected (2.35 sec) mysql> SELECT @score; +---------------------+ | @score | +---------------------+ | 0.24304823577404022 | +---------------------+ 1 row in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'hit_ratio_at_k', @score, @options); Query OK, 0 rows affected (2.30 sec) mysql> SELECT @score; +---------------------+ | @score | +---------------------+ | 0.18799902498722076 | +---------------------+ 1 row in set (0.00 sec) mysql> CALL sys.ML_SCORE('mlcorpus.recsys_item_1', 'target', @model, 'ndcg_at_k', @score, @options); Query OK, 0 rows affected (2.35 sec) mysql> SELECT @score; +---------------------+ | @score | +---------------------+ | 0.12175655364990234 | +---------------------+ 1 row in set (0.00 sec)