准备工作
一：窗口函数概况
1.1 窗口函数说明
1.2 窗口范围说明
1.2.1 窗口范围取值可选项
1.2.2 默认窗口范围含义
思考一：如何理解省略order by的情况，不能指定窗口范围？
二：窗口函数分类和特性
2.1 窗口函数分类
2.2 窗口函数功能和常见应用概括
2.2.1 功能
2.2.2 应用
三：窗口函数应用场景（基础使用）
3.1 聚合型窗口函数
3.1.1 情境一：购买物品的用户及其总人数
3.1.2 情景二：用户购买物品明细及每周各种类型用户购买总数量(补分析列)。
3.2 分析型（排序）窗口函数
3.2.1情景三:每种用户类型销量排行第一的用户及其明细（分组排名）
思考二：窗口排序遇到null
3.2.2 情景四：每种用户类型销量排名前2的用户明细（top n）
3.1.4 情景五：连续n天登陆
情景六：给定数字的频率查询中位数
3.3 取值型窗口函数
3.3.1 准备工作
3.3.2 情景七：找到车辆上一次锁车记录
3.3.3 情景八：那么如何求某个省份围栏、某小时、某辆车的最长未被骑行时间。(多次窗口使用)
思考三：多次窗口使用能否改成一次窗口和group by结合得到大致数据？
思考四：多次窗口使用能否改成一次窗口和group by结合得到所有明细数据？
3.3.4 情景九：连续出现的数字
四：进阶使用
4.1 窗口函数与group by异同点
4.1.1 窗口函数与group by 区别
4.1.2 group by去重
4.2 collect_list + partition by的特殊用法
4.2.1 问题提出
4.2.2 解决思路
4.2.3 实际解决
4.2.4 延伸到风控业务的实际用法
4.3 cube&grouping set这块
五：窗口函数底层运行原理
六：其他注意事项
思考问题：
思考一：如何理解省略order by的情况，不能指定窗口范围？
思考二：窗口排序遇到null
准备工作
已经有测试表：order_detail.本文接下来所有测试样例如果未特殊说明均基于下面order_detail表。
user_id
(用户ID)
order_date
（订单日期）
user_type
（用户类型）
sales
（销量）
1
qishili
2022-07-03
new
5
2
wangshi
2022-07-01
old
2
3
liiu
2022-07-01
new
1
4
lilisi
2022-07-05
new
5
5
lisi
2022-07-02
old
1
6
wutong
2022-07-02
new
6
7
qibaqiu
2022-07-05
new
1
8
liwei
2022-07-02
old
3
9
zhangsa
2022-07-02
new
2
10
wanger
2022-07-02
new
3
一：窗口函数概况
1.1 窗口函数说明
窗口函数简单的说就是在执行聚合函数的时候指定一个操作窗口，这个窗口由over来进行控制。接下来重点介绍一下over()函数。
over():指定分析函数工作的数据窗口大小，这个大小可能会随着行的变化而变化。
其基本语法如下：<分析函数> over ( partition by <用于分组的列名>order by <用于排序的列名> desc/ascrows between 开始行 and 结束行 )
样例1--窗口函数基本使用
select
user_id,
user_type,
sales,
count(user_id) over(partition by user_type order by sales desc
rows between unbounded preceding and current row) as cnt
from order_detail
可以看到over()里面一共有3部分组成：分区、排序、指定窗口范围，注意这三部分可以组合使用，也可以不使用。
- partition by可以理解为分组。over(partition by 列)搭配分析函数时候（可以接多个列，表示按照这些列分组），分析函数是按照每一组的数据进行分析计算的。
- order by是在窗口内进行排序，desc/asc可以进行选择使用。
- rows between 开始位置 and 结束位置，指定数据范围。 -- 例如常使用的窗口范围是rows between unbounded preceding and current row.常用该窗口来累加计算。
1.2 窗口范围说明
1.2.1 窗口范围取值可选项
1）范围取值
【a:rows+参数来控制范围】
默认值（不写）：这个其实是最常用的，下面会进行不同情况下默认值总结
preceding:往前；
following: 往后
current row:当前行
unbounded :起点（一般结合preceding,following使用）
unbounded preceding: 表示该窗口最前面的行（起点）
unbounded following:该窗口最后面的行（终点）
样例2--窗口范围一些完整写法
rows between unbounded preceding and current row --(表示从窗口起点到当前行)
rows between unbounded preceding and unbounded following--(表示从窗口起点到终点)
rows between 2 preceding and 1 following --(表示往前2行到往后1行)
rows between 2 preceding and 1 current row --(表示往前两行到当前行)
rows between current row and unbounded following --(表示当前行到终点)
【b.range between来控制范围】
range 表示的是值， 表示比这个值小n的行，比这个值大n的行即range between 是以当前值为锚点进行计算。
eg: sum(sales) range between 100 preceding and 200 following
【c.通过窗口函数来控制范围】
- lag(col, n, default_val): 往前第n行数据，没有数据的话用default_value代替
- lead(col, n, default_val):往后第n行数据，没有数据的话用default_value代替
- ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型.
2）为了解释rows between unbounded preceding and current row和rows between unbounded preceding and unbounded following，我们举例说明：
样例3--rows between and取值范围区别
select
user_id,
user_type,
sales,
count(user_id) over(partition by user_type order by sales desc rows between unbounded preceding and current row) as current_cnt, -- 样例1中的代码： 窗口取到当前行
count(user_id) over(partition by user_type order by sales desc rows between unbounded preceding and unbounded following) as unbounded_following_cnt -- 窗口取到所有行
from order_detail
样例3测试结果（如下表），可以看出：
- 当指定范围是从头到当前行，分析函数得到的这个局部范围（动态变化的）的分析值，例如current_cnt，每一行记录得到的都是一个变化的值（同一个分组内不同行值都是不同的）。（再具体一点是到每个组中的某一行记录）
- 当指定范围是开头到末尾行，分析函数得到的是全局范围（固定的）的分析值，例如unbounded_following_cnt，同一个组每一行记录都是一个相等值（指同一个分组内哈，不同分组值还是不同的）。
user_id
order_date
user_type
sales
current_cnt
unbounded_following_cnt
1
wutong
2022-07-01
new
6
1
7
2
lilisi
2022-07-02
new
5
3
7
3
qishili
2022-07-02
new
5
3
7
4
wanger
2022-07-01
new
3
4
7
5
zhangsa
2022-07-03
new
2
5
7
6
qibaqiu
2022-07-06
new
1
7
7
7
liiu
2022-07-05
new
1
7
7
8
liwei
2022-07-05
old
3
1
3
9
wangshi
2022-07-02
old
2
2
3
10
lisi
2022-07-01
old
1
3
3
1.2.2 默认窗口范围含义
1）情况一：当over()里面有partition by和order by的时候，但是缺少rows between and这部分，即不写这块，也就是我们常说的默认值,其分析函数对应的范围是同一组从开头到当前值。
上面的默认窗口范围和样例2中的窗口范围区别：
样例4--既有partition by和order by情况下默认值窗口范围和常见窗口范围比较
select
user_id,
user_type,
sales,
sum(sales) over(partition by user_type order by sales desc
rows between unbounded preceding and current row) as current_cnt,
-- 样例1中的代码： 窗口取到各组当前行（当前这行的记录）
sum(sales) over(partition by user_type order by sales desc) as default_cnt,
-- 默认也是窗口取到各组当前行，但是更准确的说应该是取到各组当前值，因为order by之后，
-- 当排序的字段相同的时候，
-- 相同的字段会一起被计算出来，所以说是取到当前“值/行”。会把值相同的进行一起计算，
-- 得到某一个结果。
sum(sales) over(partition by user_type order by sales desc rows between
unbounded preceding and unbounded following) as unbounded_following_cnt
-- 窗口取到所有行 ，各个分组里面也取所有行，各个组里面只有同一个值
from order_detail
样例4测试结果（如下表），可以清楚看到:
- rows between unbounded preceding and current row其分析计算出来的结果是针对开头到当前行的范围，所以同一组的各个记录对应的分析值也不一样；
- 而默认不写窗口范围的，其分析是针对开头到当前值的范围，因为当前值可以对应多条记录，需要把这几天记录一起去分析得到一个明确的结果，然后这个值所对应的这几条记录最终都是这同一个明确的结果。
- 而窗口范围明确写开头到结尾，那么同一个组内所有记录都是同一个结果，分析的是同一个组的所有数据。
user_id
user_type
sales
current_cnt
default_cnt
unbounded_following_cnt
1
wutong
new
6
1
1
7
2
lilisi
new
5
2
3
7
3
qishili
new
5
3
3
7
4
wanger
new
3
4
4
7
5
zhangsa
new
2
5
5
7
6
liiu
new
1
6
7
7
7
qibaqiu
new
1
7
7
7
8
liwei
old
3
1
1
3
9
wangshi
old
2
2
2
3
10
lisi
old
1
3
3
3
2）情况二：当over里面只有partiton by,没有order by的时候，注意这个时候写的窗口范围不能写样例2的范围，会直接报错，只能按照默认范围来。默认范围是从分组内开头到结尾的记录即ows between unbounded preceding and unbounded following，虽然范围是这个，但是sql代码不能写上ows between unbounded preceding and unbounded following，只能什么也不写。（因为没有order by每次数据记录返回来的顺序可能不一致）。
样例5--窗口里面只有partition by
-- 只partition by
select
user_id,
user_type,
sales,
sum(sales) over(partition by user_type ) as default_cnt -- 默认
-- sum(sales) over(partition by user_type rows between unbounded
-- preceding and unbounded following) as unbounded_following_cnt
-- 直接报错，不能写出窗口范围。
from order_detail
样例5测试结果：可以看待窗口范围是各个分组里面开头到结尾的数据，各个组分析得到的结果一样。
1
zhangsa
new
2
23
2
wanger
new
3
23
3
liiu
new
1
23
4
qibaqiu
new
1
23
5
wutong
new
6
23
6
lilisi
new
5
23
7
qishili
new
5
23
8
lisi
old
1
6
9
wangshi
old
2
6
10
liwei
old
3
6
3）情况三：当over()里面只有order by的时候，这个时候窗口范围的控制就和情况一中一样，可以，只不过这个时候就只有一个分组了，全部数据放到一个分组里面！
常用的窗口范围和默认窗口范围见情况一，这里不再赘述。
样例6--窗口里面没有partition by,有order by
select
user_id,
user_type,
sales,
sum(sales) over(order by sales desc rows between unbounded
preceding and current row) as current_cnt,
-- 样例1中的代码： 窗口取到当前行
sum(sales) over(order by sales desc) as default_cnt,
-- 默认:取到当前“行/值”
sum(sales) over(order by sales desc rows between unbounded preceding
and unbounded following) as unbounded_following_cnt
-- 窗口取到所有行
from order_detail
样例6测试结果
user_id
user_type
sales
current_cnt
default_cnt
unbounded_following_cnt
1
wutong
new
6
6
6
29
2
lilisi
new
5
11
16
29
3
qishili
new
5
16
16
29
4
wanger
new
3
19
22
29
5
liwei
old
3
22
22
29
6
zhangsa
new
2
24
26
29
7
wangshi
old
2
26
26
29
8
lisi
old
1
27
29
29
9
liiu
new
1
28
29
29
10
qibaqiu
new
1
29
29
29
4）情况四：当窗口里面既没有partition by,也没有order by的时候
样例7--既没有partition by也没有order by的时候
select
user_id,
user_type,
sales,
-- sum(sales) over(rows between unbounded preceding and current row)
-- as current_cnt, -- 错误写法 ，也就是说这种情况下，我们也不能自己写窗口范围。
sum(sales) over() as sales_cnt -- 默认 正确写法 范围默认为开头到结尾。
-- sum(sales) over(rows between unbounded preceding and
--unbounded following) as unbounded_following_cnt -- 错误写法
from order_detail
5)对上面5种情况进行初步总结：
- 如果over()里面没有order by，那么我们就不能指定窗口范围，只能使用默认值（代码中row between end这处什么也不写），此时的默认值代表的范围是各分组的开头到结尾数据；
- 反之，如果over()里面🈶️order by,那么我们就可以指定窗口范围，如果不指定窗口范围（默认值），此时默认值的范围代表分组里面的的开头到当前值（当前“行”）的数据范围。
- 如果over里面没有partition by,此时就只有一个分组，同上一样。
思考一：如何理解省略order by的情况，不能指定窗口范围？
在任何并行系统中，如果order by子句未生成唯一排序和总排序，则行的顺序是不确定的，也就是说，如果order by 表达式生成重复的值（部分排序），则这些行的返回顺序可能会有差别，也就是说窗口函数可能返回意外或不一致的结果。
二：窗口函数分类和特性
2.1 窗口函数分类
1）按照窗口来分
可以分为静态窗口函数、动态窗口函数。其中静态窗口函数主要是指排序函数；而动态窗口函数主要分为累计计算函数和偏分析函数。其中窗口数据集这块对应文章第一章的详细描述。
窗口函数窗口函数名窗口数据集静态窗口函数动态窗口函数
排序函数row_number():当前行在组内的排序，eg:1,2,3,4,5
dense_rank()不间断组内排序，eg:1,1,2,3,3
rank():间断的组内排序，eg:1,1,3,4,5
累计计算函数偏移分析函数sum()count()avg()max()min()percent_rank():累计百分比cume_dist():累计分布值first_value():返回组内第一个值last_value():返回组内的最后一个值nth_value():返回组内的第n行lag():从当前行往上偏移第n行，默认为nulllead():从当前行开始向下偏移第n行，默认为nullntile():返回当前行在组内截止当前行的第n行分组参数：partition by排序参数：order by窗口参数:row between ...andover()
2）按照函数功能
可以将窗口函数分为：聚合型窗口函数、分析型窗口函数、取值型窗口函数。
窗口函数聚合型取值型分析型聚合函数：sum() /max() /min() /avg() /count()排序函数和占比函数：row_number() / dense_rank() / rank() / percent_rank() /cume_dist() 取值：lag() / lead() / first_value() / last_value()
3）举例扩展一下窗口函数中三个排序函数区别
样例8--三个排序函数区别（每种用户类型销量排名）
select
user_id,
user_type,
sales,
row_number() over(partition by user_type order by sales) as rn,
dense_rank() over(partition by user_type order by sales) as rd,
rank() over(partition by user_type order by sales) as rr
from order_detail
样例8测试结果：
user_id
user_type
sales
rn
rd
rr
1
liiu
new
1
1
1
1
2
qibaqiu
new
1
2
1
1
3
zhangsa
new
2
3
2
3
4
wanger
new
3
4
3
4
5
lilisi
new
5
5
4
5
6
qishili
new
5
6
4
5
7
wutong
new
6
7
5
7
8
lisi
old
1
1
1
1
9
wangshi
old
2
2
2
2
10
liwei
old
3
3
3
3
2.2 窗口函数功能和常见应用概括
2.2.1 功能
1）窗口函数同时具有分组和排序的功能；
2）不减少原有表的行数。即窗口函数可以理解为在本行内做运算，每一行对应一行的值。
3）可指定窗口数据范围
2.2.2 应用
1）分组排名问题
2）top n问题
3）连续登陆问题
4）加一分析结果列
5）累计问题
6）上一个下一个记录问题
三：窗口函数应用场景（基础使用）
3.1 聚合型窗口函数
sum,count()这些很常见，其功能我们不再赘述
3.1.1 情境一：购买物品的用户及其总人数
样例9--查询2022年7月份购买物品的用户及其总人数
-- 正确方法：总人数是指7月份购买物品的总人数。
select
user_id,
count(1) over() as 7_mounth_all_cnt
from order_detail
where substring(order_date,1,7) = '2022-07'
-- 错误理解；不是每个人购买物品总人数。
select
user_id,
count(1) as 7_mounth_all_cnt
from order_detail
where substring(order_date,1,7) = '2022-07'
group by user_id
样例9测试结果：
user_id
7_mounth_all_cnt
1
qibaqiu
10
2
zhangsa
10
3
lisi
10
4
wanger
10
5
liiu
10
6
wangshi
10
7
liwei
10
8
wutong
10
9
lilisi
10
10
qishili
10
3.1.2 情景二：**用户购买物品明细及每周各种类型用户购买总数量(补分析列)**。
样例10:用户购买物品明细以及每周各种类型用户购买总数量
select
user_id,
user_type,
sales,
sum(sales) over(partition by week(order_date),user_type) as sales_s
from order_detail
样例10测试结果如下，像这样既要明细又需要聚合值的，显然使用group by是做不到的，要使用窗口函数。
user_id
user_type
sales
sales_s
wanger
new
3
20
2
qibaqiu
new
1
20
3
wutong
new
6
20
4
lilisi
new
5
20
5
qishili
new
5
20
6
lisi
old
1
3
7
wangshi
old
2
3
8
zhangsa
new
2
3
9
liiu
new
1
3
10
liwei
old
3
3
3.2 分析型（排序）窗口函数
在第二章中已经对常见排序函数进行总结过，可参考。
3.2.1情景三:每种用户类型销量排行第一的用户及其明细（分组排名）
可以参照第二章中的样例7。
样例11：每种用户类型销量排行第一的用户及其明细（分组排序）
-- 把窗口函数放在自查询里面得到一个每条记录的排名
select
*
from
(
select
user_id,
user_type,
sales,
row_number() over(partition by user_type order by sales desc) as rc
from order_detail
)t1
where t1.rc = 1
样例11测试结果：
user_id
user_type
sales
rc
1
wutong
new
6
1
2
liwei
old
3
1
思考二：窗口排序遇到null
1）窗口中order by的 时候，使用desc降序排列的时候，null值排在首位；
使用asc升序的时候，null值排在末尾；
2）如何控制null的位置：
nulls last和 nulls first.
如下图：此样例建议等看完第四章中部分论述的背景，再回来看这个
rrow_number() over(partition by fence,hour(lock_time),bike_id order by time_gap desc nulls last) as rc
样例12：排序的时候不需要先对null值进行处理，直接使用nulls last会很省事！
select -- 找到分组中最大间隔
fence,
hourtime,
bike_id,
time_gap
from
( -- 分组对时间间隔进行排序
select
fence,
bike_id,
hour(lock_time) as hourtime,
time_gap,
-- 发现time_gap存在很多null的情况，在参与order by排序的时候会影响排序结果，所以得到结果不准确
row_number() over(partition by fence,hour(lock_time),bike_id order by time_gap desc nulls last) as rc
from
( -- 分组找到本次记录的上次记录的锁车时间和事件间隔
select
fence,
bike_id,
hour(lock_time),
unlock_time,
lock_time,
lag(unlock_time,1,null) over(partition by fence,hour(lock_time),bike_id) as last_unlock_time,
(unix_timestamp(unlock_time) - unix_timestamp(lag(lock_time,1,null) over(partition by fence,hour(lock_time),bike_id))) as time_gap
from bike_hour_inc
)t1
)t2
where t2.rc =1
order by fence,hourtime,bike_id
3.2.2 情景四：每种用户类型销量排名前2的用户明细（top n）
可以参照第二章中的样例7
样例13:每种用户类型销量排名前2的用户明细
-- 自查询使用窗口函数得到分组里面每条记录排名，外查询进行排名的约束
select
*
from
(
select
user_id,
user_type,
sales,
row_number() over(partition by user_type order by sales desc) as rc
from order_detail
)t1
where t1.rc <=2
样例13测试结果：
user_id
user_type
sales
rc
1
wutong
new
6
1
2
lilisi
new
5
2
3
liwei
old
3
1
4
wangshi
old
2
2
3.1.4 情景五：连续n天登陆
给定测试数据集
7
2022-06-02
7
2022-06-10
7
2022-06-03
7
2022-05-30
7
2022-05-31
7
2022-06-02
1
2022-06-07
7
2022-06-01
1
2022-05-30
sql逻辑
样例14:连续n天登陆（去重、排序、间隔、分组、约束）
select
login_id
from
( select
login_id,
login_date,
-- row_number() over(partition by login_id order by login_date) as rc -- 对登陆日期进行排名
-- 找到登陆日期与时间排名之间的间隔
date_sub(login_date,row_number() over(partition by login_id order by login_date) ) as time_gap
from
(
-- 每个人一天可能不止登陆一次，需要去重
select
login_id,
login_date
from login_table
group by login_id,login_date
)t1
)t2
group by t2.login_id,t2.time_gap -- 以用户和时间间隔进行分组
having count(1) >= 5 -- 每个用户分组和时间间隔分组里面数据记录大于等于5的。
测试结果login_id = 7;
情景六：给定数字的频率查询中位数
todo
3.3 取值型窗口函数
3.3.1 准备工作
1）lag/lead() over()的使用
lag(col,n,default):用于统计窗口往上第n行值：
- 第一个参数为列名；
- 第二个参数为往上第n行（默认为1）；
- 第三个参数为默认值（当往上第n行为null的时候，取默认值，如果不指定，则取null）。
同理：
lead(col,n,default):用于统计窗口往下第n行值：
第一个参数为列名；
第二个参数为往下第n行（默认为1）；
第三个参数为默认值（当往下第n行为null的时候，取默认值，如果不指定，则取null）。
窗口partition by和group by
是否添加范围
2）first_value
todo
思考：first_value与max()区别？？？
3.3.2 情景七：找到车辆上一次锁车记录
类似情景表述：查看顾客上次的购买时间
某车辆在某一小时内某一围栏中本次骑行的明细及对应上次锁车时间。新的测试数据集：
fence
bike_id
unlock_time
lock_time
1
湖北
101
2022-09-01 07:03:23
2022-09-01 07:05:03
2
湖北
101
2022-09-01 07:12:53
2022-09-01 07:15:13
3
湖北
101
2022-09-01 07:32:11
2022-09-01 07:55:13
4
湖北
101
2022-09-01 07:56:11
2022-09-01 07:59:52
5
湖北
101
2022-09-01 09:12:10
2022-09-01 09:25:53
6
湖北
101
2022-09-01 09:42:09
2022-09-01 09:45:33
7
湖北
102
2022-09-01 07:02:21
2022-09-01 07:15:13
8
湖北
102
2022-09-01 07:47:21
2022-09-01 07:55:13
9
山东
104
2022-09-01 07:02:21
2022-09-01 07:15:13
10
山东
104
2022-09-01 07:44:21
2022-09-01 07:55:13
样例15：某哈罗单车车辆在某一小时内某一围栏中本次骑行的明细及对应上次锁车时间？，没有order by
select
fence,
bike_id,
unlock_time, -- 开锁
lock_time, -- 锁车
lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id) as last_lock_time
from bike_hour_inc
测试结果
fence
bike_id
unlock_time
lock_time
last_lock_time
(本次开锁时间对应的上一次锁车时间)
1
山东
104
2022-09-01 07:02:21
2022-09-01 07:15:13
null
2
山东
104
2022-09-01 07:44:21
2022-09-01 07:55:13
2022-09-01 07:15:13
3
湖北
101
2022-09-01 07:03:23
2022-09-01 07:05:03
null
4
湖北
101
2022-09-01 07:12:53
2022-09-01 07:15:13
2022-09-01 07:05:03
5
湖北
101
2022-09-01 07:32:11
2022-09-01 07:55:13
2022-09-01 07:15:13
6
湖北
101
2022-09-01 07:56:11
2022-09-01 07:59:52
2022-09-01 07:55:13
7
湖北
102
2022-09-01 07:02:21
2022-09-01 07:15:13
null
8
湖北
102
2022-09-01 07:47:21
2022-09-01 07:55:13
2022-09-01 07:15:13
9
湖北
101
2022-09-01 09:12:10
2022-09-01 09:25:53
null
10
湖北
101
2022-09-01 09:42:09
2022-09-01 09:45:33
2022-09-01 09:25:53
111
3.3.3 情景八：那么如何求某个省份、某小时、某拖拉机的最长未被骑行时间。(多次窗口使用)
样例16：各车辆在某一省份分小时段最大未骑行间隔（不完全对的写法，忽视了null排序情况）
-- 注意：最里层自查询要在窗口里面使用partition by进行分组，在外层查询里面对时间间隔排序也需要分组
select -- 找到分组中最大间隔
fence,
hourtime,
bike_id,
time_gap
from
( -- 分组对时间间隔进行排序
select
fence,
bike_id,
hour(lock_time) as hourtime,
time_gap,
-- 发现time_gap存在很多null的情况，在参与order by排序的时候会影响排序结果，所以得到结果不准确
row_number() over(partition by fence,hour(lock_time),bike_id order by time_gap desc) as rc
from
( -- 分组找到本次记录的上次记录的锁车时间和事件间隔
select
fence,
bike_id,
hour(lock_time),
unlock_time,
lock_time,
lag(unlock_time,1,null) over(partition by fence,hour(lock_time),bike_id) as last_unlock_time,
(unix_timestamp(unlock_time) - unix_timestamp(lag(lock_time,1,null) over(partition by fence,hour(lock_time),bike_id order by unlock_time))) as time_gap
from bike_hour_inc
)t1
)t2
where t2.rc =1
order by fence,hourtime,bike_id
测试结果：
fence
hourtime
bike_id
time_gap
1
山东
7
104
1748
2
湖北
7
101
58
3
湖北
7
102
1928
4
湖北
9
101
976
纠正后，最终正确写法1：对null先进行处理
样例17：各拖拉机在某一省份分小时段最大未骑行间隔（两次窗口函数完全正确写法）
select
*
from
(
select
t2.fence,
t2.bike_id,
t2.every_hour,
t2.unlock_time,
t2.lock_time,
t2.last_lock_time,
t2.time_gap,
t2.time_gap_not_null,
row_number() over(partition by t2.fence,t2.every_hour,t2.bike_id order by time_gap_not_null desc) as rc
from
(
select
t1.fence,
t1.bike_id,
t1.every_hour,
t1.unlock_time,
t1.lock_time,
t1.last_lock_time,
t1.time_gap,
case when t1.time_gap is not null then t1.time_gap else -1 end as time_gap_not_null
-- nvl(t1.time_gap,999999) as time_gap_not_null2 -- 不行，数值类型不一样的感觉。
from
(
select
fence,
bike_id,
hour(lock_time) as every_hour,
unlock_time,
lock_time,
-- 使用lag，窗口里面应该需要加一个order by吧！
lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id order by unlock_time) as last_lock_time,
(unix_timestamp(unlock_time) - unix_timestamp(lag(lock_time,1,null) over(partition by fence,hour(lock_time),bike_id order by unlock_time))) as time_gap
from bike_hour_inc
)t1
)t2
)t3
where t3.rc =1
order by t3.fence,t3.every_hour,t3.bike_id
测试结果：
fence
bike_id
hour
unlock_time
lock_time
last_lock_time
time_gap
time_gap_not_null
rc
1
山东
104
7
2022-09-01 07:44:21
2022-09-01 07:55:13
2022-09-01 07:15:13
1748
1748
1
2
湖北
101
7
2022-09-01 07:32:11
2022-09-01 07:55:13
2022-09-01 07:15:13
1018
1018
1
3
湖北
102
7
2022-09-01 07:47:21
2022-09-01 07:55:13
2022-09-01 07:15:13
1928
1928
1
4
湖北
101
9
2022-09-01 09:42:09
2022-09-01 09:45:33
2022-09-01 09:25:53
976
976
1
纠正后，正确写法（第二种办法）
见3.4节样例12。
注意事项：
1）null在窗口中排序问题：（回去看3.4节的论述）
2）nvl（）字段类型问题
3）多重子查询
4）两次分组
思考三：多次窗口使用能否改成一次窗口和group by结合得到大致数据？
子查询里面使用窗口，外查询里面也使用窗口
外查询如果不使用窗口函数partition by分组，我们也可以使用group by后取max即可。
样例18:各车辆在某一围栏分小时段最大未骑行间隔（使用group by+一次窗口函数 ）
-- 原始版
select
t2.fence,
t2.bike_id,
t2.every_hour,
max(t2.time_gap_not_null) as max_gap
from
(
select
t1.fence,
t1.bike_id,
t1.every_hour,
t1.unlock_time,
t1.lock_time,
t1.last_lock_time,
t1.time_gap,
case when t1.time_gap is not null then t1.time_gap else -1 end as time_gap_not_null
-- nvl(t1.time_gap,999999) as time_gap_not_null2 -- 不行，数值类型不一样的感觉。
from
(
select
fence,
bike_id,
hour(lock_time) as every_hour,
unlock_time,
lock_time,
lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id) as last_lock_time,
(unix_timestamp(unlock_time) - unix_timestamp(lag(lock_time,1,null) over(partition by fence,hour(lock_time),bike_id))) as time_gap
from bike_hour_inc
)t1
)t2
group by t2.fence,
t2.bike_id,
t2.every_hour
order by t2.fence,t2.every_hour,t2.bike_id
简洁版：因为外查询使用的是group by后的max聚合，而null是不参与聚合这种的
样例19:各拖拉机在某一省份分小时段最大未骑行间隔（使用group by+一次窗口函数 简介版）
SQL
-- 因为外查询使用的是group by后的max聚合，而null是不参与聚合这种的，
-- 所以上述代码还可以继续简化，不需要对null进行专门处理
-- 简洁版
select
t1.fence,
t1.bike_id,
t1.every_hour,
max(t1.time_gap) as max_gap
from
(
select
fence,
bike_id,
hour(lock_time) as every_hour,
unlock_time,
lock_time,
lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id) as last_lock_time,
(unix_timestamp(unlock_time) - unix_timestamp(lag(lock_time,1,null) over(partition by fence,hour(lock_time),bike_id order by unlock_time))) as time_gap
from bike_hour_inc
)t1
group by t1.fence,
t1.bike_id,
t1.every_hour
order by t1.fence,t1.every_hour,t1.bike_id
测试结果如下：可以看到除了一些明细数据没有（后续有时间可以探讨再进行关联得到所有明细数据），其余的结果是一致的！！！
1
山东
104
7
1748
2
湖北
101
7
1018
3
湖北
102
7
1928
4
湖北
101
9
976
综上所述：如果我们只需要的是大致数据，那么就可以使用上述方法
思考四：多次窗口使用能否改成一次窗口和group by结合得到所有明细数据？
样例20：各拖拉机在某一省份分小时段最大未骑行间隔（group by+窗口 ）测试是否可以得到明细数据
select
t2.fence,
t2.bike_id,
t2.every_hour,
t2.max_gap,
t3.*
from
(
select
t1.fence,
t1.bike_id,
t1.every_hour,
max(t1.time_gap) as max_gap
from
(
select
fence,
bike_id,
hour(lock_time) as every_hour,
unlock_time,
lock_time,
lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id order by unlock_time) as last_lock_time,
(unix_timestamp(unlock_time) - unix_timestamp(lag(lock_time,1,null) over(partition by fence,hour(lock_time),bike_id))) as time_gap
from bike_hour_inc
)t1
group by t1.fence,
t1.bike_id,
t1.every_hour
)t2
join bike_hour_inc t3
on t2.fence = t3.fence
and t2.bike_id = t3.bike_id
and t2.every_hour = hour(unlock_time)
order by t2.fence,t2.every_hour,t2.bike_id
测试结果：我们发现, group by得到大致数据记录然后再去关联，发现数据记录变多了，这样说明如果group by的不是主键，那么就会数据量变多，得不到我们需要的记录，当仅仅group by的是主键，才能用这种group by分组再进行关联得到所需的最终结果！
fence
bike_id
every_hour
max_gap
fence
bike_id
unlock_time
lock_time
1
山东
104
7
1748
山东
104
2022-09-01 07:44:21
2022-09-01 07:55:13
2
山东
104
7
1748
山东
104
2022-09-01 07:02:21
2022-09-01 07:15:13
3
湖北
101
7
1018
湖北
101
2022-09-01 07:32:11
2022-09-01 07:55:13
4
湖北
101
7
1018
湖北
101
2022-09-01 07:12:53
2022-09-01 07:15:13
5
湖北
101
7
1018
湖北
101
2022-09-01 07:03:23
2022-09-01 07:05:03
6
湖北
101
7
1018
湖北
101
2022-09-01 07:56:11
2022-09-01 07:59:52
7
湖北
102
7
1928
湖北
102
2022-09-01 07:02:21
2022-09-01 07:15:13
8
湖北
102
7
1928
湖北
102
2022-09-01 07:47:21
2022-09-01 07:55:13
9
湖北
101
9
976
湖北
101
2022-09-01 09:12:10
2022-09-01 09:25:53
10
湖北
101
9
976
湖北
101
2022-09-01 09:42:09
2022-09-01 09:45:33
3.3.4 情景九：连续出现的数字
id
num
1
1
2
1
3
1
4
2
5
1
6
2
7
2
sql逻辑：
样例21:连续三次出现的数字
select
distinct num as consecutivenums
from
(
select
num,
lead(num,1,null) over(order by id) as n1,
lead(num,2,null) over(order by id) as n2
from shuzi
)t1
where t1.num = t1.n1
and t1.n1 = t1.n2
测试结果就是：1。
四：进阶使用
4.1 窗口函数与group by异同点
4.1.1 窗口函数与group by 区别
窗口函数分组汇总会改变返回数据的行数，group by 默认返回每组中的第一行；而窗口函数不会减少返回数据行数，只可能是在原始数据表新加上一列，来存放窗口分析得到的值。
样例8--group by返回数据行数
select user_type, count(1) as cnt
from order_detail
group by user_type
样例8测试结果：可以看到group by只得到两条数据（一共两个分组），而窗口函数作用后，原来是几条数据，现在还是几条数据。
user_type
cnt
1
new
7
2
old
3
4.1.2 group by去重
group by可以完成去重效果，但是窗口函数不能。
答案：这是不行的，因为使用窗口函数并没有减少数据量。那么什么时候用窗口的时候不能使用group by呢。参考下面一节的总结。
4.2 collect_list + partition by的特殊用法
参考： sparksql中collect_list+partitionby的特殊用法 - 百度文库
4.2.1 问题提出
hive或者spark中collect_list一般都是用来做分组后的合并，也就是说经常和partition by连用，本节特定讲一下collect_list + partition by的用法。
问题：如何求一个用户的连续续课次数和续课金额？
背景：有严格规定的两个课程期次序列：s1,s2,s3,s4和p1,p2,p3,我们规定满足如下条件才算是一个用户的续课；
a:当课程属于同一课程序列的时候，必须遵循严格的期次顺序，且不得有重复。如s1,s3,s4,s2,站在s2的角 度看，续了3次（s3,s4,s2）;站在s3的角度看，续课了1次（s4）;
b：当课程属于不同课程序列的时候，只要后面跟的是不同的课程序列都算一次，重复值不算，如. s2,p2,p1,s2,站在s2的角度看，续费了2次（p2,p1）;站在p2的角度看，续费了1次（s2）
4.2.2 解决思路
1）第一种思路：如果不考虑减少数据量，那么对于用户参加的每一次的课程而言，附加一列生成一个这个用户的全量有序的购课记录就好，只是要记录一下对应的这门课在整个序列中的位置。
2）第二种思路：最好的方式肯定是对于每一个用户而言，附加一个该用户在这门课之后有序的选课记录，这里也有两种方式。
a:自身关联这张表，找出每一个用户买了这门课之后的所有购课记录和购课金额；
b:如果我们不想再关联一次，那么我们就要考虑一下如何使用collect_list + partition by进行处理。
4.2.3 实际解决
给定数据
user_id
term_id
pay_time
1
s1
1
1
s2
2
1
s3
3
1
s4
4
代码逻辑：
select user_type, count(1) as cnt
from order_detail
group by user_type
测试结果：
user_id
term_id
pay_time
category_list
1
s4
4
["s4,"]
1
s3
3
["s4,","s3,"]
1
s2
2
["s4,","s3,","s2,"]
1
s1
1
["s4,","s3,","s2,","s1,"]
4.3 cube&grouping set这块
todo 高级函数
GROUPING SETS、GROUPING__ID、CUBE、ROLLUP这几个分析函数通常用于OLAP中，不能累加，而且需要根据不同维度上钻和下钻的指标统计，比如，分小时、天、月的UV数
参考：Hive窗口函数保姆级教程
- grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的group by结果集进行union all。
- cube:根据group by的维度的所有组合进行聚合。
- rollup的使用：是cube的子集，以最左侧的维度为主，从该维度进行层级聚合。
五：窗口函数底层运行原理
todo
六：其他注意事项
参考：
一文读懂SQL窗口函数 - 墨天轮
SQL高级功能窗口函数及使用场景 - 知乎
SQL窗口函数不懂？五大应用场景让你一步到位_数据小斑马的博客-CSDN博客
HIVE 窗口及分析函数 应用场景_Data_IT_Farmer的博客-CSDN博客
Hive之窗口函数（一文搞懂）_For_dongyang的博客-CSDN博客_hive 窗口函数
Hive常用函数大全（二）（窗口函数、分析函数、增强group）_李国冬的博客-CSDN博客_常用窗口函数
版权归原作者 斑马！ 所有， 如有侵权，请联系我们删除。