保姆级教会你SQL窗口函数

SQL窗口函数

SQL中有一个很重要的函数被我们经常用到查询语句中，这个函数就是窗口函数，今天我们就来看看什么是窗口函数以及它的使用方法吧！

1. 认识窗口函数

窗口函数是一种特殊类型的SQL函数，它可以在指定的窗口范围内计算结果。窗口函数通常用于在查询结果中为每一行计算一个聚合值或分析函数值，而不需要分组或对查询结果进行子查询。

窗口函数通常与over子句一起使用。over子句定义了窗口的大小和位置，以及如何对窗口中的行进行排序。窗口函数可以通过在over子句中指定partition by 子句进行分组，也可以通过在order by 子句中指定窗口排序方式。

2. 常见的窗口函数

函数类型	函数名称	函数作用
专用窗口函数	row_number()	为每行计算一个唯一的行号，按照指定的排序规则进行排序。
	rank()	计算每行在排序后的结果中的排名，如果有并列的值，则会跳过下一个排名。
	dense_rank()	计算每行在排序后的结果中的排名，如果有并列的值，则不会跳过下一个排名。
	percent_rank()	计算窗口中值的百分比，值为0-1之间
	ntile(n)	将结果集分成n个桶，返回每行所属的桶编号。
	lag()	获取指定列上当前行之前的某一行的值。
	lead()	获取指定列上当前行之后的某一行的值。
	first_value()	用于返回窗口内第一行的值，可以搭配order by使用
	last_value()	用于返回窗口内最后一行的值，可以搭配order by使用
聚合函数	sum()	计算分组字段的和
	avg()	计算分组字段的平均值
	count()	对分组字段进行计数

3. 如何使用窗口函数

窗口函数通用语法：

<窗口函数> over (partiton by <被分组的字段> order by <被排序的字段>) as <被排序的字段>

<窗口函数>：为自己需要的窗口函数

<被分组的字段>：需要对某一字段进行开窗口（分组）

<被排序的字段>：需要增加的排序要求（按需求添加）

<被排序的字段>：就是一个名字

4. 实战使用窗口函数

接下来就对每个常用的窗口函数进行实操演练，以便更好理解各函数用法。

这里举例一个日常生活的例子：

员工表：employees

字 段：

部门（department_id）；

部门名称（department_name）；

部门地址（department_location）；

员工编号（employee_id）；

姓名（name）；

薪资（salary）；

入职时期（hire_date）

4.1 row_number()

row_number()函数是为查询结果集中的每一行分配一个唯一的行号。这个行号是根据窗口定义中指定的顺序（例如按照特定的列排序）计算的。

SELECT department_id, employee_id, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id
                          ORDER BY salary DESC) AS row_num
FROM employees;

此代码就是按照不同部门根据薪资高低（降序）对每一个查询结果分配有序且唯一的行号。

4.2 rank()

rank()函数作用是计算并分配给查询结果集中的行。与row_number()的作用不同，使用rank()时如果有两个相同的值，那么rank()将分配其相同的排名。

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

此代码依然是按照不同的部门根据薪资的高低（降序）进行排序，只不过遇到相同薪资时，赋予它们相同的排名，给它们分配有序但不一定唯一的行号。

4.3 dense_rank()

它计算并分配密集排名给查询结果集中的行。与rank()不同，如果有两个或多个行具有相同的排序值，它们将分配相同的密集排名，并跳过相同数量的排名值。

SELECT department_id, employee_id, salary,
       DENSE_RANK() OVER (PARTITION BY department_id
                          ORDER BY salary DESC) AS dense_rank_num
FROM employees;

关于dense_rank()函数的详细解答文章在此：https://blog.csdn.net/qq_67780151/article/details/129102199?spm=1001.2014.3001.5501

4.4 percent_rank()

它为查询结果集中的每一行计算并分配一个百分比排名值，百分比排名值是从1到0的浮点数据，1表示最大，0表示最小。

SELECT department_id, employee_id, salary,
       PERCENT_RANK() OVER (PARTITION BY department_id
                            ORDER BY salary DESC) AS percent_rank_num
FROM employees;

此查询就是按照不同部门查询每个员工的薪资（降序）在部门里面的占比。

4.5 first_value()

它是用于访问某个窗口中第一行数据的函数，至少需要一个参数，该参数是需要获取的列，通过over子句定义窗口并指定窗口排序方式。若没有指定排序方式，就按默认排序方式（参数）作为当前查询中的排序方式。

SELECT department_id, salary, 
       first_value(salary) OVER (PARTITION BY department ORDER BY hire_date) as first_hire_salary
FROM employees;

此查询就是寻找不同部门最早入职员工的薪资；先对部门进行分组，在对入职时期进行升序排序，最后取每个窗口第一行数据。

4.6 last_value()

与first_value()同理，区别在于取的是最后一行数据

SELECT department_id, salary, 
       last_value(salary) OVER (PARTITION BY department_id ORDER BY hire_date) as last_hire_salary
FROM employees;

4.7 sum()

它用于计算每个窗口数据的总和。

SELECT department_id, 
       SUM(salary) OVER (PARTITION BY department_id) as total_salary
FROM employees;

此查询就是对每个部门的所有员工薪资的求和计算。

4.8 avg()

它用于计算每个窗口数据的平均值。

select department_id 
     , salary
     , avg(salary) over (partition by department_id) as avg_salary
from employees；

此查询就是计算每个部门的平均薪资。

4.9 count()

它作为窗口函数时，并不是计算整个表中的行数，而是计算与当前字段相关的行数。

SELECT department_id
     , department_name 
     , department_location
COUNT(*) OVER () AS department_count
FROM employees;

次函数就是对部门计数，返回的值有部门编号、部门名称、部门地址。

以上就是写SQL语句一定会用到的窗口函数细解！