什么是窗口函数

窗口函数是类似于可以返回聚合值的函数,例如SUM(),COUNT(),MAX()。但是窗口函数又与普通的聚合函数不同,它不会对结果进行分组,使得输出中的行数与输入中的行数相同。

大概长这个样子

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

也可以理解为窗口函数是把聚合函数的值直接加在表上(不太准确)

例子:

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

image-20211202192752035

可以直接把gpa的平均值直接加在表的后面

窗口函数优点

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

窗口函数的基本语法

本文SQL数据下载:https://cloudreve.pandolar.top/s/lwUA

创建名为db_winframes,编码格式utf8

数据集介绍

三张表:员工表,部门表,采购表

员工表:员工id,姓名,员工所属部门id(department_id),工资(salary),工龄(years_worked)

部门表:部门id,部门名称

采购表:每个部门(department_id)采购的物品明细(item),物品价格(price

窗口函数关系表1

窗口函数引入

例如:想计算每三天的销售总金额,就可以使用窗口函数,以当前行为基准,选前一行,后一行,三行一组如下图所示

666

之所以称之为窗口函数,是因为好像有一个固定大小的窗框划过数据集,滑动一次取一次,对窗口内的数据进行处理。

基本用法

<window_function> OVER (...)

: 这里可以是我们之前已经学过的聚合函数,比如(COUNT(), SUM(), AVG() 等)。也可以是其他函数,比如ranking 排序函数,分析函数等,后面会介绍。

OVER(…):窗口函数的窗框通过OVER(...) 子句定义,窗口函数中很重要的部分就是通过OVER(...) 定义窗框 (开窗方式和大小)

窗口函数中OVER()子句

基本用法

AVG(salary) 统计出公司每月的工资支出
代码:

1
2
3
4
5
6
SELECT
first_name,
last_name,
salary,
SUM(salary) OVER()
FROM employee;

结果:

image-20211202194641887


统计采购表中的平均采购价格,并与明细一起显示(每件物品名称,价格)
代码:

1
2
3
4
5
SELECT
item,
price,
AVG(price) OVER()
FROM purchase;

结果:image-20211202194848918


创建报表统计每个员工的工龄和平均工龄之间的差值。

报表中包含如下字段:

员工的名字,员工的姓氏,员工的工龄,所有员工的平均工龄,员工工龄和平均工龄之间的差值
代码:

1
2
3
4
5
6
7
SELECT
first_name,
last_name,
years_worked,
AVG(years_worked) OVER(),
years_worked - AVG(years_worked) OVER() as difference
FROM employee;

结果:image-20211202194930821


统计人力资源部(部门ID为3) 的员工薪资,并将每名员工的薪资与部门平均薪资进行比较,first_namelast_namesalarydifference(员工薪资与部门平均薪资的差值)

代码:

1
2
3
4
5
6
7
select 
first_name,
last_name,
salary,
salary-AVG(salary) over() as difference
from employee
where department_id = 3;

注意:where语句的执行顺序大于窗口函数!

结果:image-20211202195032609


OVER()和COUNT()组合

需求:查询月薪超过4000的员工,并统计所有月薪超过4000的员工数量

查询结果字段:first_name, last_name, salary 和 超过4000的员工数量

代码:

1
2
3
4
5
6
7
SELECT
first_name,
last_name,
salary,
COUNT(id) OVER()
FROM employee
WHERE salary > 4000;

结果:image-20211202195638157


需求:查询人力资源部(department_id = 3)的采购情况- 查询如下字段:

id,department_id,item,price,最高采购金额,最高采购金额和每项采购的金额差值

代码:

1
2
3
4
5
6
7
8
9
SELECT
id,
department_id,
item,
price,
MAX(price) OVER() as max_price,
MAX(price) OVER() - price AS difference
FROM purchase
WHERE department_id = 3;

结果:image-20211202195745612


一句SQL中使用两个窗口函数

需求:创建报表,在purchase表基础上,添加平均价格和采购总金额两列

​ 包含如下字段:id, item, price, 平均价格和所有物品总价格

代码:

1
2
3
4
5
6
7
SELECT
id,
item,
price,
AVG(price) OVER() AS avg,
SUM(price) OVER() AS sum
FROM purchase;

结果:image-20211202195832681


和where语句的结合

查询部门id为1,2,3三个部门员工的姓名,薪水,和这三个部门员工的平均薪资

代码:

1
2
3
4
5
6
7
SELECT
first_name,
last_name,
salary,
AVG(salary) OVER() as avg
FROM employee
WHERE department_id IN (1, 2, 3);

注意:不能在WHERE子句中使用窗口函数

结果:image-20211202200027816


OVER( PARTITION BY )的使用

数据表介绍:

image-20211202200314957

partition by相比较于group by,能够在保留全部数据的基础上,只对其中某些字段做分组排序(类似excel中的操作),而group by则只保留参与分组的字段和聚合函数的结果(类似excel中的pivot透视表)

需求:查询每天的车次数量

查询结果包括:时刻表车次ID,日期,每天的车次数量

代码:

1
2
3
4
5
SELECT
id,
date,
COUNT(id) OVER(PARTITION BY date) as count
FROM journey;

结果:image-20211202200522030


需求:按车型分组,每组中满足一等座>30,二等座>180的有几条记录

查询结果包括如下字段:id, model,first_class_places, second_class_places,count 满足条件的记录数量

代码:

1
2
3
4
5
6
7
8
9
SELECT
id,
model,
first_class_places,
second_class_places,
COUNT(id) OVER (PARTITION BY model)
FROM train
WHERE first_class_places > 30
AND second_class_places > 180;

PARTITION BY传入多列

需求:查询每天,每条线路速的最快车速

查询结果包括如下字段:线路ID,日期,车型,相同线路每天的最快车速

代码:

1
2
3
4
5
6
7
8
9
SELECT
journey.id,
journey.date,
train.model,
train.max_speed,
MAX(max_speed) OVER(PARTITION BY route_id, date)
FROM journey
JOIN train
ON journey.train_id = train.id;

结果:image-20211202200726640


查询时刻表中的车次ID,运营车辆的生产日期(production_year),同一种车型的车次数量,同一线路的车次数量

代码:

1
2
3
4
5
6
7
8
SELECT
journey.id,
production_year,
COUNT(journey.id) OVER(PARTITION BY train_id) as count_train,
COUNT(journey.id) OVER(PARTITION BY route_id) as count_journey
FROM train
JOIN journey
ON train.id = journey.train_id;

结果:image-20211203104457667


查询票价表,返回id, price, date ,并统计每天的在售车票的平均价格,每天的在售车票种类。(不统计运营车辆id为5的数据)

代码:

1
2
3
4
5
6
7
8
9
10
SELECT
ticket.id,
date,
price,
AVG(price) OVER(PARTITION BY date),
COUNT(ticket.id) OVER(PARTITION BY date)
FROM ticket
JOIN journey
ON ticket.journey_id = journey.id
WHERE train_id != 5;

结果:image-20211203104515520


OVER(PARTITION BY x)的工作方式与GROUP BY类似,将x列中,所有值相同的行分到一组中

PARTITON BY 后面可以传入一列数据,也可以是多列(需要用逗号隔开列名)

代码:

1
2
3
4
5
6
7
8
9
10
SELECT
ticket.id,
date,
price,
AVG(price) OVER(PARTITION BY date),
COUNT(ticket.id) OVER(PARTITION BY date)
FROM ticket
JOIN journey
ON ticket.journey_id = journey.id
WHERE train_id != 5;

结果:image-20211203104603285


使用上一小节的员工,部门表,需求:统计每一个员工的姓名,所在部门,薪水,该部门的最低薪水,该部门的最高薪水

代码:

1
2
3
4
5
6
7
8
select first_name,
last_name,
d.name,
salary,
max(salary) over (partition by d.id) as max,
min(salary) over(partition by d.id) as min
from employee
join department d on employee.department_id = d.id;

结果:image-20211203105845911


排序函数

排序函数

image-20211203110130431


按游戏评分排序

代码:

1
2
3
4
5
6
SELECT
name,
platform,
editor_rating,
RANK() OVER(ORDER BY editor_rating) as rank_
FROM game;

rank()并列不连续

结果:image-20211203110251699


统计每个游戏的名字,分类,更新日期,更新日期序号

代码:

1
2
3
4
5
6
SELECT
name,
genre,
updated,
RANK() OVER(ORDER BY updated) as date_rank
FROM game;

结果:image-20211203110403099


题目同上

代码:

1
2
3
4
5
6
SELECT
name,
platform,
editor_rating,
DENSE_RANK() OVER(ORDER BY editor_rating) as rank_
FROM game;

结果:image-20211203111642456

DENSE_RANK()函数有并列且连续


想获取排序之后的序号,也可以通过ROW_NUMBER() 来实现,从名字上就能知道,意思是返回行号

代码:

1
2
3
4
5
6
SELECT
name,
platform,
editor_rating,
ROW_NUMBER() OVER(ORDER BY editor_rating) `row_number`
FROM game;

结果:image-20211203111734206


RANK()与ORDER BY多列排序

在列表中查找比较新,且安装包体积较小的游戏(releasedsize)

代码:

1
2
3
4
5
6
SELECT
name,
genre,
editor_rating,
RANK() OVER(ORDER BY released DESC, size ASC) `rank`
FROM game;

结果:image-20211203111816485


ORDER BY 排序都是写在窗口函数OVER() 中,窗口函数也可以和常规的ORDER BY写法一起使用,看下面的例子:

窗口函数执行顺序大于order by

代码:

1
2
3
4
5
SELECT
name,
RANK() OVER (ORDER BY editor_rating) `rank`
FROM game
ORDER BY size DESC;

结果:image-20211203111956306


在游戏销售表中添加日期排序列(按日期从近到远排序),最终结果按打分(editor_rating)排序。

代码:

1
2
3
4
5
6
7
8
SELECT
name,
price,
date,
ROW_NUMBER() OVER(ORDER BY date DESC) `row_number`
FROM game_purchase, game
WHERE game.id = game_id
ORDER BY editor_rating;

结果:image-20211203112049754


**NTILE(X)**函数

将数据分成X组,并给每组分配一个数字(1,2,3….),例如:

代码:

1
2
3
4
5
6
SELECT
name,
genre,
editor_rating,
NTILE(3) OVER (ORDER BY editor_rating DESC)
FROM game;

结果:image-20211203112210407

在上面的查询中,通过 NTILE(3) 我们根据editor_rating 的高低,将数据分成了三组,并且给每组指定了一个标记

1 这一组是评分最高的

3 这一组是评分较低的

2 这一组属于平均水平

image-20211203112233562


将所有的游戏按照升级日期降序排列分成4组,返回游戏名字,类别,更新日期,和分组序号

代码:

1
2
3
4
5
6
SELECT
name,
genre,
updated,
NTILE(4) OVER(ORDER BY updated DESC) `ntile`
FROM game;

结果image-20211203112403043


with…as临时表

查找打分排名第二的游戏

代码:

1
2
3
4
5
6
7
8
9
10
WITH ranking AS (
SELECT
name,
DENSE_RANK() OVER(ORDER BY editor_rating DESC) AS `rank`
FROM game
)

SELECT name,ranking.`rank`
FROM ranking
WHERE `rank` = 2;

结果image-20211203112658559


查询安装包大小最小的游戏,返回游戏名称,类别,安装包大小,字段:name, genre and size

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH ranking AS (
SELECT
name,
genre,
size,
RANK() OVER(ORDER BY size) AS `rank`
FROM game
)

SELECT
name,
genre,
size
FROM ranking
WHERE `rank` = 1;

结果image-20211203112714759


查询最近更新的游戏中,时间第二近的游戏,返回游戏名称,运行平台,更新时间

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH ranking AS (
SELECT
name,
platform,
updated,
dense_rank() OVER(ORDER BY updated DESC) AS `rank`
FROM game
)

SELECT
name,
platform,
updated
FROM ranking
WHERE `rank` = 2;

结果image-20211203112739323


小结:

  1. RANK() – 返回排序后的序号 rank ,有并列的情况出现时序号不连续
  2. DENSE_RANK() – 返回 连续 序号
  3. ROW_NUMBER() – 返回连续唯一的行号,与排序ORDER BY 配合返回的是连续不重复的序号
  4. NTILE(x) – 将数据分组,并为每组添加一个相同的序号
  5. 获取排序后,指定位置的数据(第一位,第二位)可以通过with…as临时表操作

window frames自定义窗口

窗口框架(Window frames) 可以以当前行为基准,精确的自定义要选取的数据范围。

例如:想选取当前行的前三行和后三行一共7行数据进行统计,相当于自定义一个固定大小的窗口,当当前行移动的时候,窗口也会随之移动

看下面的例子,我们选中了当前行的前两行和后两行,一共5行数据

image-20211203115549685


用到的表:

image-20211204102301188


ROWS()函数


取single_order表截止当天的所有订单金额

代码:

1
2
3
4
5
select id,
placed,
total_price,
sum(total_price) over(order by placed ROWS UNBOUNDED PRECEDING) as running_total
from single_order;

结果image-20211204104003991


按下单日期排序,统计订单日期,下单日期,到当前行为止的累计下单数量

代码:

1
2
3
4
select id,
placed,
count(id) over(order by placed ROWS UNBOUNDED PRECEDING) as all_
from single_order;

结果


针对每一笔订单,统计下单日期,订单总价,每5笔订单计算一次平均价格(当前行,前后各两行,按下单日期排序),并计算当前订单价格和每5笔订单平均价格的比率

代码

1
2
3
4
5
select placed,
total_price,
avg(total_price) over (order by placed ROWS between 2 preceding and 2 following) as avg,
total_price / avg(total_price)over (order by placed ROWS between 2 preceding and 2 following) as ratio
from single_order;

结果

image-20211204111621982


如果在我们定义window frames 边界时,使用了CURRENT ROW 作为上边界或者下边界,可以使用如下简略写法

ROWS UNBOUNDED PRECEDING 等价于 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

ROWS n PRECEDING 等价于 BETWEEN n PRECEDING AND CURRENT ROW

ROWS CURRENT ROW 等价于 BETWEEN CURRENT ROW AND CURRENT ROW

注意,这种简略的写法不适合 FOLLOWING的情况


统计每个订单的下单日期,总价,每4个订单的平均价格(当前行以及前3行,按下单日期排序)

代码

1
2
3
4
select placed,
total_price,
avg(total_price) over (order by placed rows between 3 preceding and current row ) as Threast
from single_order;

结果

image-20211204113001371


RANGE()函数

和使用 ROWS一样,使用RANGE 一样可以通过 BETWEEN ... AND... 来自定义窗口

在使用RANGE 时,我们一般用

RANGE UNBOUNDED PRECEDING

RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

RANGE CURRENT ROW

但是在使用RANGE 确定窗口大小是,一般 不与 n PRECEDING 或 n FOLLOWING 一起使用

使用ROWS,通过当前行计算前n行/后n行,很容易确定窗口大小

使用RANGE,是通过行值来进行判断,如果使用 3 PRECEDING 或 3 FOLLOWING 需要对当前行的值进行-3 或者+3操作,具体能选中几行很难确定,通过WINDOW FRAMES 我们希望定义的窗口大小是固定的、可预期的,但当RANGE 和 n PRECEDING 或 n FOLLOWING 具体会选中几行数据,跟随每行取值不同而发生变化,窗口大小很可能不固定。


统计累计销售金额,返回如下内容id, placed (下单日期), total_price,累计总金额sum,按下单日期由远及近,统计当前日期之后的 total_price 之和。

代码:

1
2
3
4
5
select id,
placed,
total_price,
sum(total_price) over (order by placed range between CURRENT ROW AND unbounded following) as sum
from single_order;

结果image-20211204194618475


ROWS RANGE 小总结


ROWS 按行来处理数据(例如ROW_NUMBER()函数)

RANGE按行值来处理数据(例如RANK()函数)

<window frame definition> 按如下方式定义: between <lower bound> and <upper bound>, 其中边界通过以下方式定义:

  • unbounded preceding
  • … avg(total_price) over (order by placed rows unbounded preceding) …## 截止到现在所在行的全部
  • n preceding (rows only)
  • … avg(total_price) over (order by placed rows between 3 preceding and current row ) …## 前3行到现在所在行
  • current row
  • 参考上面
  • n following (rows only)
  • … avg(total_price) over (order by placed rows between current row and 3 following ) …## 前3行到现在所在行
  • unbounded following
  • > … avg(total_price) over (order by placed rows between current row and unbounded following ) …## 现在所在行到末行

ORDER BY默认情况

在之前中,我们并没有写rangerows 这样的语句,这种情况下,会有一个默认的window frames 在工作,分两种情况:

如果在OVER(…)中没有ORDER BY子句,则所有行视为一个window frames

如果在OVER(…)中指定了ORDER BY子句,则会默认添加RANGE UNBOUNDED PRECEDING作为window frames,就是从头部到当前行。


lag ()函数lead ()函数

数据表介绍:

image-20211204200046758


LEAD(X)函数

image-20211204200135222

  • 注意:最后一列没有下一列结果所以这里显示NULL
  • LEAD() 中传入的列名与排序的列可以不同

需求: 统计id 为1的网站,每天访问的人数以及下一天访问的人数- 返回字段:day日期,users访问人数,lead

一天访问人数

代码

1
2
3
4
5
select day,
users,
lead(users) over (order by day) as lead_
from statistics
where website_id = 1;

结果

image-20211204200437332


lead函数在计算增量的时候非常有用,比如我们想比较同一列两个值的差值

代码

1
2
3
4
5
6
7
SELECT
day,
clicks,
LEAD(clicks) OVER(ORDER BY day) as old,
clicks - LEAD(clicks) OVER(ORDER BY day) as diff
FROM statistics
WHERE website_id = 2;

结果

image-20211204200608440


LEAD函数传入三个参数LEAD(x,y)

参数1 跟传入一个参数时的情况一样:一列的列名

参数2 代表了偏移量,如果传入2 就说明要以当前行为基准,向前移动两行作为返回值

参数3 lead函数也可以接收三个参数,第三个参数用来传入默认值,应用场景是当使用lead函数返回null的时候,可以用第三个参数传入的默认值进行填充

统计id为2的网站,在2016年5月1日到5月14日之间,每天的用户访问数量以及7天后的用户访问数量

需要注意,最后7行最后一列会返回-1,因为最后7行没有7日后的数据。

代码:

1
2
3
4
5
6
7
SELECT
day,
users,
LEAD(users, 7,-1) OVER(ORDER BY day) AS `lead`
FROM statistics
WHERE website_id = 2
AND day BETWEEN '2016-05-01' AND '2016-05-14';

结果

image-20211204200929512


LAG(x)函数

image-20211204201054223

LAG(x)函数与LEAD(x)用法类似,区别是,LEAD返回当前行后面的值,LAG返回当前行之前的值

另外:

LEAD(…)和LAG(…)之间可以互相替换,可以在ORDER BY的时候通过DESC来改变排序方式,使LEAD()和
LAG(…)返回相同结果,比如:

LEAD(…)OVER(ORDER BY…)
与下面的写法相似
LAG(…)OVER(ORDER BY…DESC)

再看:

LEAD(…)OVER(ORDER BY…DESC)
与下面的写法相似
LAG (..)OVER (ORDER BY …)


统计id为3的网站每天的点击数量,前一天的点击数量

代码

1
2
3
4
5
6
SELECT
day,
clicks,
LAG(clicks) OVER(ORDER BY day) as `lag`
FROM statistics
WHERE website_id = 3;

结果image-20211204201301888

==其他案例参考lead()==


综合使用:

转化率定义:转化率= 点击次数 clicks / 展示次数 impressions * 100

需求:统计id = 1的网站,5月15日至5月31日,每天点击次数 clicks,展示次数 impressions,转化率

(conversion)和前一天的转化率(previous_conversion)

代码:

1
2
3
4
5
6
7
8
SELECT day,
clicks,
impressions,
clicks / impressions * 100 AS conversion,
LAG(clicks / impressions) OVER (ORDER BY day) * 100 AS previous_conversion
FROM statistics
WHERE website_id = 1
AND day BETWEEN '2016-05-15' AND '2016-05-31';

结果

image-20211204201635842


first_value(x)与last_value(x)

first_value(x)

first_value(x)函数,从名字中能看出,返回指定列的第一个值

代码

1
2
3
4
5
6
SELECT
name,
opened,
budget,
FIRST_VALUE(budget) OVER(ORDER BY opened)
FROM website;

结果

image-20211204201807985


last_value(x)

这里有个坑!

当over子句中包含order by时,如果我们不显式定义时,SQL会自动带上默认的语句:range unbounded preceding, 意味着我们的查询范围被限定在第一行到当前行(current row)

如果想通过last_value 与order by配合得到所有数据排序后的最后一个值,需要加语句

range/rows between unbounded preceding and unbounded following

代码

1
2
3
4
5
6
7
SELECT name,
opened,
LAST_VALUE(opened) OVER (
ORDER BY opened
RANGE BETWEEN UNBOUNDED preceding and UNBOUNDED FOLLOWING
) AS `last_value`
FROM website;

结果

image-20211204202249627


综合案例:

统计id为1的网站,每日的访问用户数,最后一天的访问用户数,每日用户数与最后一天用户数的差值

代码

1
2
3
4
5
6
7
8
9
10
11
12
SELECT day,
users,
LAST_VALUE(users) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) `last_day_users`,
users - LAST_VALUE(users) OVER (
ORDER BY day
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) `diff`
FROM statistics
WHERE website_id = 1;

