Розробка простійшої системи рекомендацій товарів за допомогою SQL

У сучасну цифрову еру користувачі щодня стикаються з величезною кількістю контенту та продуктів, що часто призводить до перевантаження інформацією.

Щоб допомогти користувачам знаходити найбільш відповідні та підходящі для них товари, багато компаній в різних галузях, таких як Netflix, Amazon та інші платформи електронної комерції, покладаються на системи рекомендацій.

Що таке системи рекомендацій?

pic

Системи рекомендацій — це розумні інструменти, створені для того, щоб допомогти користувачам знаходити продукти чи контент, найбільш відповідні їхнім інтересам. Щоб робити персоналізовані рекомендації, ці системи аналізують переваги користувачів, їхню поведінку та інколи переваги схожих користувачів.

Для створення систем рекомендацій можна використовувати різноманітні методи. Ось декілька прикладів:

  1. Фільтрація на основі популярності: Рекомендує найбільш популярні товари.
  2. Колаборативна фільтрація: Пропонує товари на основі переваг інших користувачів з подібною поведінкою.
  3. Фільтрація за категорією: Рекомендує товари на основі найулюбленішої категорії користувачів.
  4. Контентна фільтрація: Пропонує товари зі схожими атрибутами до тих, з якими користувач раніше взаємодіяв.
  5. Гібридні методи: Поєднує кілька технік.

Для створення більш складних систем рекомендацій часто потрібні моделі машинного навчання. Однак у цій статті ми розглянемо, як простий інструмент, як SQL, може бути використаний для побудови кількох базових систем рекомендацій продуктів.

Схема бази даних

Перед тим як почати, нам потрібен вигаданий набір даних про клієнтів, продукти та їх взаємодію з товарами.
Це будуть дані, які необхідні будь-якій системі рекомендацій для подальшого аналізу.

Нижче наведено Python код, який використовувався для створення вигаданого набору даних:

(Для створення списку товарів був використаний ChatGPT, щоб випадковим чином додати елементи до коду).

import random  
import pandas as pd  
from faker import Faker  

fake = Faker()  

# Генерація даних профілю користувача  
users = [{"UserID": i, "Name": fake.first_name(), "Email": fake.email(), "Age": random.randint(18, 50)} for i in range(1, 21)]  

# Генерація даних товарів  
items = [  
 {"ItemID": i,   
 "Title": title,   
 "Description": description,   
 "Category": category}  
 for i, (title, description, category) in enumerate([  
 ("Wireless Headphones", "Noise-cancelling over-ear headphones", "Electronics"),  
 ("Running Shoes", "Lightweight and breathable running shoes", "Sportswear"),  
 ("Electric Kettle", "Fast-boiling stainless steel electric kettle", "Kitchen Appliances"),  
 ("Smartwatch", "Fitness tracker with heart rate monitoring", "Electronics"),  
 ("Yoga Mat", "Non-slip, eco-friendly yoga mat", "Fitness"),  
 ("Mystery Novel", "A gripping mystery novel with a twist ending", "Books"),  
 ("Gaming Mouse", "Ergonomic mouse with adjustable DPI settings", "Electronics"),  
 ("Office Chair", "Adjustable ergonomic office chair", "Furniture"),  
 ("Blender", "High-speed blender with multiple settings", "Kitchen Appliances"),  
 ("DSLR Camera", "Professional DSLR camera with 24MP sensor", "Electronics"),  
 ("Hiking Backpack", "Waterproof backpack with multiple compartments", "Outdoor Gear"),  
 ("Coffee Table", "Wooden coffee table with a modern design", "Furniture"),  
 ("Cookware Set", "Non-stick cookware set with 5 pieces", "Kitchen Appliances"),  
 ("Water Bottle", "Insulated stainless steel water bottle", "Fitness"),  
 ("Sci-Fi Novel", "A thrilling sci-fi adventure set in space", "Books")  
 ], start=1)  
]  

