SQL语句 | 窗口函数

 1.窗口函数

         SQL窗口函数是SQL中的一种高级函数，它允许用户在不显式分组查询的情况下对结果集进行分组和聚合计算。窗口函数的特别之处在于，它们将结果集中的每一行看作一个单独的计算对象，而不是将结果集划分为分组并计算每个分组的聚合值。这就使得窗口函数能够为结果集中的每一行计算类似排名、行号、百分比和移动聚合函数等值。

SQL窗口函数的语法如下:

窗口函数> OVER ([PARTITION BY <分组列> [, <分组列>...]]
                     [ORDER BY <排序列> [ASC | DESC] [, <排序列> [ASC | DESC]]...]
                     [<rows or range clause>])

其中：

        <窗口函数> :  定义要在窗口中计算的聚合函数或其它分析函数，如COUNT、RANK、SUM等。
        OVER :  窗口函数的核心关键字。
        PARTITION BY : 定义要用来分组的一组列名。
        ORDER BY :  定义用来排序的一组列名。
        <rows or range clause> :  定义窗口的行集合。默认为  `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` ，表示窗口包括从窗口开始到当前行的所有行。

下面的例子说明了如何使用窗口函数计算每个部门的平均工资，并根据平均工资进行排名

SELECT department,employee_name , employee_age , salary,
  AVG(salary) OVER (PARTITION BY department) average_salary,
  RANK() OVER (PARTITION BY department order by salary desc ) as salary_rank
FROM employee ;

        在这个例子中，PARTITION BY定义了用于分组计算平均工资的列名，OVER后面依次定义了需要计算的列名和相应的窗口函数。 `AVG(salary) OVER (PARTITION BY department)`  的意思是对于每个部门，计算  `salary`  列的平均值，而  `RANK() OVER (PARTITION BY department ORDER BY salary DESC)`  的意思是计算每个部门中  `salary`  列的排名。
 
         SQL窗口函数的语法和用法比较复杂，但它极大地拓展了SQL查询的能力，使得更多复杂的查询可以得以实现。

 2.SQL的窗口函数中常见的排序方法

SQL的窗口函数中常见的排序方法包括以下几种：

1. ROW_NUMBER()函数：按照指定的列或表达式对结果集进行排序，并为排序后的每一行分配一个唯一的行号。
2. RANK()函数：对结果集进行排序，并为排序后的相同值的行分配相同的排名，排名之间存在空缺。
3. DENSE_RANK()函数：对结果集进行排序，并为排序后的相同值的行分配相同的排名，排名之间不存在空缺。
4. CUME_DIST()函数：计算结果集中某个值在排序后出现的位置占总行数的比例。
5. NTILE(n)函数：将结果集划分为n份，并为每个分组分配一个编号。
6. `LAG()/LEAD()：查询当前行向上/下偏移n行对应的结果。
7. SUM()、AVG()、MIN()、MAX()：对结果集中的一部分数据进行分组计算。

注：以上窗口函数中的排序方法都是基于ORDER BY子句中指定的列或表达式的排序。

以下是五种窗口函数的排序方法及其例子：

数据准备-表创建

-- employee definition

CREATE TABLE `employee` (
  `id` varchar(10) CHARACTER SET utf8mb4 NOT NULL,
  `employee_name` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `employee_age` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `department` varchar(100) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '部门'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 表数据
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('1', '张三', '10', 8000, '技术部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('6', '张三', '10', 888, '技术部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('1', '李四', '11', 6500, '职能部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('2', '王五', '12', 6500, '技术部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('2', '赵六', '13', 4500, '职能部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('3', '田七', '14', 6500, '管理部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('3', '钱八', '15', 20000, '职能部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('4', '老九', '16', 30000, '管理部');
INSERT INTO test.employee (id, employee_name, employee_age, salary, department) VALUES('5', '老十', '17', 30000, '技术部');

• ROW_NUMBER()：按照指定的排序方法为每行分配一个唯一的整数行号，行号从1开始递增。示例如下：

SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num, employee_name, salary
FROM employee;

该语句会对employee表按照salary字段降序排序，并为每行分配一个不重复的行号存储在row_num字段中。

• RANK()：按照指定的排序方法为每行分配一个唯一的整数行号，行号从1开始递增。示例如下:

SELECT RANK () OVER (ORDER BY salary DESC) AS row_num, employee_name, salary
FROM employee;

例：按照指定的排序方法为每行分配一个排名，相等值将被分配相同的排名并跳过下一个排名。示例如下：

SELECT RANK() OVER (ORDER BY total_sales DESC) AS ranking, region_name, total_sales
FROM sales_summary
WHERE year = '2021';

• DENSE_RANK()：按照指定的排序方法为每行分配一个唯一的整数行号，行号从1开始递增。示例如下:

SELECT DENSE_RANK () OVER (ORDER BY salary DESC) AS row_num, employee_name, salary
FROM employee;

例：按照指定的排序方法为每行分配一个密集排名，相等值将被分配相同的排名但不会跳过下一个排名。示例如下：

SELECT DENSE_RANK() OVER (ORDER BY exam_score DESC) AS rank, student_name, exam_score
FROM exam_results
WHERE exam_type = 'final';

该语句会对final考试的成绩按照降序排序，并为每行分配一个密集排名存储在rank字段中。

• CUME_DIST()：统计小于等于当前工资的人数占总人数的比例。示例如下：

如果按升序排列，则统计：小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows）。如果是降序排列，则统计：大于等于当前值的行数/总行数。

SELECT employee_name,  department ,  salary,
       cume_dist() OVER (ORDER BY salary) as cume_dist
FROM employee;

如果要统计大于等于当前工资的人数占总人数的比例，只需更改salary排序规则为降序desc即可。

例：根据部门统计小于等于当前工资的人数占部门总人数的比例

SELECT name,  dept_no, salary,
    cume_dist() OVER (PARTITION BY dept_no ORDER BY salary) as cume_dist
FROM data;

• NTILE(n)：根据年龄分成n个近似相等的桶，并为每行分配一个桶号（从1到n）。示例如下：

SELECT NTILE (4) OVER (ORDER BY employee_age DESC) AS row_num, employee_name,employee_age, salary
FROM employee;

例：将行分成n个近似相等的桶，并为每行分配一个桶号（从1到n）。示例如下：

SELECT NTILE(4) OVER (ORDER BY product_sales DESC) AS quartile, product_name, product_sales
FROM product_summary
WHERE year = '2021';

该语句会将2021年产品销售额按照降序排序，并将其分成四个桶，为每行分配一个桶号存储在quartile字段中。

• LAG()/LEAD()：LAG函数用于获取当前行之前的行数据，LEAD函数用于获取当前行之后的行数据。示例如下：

SELECT employee_name, salary,
       LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary,
       LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employee;

该语句会对employee表按照salary字段排序，并为每行获取前一行和后一行的salary值存储在prev_salary和next_salary字段中。