Hive常见开窗函数(工作常用，面试常问)

一 row_number() over()

有如下数据：

江西,高安,100 江西,南昌,200 江西,丰城,100 江西,上高,80 江西,宜春,150 江西,九江,180 湖北,黄冈,130 湖北,武汉,210 湖北,宜昌,140 湖北,孝感,90 湖南,长沙,170 湖南,岳阳,120 湖南,怀化,100

​ 

需要查询出每个省下人数最多的2个市

create table wedw_tmp.t_rn(  province_name string COMMENT '省份' ,city_name    string COMMENT '市' ,pc_cnt     bigint COMMENT '人数' ) row format delimited fields terminated by ',';

 

使用row_number函数，对表中的数据按照省份分组，按照人数倒序排序并进行标记

select  province_name ,city_name     ,pc_cnt        ,row_number() over(partition by province_name order by pc_cnt desc) as rn from wedw_tmp.t_rn ;

 

产生结果：

 

 

然后，利用上面的结果，查询出rn<=2的即为最终需求

 

select  tmp.province_name ,tmp.city_name     ,tmp.pc_cnt from ( select  province_name ,city_name     ,pc_cnt        ,row_number() over(partition by province_name order by pc_cnt desc) as rn from wedw_tmp.t_rn ) tmp where tmp.rn <= 2 ;

 

 

二 sum() over()

数据准备

A,2020-01,15 A,2020-02,19 A,2020-03,12 A,2020-04,5 A,2020-05,29 B,2020-01,8 B,2020-02,6 B,2020-03,13 B,2020-04,5 B,2020-05,24 C,2020-01,16 C,2020-02,2 C,2020-03,33 C,2020-04,51 C,2020-05,54

 

建表

create table wedw_tmp.t_sum_over(  user_name       string COMMENT '姓名' ,month_id        string COMMENT '月份' ,sale_amt        int   COMMENT '销售额' ) row format delimited fields terminated by ',';

 

对于每个人的一个月的销售额和累计到当前月的销售总额

 

select user_name ,month_id ,sale_amt ,sum(sale_amt) over(partition by user_name order by month_id rows between unbounded preceding and current row) as all_sale_amt from wedw_tmp.t_sum_over;

 

 

注：这些窗口的划分都是在分区内部！超过分区大小就无效了

可以看到如果不指定ROWS BETWEEN,默认统计窗口为从起点到当前行;

关键是理解 ROWS BETWEEN 含义,也叫做window子句：

• PRECEDING：往前

• FOLLOWING：往后

• CURRENT ROW：当前行

• UNBOUNDED：无边界，UNBOUNDED PRECEDING 表示从最前面的起点开始， UNBOUNDED FOLLOWING：表示到最后面的终点

 

其他测试：

select user_name ,month_id ,sale_amt ,sum(sale_amt) over(partition by user_name order by month_id) as all_sale_amt1 --默认为从起点行到当前行 ,sum(sale_amt) over(partition by user_name order by month_id rows between unbounded preceding and current row) as all_sale_amt2 --从起点行到当前行 ,sum(sale_amt) over(partition by user_name order by month_id rows between 3 preceding and current row) as all_sale_amt3 --当前行及往前3行之和 ,sum(sale_amt) over(partition by user_name order by month_id rows between 3 preceding and 1 following) as all_sale_amt4 --当前行及往前3行往后1行之和 ,sum(sale_amt) over(partition by user_name order by month_id rows between current row and unbounded following) as all_sale_amt5 --当前行及往后所有行之和 from wedw_tmp.t_sum_over;

 

 

三 lag/lead() over()

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

 

第一个参数为列名，第二个参数为往上第n行（默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

 

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

 

第一个参数为列名，第二个参数为往下第n行（默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

 

以lag() over()为例：

 

数据准备

create table t_hosp(  user_name string ,age int ,in_hosp date ,out_hosp date) row format delimited fields terminated by ',';   xiaohong,25,2020-05-12,2020-06-03 xiaoming,30,2020-06-06,2020-06-15 xiaohong,25,2020-06-14,2020-06-19 xiaoming,30,2020-06-20,2020-07-02   user_name:用户名 age:年龄 in_hosp:住院日期 out_hosp：出院日期

 

需求：求同一个患者每次住院与上一次出院的时间间隔

 

第一步：

select user_name ,age ,in_hosp ,out_hosp ,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc) AS pre_out_date from t_hosp ; 其中，LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc) 表示根据user_name分组按照out_hosp升序取每条数据的上一条数据的out_hosp， 如果上一条数据为空，则使用默认值in_hosp来代替

 

结果：

 

 

第二步：每条数据的in_hosp与pre_out_date的差值即本次住院日期与上次出院日期的间隔

select user_name ,age ,in_hosp ,out_hosp ,datediff(in_hosp,LAG(out_hosp,1,in_hosp) OVER(PARTITION BY user_name ORDER BY out_hosp asc)) as days from t_hosp ;

结果：

 

2020大数据面试题真题总结(附答案)

一文探究数据仓库体系(2.7万字建议收藏)

一文探究Hadoop(3万字长文，建议收藏)

一文带你走进HIVE的世界(1.8W字建议收藏)

一文带你全方位了解Flink(3.2W字建议收藏)

一文带你走进clickhouse的世界(3W字建议收藏)

你要悄悄学会HBase，然后惊艳所有人(1.7万字建议收藏)

一文带你深入了解kafka并提供52道企业常见面试题(1.8W字建议收藏)

非常全面的DolphinScheduler(海豚调度)安装使用文档

Hive调优，数据工程师成神之路

数据质量那点事

简述元数据管理