3.11.7 Generating Recommendations for Similar Items

This section describes how to generate recommendations for similar items.

• For known items, the output includes a list of predicted items that have similar ratings and are appreciated by similar users.

• The predictions are expressed in cosine similarity, and range from 0, very dissimilar, to 1, very similar.

• For a new item, there is no information to provide a prediction. This will produce an error.

Before You Begin

1. Connect to your HeatWave DB System.

2. Complete the steps for training the recommendation model. See Section 3.11.2, “Training a Recommendation Model”.

3. Review Section 3.11.3, “Using a Recommendation Model”.

Generating Similar Items

To generate recommendations for similar items:

1. Use the ML_MODEL_LOAD routine to load the trained model with recommendations. For example:

   mysql> CALL sys.ML_MODEL_LOAD(@model, NULL);

2. Run the ML_PREDICT_TABLE or ML_PREDICT_ROW routine to generate predictions. You must set the recommend option to items_to_items.

   The following example uses explicit feedback and runs the ML_PREDICT_ROW routine to predict the top three 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]}}"} |
   +------------------------------------------------------------------------------------------------------------------------+

   Item 524 is predicted to be most similar to items 665, 633, and 378. The items have the highest similarity value of 1.0.

   The following example uses implicit feedback and runs the ML_PREDICT_TABLE routine to predict the top three 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;
   +-------------------+---------+---------+--------+---------------------------------------------------------------------------------------------------+
   | _4aad19ca6e_pk_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)

   • Items 13751, 13711, and 13668 are predicted to be most similar to item 13763.

   • Items 14050, 16413, and 16454 are predicted to be most similar to item 16114.

   • Items 6008, 1873, and 1650 are predicted to be most similar to item 4527.

   • The similarity values for each item-item pair are in ml_results after similarity. For example, for item 13763, item 13751 has a similarity value of 1.0, item 13711 has a similarity value of 0.9999, and item 13668 has a similarity value of 0.9999.

   The values in columns user_id and rating can be disregarded as they are part of the data that was trained from the input table.