SQL窗口函数怎么用

SQL 窗口函数（Window Functions） 用于在“一组相关行”上进行计算，同时不把行合并成一行（和普通聚合函数不同）。常见用途：排名、累计求和、移动平均、前后行比较等。

一、基本语法

函数名(列)
OVER (
  [PARTITION BY 分组列]
  [ORDER BY 排序列]
  [窗口范围]
)

关键点：

• OVER()：必须有，表示“窗口”
• PARTITION BY：类似 GROUP BY，但不合并行
• ORDER BY：决定计算顺序
• 窗口范围（可选）：如 ROWS BETWEEN 1 PRECEDING AND CURRENT ROW

二、常见窗口函数分类

1️⃣ 排名函数

ROW_NUMBER() OVER (ORDER BY score DESC)        -- 1,2,3,4
RANK() OVER (ORDER BY score DESC)              -- 1,1,3
DENSE_RANK() OVER (ORDER BY score DESC)        -- 1,1,2

示例：

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rk
FROM students;

2️⃣ 聚合 + 窗口

SUM(salary) OVER (PARTITION BY dept)           -- 部门工资总和
AVG(salary) OVER (PARTITION BY dept)           -- 部门平均工资
COUNT(*) OVER (PARTITION BY dept)

示例：

SELECT
  name,
  dept,
  salary,
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

3️⃣ 累计 & 移动计算

SUM(amount) OVER (
  PARTITION BY user_id
  ORDER BY date
)  -- 累计求和

移动平均：

AVG(price) OVER (
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

4️⃣ 获取前后行数据

LAG(salary) OVER (ORDER BY hire_date)   -- 上一行
LEAD(salary) OVER (ORDER BY hire_date)  -- 下一行

示例：

SELECT
  name,
  salary,
  LAG(salary) OVER (ORDER BY salary) AS prev_salary
FROM employees;

三、完整示例

SELECT
  dept,
  name,
  salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total
FROM employees;

结果特点：

• 每一行都保留
• 同时看到“个人”和“部门汇总”

四、窗口函数 vs GROUP BY

五、支持的数据库

✅ PostgreSQL
✅ MySQL 8.0+
✅ SQL Server
✅ Oracle
✅ SQLite 3.25+

如果你有具体业务场景（比如：销售排名、用户留存、同比环比），我可以帮你写可直接用的 SQL。