怎样在SQL中使用JOIN实现排名的滑动窗口计算-SQL-PHP中文网

sql中无内置滑动窗口排名函数，需用自连接模拟：对每条记录t1，join出同用户且时间邻近的t2，count比t1金额大的t2数量+1得排名，性能为o(n²)，须建联合索引优化。

怎样在sql中使用join实现排名的滑动窗口计算

SQL中没有内置滑动窗口排名函数，得靠JOIN模拟

标准SQL（包括MySQL 5.7、PostgreSQL 9.x、SQL Server 2012之前）不支持ROW_NUMBER() OVER (ORDER BY ... ROWS BETWEEN ...)这类窗口帧语法来直接做“按时间/序号滑动的排名”。想实现“每个用户最近3条订单中按金额排第几”，不能靠单个ROW_NUMBER()，必须用自连接（JOIN）构造邻域关系。

用INNER JOIN + 条件限制构造滑动范围

核心思路是：对每一条记录 t1，JOIN出所有满足“在它之前且不超过N条”的记录 t2，再用COUNT(*)统计比它大的数量，从而得出名次。比如按created_at倒序取最近3条中的排名：

SELECT t1.user_id, t1.amount,
       COUNT(t2.amount) + 1 AS rank_in_last_3
FROM orders t1
INNER JOIN orders t2
  ON t1.user_id = t2.user_id
 AND t2.created_at  (
   SELECT COALESCE(MAX(t3.created_at), '1970-01-01')
   FROM orders t3
   WHERE t3.user_id = t1.user_id
     AND t3.created_at <p>这个写法本质是手动算“t1之前的第2个时间点”，再让t2落在(t1前第2个时间, t1]区间内。实际中更常用的是基于序号（如<code>id</code>或<code>row_num</code>）的简化版：</p>

先用ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)生成临时序号
再用JOIN关联序号差≤2的行：ON t1.rn >= t2.rn AND t2.rn > t1.rn - 3
注意GROUP BY必须包含所有非聚合字段，否则MySQL会报错

性能差是必然的，别在大表上直接跑

JOIN模拟滑动窗口是O(n²)复杂度，10万行订单表可能产生百亿级中间连接结果。常见优化路径：

务必给(user_id, created_at)建联合索引，否则JOIN ON和子查询都全表扫
避免在JOIN条件里用函数，比如DATE(created_at)会让索引失效
如果只要Top N（比如“最近3条里金额最高的”），用LIMIT 3子查询比JOIN更快
PostgreSQL 14+ 或 MySQL 8.0 可直接用ROW_NUMBER() OVER (ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)，不用JOIN

不同数据库对NULL和并列排名的处理差异大

用JOIN数“比当前值大的记录数”得到的是RANK()语义（并列不跳号），但如果你要DENSE_RANK()或ROW_NUMBER()，就得额外去重或加随机因子。例如：

MySQL中ORDER BY id, RAND()可打破并列，但不可复现
PostgreSQL中可用ROW_NUMBER() OVER (ORDER BY amount DESC, id)保证唯一排序键
当amount为NULL时，t2.amount > t1.amount整个条件为UNKNOWN，不会被计入COUNT——这意味着NULL默认排最后，且不参与排名计数，容易漏掉

滑动窗口排名不是语法糖，是数据局部性与关系代数表达力之间的硬碰硬。能用原生窗口函数就别JOIN，要用JOIN就先压测小样本，再看执行计划里的rows_examined。