First, it??™d be helpful if the list of
products was sorted by how frequently the products were ordered along with our product. The
more times two products are ordered together, the better the cross-selling recommendation
will be. We??™ll show you how to deal with this in a second.
Second, the list shouldn??™t include the product for which we??™re calculating the recommendations;
it??™s already been ordered. This problem is simple to solve, by adding one more rule to
the WHERE clause:
SELECT od2.product_id
FROM order_detail od1
JOIN order_detail od2
ON od1.order_id = od2.order_id
WHERE od1.product_id = 4 AND od2.product_id != 4;
Not surprisingly, executing this query you get a list of products that is similar to the previous
one, except it doesn??™t contain the product with a product_id of 4 anymore:
product_id
----------
5
10
43
5
10
23
25
28
10
12
14
43
Now, the list of returned products is much shorter, but it contains multiple entries for
several products (products that were ordered in several orders that also contain the product
identifier 4). To get the most relevant recommendations, we need to find out which products
appear more frequently in this list. We do this by grouping the results of the previous query by
product_id and sorting in descending order by how many times each product appears in the
list (this number is given by the rank calculated column in the following code snippet):
SELECT od2.
Pages:
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584