For example, as far as
the previous query is concerned, you can see there are five product IDs for which the rank is 1 (12, 14, 23,
25, 28). When we executed the query on our test database, we got 23 and 25, but your database may ???prefer???
other products. To obtain the product recommendations, the only criteria we??™re interested in is the rank that
we calculate; when we have many products with the same rank, it??™s OK to let the database choose for us.
Because this list of numbers doesn??™t make much sense to the human eye, you??™ll also want
to know the name and the description of the recommended products. The following query
extracts the names by joining the list of recommended product IDs with the product table:
SELECT od2.product_id, od2.product_name
FROM order_detail od1
JOIN order_detail od2 ON od1.order_id = od2.order_id
JOIN product p ON od2.product_id = p.product_id
WHERE od1.product_id = 4 AND od2.product_id != 4
GROUP BY od2.product_id
ORDER BY COUNT(od2.product_id) DESC
LIMIT 5;
Based on the data from the previous fictional results, this query returns something like
this:
product_id name
---------- -----------------------
10 Haute Couture
5 Marianne
43 Equatorial Rhino
23 Italian Airmail
25 Romulus & Remus
Alternatively, you might want to calculate the product recommendations only using data
from the orders placed in the last n days.
Pages:
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586