mysql - 对 MySQL 结果的子集进行排名

Question

我在 MySQL 数据库中有几十万个产品。每个产品映射到一个子类别，每个子类别映射到一个类别，每个类别映射到一个部门。

我需要分别计算给定产品在其子类别/类别/部门中的销售排名，并获取排名在其正上方的两个产品和排名在其正下方的两个产品。这是一个 SQL Fiddle，用于获取子类别中所有产品的排名:

http://sqlfiddle.com/#!2/d4aad/6

如何重构查询以仅返回具有给定 product_id 以及上下两行的产品？例如，对于 product_id 8，我只想返回这些行:

╔══════╦════════════════╦═════════════╦════════════╗║ RANK ║  DESCRIPTION   ║ TOTAL_SALES ║ PRODUCT_ID ║╠══════╬════════════════╬═════════════╬════════════╣║    2 ║ Digital SLR 2  ║        8000 ║          2 ║║    3 ║ Digital SLR 7  ║        5998 ║          7 ║║    4 ║ Digital SLR 8  ║        5997 ║          8 ║║    5 ║ Digital SLR 1  ║        3297 ║          1 ║║    6 ║ Digital SLR 4  ║        3200 ║          4 ║╚══════╩════════════════╩═════════════╩════════════╝

对于product_id 3，返回的数据如下:

╔══════╦════════════════╦═════════════╦════════════╗║ RANK ║  DESCRIPTION   ║ TOTAL_SALES ║ PRODUCT_ID ║╠══════╬════════════════╬═════════════╬════════════╣║    6 ║ Digital SLR 4  ║        3200 ║          4 ║║    7 ║ Digital SLR 6  ║        2599 ║          6 ║║    8 ║ Digital SLR 3  ║        2468 ║          3 ║║    9 ║ Digital SLR 10 ║        1000 ║         10 ║║   10 ║ Digital SLR 5  ║        1000 ║          5 ║╚══════╩════════════════╩═════════════╩════════════╝

所以 product_id 对于查询是已知的，但是在执行查询之前排名是未知的。

Answer 1

这是一种只使用 MySQL 变量的方法，因此您不必计算两次排名。

select t.*
from (select t.*,
             @therank := if(product_id = 8, rank, @therank) as therank
      from (SELECT @rn := @rn + 1 AS rank,
                   description, total_sales, product_id
            FROM (SELECT ol.product_id, p.description,
                         sum(ol.item_sell_price) AS total_sales
                  FROM products p INNER JOIN
                       order_lines ol
                       ON p.id = ol.product_id
                  WHERE p.subcategory_id = 1
                  GROUP BY ol.product_id
                  ORDER BY total_sales DESC
                 ) t1 cross join
                 (SELECT @rn := 0) const
           ) t cross join
           (select @therank := 0) const
     ) t
where @therank between rank - 2 and rank + 2
order by rank

内部子查询，使用@rn计算排名。下一级遍历数据并将 @therank 设置为给定产品的排名。最后，这在最外层用作 between 语句。

MySQL 确实实现了子查询。在这种情况下，这可能比重新计算排名要好得多。