结果

image-20211204202418186


nth_value(x,n) 函数

简介:NTH_VALUE(x,n) 函数返回 x列,按指定顺序的第n个值

代码

1
2
3
4
5
6
7
8
9
10
11
## 上面的SQL将数据按照开业日期排序,NTH_VALUE(opened,2) 返回开业日期排在第二位的值
## 需要注意,我们需要调整window frame 否则某些情况下不能返回正确的数据
## 提示:可以在排序的时候加上DESC 调整排序的顺序,配合 NTH_VALUE(x,n) 在某些场景下更加方便
select day,
revenue,
nth_value(revenue, 3) over (order by revenue desc
rows between unbounded preceding and unbounded following)
as 3rd
from statistics
where website_id = 2
and day between '2016-05-15' and '2016-05-31';

结果

image-20211205100957973


代码

1
2
3
4
5
6
7
8
-- 统计id为2的网站的收入情况,在5月15和5月31日之间,每天的收入,以及这半个月内的第三高的日收入金额\
select website_id,
revenue,
first_value(revenue) over (order by revenue) as low,
last_value(revenue) over (order by revenue
rows between unbounded preceding and unbounded following) as hig
from statistics
where day = '2016-05-14';

结果

image-20211205101024350


代码

1
2
3
4
5
6
7
8
9
10
## 需求:统计id为1的网站的点击量,返回如下字段
## - 日期 day, 点击量 clicks ,5月点击量的中位数
## - 提示:5月一共31天,将点击量按顺序排列,第16位点击量即为中位数
select day,
clicks,
nth_value(clicks, 16) over (order by clicks desc
rows between unbounded preceding and unbounded following
) as madian
from statistics
where website_id = 1;

