目录
一、窗口函数的知识点
1.1 窗户函数的定义
1.2 窗户函数的语法
1.3 窗口函数分类
聚合函数
排序函数
前后函数
头尾函数
1.4 聚合函数
二、实际案例
2.1每个用户累积访问次数
0 问题描述
1 数据准备
2 数据分析
3 小结
2.2各直播间最大的同时在线人数
0 问题描述
1 数据准备
2 数据分析
3 小结
2.3历史至今每个小时内同时在线人数
0 问题描述
1 数据准备
2 数据分析
3 小结
2.4 某个时间段、每个小时内同时在线人数
0 问题描述
1 数据准备
2 数据分析
3 小结
2.5 学生各学科的成绩
0 问题描述
1 数据准备
2 数据分析
3 小结
2.6 商品销售
0 问题描述
1 数据准备
2 数据分析
3 小结
2.7商品复购率
0 问题描述
1 数据准备
2 数据分析
3 小结
一、窗口函数的知识点
1.1 窗户函数的定义
窗口函数可以拆分为【窗口+函数】。窗口函数官网指路：
LanguageManual WindowingAndAnalytics – Apache Hive – Apache Software Foundation
https://cwiki.apache.org/confluence/display/Hive/LanguageManual%20WindowingAndAnalytics
- 窗口：限定函数的计算范围（窗口函数：针对分组后的数据，从逻辑角度指定计算的范围，并没有从物理上真正的切分，只有group by 是物理分组，真正意义上的分组）
- 函数：计算逻辑
- 窗口函数的位置：跟sql里面聚合函数的位置一样，from -> join -> on -> where -> group by->select 后面的普通字段，窗口函数 -> having -> order by -> lmit 。 窗口函数不能跟聚合函数同时出现。聚合函数包括count、sum、 min、max、avg。
- sql 执行顺序：from -> join -> on -> where -> group by->select 后面的普通字段，聚合函数-> having -> order by -> limit
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 andunbounded 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 ，偷懒省略窗口子句会出问题~
ps: 窗口函数的执行顺序是在where之后，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行。
【例如】求出登录记录出现间断的用户Id
selectidfrom ( 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 (--用户在同一天可能登录多次，需要去重selectid,date_format(`date`, 'yyyy-MM-dd') as login_datefrom user_loggroup by id, date_format(`date`, 'yyyy-MM-dd')) tmp1 ) tmp2wheredatediff(next_login_date, login_date) >=2group 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 .. 窗口子句);需要注意：
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_listfrom employeegroup by month(replace(hiredate,'/','-'));/*输出结果monthcnname_list42["宋青书","周芷若"]61["黄蓉"]71["郭靖"]82["张无忌","杨过"]92["赵敏","小龙女"]*/排序函数
--顺序排序——1、2、3row_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行，如果存在则返回，如果不存在，返回默认值defaultlag(column,n,default) over(partition by order by) as lag_test-- 取得column列的后n行，如果存在则返回，如果不存在，返回默认值defaultlead(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',visitdatestring comment '访问时间',visitcount int comment '访问次数')comment '用户访问次数';2 数据分析
selectuserid,visit_date,vc1, --再求出用户历史至今的累积访问次数sum(vc1) over (partition by userid order by visit_date ) as vc2from ( --先求出用户每个月的累积访问次数 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_idint comment '直播间id',user_idint comment '用户id',login_time string comment '用户进入直播间时间',logout_timestring comment '用户离开直播间时间')comment '直播间的用户访问记录';INSERT overwrite table table7VALUES (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 数据分析
selectroom_id,max(num)from ( select room_id, sum(flag) over (partition by room_id order by dt) as num from (selectroom_id,user_id,login_time as dt,--对登入该直播间的人，标记 11as flagfrom table7unionselectroom_id,user_id,logout_time as dt, --对退出该直播间的人，标记 -1-1as flagfrom 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_idint comment '直播间id',user_idint comment '用户id',login_time string comment '用户进入直播间时间',logout_timestring comment '用户离开直播间时间')comment '直播间的用户访问记录';INSERT overwrite table table7VALUES (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 (selectroom_id,user_id,login_time,logout_time,hour(login_time) as min_time,--hour('2021-12-01 19:30:00') = 19hour(logout_time) as max_time,length(space(hour(logout_time) - hour(login_time))) as lg,split(space(hour(logout_time) - hour(login_time)), '') as disfrom table7)selectroom_id,on_time,count(1) as cntfrom ( 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 ) tmp1group by room_id, on_timeorder by room_id, on_time;代码拆解分析：
--以一条数据为例， room_iduser_id login_time logout_time1 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_time1(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,',')) tmpas 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_index2.4 某个时间段、每个小时内同时在线人数
0 问题描述
根据直播间用户访问记录，统计某个时间段的各直播间每个小时内的同时在线人数，假设时间段是[‘2021-12-01 19:00:00’, ‘2021-12-01 23:00:00’]
1 数据准备
create table if not exists table7(room_idint comment '直播间id',user_idint comment '用户id',login_time string comment '用户进入直播间时间',logout_timestring comment '用户离开直播间时间')comment '直播间的用户访问记录';INSERT overwrite table table7VALUES (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 (selectroom_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 disfrom table7where login_time >= '2021-12-01 19:00:00'and login_time <= '2021-12-01 21:00:00')selectroom_id,on_time,count(1) as cntfrom (select distinctroom_id,user_id,min_time,max_time,dis_index,(min_time + dis_index) as on_timefrom temp_data1 lateral view posexplode(dis) n1 as dis_index, dis_dataorder by user_id, min_time, max_time, dis_index) tmpgroup by room_id, on_timeorder by room_id, on_time;3 小结
解题思路与2.3一致，只需要限制下时间区间
2.5 学生各学科的成绩
0 问题描述
基于不同的窗口限定范围(窗口边界)，统计各学生的学科成绩。
1 数据准备
create table if not exists table9(namestring comment '学生名称',subject string comment '学科',score int comment '分数')comment '学生分数';INSERT overwrite table table9VALUES ('a','数学',12), ('b','数学',19), ('c','数学',17), ('d','数学',24), ('a','英语',77), ('c','英语',11), ('d','英语',34), ('a','语文',61);2 数据分析
selectname,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 sum7from table9;3 小结
窗口函数 = 窗口+ 函数，解题时需要梳理清楚函数的计算范围。
2.6 商品销售
0 问题描述
从订单详情表中找出销售额连续3天超过100的商品
1 数据准备
create table if not existstable19(order_detail_idstring comment '订单明细id',order_idstring comment '订单id',sku_idstring comment '商品id',create_datestring comment '商品的下单日期',pricedouble comment '商品单价',sku_numint 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 数据分析
selectsku_idfrom (selectsku_id,create_date,date_sub(create_date, row_number() over (partition by sku_id order by create_date)) subfrom (selectsku_id,create_date,sum(sku_num * price) as sumfrom table19group by sku_id, create_datehaving sum >= 100) tmp1group by sku_id, subhaving count(1) >= 3;3 小结
上述解题方法用到了“连续登陆”的思想，该题型的解决步骤：
（1）计算 date_sub(create_date,row_number() over (partition by sku_idoder by create_date)) as sub（差值）
（2）group by sku_id,sub 分组；
（3）count(1) >= 3的商品sku_id就是销售额连续3天以上多超过xx；
更多“连续登陆”的案例 见文章：
HiveSQL题——用户连续登陆-CSDN博客文章浏览阅读803次，点赞21次，收藏9次。HiveSQL题——用户连续登陆 零食类商品中复购率top3高的商品_牛客题霸_牛客网商品信息表tb_product_info。题目来自【牛客题霸】 求解零食类商品中复购率top3高的商品 复购题型的解决思路一般是： hive sql常用函数指路： hivesql的基础知识点-CSDN博客文章浏览阅读555次，点赞14次，收藏9次。hivesql的基础知识点
https://blog.csdn.net/SHWAITME/article/details/135900251″ />2.7商品复购率https://www.nowcoder.com/practice/9c175775e7ad4d9da41602d588c5caf3?tpId=2680 问题描述
1 数据准备
create table if not existstb_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 existstb_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_detailcreate 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升序排序展示selectproduct_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_ratefrom (selectpi.product_id,too.uid,count(1) as buy_times -- 步骤1：某商品某用户的购买次数from tb_product_info pijoin (select max(date(event_time)) as max_datefrom tb_order_overall) too1left join tb_order_detail todon pi.product_id = tod.product_idleft join tb_order_overall tooon tod.order_id = too.order_idwhere 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) tmp1group by product_idorder by repurchase_rate desc,product_id; --复购率倒序,商品ID升序排序3 小结
https://blog.csdn.net/SHWAITME/article/details/135986201?spm=1001.2014.3001.5502