目录
一、窗口函数的知识点
1.1 窗户函数的定义
窗口函数可以拆分为【窗口+函数】。窗口函数官网指路：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics- 窗口：限定函数的计算范围（窗口函数：针对分组后的数据，从逻辑角度指定计算的范围，并没有从物理上真正的切分，只有group by 是物理分组，真正意义上的分组）
- 函数：定义函数计算逻辑
- sql 执行顺序：
from ->
join ->
on ->
where ->
group by->
with (可以在分组后面加上 with rollup,在分组之后对每个组进行全局汇总) ->
select 后面的普通字段，聚合函数->
having(having中可以使用select 字段别名) ->
distinct ->
order by ->
limit- 窗口函数执行顺序：窗口函数是作用于select后的结果集。select 的结果集作为窗口函数的输入，但是位于 distcint 之前。窗口函数的执行结果只是在原有的列中单独添加一列，形成新的列，它不会对已有的行或列做修改
1.2 窗户函数的语法
<窗口函数>window_name over ( [partition by 字段...] [order by 字段...] [窗口子句] )
- window_name：给窗口指定一个别名。
- over：用来指定函数执行的窗口范围，如果后面括号中什么都不写，即over() ，意味着窗口包含满足where 条件的所有行，窗口函数基于所有行进行计算。
- 符号[] 代表：可选项； | ： 代表二选一
- partition by 子句： 窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行。分组间互相独立。
- order by 子句：每个partition内部按照哪些字段进行排序，如果没有partition ，那就直接按照最大的窗口排序，且默认是按照升序（asc）排列。
- 窗口子句：显示声明范围（不写窗口子句的话，会有默认值）。常用的窗口子句如下：
rows between unbounded preceding and unbounded following; -- 上无边界到下无边界（一般用于求 总和）
rows between unbounded preceding and current row; --上无边界到当前记录(累计值)
rows between 1 preceding and current row; --从上一行到当前行
rows between 1 preceding and 1 following; --从上一行到下一行
rows between current row and 1 following; --从当前行到下一行ps: over()里面有order by子句，但没有窗口子句时 ，即： <窗口函数> over ( partition by 字段... order by 字段... )，此时窗口子句是有默认值的 --> rows between unbounded preceding and current row (上无边界到当前行)。
此时窗口函数语法：<窗口函数> over ( partition by 字段... order by 字段... ) 等价于
<窗口函数> over ( partition by 字段... order by 字段... rows between unbounded preceding and current row)
需要注意有个特殊情况：当order by 后面跟的某个字段是有重复行的时候， <窗口函数> over ( partition by 字段... order by 字段... ) 不写窗口子句的情况下，窗口子句的默认值是：range between unbounded preceding and current row(上无边界到当前相同行的最后一行)。
因此，遇到order by 后面跟的某个字段出现重复行，且需要计算【上无边界到当前行】，那就需要手动指定窗口子句 rows between unbounded preceding and current row ，偷懒省略窗口子句会出问题~
总结如下：
1、窗口子句不能单独出现，必须有order by子句时才能出现。
2、当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行，即在当前组中，第一行到当前行
b) 如果没有order by则默认的窗口是unbounded preceding and unbounded following --整个组ps: 窗口函数的执行顺序是在where之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。
【例如】求出登录记录出现间断的用户Id
select
id
from (
select
id,
login_date,
lead(login_date, 1, '9999-12-31')
over (partition by id order by login_date) next_login_date
--窗口函数 lead(向后取n行)
--lead(column1,n,default)over(partition by column2 order by column3) 查询当前行的后边第n行数据，如果没有就为null
from (--用户在同一天可能登录多次，需要去重
select
id,
date_format(`date`, 'yyyy-MM-dd') as login_date
from user_log
group by id, date_format(`date`, 'yyyy-MM-dd')
) tmp1
) tmp2
where datediff(next_login_date, login_date) >=2
group by id;1.3 窗口函数分类
哪些函数可以是窗口函数呢？(放在over关键字前面的)
sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句);
collect_list (column) over (partition by .. order by .. 窗口子句);
collect_set (column) over (partition by .. order by .. 窗口子句);需要注意：
1.count(*)操作时会统计null值，count(column)会过滤掉null值;
2.事实上除了count(*)计算，剩余的聚合函数例如： max(column),min(column),avg(column),count(column) 函数会过滤掉null值ps : 高级聚合函数：
collect_list 收集并形成list集合，结果不去重;
collect_set 收集并形成set集合，结果去重;
举例:
--每个月的入职人数以及姓名
select
month(replace(hiredate,'/','-')),
count(*) as cnt,
collect_list(name) as name_list
from employee
group by month(replace(hiredate,'/','-'));
/*
输出结果
month cn name_list
4 2 ["宋青书","周芷若"]
6 1 ["黄蓉"]
7 1 ["郭靖"]
8 2 ["张无忌","杨过"]
9 2 ["赵敏","小龙女"]
*/高级聚合函数collect_set()/collect_list()的用法见：
HiveSQL题——collect_set()/collect_list()聚合函数-CSDN博客文章浏览阅读1.1k次，点赞20次，收藏20次。HiveSQL题——collect_set()/collect_list()聚合函数
https://blog.csdn.net/SHWAITME/article/details/136011647?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522170762720816800226594415%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=170762720816800226594415&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-2-136011647-null-null.nonecase&utm_term=%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0&spm=1018.2226.3001.4450
-
排序函数
-- 顺序排序——1、2、3
row_number() over(partition by .. order by .. )
-- 并列排序，跳过重复序号——1、1、3（横向加）
rank() over(partition by .. order by .. )
-- 并列排序，不跳过重复序号——1、1、2（纵向加）
dense_rank() over(partition by .. order by .. )-
前后函数
-- 取得column列的前n行，如果存在则返回，如果不存在，返回默认值default
lag(column,n,default) over(partition by order by) as lag_test
-- 取得column列的后n行，如果存在则返回，如果不存在，返回默认值default
lead(column,n,default) over(partition by order by) as lead_test-
头尾函数
---当前窗口column列的第一个数值，如果有null值，则跳过
first_value(column,true) over (partition by ..order by.. 窗口子句)
---当前窗口column列的第一个数值，如果有null值，不跳过
first_value(column,false) over (partition by ..order by.. 窗口子句)
--- 当前窗口column列的最后一个数值，如果有null值，则跳过
last_value(column,true) over (partition by ..order by.. 窗口子句)
--- 当前窗口column列的最后一个数值，如果有null值,不跳过
last_value(column,false) over (partition by ..order by.. 窗口子句)
1.4 聚合函数
sum() /count() /max() /min() /avg() 函数，一般用于开窗求累积汇总值。
sum(column) over (partition by .. order by .. 窗口子句);
count(column) over (partition by .. order by .. 窗口子句);
max(column) over (partition by .. order by .. 窗口子句);
min(column) over (partition by .. order by .. 窗口子句);
avg(column) over (partition by .. order by .. 窗口子句);二、实际案例
2.1 每个用户累积访问次数
0 问题描述
统计每个用户累积访问次数
1 数据准备
create table if not exists table6
(
userid string comment '用户id',
visitdate string comment '访问时间',
visitcount int comment '访问次数'
)
comment '用户访问次数';2 数据分析
select
userid,
visit_date,
vc1,
--再求出用户历史至今的累积访问次数
sum(vc1) over (partition by userid order by visit_date ) as vc2
from ( --先求出用户每个月的累积访问次数
select
userid,
date_format(visitdate, 'yyyy-MM') as visit_date,
sum(visitcount) as vc1
from table6
group by userid, date_format(visitdate, 'yyyy-MM')
) tmp1;
3 小结
2.2 各直播间最大的同时在线人数
0 问题描述
根据直播间的用户访问记录，统计各直播间最大的同时在线人数。
1 数据准备
create table if not exists table7
(
room_id int comment '直播间id',
user_id int comment '用户id',
login_time string comment '用户进入直播间时间',
logout_time string comment '用户离开直播间时间'
)
comment '直播间的用户访问记录';
INSERT overwrite table table7
VALUES (1,100,'2021-12-01 19:00:00', '2021-12-01 19:28:00'),
(1,100,'2021-12-01 19:30:00', '2021-12-01 19:53:00'),
(2,100,'2021-12-01 21:01:00', '2021-12-01 22:00:00'),
(1,101,'2021-12-01 19:05:00', '2021-12-01 20:55:00'),
(2,101,'2021-12-01 21:05:00', '2021-12-01 21:58:00'),
(1,102,'2021-12-01 19:10:00', '2021-12-01 19:25:00'),
(2,102,'2021-12-01 19:55:00', '2021-12-01 21:00:00'),
(3,102,'2021-12-01 21:05:00', '2021-12-01 22:05:00'),
(1,104,'2021-12-01 19:00:00', '2021-12-01 20:59:00'),
(2,104,'2021-12-01 21:57:00', '2021-12-01 22:56:00'),
(2,105,'2021-12-01 19:10:00', '2021-12-01 19:18:00'),
(3,106,'2021-12-01 19:01:00', '2021-12-01 21:10:00');
2 数据分析
select
room_id,
max(num)
from (
select
room_id,
sum(flag) over (partition by room_id order by dt) as num
from (
select
room_id,
user_id,
login_time as dt,
--对登入该直播间的人，标记 1
1 as flag
from table7
union
select
room_id,
user_id,
logout_time as dt,
--对退出该直播间的人，标记 -1
-1 as flag
from table7
) tmp1
) tmp2
--求出直播间最大的同时在线人数
group by room_id;3 小结
该题的关键点在于：对每个用户进入/退出直播间的行为进行打标签，再利用sum()over聚合函数计算最终的数值。
2.3 历史至今每个小时内同时在线人数
由案例2.2 引申出来的案例 2.3和 案例2.4
0 问题描述
根据直播间用户访问记录，不限制时间段，统计历史至今的各直播间每个小时内的同时在线人数
1 数据准备
create table if not exists table7
(
room_id int comment '直播间id',
user_id int comment '用户id',
login_time string comment '用户进入直播间时间',
logout_time string comment '用户离开直播间时间'
)
comment '直播间的用户访问记录';
INSERT overwrite table table7
VALUES (1,100,'2021-12-01 19:00:00', '2021-12-01 19:28:00'),
(1,100,'2021-12-01 19:30:00', '2021-12-01 19:53:00'),
(2,100,'2021-12-01 21:01:00', '2021-12-01 22:00:00'),
(1,101,'2021-12-01 19:05:00', '2021-12-01 20:55:00'),
(2,101,'2021-12-01 21:05:00', '2021-12-01 21:58:00'),
(1,102,'2021-12-01 19:10:00', '2021-12-01 19:25:00'),
(2,102,'2021-12-01 19:55:00', '2021-12-01 21:00:00'),
(3,102,'2021-12-01 21:05:00', '2021-12-01 22:05:00'),
(1,104,'2021-12-01 19:00:00', '2021-12-01 20:59:00'),
(2,104,'2021-12-01 21:57:00', '2021-12-01 22:56:00'),
(2,105,'2021-12-01 19:10:00', '2021-12-01 19:18:00'),
(3,106,'2021-12-01 19:01:00', '2021-12-01 21:10:00');2 数据分析
完整代码如下：
with temp_data as (
select
room_id,
user_id,
login_time,
logout_time,
hour(login_time) as min_time,
-- hour('2021-12-01 19:30:00') = 19
hour(logout_time) as max_time,
length(space(hour(logout_time) - hour(login_time))) as lg,
split(space(hour(logout_time) - hour(login_time)), '') as dis
from table7
)
select
room_id,
on_time,
count(1) as cnt
from (
select distinct
room_id,
user_id,
min_time,
max_time,
dis,
dis_index,
(min_time + dis_index) as on_time
from temp_data lateral view posexplode(dis) n as dis_index,dis_data
order by user_id,
min_time,
max_time,
dis,
dis_index
) tmp1
group by room_id, on_time
order by room_id, on_time;
代码拆解分析：
--以一条数据为例，
room_id user_id login_time logout_time
1 100 '2021-12-01 19:00:00' '2021-12-01 21:28:00'
（1）上述数据取时间hour(login_time) as min_time 、hour(logout_time)as max_time
1(room_id),100(user_id),19(min_time),21(max_time)
（2）split(space(hour(logout_time) - hour(login_time)), '') 的结果：
根据[21-19]=2,利用space函数生成长度是2的空格字符串，再用split拆分
1(room_id),100(user_id),19(min_time),21(max_time),['','','']
（3）用posexplode经过转换增加行（列转行，炸裂），通过下角标index来获取 on_time时间，
根据数组['','','']，得到index的取值是0,1,2
炸裂得出下面三行数据（一行变三行）
1(room_id),100(user_id),19(min_time),19 = 19+0 (on_time = min_time+index)
1(room_id),100(user_id),19(min_time),20 = 19+1 (on_time = min_time+index)
1(room_id),100(user_id),19(min_time),21 = 19+2 (on_time = min_time+index)
炸裂的目的：将用户在线的时间段[19-21] 拆分成具体的小时，19,20,21;
（4）根据room_id,on_time进行分组，求出每个直播间分时段的在线人数 3 小结
上述代码中用到的函数有：
一、字符串函数
1、空格字符串函数：space
语法：space(int n)
返回值：string
说明：返回值是n的空格字符串
举例：select length (space(10)) --> 10
一般space函数和split函数结合使用：select split(space(3),''); --> ["","","",""]
2、split函数（分割字符串）
语法：split(string str,string pat)
返回值：array
说明：按照pat字符串分割str,会返回分割后的字符串数组
举例：select split ('abcdf','c') from test; -> ["ab","df"]
3、repeat：重复字符串
语法：repeat(string A, int n)
返回值：string
说明：将字符串A重复n遍。
举例：select repeat('123', 3); -> 123123123
一般repeat函数和split函数结合使用：select split(repeat(',',4),','); -->
["","","","",""]
二、炸裂函数
explode
语法：lateral view explode(split(a,',')) tmp as new_column
返回值：string
说明:按照分隔符切割字符串，并将数组中内容炸裂成多行字符串
举例：select student_score from test lateral view explode(split(student_score,','))
tmp as student_score
posexplode
语法：lateral view posexploed(split(a,',')) tmp as pos,item
返回值：string
说明:按照分隔符切割字符串，并将数组中内容炸裂成多行字符串(炸裂具备瞎下角标 0,1,2,3)
举例：select student_name, student_score from test
lateral view posexplode(split(student_name,',')) tmp1 as student_name_index,student_name
lateral view posexplode(split(student_score,',')) tmp2 as student_score_index,student_score
where student_score_index = student_name_index
2.4 某个时间段、每个小时内同时在线人数
0 问题描述
根据直播间用户访问记录，统计某个时间段的各直播间每个小时内的同时在线人数，假设时间段是['2021-12-01 19:00:00', '2021-12-01 23:00:00']
1 数据准备
create table if not exists table7
(
room_id int comment '直播间id',
user_id int comment '用户id',
login_time string comment '用户进入直播间时间',
logout_time string comment '用户离开直播间时间'
)
comment '直播间的用户访问记录';
INSERT overwrite table table7
VALUES (1,100,'2021-12-01 19:00:00', '2021-12-01 19:28:00'),
(1,100,'2021-12-01 19:30:00', '2021-12-01 19:53:00'),
(2,100,'2021-12-01 21:01:00', '2021-12-01 22:00:00'),
(1,101,'2021-12-01 19:05:00', '2021-12-01 20:55:00'),
(2,101,'2021-12-01 21:05:00', '2021-12-01 21:58:00'),
(1,102,'2021-12-01 19:10:00', '2021-12-01 19:25:00'),
(2,102,'2021-12-01 19:55:00', '2021-12-01 21:00:00'),
(3,102,'2021-12-01 21:05:00', '2021-12-01 22:05:00'),
(1,104,'2021-12-01 19:00:00', '2021-12-01 20:59:00'),
(2,104,'2021-12-01 21:57:00', '2021-12-01 22:56:00'),
(2,105,'2021-12-01 19:10:00', '2021-12-01 19:18:00'),
(3,106,'2021-12-01 19:01:00', '2021-12-01 21:10:00');
2 数据分析
完整代码如下：
with temp_data1 as (
select
room_id,
user_id,
login_time,
logout_time,
hour(login_time) as min_time,
hour(logout_time) as max_time,
split(space(hour(logout_time) - hour(login_time)), '') as dis
from table7
where login_time >= '2021-12-01 19:00:00'
and login_time <= '2021-12-01 21:00:00'
)
select
room_id,
on_time,
count(1) as cnt
from (select distinct
room_id,
user_id,
min_time,
max_time,
dis_index,
(min_time + dis_index) as on_time
from temp_data1 lateral view posexplode(dis) n1 as dis_index, dis_data
order by user_id,
min_time,
max_time,
dis_index) tmp
group by room_id, on_time
order by room_id, on_time;
3 小结
解题思路与2.3一致，只需要限制下时间区间
2.5 学生各学科的成绩
0 问题描述
基于不同的窗口限定范围(窗口边界)，统计各学生的学科成绩。
1 数据准备
create table if not exists table9
(
name string comment '学生名称',
subject string comment '学科',
score int comment '分数'
)
comment '学生分数';
INSERT overwrite table table9
VALUES ('a','数学',12),
('b','数学',19),
('c','数学',17),
('d','数学',24),
('a','英语',77),
('c','英语',11),
('d','英语',34),
('a','语文',61);2 数据分析
select
name,
subject,
score,
--1.全局聚合
sum(score) over () as sum1,
--2.根据学科分组，组内全局聚合
sum(score) over (partition by subject) as sum2,
--3.根据学科分组，根据分数排序，计算由起点到当前行的累积值
sum(score) over (partition by subject order by score) as sum3,
--4.根据学科分组，根据分数排序，计算由起点到当前行的累积值 （sum3跟sum4的结果是一样的）
sum(score) over (partition by subject order by score rows between unbounded preceding and current row ) as sum4,
--5.根据学科分组，根据分数排序，计算上一行到当前行的累积值
sum(score) over (partition by subject order by score rows between 1 preceding and current row ) as sum5,
--6.根据学科分组，根据分数排序，计算上一行到下一行的累积值
sum(score) over (partition by subject order by score rows between 1 preceding and 1 following) as sum6,
--7.根据学科分组，根据分数排序，计算当前行到后面所有行的累积值
sum(score) over (partition by subject order by score rows between current row and unbounded following ) as sum7
from table9;3 小结
窗口函数 = 窗口+ 函数，解题时需要梳理清楚函数的计算范围。
2.6 商品销售
0 问题描述
从订单详情表中找出销售额连续3天超过100的商品
1 数据准备
create table if not exists table19
(
order_detail_id string comment '订单明细id',
order_id string comment '订单id',
sku_id string comment '商品id',
create_date string comment '商品的下单日期',
price double comment '商品单价',
sku_num int comment '商品件数'
) comment '订单明细表';
insert overwrite table table19 values
('1','1','1','2021-09-30',2000.00,2),
('2','1','3','2021-09-30',5000.00,5),
('22','10','4','2020-10-02',6000.00,1),
('23','10','5','2020-10-02',500.00,24),
('24','10','6','2020-10-02',2000.00,5);2 数据分析
select
sku_id
from (select
sku_id,
create_date,
date_sub(create_date, row_number() over (partition by sku_id order by create_date)) sub
from (select
sku_id,
create_date,
sum(sku_num * price) as sum
from table19
group by sku_id, create_date
having sum >= 100) tmp1
group by sku_id, sub
having count(1) >= 3;3 小结
上述解题方法用到了“连续登陆”的思想，该题型的解决步骤：
（1）计算 date_sub(create_date,row_number() over (partition by sku_id oder by create_date)) as sub（差值）
（2）group by sku_id,sub 分组；
（3）count(1) >= 3的商品sku_id就是销售额连续3天以上多超过xx；
更多“连续登陆”的案例 见文章：
2.7 商品复购率
0 问题描述
求解零食类商品中复购率top3高的商品
1 数据准备
create table if not exists tb_order_overall(
order_id int comment '订单号',
uid int comment '用户ID',
event_time string comment '下单时间',
total_amount double comment '订单总金额',
total_cnt int comment '订单商品总件数',
`status` int comment '订单状态'
) comment '订单总表';
insert overwrite table tb_order_overall values
(301001, 101, '2021-09-30 10:00:00', 140, 1, 1),
(301002, 102, '2021-10-01 11:00:00', 235, 2, 1),
(301011, 102, '2021-10-31 11:00:00', 250, 2, 1),
(301003, 101, '2021-11-02 10:00:00', 300, 2, 1),
(301013, 105, '2021-11-02 10:00:00', 300, 2, 1),
(301005, 104, '2021-11-03 10:00:00', 170, 1, 1);
create table if not exists tb_product_info (
product_id int comment '商品ID',
shop_id int comment '店铺ID',
tag string comment '商品类别标签',
in_price double comment '进货价格',
quantity int comment '进货数量',
release_time string comment '上架时间'
) comment '商品信息表';
insert overwrite table tb_product_info values
(8001, 901, '零食', 60, 1000, '2020-01-01 10:00:00'),
(8002, 901, '零食', 140, 500, '2020-01-01 10:00:00'),
(8003, 901, '零食', 160, 500, '2020-01-01 10:00:00');
drop table tb_order_detail
create table if not exists tb_order_detail (
order_id int comment '订单号',
product_id int comment '商品ID',
price double comment '商品单价',
cnt int comment'下单数量'
) comment '订单明细表';
insert overwrite table tb_order_detail values
(301001, 8002, 150, 1),
(301011, 8003, 200, 1),
(301011, 8001, 80, 1),
(301002, 8001, 85, 1),
(301002, 8003, 180, 1),
(301003, 8002, 140, 1),
(301003, 8003, 180, 1),
(301013, 8002, 140, 2),
(301005, 8003, 180, 1);
2 数据分析
注：复购率指用户在一段时间内对某商品的重复购买比例，复购率越大，则反映出消费者对品牌的忠诚度就越高，也叫回头率.
此处我们定义：某商品复购率 = 近90天内购买它至少两次的人数 / 购买它的总人数
近90天 指包含 最大日期（记为当天）在内的近90天。
要求：结果的复购率保留3位小数，并按复购率倒序、商品ID升序排序展示
select
product_id,
--步骤2：购买次数大于2的代表复购，复购的标识为1，再求累积值 ：sum(if(buy_times >= 2, 1, 0))
-- 步骤3：复购率保留3位小数：round(result,3)
round(sum(if(buy_times >= 2, 1, 0)) / count(*), 3)
as repurchase_rate
from (select
pi.product_id,
too.uid,
count(1) as buy_times -- 步骤1：某商品某用户的购买次数
from tb_product_info pi
join (select max(date(event_time)) as max_date
from tb_order_overall) too1
left join tb_order_detail tod
on pi.product_id = tod.product_id
left join tb_order_overall too
on tod.order_id = too.order_id
where pi.tag = '零食' -- 零食类商品
and too.status = 1 --成功购买的
and datediff(too1.max_date
,date(too.event_time)) <= 89 -- 近89天(用 子查询too1得出最大的日期)
group by pi.product_id, too.uid) tmp1
group by product_id
order by repurchase_rate desc,product_id; --复购率倒序,商品ID升序排序3 小结
复购题型的解决思路一般是：
- 筛选复购行为发生的时间区间（datediff函数）;
- 对复购行为打标签（if函数）;
- 标签聚合求出复购人数（sum聚合函数）;
- 求出复购率：复购人数/总购买人数；
hive sql常用函数指路：
扫一扫
1084
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
