Input
From the KNNRecommender Example:
- Ratings: ml_ratings
- Weights: ml_weights
- Bias: ml_bias
SQL Call
The call recommends five movies for ten users.
SELECT * FROM KnnRecommenderPredict ( ON (SELECT * FROM ml_ratings WHERE userid <= 10) AS Ratings PARTITION BY userid ON ml_bias AS Bias DIMENSION ON ml_weights AS Weights DIMENSION USING TopK (5) ) AS dt ORDER BY 1 DESC;
Output
userid itemid prediction ------ ------ ------------------ 5 454 4.43944806005997 5 2890 4.353130720557537 5 34 4.43944806005997 5 420 4.5586737193706535 5 357 4.5150229309770635 4 2023 4.472692557514834 4 1258 4.472692557514834 4 300 4.622911229674568 4 1704 4.472692557514834 4 2571 4.722330311219902 3 1258 4.396245483590272 3 1704 4.396245483590272 3 2023 4.396245483590272 3 2571 4.645740472524265 3 1136 4.146753684279514 3 1711 4.146753684279514 3 1267 4.146753684279514 2 515 4.540419986677616 2 480 4.540419986677616 2 296 4.463307355858737 2 265 4.540419986677616 2 648 4.540419986677616 1 1278 4.228140795465176 1 553 3.973010311922945 1 21 4.02887733802919 1 420 4.466978626573491 1 1639 4.479795437756031
Some predicted ratings are higher than 5, even though the maximum rating is 5. The weighted KNN recommendation algorithm does not limit its results to the range of the input data. The outcome of interest are the items with the highest recommendation score; if the resulting ratings must be limited to a specific range, normalize the output data.
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.