Simple Re-Ranking of SQL Queries with Machine Learning

In this tutorial, we will demonstrate how to use the Scorer and RewardTracker classes to update a ‘score’ column for a number of products in a ‘products’ table. The ‘score’ column will be used to learn an optimized ranking for these products, allowing for extremely fast and simple product recommendations. By sorting the queries based on the score column, we can provide a highly efficient product recommendation system.

Step 1: Initialize Scorer and RewardTracker Instances

First, you need to create instances of the Scorer and RewardTracker classes.

from improveai import Scorer, RewardTracker

# Initialize Scorer instance
scorer = Scorer(model_url)

# Initialize RewardTracker instance
reward_tracker = RewardTracker("products", track_url)

Step 2: Query Products from Database and Score Them

Next, you should query the products from your database and score them using the Scorer instance. Scores are updated periodically via a CRON job.

import your_database_module as db

# Query products from the database
products = db.query("SELECT * FROM products")

# Score the products using the Scorer instance
scores = scorer.score(products)

# Update the 'score' column for each product in the products table
for product, score in zip(products, scores):
    db.update("UPDATE products SET score = %s WHERE product_id = %s", (score, product["product_id"]))

Step 3: Sort Products by Score and Display Them

When querying the list of products, sort them descending by score.

# Query products sorted by score
sorted_products = db.query("SELECT * FROM products ORDER BY score DESC")

# Display the sorted products
for product in sorted_products:
    print(product)

Step 4: Track Product Selection and Purchase

When the user selects a product to browse, track the product using the RewardTracker. If the product is purchased, track a reward for the amount of profit generated.

def on_product_selected(selected_product, products):
    # Track the selected product using the RewardTracker
    reward_id = reward_tracker.track(selected_product, products)

    return reward_id

def on_product_purchased(profit, reward_id):
    # Track a reward for the amount of profit generated
    reward_tracker.addReward(profit, reward_id)

Now, you can use the on_product_selected and on_product_purchased functions to track product selection and purchase events in your application.

To wrap up, we have shown you how to use the Scorer class from the improveai module to update the ‘score’ column of a products table, sort the products descending by score, and track product selection and purchase events using the RewardTracker class. By implementing these functionalities, you can improve your product recommendation system by leveraging the Scorer and RewardTracker classes.

Getting Started

Improve AI is available for Python, Swift, and Java. See the Quick-Start Guide to learn more.

Thank you for your efforts to improve the world a little bit today.