如何用SQL窗口函数高效计算逐行动态经验排名？

你的需求是计算每行在所有n≤当前行n的记录中，x的经验排名（即统计截至当前n时，x小于等于当前行x的记录总数），完全可以通过窗口函数实现，效率远高于原有的自连接查询。

核心解决方案 #

支持窗口FILTER的数据库（如PostgreSQL） #

直接用带过滤条件的窗口计数：

SELECT
    n,
    x,
    COUNT(*) OVER (
        ORDER BY n ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        FILTER (WHERE x <= CURRENT_ROW.x)
    ) AS cumulative_rank
FROM data
ORDER BY n;

通用兼容写法（如MySQL 8.0+、SQL Server） #

如果数据库不支持窗口内的FILTER，用CASE表达式替代：

SELECT
    n,
    x,
    SUM(CASE WHEN x <= current_x THEN 1 ELSE 0 END) OVER (
        ORDER BY n ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_rank
FROM (
    SELECT n, x, x AS current_x FROM data
) t
ORDER BY n;

为什么这个方法高效 #

原自连接查询的逻辑是对每一行都扫描所有n≤当前行的记录，时间复杂度为O(n²)，数据量增大时性能会急剧下降。

而窗口函数的实现是：

1. 先按n对全表做一次排序（时间复杂度O(n log n)）
2. 按顺序遍历数据，在滑动窗口（从开头到当前行）内实时统计符合条件的记录数
   整个过程只需要扫描表1-2次，和你用Python SortedList流式处理的效率量级接近。

示例验证 #

假设data表数据如下：

执行上述SQL后会得到期望结果：

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