SQL中的窗口函数

yifanghub

于 2024-08-03 23:10:08 发布

阅读量574

点赞数 8

分类专栏：技术学习文章标签： sql 数据库窗口函数

本文链接：https://blog.csdn.net/zhangyifang_009/article/details/140756701

版权

技术学习专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1.窗口函数简介

窗口函数是SQL中的一项高级特性，用于在不改变查询结果集行数的情况下，对每一行执行聚合计算或者其他复杂的计算，也就是说窗口函数可以跨行计算，可以扫描所有的行，并把结果填到每一行中。这些函数通常与OVER()子句一起使用，可以定义窗口或分区，并在上面执行计算，使用窗口函数，可以使许多难以处理的棘手问题变得较为容易。
窗口函数的特点包括：

输入多行（一个窗口），返回一个值：窗口函数为每行数据进行一次计算，但不会改变原始查询结果集的行数
计算方式灵活：可以使用partition by字句将数据分区，并使用order by子句来进行排序等一些复杂运算
与聚合函数结合使用：可以与聚合函数结合使用，在不分组的情况下计算如总和、平均值、最小值、最大值等聚合值。

2.语法结构解析

<窗口函数> OVER (
    [PARTITION BY <分组列>]
    [ORDER BY <排序列>]
    [ROWS 或 RANGE <窗口框架定义>]
)

其中：

PARTITION BY 子句用于将数据分成不同的分区，窗口函数将在每个分区内执行。可以理解为group by
ORDER BY 子句定义了数据的排序方式，决定窗口函数的计算顺序。
ROWS BETWEEN 子句指定了窗口的范围，可以是行数、区间等。

3.常用的窗口函数SQL示例

常用的窗口函数有：

聚合函数：SUM()、AVG()、COUNT()、MAX()、MIN()等
排名函数：
- ROW_NUMBER()：为窗口内的每一行分配一个唯一的序号，序号连续且不重复；
- RANK()：排名函数，允许有并列的名次，名次后面会出现空位。
- ENSE_RANK()：排名函数，允许有并列的名次，名次后面不会空出位置，即序号连续。
分组窗口函数：
- NTILE()：将窗口内的行分为指定数量的组，每组的行数尽可能相等。
分布窗口函数
- PERCENT_RANK()：计算每一行的相对排名，返回一个介于0到1之间的值，表示当前行在分区中的排名百分比。
- CUME_DIST()：计算小于或等于当前行的行数占窗口总行数的比例。
取值窗口函数
- LAG()：访问当前行之前的第n行数据。
- LEAD()：访问当前行之后的第n行数据。
- FIRST_VALUE()：获取窗口内第一行的值。
- LAST_VALUE()：获取窗口内最后一行的值。
- NTH_VALUE()：获取窗口内第n行的值，如果存在多行则返回第一个。

这里以employees表为例：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(255),
    department_name VARCHAR(255),
    salary DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO employees (employee_id, department_name, name, salary) VALUES
(1, '财务部', '张三', 30000),
(2, '财务部', '李四', 25000),
(3, '市场部', '王五', 40000),
(4, '市场部', '赵六', 35000),
(5, '市场部', '孙七', 50000),
(6, '技术部', '周八', 45000),
(7, '技术部', '钱九', 60000),
(8, '技术部', '吴十', 55000);

聚合窗口函数查询

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

执行输入如下：
在这里插入图片描述

排名窗口函数查询

ROW_NUMBER()窗口函数查询

SELECT
    employee_id,
    name,
    department_name,
    salary,
    ROW_NUMBER() OVER () AS salary_rank
FROM employees;

执行输出如下：
在这里插入图片描述
如果想要在部门内分区添加行号

SELECT
    employee_id,
    name,
    department_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank
FROM employees;

执行结果如下：
在这里插入图片描述

RANK() && DENSE_RANK()

RANK() 和 DENSE_RANK() 函数的主要区别在于处理并列名次的方式。RANK() 函数在遇到并列名次时会在下一个名次处留出空位，而 DENSE_RANK() 函数则不会留出空位，名次连续。
我们需要确保每个部门至少有两个员工的薪资是相同的。我们假如插入的数据如下：

INSERT INTO employees (employee_id, name, department_name, salary) VALUES
(1, 'Alice', '财务部', 70000),
(2, 'Bob', '财务部', 60000),
(3, 'Charlie', '财务部', 60000), -- 与Bob薪资相同
(4, 'David', '市场部', 80000),
(5, 'Eve', '市场部', 80000), -- 与David薪资相同
(6, 'Frank', '市场部', 50000),
(7, 'Grace', '技术部', 90000),
(8, 'Heidi', '技术部', 75000),
(9, 'Ivan', '技术部', 75000), -- 与Heidi薪资相同
(10, 'Judy', '财务部', 60000), -- 与Bob和Charlie薪资相同
(11, 'Karl', '市场部', 50000), -- 与Frank薪资相同
(12, 'Linda', '技术部', 90000), -- 与Grace薪资相同
(13, 'Mike', '财务部', 50000), -- 新薪资水平
(14, 'Nancy', '市场部', 60000), -- 与Bob和Charlie薪资相同
(15, 'Oliver', '技术部', 60000); -- 与Bob和Charlie薪资相同