结果

image-20211205101038265


代码

1
2
3
4
5
6
7
8
9
10
## 需求:统计id为3的网站每天点击的情况,返回如下字段
## - 日期`day`,点击量`clicks`,最高点击量和当天点击量的比例`ratio`(用整数表示)

select day,
clicks,
round(clicks / last_value(clicks) over (
order by clicks
rows between unbounded preceding and unbounded following) * 100) as ratio
from statistics
where website_id = 3;

结果

image-20211205101055182


partition by 与 order by 详解

在分组聚合计算时,数据的顺序并不会影响计算结果

如何将PARTITION BY 与排序函数,window frames 和 分析函数组合使用,此时需要将PARTITION BY 与 ORDER BY组合起来,需要注意,PARTITION BY 需要在 ORDER BY前面


数据表详解:

image-20211205101436485


与常见函数结合

代码

1
2
3
4
5
6
## 每家商店所在的国家,城市,评分以及每个国家商店的平均评分
select country,
city,
rating,
avg(rating) over (partition by country) as avg
from store;

结果

image-20211205101807454


代码:

1
2
3
4
5
6
## 需求: 统计每个商店的收入情况,返回字段如下:商店`id` ,日期`day`,收入`revenue`,每个商店的平均收入`avg_revenue`
select store_id,
day,
revenue,
avg(revenue) over (partition by store_id) as avg
from sales;

