时间戳

首页 > 解决方案 > 按相关性匹配的 SQL 排序结果并根据卖家排名显示价格

mysql - 按相关性匹配的 SQL 排序结果并根据卖家排名显示价格

问题描述

希望根据用户搜索的“相关性”以及排名最高的卖家的价格来显示结果。我所追求的一个活生生的例子是亚马逊的搜索结果，现在我知道他们的算法非常复杂，但我追求的是简化版本。

假设我们搜索“Jumper”，返回的结果是与“Jumper”相关的产品，但价格并不总是最便宜的，而是基于卖家排名。排名最高的卖家会显示他/她的价格。

这是我一直在做的工作，但没有给我上面提到的预期结果，老实说，我认为这不是很有效。

SELECT a.catalogue_id, a.productTitle, a.prod_rank, b.catalogue_id, b.display_price, b.sellers_rank
FROM 

(
    SELECT c.catalogue_id,
           c.productTitle,
           MATCH(c.productTitle) AGAINST ('+jumper*' IN BOOLEAN MODE) AS prod_rank 
    FROM catalogue AS c
    WHERE c.catalogue_id IN (1, 2, 3)
) a

JOIN 

(
    SELECT inventory.catalogue_id, 
           inventory.amount AS display_price,
           (accounts.comsn + inventory.quantity - inventory.amount) AS sellers_rank

    FROM inventory 
    JOIN accounts ON inventory.account_id = accounts.account_id 
    WHERE inventory.catalogue_id IN (1, 2, 3)
) AS b

ON a.catalogue_id = b.catalogue_id 

ORDER BY a.prod_rank DESC
LIMIT 100;

样品表：

Accounts:
----------------------------
account_id | comsn
----------------------------
1          | 100
2          | 9999




Catalogue:
----------------------------
catalogue_id | productTitle
----------------------------
1            | blue jumper
2            | red jumper
3            | green jumper




Inventory:
-----------------------------------------------
product_id | catalogue_id | account_id | quantity | amount |
-----------------------------------------------
1           | 2           | 1          | 6        | 699
2           | 2           | 2          | 2        | 2999

预期成绩：

Product Title:
red jumper

Amount:
29.99 (because he/she has sellers rank of: 7002)

标签： mysqlsql

解决方案

首先，您应该将结果仅限于第一个子查询的匹配项：

其次，您应该消除第二个子查询：

SELECT p.catalogue_id, p.productTitle, p.prod_rank,
       i.amount as display_price,
       (a.comsn + i.quantity - i.amount) 
FROM (SELECT c.catalogue_id, c.productTitle,
             MATCH(c.productTitle) AGAINST ('+jumper*' IN BOOLEAN MODE) AS prod_rank 
      FROM catalogue AS c
      WHERE c.catalogue_id IN (1, 2, 3)
      HAVING prod_rank > 0
     ) p JOIN 
     inventory i
     ON i.catalogue_id = c.catalogue_id join
     accounts a
     ON i.account_id = a.account_id 
ORDER BY c.prod_rank DESC
LIMIT 100;

我不确定你是否可以摆脱 final ORDER BY。 MATCHwithJOIN在这方面可能有点棘手。但只有按比赛排序应该会有所帮助。

推荐阅读