Let’s talk about how to use MySQL to quickly implement a recommendation algorithm

This article brings you relevant knowledge about MySQL. It mainly introduces what a recommendation algorithm is, what problems this algorithm can help us solve, and how to use MySQL to implement a simple recommendation algorithm. Interested Let's take a look at it together, friends. I hope it will be helpful to everyone.

Using MySQL to implement a simple recommendation algorithm

The recommendation algorithm is a technology that is often encountered. Basically the problem solved is: if you like book A, then you'll probably like book B.

In this article, we use MySQL and disassemble and implement a simple recommendation algorithm based on data statistics.

First, create a data table of books that the user likes, which represents user_id likes book_id.

CREATE TABLE user_likes (
    user_id INT NOT NULL,
    book_id VARCHAR(10) NOT NULL,
    PRIMARY KEY (user_id,book_id),
    UNIQUE KEY book_id (book_id, user_id)
);
CREATE TABLE user_likes_similar (
    user_id INT NOT NULL,
    liked_user_id INT NOT NULL,
    rank INT NOT NULL,
    KEY book_id (user_id, liked_user_id)
);

Insert 4 pieces of test data

INSERT INTO user_likes VALUES (1, 'A'), (1, 'B'), (1, 'C');
INSERT INTO user_likes VALUES (2, 'A'), (2, 'B'), (2, 'C'), (2,'D');
INSERT INTO user_likes VALUES (3, 'X'), (3, 'Y'), (3, 'C'), (3,'Z');
INSERT INTO user_likes VALUES (4, 'W'), (4, 'Q'), (4, 'C'), (4,'Z');

means: user 1 likes A, B, C, user 2 likes A, B, C, D, user 3 likes X, Y ,C,Z, user 4 likes W,Q,C,Z.

Taking user 1 as an example to calculate recommended books, we need to calculate the similarity between user 1 and other users, and then sort according to the similarity.

Clear the similarity data table

DELETE FROM user_likes_similar WHERE user_id = 1;

Calculate the user similarity data table

INSERT INTO user_likes_similar
SELECT 1 AS user_id, similar.user_id AS liked_user_id, COUNT(*) AS rank
    FROM user_likes target
    JOIN user_likes similar ON target.book_id= similar.book_id AND target.user_id != similar.user_id
    WHERE target.user_id = 1
    GROUP BY similar.user_id ;

You can see that the found similarity result is

user_id, liked_user_id, rank
1, 2, 2
1, 3, 1
1, 4, 1

and then based on Sort by similarity, and take the top 10 books, which are the recommended books.

SELECT similar.book_id, SUM(user_likes_similar.rank) AS total_rank
    FROM user_likes_similar
    JOIN user_likes similar ON user_likes_similar.liked_user_id = similar.user_id
    LEFT JOIN user_likes target ON target.user_id = 1 AND target.book_id = similar.book_id
    WHERE user_likes_similar.user_id = 1 AND target.book_id IS NULL
    GROUP BY similar.book_id
    ORDER BY total_rank desc
    LIMIT 10;

[Recommended learning: mysql video tutorial]

The above is the detailed content of Let’s talk about how to use MySQL to quickly implement a recommendation algorithm. For more information, please follow other related articles on the PHP Chinese website!