如何为MySQL表中已有记录添加按价格排序的SERP位置值

你使用的MySQL 8原生支持窗口函数，不需要自定义变量就能直接实现按分组生成递增序号的需求，性能和稳定性都更好。

实现逻辑 #

• 分组规则：以DATE（日期）+SKU（产品编号）作为唯一分组维度，每个分组内单独生成排序序号
• 排序规则：分组内按PRICE升序排序，价格越低SERP值越小
• 同价补充规则：如果出现价格相同的情况，可额外按商户ID、店铺ID排序，避免排序不稳定导致序号随机生成，你也可以根据自己的业务需求调整排序字段。

全量更新SQL #

UPDATE shops_prices sp
INNER JOIN (
    SELECT 
        DATE,
        SKU,
        MERCHANT_ID,
        SHOP_ID,
        ROW_NUMBER() OVER (
            PARTITION BY DATE, SKU 
            ORDER BY PRICE ASC, MERCHANT_ID ASC, SHOP_ID ASC
        ) AS new_serp
    FROM shops_prices
) AS serp_calc
ON sp.DATE = serp_calc.DATE 
AND sp.SKU = serp_calc.SKU 
AND sp.MERCHANT_ID = serp_calc.MERCHANT_ID 
AND sp.SHOP_ID = serp_calc.SHOP_ID
SET sp.SERP = serp_calc.new_serp;

注意事项 #

• 执行前务必先备份全表数据，避免误操作导致数据损失
• 如果表数据量超过百万级，建议按日期范围分批更新，避免长时间锁表影响业务，示例分批SQL：

  -- 示例：仅更新2021年9月的存量数据
  UPDATE shops_prices sp
  INNER JOIN (
      SELECT 
          DATE,
          SKU,
          MERCHANT_ID,
          SHOP_ID,
          ROW_NUMBER() OVER (
              PARTITION BY DATE, SKU 
              ORDER BY PRICE ASC, MERCHANT_ID ASC, SHOP_ID ASC
          ) AS new_serp
      FROM shops_prices
      WHERE DATE BETWEEN '2021-09-01' AND '2021-09-30'
  ) AS serp_calc
  ON sp.DATE = serp_calc.DATE 
  AND sp.SKU = serp_calc.SKU 
  AND sp.MERCHANT_ID = serp_calc.MERCHANT_ID 
  AND sp.SHOP_ID = serp_calc.SHOP_ID
  SET sp.SERP = serp_calc.new_serp;
  

• 如果业务要求价格相同的记录拿到相同的SERP值，把ROW_NUMBER()替换为RANK()或者DENSE_RANK()即可。

内容的提问来源于stack exchange，提问作者merlin