sql server如何利用开窗函数over()进行分组统计

1. 开窗函数over()介绍

在SQL Server中，开窗函数（Windowing Function）是一种可以实现类似于聚合函数的功能，但是又不会对数据进行分组聚合的函数。开窗函数可以通过使用over()函数来实现，它可以让我们在不进行分组的情况下，对数据进行统计、排序、排名等操作。开窗函数通过指定窗口（Window），来确定函数计算的数据范围。在开窗函数中，可以使用PARTITION BY子句来对数据进行分组，也可以使用ORDER BY子句来对数据进行排序，还可以使用ROWS或RANGE子句来指定窗口的大小和位置。

2. 开窗函数over()的语法

下面是开窗函数over()的一般语法：

SELECT column1, column2, ..., function(column_name) OVER (

    [PARTITION BY partition_expression, ... ]
    [ORDER BY order_expression [ASC | DESC], ... ]
    [ROWS or RANGE frame_specification]
) as column_alias
FROM table_name;

2.1 PARTITION BY子句

PARTITION BY子句用于指定分组的列，它的语法为：

PARTITION BY column_name1, column_name2, ...

这样就可以对表中的数据按照指定的列进行分组，然后在每个分组内进行开窗函数的计算。

2.2 ORDER BY子句

ORDER BY子句用于指定排序的列，它的语法为：

ORDER BY column_name [ASC | DESC], ...

这样就可以对表中的数据按照指定的列进行排序，然后再对排序之后的数据进行开窗函数的计算。

2.3 ROWS或RANGE子句

ROWS或RANGE子句用于指定窗口的大小和位置，语法为：

ROWS BETWEEN start_expression AND end_expression
RANGE BETWEEN start_expression AND end_expression

其中，ROWS子句指定窗口的大小为多少行，RANGE子句则指定窗口的大小为多少值域（Value Range）。start_expression和end_expression可以是以下任意一个表达式：

CURRENT ROW

UNBOUNDED PRECEDING

n PRECEDING

UNBOUNDED FOLLOWING

n FOLLOWING

其中，CURRENT ROW表示当前行，UNBOUNDED PRECEDING表示从第一行开始，UNBOUNDED FOLLOWING表示到最后一行结束，n PRECEDING表示当前行的前n行，n FOLLOWING表示当前行的后n行。

3. 开窗函数over()的应用

3.1 统计每个部门的平均工资

假设我们有一个表Emp，它包含了员工的信息，如下所示：

CREATE TABLE Emp

(
    EmpNo INT,
    Name VARCHAR(50),
    DeptNo INT,
    Salary DECIMAL(18,2)
);
INSERT INTO Emp VALUES(1, '张三', 1, 5000.00);
INSERT INTO Emp VALUES(2, '李四', 1, 6000.00);
INSERT INTO Emp VALUES(3, '王五', 2, 7000.00);
INSERT INTO Emp VALUES(4, '赵六', 2, 8000.00);
INSERT INTO Emp VALUES(5, '孙七', 3, 9000.00);
INSERT INTO Emp VALUES(6, '周八', 3, 10000.00);

现在我们需要统计每个部门的平均工资，可以使用如下语句：

SELECT DeptNo, AVG(Salary) OVER(PARTITION BY DeptNo) AS AvgSalary FROM Emp;

执行以上SQL语句后，得到如下结果：

DeptNo  AvgSalary
1       5500.00
1       5500.00
2       7500.00
2       7500.00
3       9500.00
3       9500.00

可以看到结果与我们期望的相符，每个部门的平均工资被正确地计算了出来。

3.2 计算每个员工的工资排名

假设我们需要计算每个员工的工资排名，并将排名存入一个新的列Rank中，可以使用如下语句：

SELECT EmpNo, Name, Salary, RANK() OVER(ORDER BY Salary DESC) AS Rank FROM Emp;

执行以上SQL语句后，得到如下结果：

EmpNo   Name    Salary  Rank
6       周八    10000.00    1
5       孙七    9000.00     2
4       赵六    8000.00     3
3       王五    7000.00     4
2       李四    6000.00     5
1       张三    5000.00     6

可以看到，每个员工的工资排名都被正确地计算了出来，并存放在了新的列Rank中。

3.3 统计每个部门的工资排名

假设我们需要统计每个部门内的员工的工资排名，可以使用如下语句：

SELECT EmpNo, DeptNo, Salary, 
        DENSE_RANK() OVER(PARTITION BY DeptNo ORDER BY Salary DESC) AS Rank 
FROM Emp;

执行以上SQL语句后，得到如下结果：

EmpNo   DeptNo  Salary  Rank
2       1       6000.00 1
1       1       5000.00 2
4       2       8000.00 1
3       2       7000.00 2
6       3       10000.00    1
5       3       9000.00     2

可以看到，每个部门内的员工的工资排名都被正确地计算了出来，并存放在了新的列Rank中。

4. 总结

开窗函数over()是一种强大的功能，它可以帮助我们对不同的数据集合进行分析、计算和排序等操作。在实际的工作中，我们可以根据需要使用不同的窗口函数来满足不同的需求。对于需要统计、排序、排名等操作的情况，开窗函数over()可以方便地完成这些任务，使我们的工作变得更加高效和便捷。