# Перетворення в DataFrame і збереження в CSV  
item_df = pd.DataFrame(items)  
item_df.to_csv("Item.csv", index=False)  

# Генерація даних взаємодії  
actions = ["click", "add_to_cart", "purchase"]  
interactions = [{"InteractionID": i,   
 "UserID": random.randint(1, 20),   
 "ItemID": random.randint(1, 15),   
 "Action": random.choice(actions),   
 "Timestamp": fake.date_time_this_year()} for i in range(1, 200)]  

# Збереження в CSV  
pd.DataFrame(users).to_csv("UserProfile.csv", index=False)  
pd.DataFrame(items).to_csv("Item.csv", index=False)  
pd.DataFrame(interactions).to_csv("Interaction.csv", index=False)

Будуть використовуватися 3 таблиці: UserProfile, Item, Interaction для демонстрації системи.

Спочатку подивимося, як виглядають дані в кожній таблиці, використовуючи ці 3 запити.

SELECT * FROM UserProfile LIMIT 5;  
SELECT * FROM Item LIMIT 5;  
SELECT * FROM Interaction LIMIT 5;

Нижче наведені результати для кожної таблиці.

pic

Таблиця UserProfile

pic

Таблиця Item

pic

Таблиця Interaction

  • Таблиця UserProfile містить деталі кожного користувача.
  • Таблиця Item містить деталі товарів.
  • Таблиця Interaction записує взаємодії користувачів з товарами.

ER-діаграма

ER-діаграму можна побудувати наступним чином.

pic

Фільтрація на основі популярності

Щоб побачити найбільш популярні товари з найбільшою кількістю взаємодій, можна використати наступний запит.

--- Рекомендуємо товари з найбільшою кількістю взаємодій від користувачів.  
SELECT   
 t1.ItemID,  
 t2.Title,  
 COUNT(*) AS Interaction_Count  
FROM   
 Interaction AS t1  
JOIN   
 Item AS t2 ON t1.ItemID = t2.ItemID  
GROUP BY   
 t1.ItemID, t2.Title  
ORDER BY   
 COUNT(*) DESC  
LIMIT 5;

Ось що робить цей запит:

  1. Об'єднує таблицю взаємодій з таблицею товарів на основі ItemID.
    2.
    Переклад:

Підрахунок кількості взаємодій для кожного елемента

  1. Відсортуйте за загальною кількістю взаємодій.
  2. Виведіть 5 найпопулярніших елементів з найбільшою кількістю взаємодій.

pic

Вихідний результат запиту вказує на те, що ці 5 елементів є найпопулярнішими за кількістю взаємодій.

Припустимо, що рекомендація має базуватися на популярності покупок, запит можна адаптувати так:

--- Рекомендація найпопулярніших товарів  
SELECT   
 t1.ItemID,  
 t2.Title,  
 COUNT(t1.Action) AS Purchase_frequency  
FROM   
 Interaction AS t1  
JOIN   
 Item AS t2 ON t1.ItemID = t2.ItemID  
WHERE  
 t1.Action = 'purchase'  
GROUP BY   
 t1.ItemID, t2.Title  
ORDER BY   
 COUNT(t1.Action) DESC  
LIMIT 5;

pic

Вихідний результат запиту вказує на те, що ці 5 елементів мають найвищу частоту покупок.

Колаборативне фільтрування

Припустимо, що ми хочемо порекомендувати елемент, з яким взаємодіяли схожі користувачі з цільовим користувачем. Ми можемо використати наступний запит.

--- Рекомендація елементів, з якими взаємодіяли схожі користувачі.  
SELECT   
 t1.ItemID,  
 t2.Title,  
 COUNT(*) AS InteractionCount  
FROM   
 Interaction AS t1  
JOIN   
 Item AS t2 ON t1.ItemID = t2.ItemID  
WHERE   
 t1.UserID IN (  
 SELECT DISTINCT UserID   
 FROM Interaction   
 WHERE ItemID IN (  
 SELECT ItemID   
 FROM Interaction   
 WHERE UserID = 1  
 )  
 AND UserID != 1  
 )  
 AND t1.ItemID NOT IN (  
 SELECT ItemID   
 FROM Interaction   
 WHERE UserID = 1  
 )  