结果

image-20211205102105280


rank和partition by 结合

代码

1
2
3
4
5
6
## 用国家分组,对不同国家的商店按评分分别排序
select id,
country,
city,
rank() over (partition by country order by rating ) as rank_
from store;

结果

image-20211205103854861


代码

1
2
3
4
5
6
7
8
## 需求:统计2016年8月10日至8月14日之间的销售情况,
## 返回如下字段- `store_id`, `day`,顾客数量`customers`, 每个商店在该段时间内按每日顾客数量排名(降序排列)
select store_id,
day,
customers,
rank() over (partition by store_id order by customers desc ) as rank_
from sales
where day between '2016-08-10' and '2016-08-14';

结果

image-20211205105711506


ntitle和partition by 结合

代码

1
2
3
4
5
6
7
8
9
## 将数据按国家分组,进一步按照开业的时间远近划分成两组,一组开业时间较长,另一组开业时间较短。

select id,
country,
city,
opening_day,
rating,
ntile(2) over (partition by country order by opening_day) as group_
from store;

结果image-20211205110049133


代码

1
2
3
4
5
6
7
8
9
10
11
## 统计2016年8月1日至8月10日之间的销售额,
## 查询结果返回:商店ID => `store_id`,日期`day` ,收入`revenue`,
## 并将每个商店的销售数据按当日销售额分为4组
select store_id,
day,
revenue,
transactions,
customers,
ntile(4) over (partition by store_id order by revenue desc ) as title_
from sales
WHERE day BETWEEN '2016-08-01' AND '2016-08-10';

