SQL Server 开窗函数

原创

mob649e81593bda 2023-07-27 18:29:07 ©著作权

文章标签 Server SQL sql 文章分类 SQL Server 数据库

©著作权归作者所有：来自51CTO博客作者mob649e81593bda的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQL Server 开窗函数详解

在SQL Server中，开窗函数是一种特殊的函数，可以在查询结果集的基础上进行计算和聚合。开窗函数提供了一种灵活的方式来执行分析、排序和聚合操作，而不需要使用传统的分组和聚合方法。

开窗函数的语法

开窗函数的语法如下：

SELECT column1, column2, ..., window_function(column)
OVER (PARTITION BY partition_column
      ORDER BY sort_column
      ROWS BETWEEN start_row AND end_row)
FROM table_name;

其中，window_function是所使用的开窗函数，PARTITION BY用于指定分区列，ORDER BY用于指定排序列，ROWS BETWEEN用于指定行的范围。

开窗函数的分类

SQL Server中的开窗函数可以分为以下几类：

聚合函数：包括SUM、COUNT、AVG等函数，用于计算分组的聚合值。
排名函数：包括RANK、DENSE_RANK、ROW_NUMBER等函数，用于计算行的排名。
分析函数：包括LAG、LEAD、FIRST_VALUE、LAST_VALUE等函数，用于计算与当前行相关的其他行的值。

开窗函数的示例

下面是一些常见的开窗函数的示例：

1. 求每个部门的平均工资

SELECT department, AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

2. 求每个部门的工资排名

SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

3. 求每个部门的工资排名和平均工资

SELECT name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

4. 求每个部门的销售额与上一个月的销售额的差值

SELECT month, department, sales, 
       sales - LAG(sales) OVER (PARTITION BY department ORDER BY month) AS difference
FROM sales;

开窗函数的注意事项

在使用开窗函数时，需要注意以下几点：

开窗函数只能在SELECT语句中使用，不能在WHERE、GROUP BY等语句中使用。
开窗函数可以与其他函数和操作符一起使用，例如SUM(salary) OVER (PARTITION BY department) * 2。
开窗函数的PARTITION BY和ORDER BY子句可以使用多个列。
开窗函数的ROWS BETWEEN子句可以用来指定行的范围，例如ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING表示包括前两行和后两行。