执行包含 RANK() 和 DENSE_RANK() 函数的查询：

SELECT
    employee_id,
    name,
    department_name,
    salary,
    RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS dense_salary_rank
FROM employees
ORDER BY department_name, salary DESC;

执行结果如下：
在这里插入图片描述
在这个结果中，以财务部为例，

Alice 薪资最高，排名 1。
Bob、Charlie 和 Judy 薪资相同，使用 RANK() 函数时，他们的排名依次为 2、2、2（跳过3、4），使用 DENSE_RANK() 函数时，排名连续为 2、2、2（名次连续）。

分组窗口函数查询

分组窗口函数NTILE() 将数据分为指定数量的组，每组的行数尽可能相等。假设我们要根据员工的薪资将他们分为四组（例如：高收入、中等收入、较低收入和最低收入），我们可以对每个部门使用 NTILE(4) 来实现：

    SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTILE(4) OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_quartile
FROM employees;

执行结果如下：
在这里插入图片描述
在这个结果中，每个部分都被分为了4组，以市场部为例：
市场部的 David 和 Eve 位于最高收入组（1），Nancy在中等收入组(2)，Frank分到了较低收入组（3），Karl 被分到了最低收入组（4）注意，由于 NTILE() 函数的目的是将数据分为尽可能相等的组，每个部门5个人，分为4组，每个部门肯定有两个人会分到同一个组内。

分布窗口函数查询

SELECT
    employee_id,
    name,
    department_name,
    salary,
    PERCENT_RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_percent_rank,
    CUME_DIST() OVER (PARTITION BY department_name ORDER BY salary DESC) AS salary_cume_dist
FROM employees;

执行结果如下：
在这里插入图片描述
ERCENT_RANK() 说明：
PERCENT_RANK() 函数返回一个介于0到1之间的值，表示当前员工薪资在部门中的排名百分比。例如，如果一个员工的 salary_percent_rank 是0.50，这意味着他的薪资低于或等于部门中50%的员工薪资。

在 “财务部” 中，Alice 的薪资是最高的，所以她的薪资百分比排名是0.00（即她是最高薪）。Judy、Bob 和 Charlie 的薪资相同，并且低于Alice，所以他们的薪资百分比排名是0.25。
Mike 的薪资是最低的，所以他的薪资百分比排名是1.00。

CUME_DIST() 说明：

CUME_DIST() 函数返回一个介于0到1之间的值，用于求分区中大于等于或小于等于当前行的数据在分区中的占比。如果是升序排列，则统计是：小于等于当前值的行数/总行数，如果是降序排列，则统计：大于等于当前值的行数/总行数。
这里按薪水降序排列，表示大于或等于当前员工薪资的员工数量占部门总员工数量的比例。在 “财务部” 中，Alice 的 salary_cume_dist 是0.2，因为她的薪资是最高的。Judy、Bob 和 Charlie 的薪资相同，并且有4个的员工薪资大于等于他们，所以他们的 salary_cume_dist 是0.80。Mike 是最低薪资，部门所有人都大于等于他，所以他的 salary_cume_dist 是1。

取值窗口函数查询

LAG

SELECT
    employee_id,
    name,
    department_name,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department_name ORDER BY salary DESC) AS prev_salary
FROM employees
ORDER BY department_name, salary DESC;

在这里插入图片描述
LAG(salary, 1, 0) 说明：

LAG() 函数返回当前员工之前第一个员工的薪资。如果没有前一个员工（即当前是部门中薪资最高的员工），则返回指定的默认值，这里我们使用0作为默认值。

LEAD

SELECT
    employee_id,
    name,
    department_name,
    salary,
    LEAD(salary, 1, 0) OVER (PARTITION BY department_name ORDER BY salary DESC) AS next_salary
FROM employees
ORDER BY department_name, salary DESC;

在这里插入图片描述
LEAD(salary, 1, 0) 说明：

LEAD() 函数返回当前员工之后第一个员工的薪资。如果没有后一个员工（即当前是部门中薪资最低的员工），则返回指定的默认值，这里我们使用0作为默认值。

FIRST_VALUE

FIRST_VALUE函数用于取当前行所对应窗口的第一条数据的值。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department_name ORDER BY salary DESC) AS max_salary_in_department
FROM employees
ORDER BY department_name, salary DESC;

执行结果如下：
在这里插入图片描述
FIRST_VALUE(salary) 说明：
FIRST_VALUE() 函数返回部门中薪资是按降序排列的，因此 FIRST_VALUE() 实际上是返回该部门的最高薪资。

LAST_VALUE

LAST_VALUE函数用于取当前行所对应窗口的最后一条数据的值。