结果image-20211205110453523


在cte中使用partition by order by

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
## 通过在CTE中使用窗口函数来获取每个国家/地区按评分的商店排名
## 在外部查询中直接查询每个国家分数最高的店铺所在城市
with ranking AS (
select id,
country,
city,
opening_day,
rating,
rank() over (partition by country order by rating desc ) as rank_
from store
)
select country,
city,
rank_
from ranking
where rank_ = 1;

结果

image-20211205111128335


代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 查询每个商店收入最高的那一天的具体日期,返回商店store id,收入revenue,日期day
with ranking as (
select store_id,
day,
revenue,
transactions,
customers,
rank() over (partition by store_id order by revenue desc ) as rank_
from sales
)
select store_id,
revenue,
day
from ranking
where rank_ = 1;

结果

image-20211205111608095


partition by order by 和 window frames组合

代码

1
2
3
4
5
6
7
8
9
10
## 分析2016年8月1日到8月7日的销售数据,统计到当前日期为止的单日最高销售收入
## 返回字段:商店id`store_id`,日期 `day`,销售收入 `revenue` 和 最佳销售收入 best revenue

select store_id, day, revenue,
max(revenue) over (partition by store_id
order by day
rows unbounded preceding) as best

from sales
WHERE day BETWEEN '2016-08-01' AND '2016-08-07';

