window窗口函数

窗口函数

学习目标

• 掌握窗口函数的基本语法和OVER()的使用方法
• 掌握RANK、DENSE_RANK、ROW_NUMBER函数的使用方法
• 知道RANK、DENSE_RANK、ROW_NUMBER的不同之处
• 能够说出如何避免 SQL 注入问题

一、窗口函数基本用法

1.窗口函数简介

接下来的课程中我们来介绍窗口函数window functions.

• MYSQL 8.0 之后，加入了窗口函数功能，简化了数据分析工作中查询语句的书写
• 在没有窗口函数之前，我们需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数实现起来更加简洁高效
• 窗口函数是数据分析工作中必须掌握的工具，在SQL笔试中也是高频考点
• 什么是窗口函数? 为什么说窗口函数可以使复杂的查询变得更加简单方便？

1.1 什么是窗口函数

• 窗口函数是类似于可以返回聚合值的函数，例如SUM()，COUNT()，MAX()。但是窗口函数又与普通的聚合函数不同，
• 分组聚合后，每一组的结果只有一行，如果不写子查询，组内每个成员的数据没法和聚合的结果放在一起展示
• 但窗口函数不会对结果进行分组，使得输出中的行数与输入中的行数相同。
• 窗口函数基本写法：

SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table 

这里有3点需要牢记：

• 聚合功能：在上述例子中，我们用了SUM()，但是你也可以用COUNT(), AVG()之类的计算功能
• PARTITION BY：你只需将它看成GROUP BY子句，但是在窗口函数中，你要写PARTITION BY
• ORDER BY：ORDER BY和普通查询语句中的ORDER BY没什么不同。注意，输出的顺序要仔细考虑

1.2 窗口函数示例

示例：集合函数VS窗口函数

假设我们有如下这个表格：

如果要按性别获取平均GPA，可以使用聚合函数并运行以下查询：

SELECT Gender, AVG(GPA) as avg_gpa FROM students GROUP BY Gender

结果如下：

现在我们想得到增加一个不同性别成绩与平均成绩差别的列avg_gpa的结果：

我们当然可以用我们刚刚提到的聚合函数，然后再将结果join到初始表，但这需要两个步骤。但如果我们使用窗口函数，我们则可以一步到位，并得到相同的结果：

SELECT *, AVG(GPA) OVER (PARTITION BY Gender) as avg_gpa FROM students 

通过上面的查询，就按性别对数据进行划分，并计算每种性别的平均GPA。然后，它将创建一个称为avg_gpa的新列，并为每行附加关联的平均GPA

1.3 窗口函数的优点

• 简单，窗口函数更易于使用。在上面的示例中，与使用聚合函数然后合并结果相比，使用窗口函数仅需要多一行就可以获得所需要的结果。
• 快速，这一点与上一点相关，使用窗口函数比使用替代方法要快得多。当你处理成百上千个千兆字节的数据时，这非常有用。
• 多功能性，主要用于数据的分析最重要的是，窗口函数具有多种功能，比如，添加移动平均线，添加行号和滞后数据，等等。

2. 数据集准备

• 本节我们先介绍窗口函数中最重要的关键字 OVER
• 在介绍具体内容之前先熟悉一下要用到的数据，我们选择了很常见的业务来介绍窗口函数的使用
• 三张表：员工表，部门表，采购表
• 员工表：员工id，姓名，员工所属部门id（department_id），工资（salary），工龄（years_worked)
• 部门表：部门id，部门名称
• 采购表：每个部门（department_id）采购的物品明细（item)，物品价格（price）

2.1 创建数据库

create database ai charset=utf8;
use ai;

2.2 employee员工信息表

• 建表 employee

create table employee(
    id int unsigned primary key not null,
    first_name varchar(20) not null,
    last_name varchar(30) not null,
    department_id  tinyint not null,
    salary int not null,
    years_worked  tinyint not null
);

• 写入数据

insert into employee values
(1, 'Diane', 'Turner', 1, 5330, 4),
(2, 'Clarence', 'Robinson', 1, 3617, 2),
(3, 'Eugene', 'Phillips', 1, 4877, 2),
(4, 'Philip', 'Mitchell', 1, 5259, 3),
(5, 'Ann', 'Wright', 2, 2094, 5),
(6, 'Charles', 'Wilson', 2, 5167, 5),
(7, 'Russell', 'Johnson', 2, 3762, 4),
(8, 'Jacqueline', 'Cook', 2, 6923, 3),
(9, 'Larry', 'Lee', 3, 2796, 4),
(10, 'Willie', 'Patterson', 3, 4771, 5),
(11, 'Janet', 'Ramirez', 3, 3782, 2),
(12, 'Doris', 'Bryant', 3, 6419, 1),
(13, 'Amy', 'Williams', 3, 6261, 1),
(14, 'Keith', 'Scott', 3, 4928, 8),
(15, 'Karen', 'Morris', 4, 6347, 6),
(16, 'Kathy', 'Sanders', 4, 6286, 1),
(17, 'Joe', 'Thompson', 5, 5639, 3),
(18, 'Barbara', 'Clark', 5, 3232, 1),
(19, 'Todd', 'Bell', 5, 4653, 1),
(20, 'Ronald', 'Butler', 5, 2076, 5)
;

• employee员工信息表字段说明
• id 员工id
• first_name 员工名字
• last_name 员工的姓
• department_id 员工所属部门id
• salary 员工工资
• years_worked 工龄

2.3 department部门信息表

• 建表 department

create table department(
    id int unsigned primary key not null,
    name varchar(30) not null
);

• 写入数据

insert into department values
(1, 'IT'),
(2, 'Management'),
(3, 'Human Resources'),
(4, 'Accounting'),
(5, 'Help Desk')
;

• department部门信息表字段说明
• id 部门id
• name 部门名称

2.4 purchase采购信息表

• 建表 purchase

create table purchase(
    id int unsigned primary key