SQL窗口函数OVER用法实例整理

在SQL中，窗口函数是一种特殊类型的函数，它可以在一组相关的行上执行计算，这些行通常是根据一个或多个与当前行相关的排序键进行分组的，窗口函数不会改变查询的结果集，但它们可以用于计算每个组的累积和、平均值、最大值、最小值等，OVER子句用于定义窗口函数的执行环境，包括分区、排序和排名。

本文将通过实例来介绍SQL窗口函数OVER的用法。

1、基本用法

最基本的窗口函数是ROW_NUMBER()，它为每一行分配一个唯一的数字，以下查询为employees表中的每一行分配一个员工编号：

SELECT employee_id, first_name, last_name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

2、分组窗口

窗口函数可以在每个分组上执行计算，以下查询计算每个部门的员工平均工资：

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

3、排序窗口

窗口函数可以根据一个或多个排序键对行进行排序，以下查询按部门和工资降序排列员工：

SELECT employee_id, first_name, last_name, salary, department_id,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

4、累计和窗口

窗口函数可以计算每个组的累积和，以下查询计算每个部门的累计工资：

SELECT department_id, employee_id, first_name, last_name, salary,
       salary * SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
FROM employees;

5、移动窗口

窗口函数可以计算每个组的移动平均值，以下查询计算每个部门过去3个月的平均工资：

SELECT department_id, employee_id, first_name, last_name, salary,
       AVG(salary) OVER (PARTITION BY department_id ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_salary
FROM employees;

6、排名窗口

窗口函数可以计算每个组的排名，以下查询计算每个部门员工的排名：

SELECT department_id, employee_id, first_name, last_name, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
       NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

7、聚合窗口

窗口函数可以在每个分组上执行聚合操作，以下查询计算每个部门的员工数量：

SELECT department_id, COUNT(*) OVER (PARTITION BY department_id) AS employee_count,
       SUM(salary) OVER (PARTITION BY department_id) AS total_salary,
       AVG(salary) OVER (PARTITION BY department_id) AS average_salary,
       MIN(salary) OVER (PARTITION BY department_id) AS min_salary,
       MAX(salary) OVER (PARTITION BY department_id) AS max_salary
FROM employees;

8、同时使用多个窗口函数

窗口函数可以同时使用多个排序键和聚合操作，以下查询按部门和工资降序排列员工，并计算每个部门的累计工资：

SELECT department_id, employee_id, first_name, last_name, salary,
       salary * SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
       NTILE(4) OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

相关问题与解答：

问题1：如何在SQL中使用窗口函数？

答案：在SQL中，可以使用OVER子句定义窗口函数的执行环境，包括分区、排序和排名，在SELECT语句中使用窗口函数对数据进行计算，可以使用ROW_NUMBER()、AVG()、RANK()等窗口函数。