结果

image-20211205112422744


代码

1
2
3
4
5
6
7
8
9
10
11
12
## 统计2016年8月1日至2016年8月10日期间,每间商店的五日平均交易笔数(以当前行为基准,从两天前到两天后共五天)
## 返回字段:商店ID => `store_id`,日期` day`,交易笔数`transactions`,五日平均交易笔数 5day_avg
select store_id,
day,
transactions,
avg(transactions) over (
partition by store_id
order by day
rows between 2 preceding and 2 following
) as 5_day
from sales
WHERE day BETWEEN '2016-08-01' AND '2016-08-10';

结果

image-20211205113142472


代码

1
2
3
4
5
6
7
8
9
10
#分析销售情况,返回如下内容:商店ID => `store_id`,日期`date`,收入`revenue`
## 和总部应收的未来现金流量(即该商店的总收入,从表中的当天到最后一天收入求和)
select store_id,
day,
revenue,
sum(revenue) over (partition by store_id
order by day
rows between current row and unbounded following
) as cash_receivable
from sales;

结果image-20211205112748369


分析函数 与 partition by order by组合


代码:

1
2
3
4
5
6
7
## 将数据按国家分组组内按商店开业日期排序,查询了每个店铺开店的日期,以及同一国家下一个开业的商店所在城市:
select id,
country,
city,
opening_day,
lead(city,1,'Nan') over (partition by country order by opening_day) AS Lead_
from store;

