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

当我们需要对 SQL Server 数据库中的数据进行统计计算并按照一定的规则进行分组时，可以运用开窗函数 over()。over() 函数的作用是在查询结果集中为每一行计算指定的聚合函数，并且聚合函数的计算范围是针对整个数据集而不是单个分组的范围内。

此外，开窗函数 over() 还可以指定分区（partition by）和排序（order by）规则，进一步对数据进行筛选、分组和排序。

下面是 SQL Server 利用开窗函数 over() 进行分组统计的完整攻略：

1. 聚合统计函数

在使用 over() 函数进行分组统计之前，首先需要选择合适的聚合统计函数。SQL Server 支持的聚合函数包括但不限于以下几种：

• COUNT：统计行数
• SUM：求和
• AVG：求平均值
• MAX：求最大值
• MIN：求最小值

2. over() 函数基本语法

下面是 over() 函数的基本语法：

<aggregate_function> OVER (
    [PARTITION BY <partition_expression>]
    [ORDER BY <order_expression> [ASC | DESC] [, ...] ]
)

其中：

• aggregate_function：需要进行聚合统计的函数，可以使用上述的聚合函数。
• PARTITION BY：可选参数，用于指定分组的规则。
• ORDER BY：可选参数，用于指定排序的规则。

3. 利用 over() 函数进行分组统计的示例

3.1 根据不同城市计算员工工资的平均值

假设存在一个员工信息表 employee，其中包括员工编号、员工姓名、所在城市、员工工资等信息。需要根据不同城市计算员工工资的平均值，可以使用如下 SQL 语句：

SELECT 
    city, 
    AVG(salary) OVER (PARTITION BY city) AS avg_salary
FROM employee;

上述 SQL 语句中，使用 AVG(salary) OVER (PARTITION BY city) 计算不同城市的员工工资平均值，并且使用 PARTITION BY city 按照城市进行分组，最终得到每个城市的平均工资。

3.2 根据年份计算销售额的排名

假设存在一个销售记录表 sales，其中包括销售记录编号、销售日期、销售金额等信息。需要根据年份计算每年的销售额，并按照销售额从大到小排序，可以使用如下 SQL 语句：

SELECT 
    YEAR(sale_date) AS year, 
    SUM(sale_amount) OVER (PARTITION BY YEAR(sale_date)) AS total_sales,
    RANK() OVER (ORDER BY SUM(sale_amount) DESC) AS sales_rank
FROM sales
GROUP BY YEAR(sale_date);

上述 SQL 语句中，使用 SUM(sale_amount) OVER (PARTITION BY YEAR(sale_date)) 计算每年的销售额，并使用 PARTITION BY YEAR(sale_date) 按照年份进行分组。同时，使用 RANK() OVER (ORDER BY SUM(sale_amount) DESC) 根据销售额从大到小排序，得到每年的销售额排名。

总之，利用 SQL Server 的开窗函数 over() 进行分组统计可以大大简化针对数据库的复杂计算和排序操作，从而提升数据库查询效率和数据分析能力。