SELECT
    employee_id,
    name,
    department_name,
    salary,
    LAST_VALUE(salary) OVER (PARTITION BY department_name ORDER BY salary DESC) AS min_salary_in_department
FROM employees
ORDER BY department_name, salary DESC;

执行结果如下：
在这里插入图片描述

NTH_VALUE

获取窗口内第n行的值

SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTH_VALUE(salary, 3) OVER (PARTITION BY department_name ORDER BY salary DESC) AS third_salary_in_department
FROM employees
ORDER BY department_name, salary DESC;

执行结果

在这里插入图片描述

注意，这里third_salary_in_department会有null值，实际上这和分区内默认的取值范围有关，NTH_VALUE(salary, 3) 将始终返回每个分区中的第三行的值，因为这里默认取值范围从排序后的第一个行（即 ORDER BY 子句中的第一个值）到当前行，所以在当前行窗口范围内没有第三行的值时，就会显示null值。如果我们指定窗口取值范围如下：

SELECT
    employee_id,
    name,
    department_name,
    salary,
    NTH_VALUE(salary, 3) OVER (PARTITION BY department_name ORDER BY salary desc
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_salary_in_department
FROM employees
ORDER BY department_name;

则执行结果如下：
在这里插入图片描述
这里使用了ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING来指定窗口的范围为第一行到最后一行，这意味着窗口包括了分区内的所有行。在这种情况下，NTH_VALUE(salary, 3) 将始终返回每个分区中的第三行的值，只要该分区至少有三行数据。

4.窗口的范围

窗口的范围，有的资料上面也叫Framing（分帧）,大致可以分为两种，一种是根据行（rows between）来划分，一种是根据列值（range between）来划分，它们都可以确定一个窗口应该包含哪些行。窗口的开始和结束可以使用以下关键字来定义：

UNBOUNDED PRECEDING：从分区中的第一行开始（前面所有行）。
CURRENT ROW：包括当前行。
n PRECEDING：从当前行之前的第 n 行开始。
n FOLLOWING：包括当前行之后第 n 行。
UNBOUNDED FOLLOWING：到分区中的最后一行结束（后面所有行）。

基于行划分：rows between…and…

语法格式如下：

sum(amount) over(order by <column> rows between <start> and <end>)

例如，假设你有一个包含销售数据的表，并希望计算每一行及其前两行的总和：

SELECT
    sales_date,
    sales_amount,
    SUM(sales_amount) OVER (
        ORDER BY sales_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS running_total
FROM
    sales;

基于列值划分：range between…and…

语法格式如下：

sum(amount) over(order by <column> range between <start> and <end>)

例如，我们有一个包含商品数据的表，我们希望计算每个商品价格及其前后20单位价格范围内的商品总和：

SELECT
    product_id,
    product_price,
    SUM(product_price) OVER (
        ORDER BY product_price
        RANGE BETWEEN 20 PRECEDING AND 20 FOLLOWING
    ) AS price_range_total
FROM
    products;

假设当前行product_price值为70，那其窗口范围是product_price列的值位于50（70-20）到90（70+20）之间的所有的行。

5.窗口函数的缺省值

over后的窗口函数划分语句，包括partition by 、order by 、（row|range）between ...and...这三部分，实际上这些内容也都可以省略不写。

partition by省略不写，表示不分区。在不进行分区的情况下，将会把整张表的全部内容作为窗口进行划分。
order by 省略，表示不排序
（row|range）between …and…省略不写，则使用其默认值，默认值分为以下两种情况：
- over后面包含order by ,则默认值为：range between unbounded preceding and current row
- over后面不包含order by ,则默认值为：rows between unbounded preceding and unbounded following。

小结：

可以看出，窗口产生的用法很类似聚合函数，不同的是聚合函数是将多行数据汇总为单个结果，窗口函数的话在同一个select中我们可以按照不同的列进行分区，而且多个窗口函数的列之间不受影响，功能很强大，语法也比较灵活，在进行复杂的数据分析或写一些报表时我们就可以考虑用窗口函数来去实现。

参考文档：

https://developer.aliyun.com/article/1541419
https://mysql.net.cn/doc/refman/8.0/en/window-functions.html
https://support.huaweicloud.com/intl/zh-cn/sqlref-spark-dli/dli_08_0069.html
https://www.cnblogs.com/xfeiyun/p/16965394.html

yifanghub

关注

8
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
SQL中的窗口函数

窗口函数是SQL中的一项高级特性，用于在不改变查询结果集行数的情况下，对每一行执行聚合计算或者其他复杂的计算，也就是说窗口函数可以跨行计算，可以扫描所有的行，并把结果填到每一行中。这些函数通常与OVER()子句一起使用，可以定义窗口或分区，并在上面执行计算，使用窗口函数，可以使许多难以处理的棘手问题变得较为容易。窗口函数的特点输入多行（一个窗口），返回一个值：窗口函数为每行数据进行一次计算，但不会改变原始查询结果集的行数计算方式灵活：可以使用字句将数据分区，并使用order by。
复制链接

扫一扫

专栏目录