结果:

image-20211205113825369


代码:

1
2
3
4
5
6
7
8
## 按国家分组,每组内按开业日期排序,查询了到当前日期位置,该国家以开业商店的最高得分。
select id,
country,
city,
opening_day,
rating,
max(rating) over (partition by country order by opening_day rows unbounded preceding) as max_
from store;

结果:

image-20211205120550494


代码:

1
2
3
4
5
6
## 返回每个商店的评分,还返回了该店铺所在国家评分最高的商店所在城市
select country,
city,
rating,
first_value(city) over (partition by country order by rating DESC) as first_
from store;

结果:image-20211205114119344


代码:

1
2
3
4
5
6
7
8
## 需求:统计2016年8月5日 到 2016年8月10日之间,每天的单店交易笔数,前一天交易笔数,前一天和当天的交易笔数的差值
## 返回字段:商店ID`store_id`, 日期`day`, 交易笔数`transactions`, 前一天交易笔数`lag`, 差值`diff`
select store_id,
day,
transactions,
lag(transactions) over (partition by store_id order by day) as lag_,
transactions - lag(transactions) over (partition by store_id order by day) as diff
from sales;

结果:

image-20211205114401952


代码:

1
2
3
4
5
6
7
8
## 统计8月1日到8月3日的销售数据找到每个商店在这段时间内销售额最高的一天,返回如下信息:
## 商店ID => `store_id`, 日期 `day`, 当日销售收入 `revenue` , 销售收入最高一天的日期`best_revenue_day`
select store_id,
day,
revenue,
first_value(day) over (partition by store_id order by revenue desc ) as best_day
from sales
where day between '2016-08-01' and '2016-08-03';

结果:image-20211205121733482


练习表

image-20211205122435348

代码:

1
2
3
4
5
6
7
8
9
10
11
## 统计每一天,免费维修数量排名第二的手机型号,返回日期  `day` ,手机型号`phone`
with ranking as (
select day,
phone,
dense_rank() over (partition by day order by free_repairs) as max_free
from repairs
)
select day,
phone
from ranking
where max_free = 2;

结果:

image-20211205141008900


窗口函数避坑指南

image-20211205145129954

不能直接使用窗口函数的子句

我们在第一小节介绍过,where 条件中不能使用窗口函数,原因是sql的执行顺序决定了窗口函数实在where子句之后执行的,具体执行顺序如下:
from
where
group by
聚合函数
having
窗口函数
select
distinct
union
order by
offset
limit

不能在where子句中使用窗口函数

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
## 错误代码写法:
SELECT
id,
final_price
FROM auction
WHERE final_price > AVG(final_price) OVER();

## 正确代码写法
SELECT
id,
final_price
FROM (
SELECT
id,
final_price,
AVG(final_price) OVER() AS avg_final_price
FROM auction) c
WHERE final_price > avg_final_price

不能在having子句中使用窗口函数

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
## 错误写法
SELECT
country,
AVG(final_price)
FROM auction
GROUP BY country
HAVING AVG(final_price) > AVG(final_price) OVER ();

## 正确写法
SELECT
country,
AVG(final_price)
FROM auction
GROUP BY country
HAVING AVG(final_price) > (SELECT AVG(final_price) FROM auction);

不能在group by子句中使用窗口函数

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
## 错误写法
SELECT
NTILE(4) OVER(ORDER BY views DESC) AS quartile,
MIN(views),
MAX(views)
FROM auction
GROUP BY NTILE(4) OVER(ORDER BY views DESC);

## 正确写法
SELECT
quartile,
MIN(views),
MAX(views)
FROM
(SELECT
views,
ntile(4) OVER(ORDER BY views DESC) AS quartile
FROM auction) c
GROUP BY quartile;

结果:

不能直接使用窗口函数的子句

在order by中使用窗口函数


代码:

1
2
3
4
5
6
## 将所有的拍卖按照浏览量降序排列,并均分成4组,按照每组编号升序排列,返回字段: `id`, `views` 和 分组情况( `quartile`)
select id,
views,
ntile(4) over (order by views desc ) as quartile
from auction
order by quartile;

结果:

image-20211205153233207


在group by中使用窗口函数

代码:

1
2
3
4
5
6
7
8
9
## 需求:将拍卖数据按国家分组,查询如下字段:
## - 国家 `country`
## - 每组最少参与人数 `min`
## - 所有组最少参与人数的平均值 `avg`
select country,
min(participants)as min_,
avg(min(participants)) over()as avg
from auction
group by country;

结果:

image-20211205160455076


Rank时使用聚合函数

