MSDN Documentation

SQL Server Machine Learning Services: Recommendation Samples

This section provides sample code and guidance for implementing recommendation systems using SQL Server Machine Learning Services. Leverage the power of Python and R directly within your SQL Server database to build sophisticated and scalable recommendation engines.

Overview of Recommendation Systems

Recommendation systems aim to predict the "rating" or "preference" a user would give to an item. They are widely used in e-commerce, media streaming, and content platforms to personalize user experiences and drive engagement.

Key Technologies Used:

• SQL Server Machine Learning Services: Enables running R and Python scripts directly on SQL Server.
• Python Libraries: scikit-learn, pandas, numpy, surprise (for recommendation algorithms).
• R Packages: Recommenderlab, dplyr, data.table.

Sample Scenarios and Code

Getting Started

To use these samples, ensure you have SQL Server Machine Learning Services installed and configured. You'll need appropriate permissions to create stored procedures and execute external scripts.

For detailed installation and configuration guides, please refer to the official SQL Server Machine Learning Services documentation.

Best Practices for Recommendation Systems

• Data Preprocessing: Clean and prepare your user-item interaction data effectively.
• Algorithm Selection: Choose the recommendation algorithm that best suits your data and business goals.
• Scalability: Design your solutions for performance and scalability, especially with large datasets.
• Evaluation: Regularly evaluate the performance of your recommendation models using appropriate metrics (e.g., precision, recall, RMSE).

Sample Code: User-Based Collaborative Filtering (Python)

-- Stored procedure to generate user-based recommendations
CREATE PROCEDURE dbo.sp_GenerateUserRecommendations
    @UserID INT,
    @NumRecommendations INT = 5
AS
BEGIN
    SET NOCOUNT ON;

    -- Execute Python script to get recommendations
    EXEC sp_execute_external_script
        @language = N'Python',
        @script = N'
import pandas as pd
from sklearn.metrics.pairwise import cosine_similarity

# Assume ratings_df is loaded from SQL Server
# Example structure: UserID, ItemID, Rating

# Placeholder for actual data loading from SQL
# In a real scenario, you would fetch this from SQL Server
# For demonstration, let''s create a dummy DataFrame
data = {
    "UserID": [1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4],
    "ItemID": [101, 102, 103, 101, 104, 105, 102, 103, 104, 106, 101, 105, 107],
    "Rating": [5, 4, 3, 4, 5, 2, 5, 4, 3, 5, 3, 4, 5]
}
ratings_df = pd.DataFrame(data)

# Pivot table to create user-item matrix
user_item_matrix = ratings_df.pivot_table(index="UserID", columns="ItemID", values="Rating").fillna(0)

# Calculate user similarity (cosine similarity)
user_similarity = cosine_similarity(user_item_matrix)
user_similarity_df = pd.DataFrame(user_similarity, index=user_item_matrix.index, columns=user_item_matrix.index)

# Get recommendations for the target user
target_user = @UserID # Pass @UserID from SQL

if target_user not in user_similarity_df.index:
    print("User not found.")
    output_df = pd.DataFrame()
else:
    # Get similar users, excluding the target user itself
    similar_users = user_similarity_df[target_user].sort_values(ascending=False)
    similar_users = similar_users.drop(target_user)

    # Get items rated by the target user
    user_rated_items = ratings_df[ratings_df["UserID"] == target_user]["ItemID"].tolist()

    recommendations = {}
    for similar_user, similarity_score in similar_users.items():
        # Get items rated by similar user
        similar_user_rated_items = ratings_df[ratings_df["UserID"] == similar_user]

        # Recommend items not rated by the target user
        for index, row in similar_user_rated_items.iterrows():
            if row["ItemID"] not in user_rated_items:
                if row["ItemID"] not in recommendations:
                    recommendations[row["ItemID"]] = 0
                recommendations[row["ItemID"]] += similarity_score * row["Rating"] # Weighted by similarity and rating

    # Sort recommendations by score and take top N
    sorted_recommendations = sorted(recommendations.items(), key=lambda item: item[1], reverse=True)
    top_recommendations = sorted_recommendations[:@NumRecommendations] # Pass @NumRecommendations from SQL

    output_df = pd.DataFrame(top_recommendations, columns=["RecommendedItemID", "Score"])
    output_df["UserID"] = target_user

',
        @input_data_1 = N'SELECT UserID, ItemID, Rating FROM dbo.UserRatings' -- Replace with your actual table
    ) WITH RESULT SETS ((RecommendedItemID INT, Score FLOAT));
END
GO

-- Example execution:
-- EXEC dbo.sp_GenerateUserRecommendations @UserID = 1, @NumRecommendations = 3;