GROUP BY   
 t1.ItemID, t2.Title  
ORDER BY   
 InteractionCount DESC  
LIMIT 5;
  1. Визначає схожих користувачів та елементи, з якими ці користувачі взаємодіяли
  2. SELECT DISTINCT UserID FROM Interaction
    Цей підзапит вибирає унікальні UserID з таблиці Interaction.
  3. WHERE ItemID IN (SELECT ItemID FROM Interaction WHERE UserID = 1) Ця частина гарантує, що запит вибере тільки тих користувачів, які взаємодіяли з будь-яким елементом, з яким взаємодіяв цільовий користувач.
  4. AND UserID != 1
    Ця частина виключає цільового користувача зі списку схожих користувачів
  5. AND t1.ItemID NOT IN (SELECT ItemID FROM Interaction WHERE UserID = 1) Ця частина виключає елементи, з якими вже взаємодіяв цільовий користувач.
  6. Сортує елементи за популярністю серед схожих користувачів

pic

Вихідний результат запиту вказує на те, що серед продуктів, з якими взаємодіяли схожі користувачі, ці 3 елементи є найпопулярнішими.

Фільтрування за категорією

Припустимо, ми хочемо порекомендувати елемент, що належить до схожої категорії з тим, з яким цільовий користувач взаємодіяв найбільше. Можна використати наступний запит.

--- Рекомендація на основі улюбленої категорії цільового користувача  
SELECT   
 t2.ItemID,  
 t2.Title,  
 COUNT(*) AS InteractionCount,  
 t2.Category  
FROM   
 Interaction AS t1  
JOIN   
 Item AS t2 ON t1.ItemID = t2.ItemID  
WHERE   
 t2.Category = (  
 SELECT   
 t2.Category  
 FROM   
 Interaction AS t1  
 JOIN   
 Item AS t2 ON t1.ItemID = t2.ItemID  
 WHERE   
 t1.UserID = 4 --- цільовий користувач  
 GROUP BY   
 t2.Category  
 ORDER BY   
 COUNT(*) DESC  
 LIMIT 1  
 )  
GROUP BY   
 t2.ItemID, t2.Title  
ORDER BY   
 InteractionCount DESC  
LIMIT 5;
  1. Визначає категорію, з якою цільовий користувач (UserID = 4) взаємодіяв найбільше
  2. Вибирає елементи з цієї категорії та сортує їх за популярністю серед користувачів

pic

Вихідний результат запиту вказує на те, що серед продуктів у найбільш улюбленій категорії (Електроніка) цільового користувача, ці 4 елементи є найпопулярнішими.

Фільтрування на основі контенту

  • Ця техніка часто вимагає наявності великої кількості різноманітних даних.
  • Оскільки вона рекомендує користувачам продукт, що має подібні атрибути до тих, з якими вони взаємодіяли раніше, необхідно мати детальні та різноманітні атрибути кожного елемента.
  • Дані можуть бути покращені шляхом розширення атрибутів продуктів (наприклад...
    Переклад:
додавання більш детальних ключових слів у опис таблиці Item та подальший аналіз цього)

Висновок

Ця стаття демонструє, як SQL може бути використаний для створення простих систем рекомендацій, включаючи на основі популярності, колаборативне фільтрування, фільтрування за категорією. Більш складні техніки можуть бути реалізовані за допомогою моделей машинного навчання.

Ця стаття, SQL-коди та Python-коди були написані за допомогою ChatGPT та Gemini.

Перекладено з: Design a simple Product Recommendation System Using SQL

Вам також може сподобатися

Посібник по проходженню завдання Try Hack Me Whiterose— Опис рішення
Звіт: Підтримка Джордано Елією безкоштовних програм розвитку кар’єри: сміливий крок для засновників та CEO?
Розуміння закриттів JavaScript: Посібник для початківців