必须在group by后才能使用rank+聚合

代码:

1
2
3
4
5
6
## 国家进行分组,计算了每个国家的拍卖次数,再根据拍卖次数对国家进行排名
select country,
count(id),
rank() over (order by count(id) desc ) as rank_
from auction
group by country;

结果:image-20211205194112017


代码:

1
2
3
4
5
6
7
## 按拍卖结束日期`ended`分组,计算每组平均浏览量(`views`),并按平均浏览量对所有组排名返回序号`rank`
## 返回字段:拍卖结束日期`ended`,平均浏览量`avg`,每组排名`rank`
select ended,
avg(views) as avg_,
rank() over (order by avg(views) desc ) as rank_
from auction
group by ended;

结果:image-20211205194332377

利用GROUP BY计算环比


代码:

1
2
3
4
5
6
7
8
9
10
## 利用GROUP BY按结束拍卖日期对所有拍卖进行分组,对每天结束的所有拍卖的最终成交价格求和,利用LAG函数计算前一天的全天最终成交价格
select ended,
sum(final_price),
lag(sum(final_price)) over (order by ended) as lag_,
sum(final_price) - lag(sum(final_price)) over (order by ended) as diff,
(sum(final_price) - lag(sum(final_price)) over (order by ended))
/ lag(sum(final_price)) over (order by ended) * 100 as chainRatio
from auction
group by ended
order by ended;

结果:

image-20211205162527390


代码:

1
2
3
4
5
6
7
8
9
10
11
12
## 需求:按拍卖结束日期`ended`分组分析所有拍卖的浏览数据`views`,返回如下字段:
## 每组的拍卖结束日期`ended`
## 每组的总浏览量 `sum`
## 每组的前一组总浏览量 `previous_day`
## 比较结束日期相邻两天浏览量的差值 `delta`
select ended,
sum(views) as sum_,
lag(sum(views)) over (order by ended) as previous_day,
sum(views) - lag(sum(views))
over (order by ended) as delta
from auction
group by ended;

结果:

image-20211205163057387


对group by分组后的数据使用 partition by

group by 先执行
partition by是与窗口函数一起执行

group by分组后的数据进一步分组(partition by)

代码:

1
2
3
4
5
6
7
8
9
10
11
## 对分组后的数据再进行分组
## 我们先将所有的数据按照国家country 和拍卖结束时间ended 分组,然后显示了国家名字和拍卖结束日期
## 接下来SUM(views) AS views_on_day根据 GROUP BY 分组结果(先国家,后日期),对每组的浏览量求和
## SUM(SUM(views)) OVER(PARTITION BY country) AS views_country 这是一个窗口函数,只对国家进行分组,计算每个国家拍卖的总浏览量
select country,
ended,
sum(views) as one_day_views,
sum(sum(views)) over (partition by country) as views_country
from auction
group by country,ended
order by country,ended;

结果:image-20211205170316635


代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
## 将所有数据按照类别`category_id`和拍卖结束日期`ended`分组,返回
## 类别ID,`category_id`
## 结束日期`ended`
## 当前类别,当日结束的拍卖的平均成交价格 `daily_avg_final_price`
## 所有类别日平均成交价格最大值 `daily_max_avg`
select category_id,
ended,
avg(final_price) as daily_avg_final_price,
max(avg(final_price))
over (partition by category_id) as daily_max_avg
from auction
group by category_id, ended
order by category_id, ended;

结果:

image-20211205170856465


窗口函数综合练习

image-20211205194906140

代码:

1
2
3
4
5
6
7
8
9
10
## 把所有的书按照评分从低分到高分平均分成4组,给每组分配一个编号(`bucket`),
## 分别取出每组最低`min`和最高`max`的得分,返回字段:`bucket` ,`min`, `max`
select id,
bucket,
min(rating) over (partition by bucket) as min,
max(rating) over (partition by bucket) as max
from (select id,
rating,
ntile(4) over (order by rating) as bucket
from book) as rank_;

结果:

image-20211205200303467


代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
## 统计作者的出版数量,对每一个作者显示:
## 作者id`author_id`
## 这个作者出版了多少本书( `number_of_books`)
## 按照出版书籍多少降序排列的排名`rank`
with rank_ as (
select author_id,
count(author_id) over (partition by author_id ) as number_of_books
from book
)
select author_id,
number_of_books,
rank() over (order by number_of_books) as rank_
from rank_
group by author_id;

结果:

image-20211205201321747


代码:

1
2
3
4
5
6
7
8
9
10
11
## 分析每年书籍出版情况- 发行年`publish_year`- 当年发行了多少本书 `count`- 前一年发行了多少本书 `lag`
with number as (
select publish_year,
count(publish_year) over (partition by publish_year) as count_
from book
)
select publish_year,
count_,
lag(count_) over ()
from number
group by publish_year;

结果:

image-20211205202308177