首页 > 数据库 >SQL中如何用JOIN和窗口函数实现复杂排名统计？

SQL中如何用JOIN和窗口函数实现复杂排名统计？

来源：互联网 2026-07-21 08:32:13

SQL窗口函数中RANK、DENSE_RANK、ROW_NUMBER的区别在于是否跳号；JOIN与窗口函数配合时推荐将窗口计算封装进子查询或CTE；ROWSBETWEEN用于指定滚动计算范围；MySQL和PostgreSQL在RANGE与时间类型组合、GROUPBY后使用窗口函数等方面存在差异，迁移时需注意。

# SQL窗口函数实战：从排名到统计的全场景解析做数据分析的朋友一定遇到过这样的情况：明明用`RANK()`给销售数据排名，结果出来却是1,1,3，中间跳过了2号位。这到底是怎么回事？其实，这三个排名函数各有各的脾气——`RANK()`遇到并列值会跳号，`DENSE_RANK()`并列不跳号，而`ROW_NUMBER()`则严格按顺序编号，不管值是否相等。

## 排名函数的选择，本质是业务逻辑的选择 `RANK()`会跳号，原因很简单——相同值获得相同排名后，后续序号会跳过被占用的位置。比如两个第1名后，下一个直接是第3名。如果业务要求「并列不跳号」——即1,1,2,3,3,3,4这种连续稠密排名，那得换`DENSE_RANK()`。如果只是想给每行分配一个唯一的序列号，不关心值是否重复，那就是`ROW_NUMBER()`的用武之地。实际选哪个，取决于业务场景：销售榜单通常用`DENSE_RANK()`（并列第1，下一位是第2）；考试成绩单可能用`RANK()`（强调名次含金量）；而生成唯一行号必须用`ROW_NUMBER()`。这里有个最常见的踩坑点：忘了写`ORDER BY`子句。`RANK()`等窗口函数必须搭配`OVER(ORDER BY ...)`才能工作，否则会直接报错`window function requires an ORDER BY clause`。这个错误在初学者中间出现的频率，比你想象的高得多。 ## JOIN和窗口函数如何配合使用？这两个功能完全可以一起用，而且非常实用。比如「查每个部门销售额前3的员工，并附带部门平均业绩」这种需求，就是典型的组合场景。但需要注意执行顺序：窗口函数在`SELECT`阶段执行，而`JOIN`在之前完成。如果先`JOIN`再开窗，数据量可能暴增；如果先开窗再`JOIN`，又可能丢失关联信息。推荐的做法是把窗口计算封装进子查询或CTE： ```sql WITH ranked_sales AS ( SELECT emp_id, dept_id, amount, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY amount DESC) AS rn FROM sales ) SELECT r.*, d.dept_name FROM ranked_sales r JOIN dept d ON r.dept_id = d.dept_id WHERE r.rn <= 3; ``` 几个关键点需要注意： - `PARTITION BY dept_id`必须明确指定，否则就是全表排序，而不是「每部门内排名」 - 别在`WHERE`里直接过滤窗口函数结果（如`WHERE ROW_NUMBER() > 1`），这样会报错，必须用子查询或CTE包一层 - `JOIN`条件字段最好有索引，特别是`dept_id`，否则`PARTITION BY`后的大量分组操作，性能会急剧下降 ## `ROWS BETWEEN`的妙用：不止是累计统计默认的`OVER(ORDER BY x)`是从第一行到当前行，等价于`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`。但要算「最近3天滚动销量」或「前后2行均值」，就必须显式写范围。比如滚动3行平均： ```sql A VG(amount) OVER ( PARTITION BY dept_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) ``` 这里有两个容易混淆的概念： - `ROWS`按物理行数计算，`RANGE`按排序值的逻辑区间计算（比如`RANGE BETWEEN INTERVAL '1 DAY' PRECEDING AND CURRENT ROW`） - 如果`sale_date`有重复值，`RANGE`可能吞掉多行，而`ROWS`更可控 - `UNBOUNDED FOLLOWING`很少用，但配合`UNBOUNDED PRECEDING`可实现全分区累计，比如`SUM(amount) OVER (PARTITION BY dept_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)` ## MySQL 8.0和PostgreSQL的差异，迁移时要注意基本语法看似一致，但两个细节常导致迁移出错： - MySQL不支持`RANGE`与时间类型的组合（比如`RANGE BETWEEN INTERVAL '7 DAY' PRECEDING AND CURRENT ROW`），PostgreSQL支持。MySQL得改用`ROWS` + 自关联或变量模拟 - PostgreSQL允许在`GROUP BY`后直接用窗口函数，但MySQL要求所有非聚合字段必须出现在`GROUP BY`或被窗口函数包裹，否则会报错`Expression #1 of SELECT list is not in GROUP BY clause` - MySQL 8.0.2+才支持完整窗口函数，低版本只能用变量或自连接硬撸，性能差且易错跨数据库写法最稳妥的策略是：只用`ROWS`、避开`RANGE`、所有`SELECT`非聚合字段都显式`PARTITION BY`或`GROUP BY`，别依赖隐式行为。这样不管在哪个数据库上跑，都能拿到一致的结果。