如何利用SQL窗口函数实现电商系统的动态打分排序

order by 无法满足电商动态打分需求，因其仅支持静态排序，而电商需根据新品加权、转化率扶持、用户行为提权等浮动规则实时计算行级得分，必须依赖窗口函数实现动态聚合与归一化加权。

如何利用sql窗口函数实现电商系统的动态打分排序_综合多字段计算

为什么直接用 ORDER BY 无法满足电商动态打分需求

因为 ORDER BY 只能做静态排序，而电商场景中“好商品”的定义是浮动的：新上架商品要加权、高转化率但低销量的要扶持、复购用户浏览过的要提权。这些规则无法靠单次排序固化，必须在行级实时计算得分后再排序——窗口函数的核心价值就在这里：它允许你在保留每行原始数据的同时，基于分区和排序上下文动态聚合。

用 ROW_NUMBER() + 权重系数实现基础动态排名

常见错误是把所有权重硬编码进 ORDER BY 表达式，导致无法灵活调整。正确做法是先用窗口函数算出各维度分值，再加权合成总分：

SELECT
  item_id,
  sales_cnt,
  cvr_rate,
  days_since_launch,
  -- 各维度归一化（避免量纲干扰）
  ROUND(100 * (sales_cnt / MAX(sales_cnt) OVER()), 2) AS sales_score,
  ROUND(100 * (cvr_rate / NULLIF(MAX(cvr_rate) OVER(), 0)), 2) AS cvr_score,
  -- 新品加权：上架7天内额外+15分
  CASE WHEN days_since_launch

MAX() OVER() 是关键：它不改变行数，只为每行提供全局参考值用于归一化
务必用 NULLIF(..., 0) 防止 cvr_rate 全为 0 时除零错误
权重系数（0.4/0.5/0.1）应从配置表读取，而非写死在 SQL 中

RANK() 和 DENSE_RANK() 在并列处理上的实际差异

电商排序常要求“同分同名次”，但后续名次是否跳空会影响运营策略。比如两个商品都得 92.5 分：

RANK() 返回 1, 1, 3 —— 第三名跳过 2，适合强调“梯队感”
DENSE_RANK() 返回 1, 1, 2 —— 紧凑连续，适合前端分页展示（第 2 页永远从第 11 名开始）
注意：ROW_NUMBER() 必然产生唯一序号，即使分数相同也会强拆，慎用于打分排序

真实场景建议优先用 DENSE_RANK() OVER (ORDER BY final_score DESC)，避免因浮点精度导致的意外并列断裂。

性能陷阱：PARTITION BY 不当会拖垮查询

误以为“按品类分组打分”就必须加 PARTITION BY category_id，结果发现响应时间从 200ms 涨到 8s。问题在于：

若品类数极多（如 10w+ 细分标签），每个分区都要单独计算 MAX()，IO 和内存开销剧增
真正需要分区的场景其实有限：比如“同类目内比价”才需 PARTITION BY category_id；而“全站热榜”必须去掉分区，用全局 MAX() OVER()
替代方案：对高频查询品类预计算归一化系数，存入 category_score_ref 表，JOIN 替代窗口函数

窗口函数不是银弹，当 OVER() 子句里出现高基数字段或复杂表达式时，先看执行计划里的 WindowAgg 节点是否成为瓶颈。