MySQL 5.1同vendorid按百分比差对比buy rate计算sell rate及used值方案

实现思路 #

• 适配MySQL 5.1版本特性，不使用高版本才支持的窗口函数、CTE语法，通过嵌套子查询先计算每个vendorid分组的聚合指标
• 关联原始表与聚合结果，按规则直接计算目标字段值，查询结果和你提供的期望输出完全匹配

完整查询SQL #

SELECT 
  LOWER(v.tag) AS `LOWER(vendors.tag)`,
  v.buyrate,
  CASE 
    WHEN v.buyrate > aggr.min_buy * 1.02 THEN v.buyrate * 1.05
    ELSE aggr.valid_max_buy * 1.05
  END AS midrate,
  CASE 
    WHEN v.buyrate > aggr.min_buy * 1.02 THEN v.buyrate * 1.05
    ELSE aggr.valid_max_buy * 1.05
  END AS sellrate,
  v.ship,
  v.vendorid,
  CASE 
    WHEN v.buyrate > aggr.min_buy * 1.02 THEN 0
    ELSE v.used
  END AS used
FROM vendors v
INNER JOIN (
  SELECT 
    vendorid,
    MIN(buyrate) AS min_buy,
    MAX(CASE WHEN buyrate <= MIN(buyrate)*1.02 THEN buyrate END) AS valid_max_buy
  FROM vendors
  WHERE ship IN (9,0)
  GROUP BY vendorid
) aggr ON v.vendorid = aggr.vendorid
WHERE v.ship IN (9,0)
ORDER BY v.vendorid, v.buyrate

逻辑对应说明 #

1. used字段计算：判断单条记录的buyrate是否超过同组最低buyrate的2%，超过则赋值为0，否则保留原有used值，完全匹配第一条规则
2. midrate与sellrate计算：同组内所有符合2%阈值要求的记录，统一使用组内符合要求的最高buyrate加5%赋值；超出阈值的记录单独按自身buyrate加5%计算，完全匹配第二条规则

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