SQL开窗函数是一种在SQL查询中执行复杂分析的高级技术,它允许你在同一查询中对一组相关行(称为“窗口”或“分组”)进行聚合操作,而不仅仅是整个结果集。这使得处理复杂的排序、分组和计算变得更加便捷。在2003年的ISO SQL标准中引入了开窗函数,随后被MSSQL Server、Oracle、DB2等主流数据库系统支持,但遗憾的是,MySQL目前还不支持。
开窗函数的核心在于定义一个“窗口”,这个窗口可以随着查询中的每一行变化,从而提供对当前行上下文相关的聚合信息。例如,你可以计算当前行之前或之后几行的数据总和,或者找到当前行在所有行中的排名。
在Oracle数据库中,这些函数被称为分析函数。以下是一些常见的SQL开窗函数:
1. ROW_NUMBER():为窗口中的每一行分配一个唯一的整数,通常用于分页或排名。
2. RANK():类似ROW_NUMBER(),但当有相同值时会跳过编号,确保排名的唯一性。
3. DENSE_RANK():与RANK()类似,但在有相同值时不会跳过编号,而是连续分配相同的排名。
4. LAG()和LEAD():这些函数可以获取当前行之前或之后一行的值,这对于跟踪变化或计算差异非常有用。
5. AVG()、SUM()、COUNT()等聚合函数:在开窗函数中,它们可以返回窗口内的平均值、总和或计数,而不仅仅是整个结果集的聚合值。
6. PERCENT_RANK():计算当前行在所有行中的百分比排名,不包括自身。
7. CUME_DIST():计算当前行在所有非空值中的累积分布,即包含当前行及其之前的行的比例。
让我们以一个例子来说明如何使用开窗函数。假设我们要找出每个城市中工资最低的员工,并展示他们相对于其他城市工资最低的员工的工资比例。我们需要定义一个窗口,该窗口包含每个城市的员工,然后使用MIN()函数找到每个城市的最低工资。接着,我们可以用LEAD()函数找出每个员工的下一个人的工资,以便计算比例。
```sql
SELECT
FName, FCity,
FSalary,
MIN(FSalary) OVER (PARTITION BY FCity) AS MinSalary,
(FSalary / LEAD(MIN(FSalary)) OVER (PARTITION BY FCity ORDER BY FSalary ASC)) AS Ratio
FROM T_Person
WHERE FSalary = (SELECT MIN(FSalary) FROM T_Person WHERE FCity = T_Person.FCity)
```
在这个查询中,`PARTITION BY FCity`定义了按城市划分的窗口,`ORDER BY FSalary ASC`则确定了工资升序的顺序。`LEAD(MIN(FSalary))`获取每个城市的下一个最小工资,然后用来计算比例。
开窗函数的应用场景广泛,包括但不限于计算移动平均、排名、差异、百分位数等。它们极大地增强了SQL的功能,使得在单个查询中完成复杂的数据分析成为可能,避免了使用多次子查询或存储过程。理解并熟练掌握开窗函数是SQL高级查询不可或缺的一部分,对于数据分析和报表制作等工作具有极大帮助。