如何在SQL中通过JOIN和Window Function实现复杂排名统计

RANK()会跳号是因为相同值获得相同排名后，后续序号跳过被占用位置（如1,1,3）；DENSE_RANK()并列不跳号（1,1,2），ROW_NUMBER()则严格连续编号（1,2,3）。

为什么直接用 RANK() 会漏掉并列后的跳号？

因为 RANK() 按值分组后给相同值分配相同序号，但会跳过后续编号（比如两个第1名后直接是第3名）。如果你要「并列不跳号」——也就是「1,1,2,3,3,3,4」这种连续稠密排名，得换 DENSE_RANK()；如果要「严格按顺序不分组」——即「1,2,3,4,5,6,7」，就得用 ROW_NUMBER()。

实际选哪个，取决于业务定义：销售榜单通常用 DENSE_RANK()（并列第1，下一位是第2）；考试成绩单可能用 RANK()（强调名次含金量）；而生成唯一行号必须用 ROW_NUMBER()。

常见错误是没写 ORDER BY 子句——RANK() 等窗口函数必须搭配 OVER(ORDER BY ...)，否则报错 window function requires an ORDER BY clause。

JOIN 和 Window Function 能不能一起用？怎么避免笛卡尔积？

能，而且很常用，比如「查每个部门销售额前3的员工，并附带部门平均业绩」。但容易踩坑的是：窗口函数在 SELECT 阶段执行，而 JOIN 在之前完成。如果先 JOIN 再开窗，数据量可能暴增；如果先开窗再 JOIN，又可能丢失关联信息。

推荐做法是把窗口计算封装进子查询或 CTE：

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_nameFROM ranked_sales rJOIN dept d ON r.dept_id = d.dept_idWHERE r.rn <= 3;

关键点：

• PARTITION BY dept_id 必须明确，否则全表排序，不是「每部门内排名」
• 别在 WHERE 里直接过滤窗口函数结果（如 WHERE ROW_NUMBER() > 1），会报错，必须用子查询或 CTE 包一层
• JOIN 条件字段最好有索引，尤其 dept_id，否则 PARTITION BY 后的大量分组性能会骤降

OVER() 里的 ROWS BETWEEN 怎么影响累计统计？

默认的 OVER(ORDER BY x) 是从第一行到当前行（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），但你要算「最近3天滚动销量」或「前后2行均值」，就必须显式写范围。

例如滚动 3 行平均：

AVG(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，别依赖隐式行为。