HiveQL行列转换以及开窗函数案例

列和字段属性转换练习

该表是用户订单表，请统计各用户一周内各天的订单数。orders.order_dow列用数字表示一周内的第几天。

orders.order_id	orders.user_id	orders.eval_set	orders.order_number	orders.order_dow	orders.order_hour_of_day	orders.days_since_prior_order
2539329	1	prior	1	2	08
2398795	1	prior	2	3	07	15.0
473747	1	prior	3	3	12	21.0
2254736	1	prior	4	4	07	29.0
431534	1	prior	5	4	15	28.0

方法一：

select 
	user_id
	,sum(case when order_dow='0' then 1 else 0 end) dow0
	,sum(case when order_dow='1' then 1 else 0 end) dow1
	,sum(case when order_dow='2' then 1 else 0 end) dow2
	,sum(case when order_dow='3' then 1 else 0 end) dow3
	,sum(case when order_dow='4' then 1 else 0 end) dow4
	,sum(case when order_dow='5' then 1 else 0 end) dow5
	,sum(case when order_dow='6' then 1 else 0 end) dow6
from orders
group by user_id
limit 10;

方法二：

select 
user_id
, sum(if(order_dow='0',1,0)) dow0
, sum(if(order_dow='1',1,0)) dow1
, sum(if(order_dow='2',1,0)) dow2
, sum(if(order_dow='3',1,0)) dow3
, sum(if(order_dow='4',1,0)) dow4
, sum(if(order_dow='5',1,0)) dow5
, sum(if(order_dow='6',1,0)) dow6
from orders
where user_id in ('1','2')
group by user_id
limit 10;

结果：

user_id dow0    dow1    dow2    dow3    dow4    dow5    dow6
1       0       3       2       2       4       0       0
10      1       0       1       2       0       2       0
100     1       1       0       2       0       2       0
1000    4       0       1       1       0       0       2
10000   15      12      10      7       9       9       11
100000  2       1       0       4       1       0       2
100001  4       15      17      13      6       9       3
100002  0       3       0       0       3       5       2
100003  0       0       0       0       0       3       1
100004  1       2       2       2       0       2       0
Time taken: 30.904 seconds, Fetched: 10 row(s)

行列转换

concat_ws 行转列

求出用户一周内各天下单的订单有哪些。

orders.order_id	orders.user_id	orders.eval_set	orders.order_number	orders.order_dow	orders.order_hour_of_day	orders.days_since_prior_order
2539329	1	prior	1	2	08
2398795	1	prior	2	3	07	15.0
473747	1	prior	3	3	12	21.0
2254736	1	prior	4	4	07	29.0
431534	1	prior	5	4	15	28.0

select 
	user_id
	,order_dow
	,concat_ws("|", collect_set(order_id)) as orders_set
from orders
group by user_id, order_dow
;

结果：

user_id	order_dow	orders_set
45447   0       2251154|2420221
45447   1       2832829|815074|2133847|2662551|2054208|795802|1440482|1251735
45447   2       2659717|3155914|1114894|34662|689946|3246882|1629660|1935537|1100681
45447   3       2207226
45447   5       3071613|2147817|2264027
45448   0       741179|2262141|3256745|1343319|1123797|1138362|774332
45448   1       1206935|354849
45448   2       1173584|804150|2731655|2346092
45448   3       3005817|2402066|708383
45448   4       378150|648905
45448   5       199752|2157723|387694
45448   6       531980|1290464

列转行

用之前列转行的结果创建一个测试表进行转列测试。

hive> create table orders_cp as select
    > user_id,
    > order_dow,
    > order_set
    > from (
    > select user_id
    > ,order_dow
    > ,concat_ws("|", collect_set(order_id)) order_set
    > from orders
    > group by user_id, order_dow
    > ) t
    > ;

hive> select * from orders_cp limit 5;
OK
orders_cp.user_id       orders_cp.order_dow     orders_cp.order_set
1       1       550135|3108588|2295261
1       2       2539329|3367565
1       3       2398795|473747
1       4       2254736|431534|2550362|1187899
10      0       1822501
Time taken: 0.052 seconds, Fetched: 5 row(s)

列转行：

lateral view用于和split、explode等UDTF一起使用，它能够将一列数据拆分成多行数据，在此基础上可以对拆分后的数据进行聚合。

select
	user_id,
	order_dow,
	order_id
from orders_cp
lateral view explode(split(order_set, "|")) temp_tbl as order_id
;

hive> select
    > user_id,
    > order_dow,
    > order_id
    > from orders_cp
    > lateral view explode(split(order_set, "\\|")) temp_tbl as order_id # 转义字符转义
    > limit 10;
OK
user_id order_dow       order_id
1       1       550135
1       1       3108588
1       1       2295261
1       2       2539329
1       2       3367565
1       3       2398795
1       3       473747
1       4       2254736
1       4       431534
1       4       2550362

开窗函数练习

普通的聚合函数聚合的行集是组，开窗函数聚合的行集是窗口。因此，普通的聚合函数每组(Group By)只返回一个值，而开窗函数则可为窗口的每行都返回一个值。简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。

开窗函数分为聚合开窗函数和排序开窗函数。

我们在这里创建一个公司-员工-薪资表来充当数据测试集，数据来自当下传的比较火的互联网公司时薪表：

hive> CREATE table company_stuff_salary(
    > id int,
    > stuff_no int,
    > company_name string,
    > salary_per_hour int,
    > salary_medium_year int,
    > apartment string
    > )
    > ;
OK
Time taken: 0.405 seconds

hive> ALTER TABLE company_stuff_salary change apartment level_status string;
OK
Time taken: 0.15 seconds


hive> insert into table company_stuff_salary values
    >        (1, 111, 'Pinduoduo', 210, 630000, 'Junior'),
    >        (2, 112, 'Pinduoduo', 238, 720000, 'Intermediate'),
    >        (3, 113, 'Microsoft', 200, 380000, 'Junior'),
    >        (4, 114, 'Microsoft', 263, 510000, 'Intermediate'),
    >        (5, 115, 'Microsoft', 398, 770000, 'Intermediate'),
    >        (6, 116, 'Microsoft', 399, 780000, 'Senior'),
    >        (7, 117, 'ByteDance', 139, 380000, 'Junior'),
    >        (8, 118, 'ByteDance', 199, 530000, 'Intermediate'),
    >        (9, 119, 'ByteDance', 338, 920000, 'Senior'),
    >        (10, 120, 'BaiDu', 153, 370000, 'Junior'),
    >        (11, 121, 'BaiDu', 214, 520000, 'Intermediate'),
    >        (12, 122, 'BaiDu', 351, 860000, 'Senior'),
    >        (13, 123, 'Bilibili', 148, 360000, 'Junior'),
    >        (14, 124, 'Bilibili', 179, 440000, 'Intermediat'),
    >        (15, 125, 'Bilibili', 291, 710000, 'Senior'),
    >        (16, 126, 'HuaWei', 171, 450000, 'Intermediat'),
    >        (17, 127, 'HuaWei', 102, 270000, 'Junior'),
    >        (18, 128, 'HuaWei', 246, 650000, 'Senior'),
    >        (19, 129, 'Tencent', 197, 500000, 'Junior'),
    >        (20, 130, 'Tencent', 149, 380000, 'Intermediat'),
    >        (21, 131, 'Tencent', 300, 760000, 'Senior')
    >        ;
Query ID = root_20211122135624_dbc70c58-13bd-40ff-b49f-c1ac95124b60
Total jobs = 3
Launching Job 1 out of 3

聚合开窗函数

Count开窗函数案例

select *,
-- 以所有行作为窗口进行聚合输出
count(id) over() as count1,
-- 以按company_name分组的所有行作为窗口进行count聚合
count(id) over(partition by level_status) as count2,
-- 以按company_name分组，按照salary_per_hour排序的所有行作为窗口进行count聚合
count(id) over(partition by level_status order by salary_per_hour) as count3,
-- 以company_name分组，按salary_per_hour排序，并按当前行开始往前1行和往后一行(当前行+往前一行+往后一行)的行作为窗口
count(id) over(partition by level_status order by salary_per_hour rows between 1 preceding and 1 following) as count4
from company_stuff_salary
;

对于count1: 
一共有21行数据，所以count1是21，对每一行的count1输出的都是窗口中满足条件的记录总数；

对于count2:
按照员工等级分组，对每个等级分组的窗口的记录的id进行count聚合，最后结果就等于这个分组里的数据记录数；

对于count3：
按照员工等级分组，并按照salary_per_hour进行排序，窗口大小即为同一分组下排序好的到当前顺序的行为止的所有记录，根据结果，已知Tencent的时薪是分组Intermediat下最低的，排在第一位，所以到当前行为止前面没有记录，窗口大小为1。
而Microsoft的时薪是分组Intermediat下最高的，所以到stuffno为115的行为止前面一共有7条记录，加上当前行即为8条，即窗口大小为8。

对于count4
同理与count3，不过窗口大小还需要加上前一行（0）和往后一行（1），即当前所在行的窗口大小为当前行+往前一行+往后一行；

对于sum、avg、min、max等聚合函数也是如此。

这里再演示一下max()开窗函数：

select *,
-- 以所有行作为窗口进行聚合输出
max(salary_per_hour) over() as max1,
-- 以按company_name分组的所有行作为窗口进行max聚合
max(salary_per_hour) over(partition by level_status) as max2,
-- 以按company_name分组，按照salary_per_hour排序的所有行作为窗口进行max聚合
max(salary_per_hour) over(partition by level_status order by salary_per_hour) as max3,
-- 以company_name分组，按salary_per_hour排序，并按当前行开始往前1行和往后一行(当前行+往前一行+往后两行)的行作为窗口
max(salary_per_hour) over(partition by level_status order by salary_per_hour rows between 1 preceding and 1 following) as max4
from company_stuff_salary
; 

其他聚合开窗函数总结

• first_value(): 返回分区中的第一个值
• last_value(): 返回分区中的最后一个值
• lag(col, n, default): 用于统计统计窗口内col列往上第n行的值，如果为NULL则采用设置的默认值default
• lead(col, n, default): 与上面的开窗口函数作用相似但顺序相反，它是往下取col第n行的值；
• cume_dist开窗函数： 计算某个窗口或分区中某个值的累计分布。假定升序排列，则用一下公式确定累计分布：
  • 小于当前值x的行数 / 窗口或partition分区内的总行数；其中x为order by子句中指定的列的当前行中的值

排序开窗函数

rank开窗函数

该开窗函数基于over子句中的order by确定一组中一个值的排名。如果存在partition by。则为每个分区组中的每个值排名。排名是非连续的，如果两个相邻行的值相同，则在前面的行的排名相同为n，则下一行的排名为n+2。

注意：

• rank()排序函数必须和over子句中的order by配合使用，

• rank()排序函数的over子句里不能有rows between 1 preceding and 1 following，使用会报错：

Expecting left window frame boundary for function rank() org.apache.hadoop.hive.ql.parse.WindowingSpec$WindowSpec@254080e1 as rank_window_2 to be unbounded. Found : 1

select *,
-- 以所有行作为窗口进行聚合输出
rank() over(order by salary_per_hour) as rank1,
-- 以按company_name分组，按照salary_per_hour排序的所有行作为窗口进行rank聚合
rank() over(partition by level_status order by salary_per_hour) as rank2
from company_stuff_salary
; 

dense_rank()开窗函数

区别与rank()的一点在于，当前面两行的值一致，排名相同，则下一行的排名为n+1。

select *,
dense_rank() over(order by salary_per_hour) as dense_rank1,
dense_rank() over(partition by level_status order by salary_per_hour) as dense_rank2,
dense_rank() over(partition by level_status order by salary_per_hour,salary_medium_year) as dense_rank3
from company_stuff_salary
; 

ntile开窗函数

ntile(n)开窗函数将分区中已排序的行划分为大小尽可能相等的指定数量排名的组，并返回给定行所在的组的排名。

select *,
ntile(1) over(order by salary_per_hour) as ntile1,
ntile(2) over(order by salary_per_hour) as ntile2,
ntile(2) over(partition by level_status order by salary_per_hour) as ntile3,
ntile(3) over(partition by level_status order by salary_per_hour,salary_medium_year) as ntile4
from company_stuff_salary
; 

ntile1：
排序好的分区只有1个，自然只能分为一组，组的排名自然为1

ntile2：
排序好分分区有1个，要划分为两组，每组10条记录；

ntile3：
排序好分分区有三个，要划分的分区有两个，因此每个分区里都要划分两个分区并返回分区号

ntile4：
同理

row_number开窗函数

从1开始对分区的数据进行排序，区别于前面的rank和dense_rank函数，排名是连续的，不存在相同或跳跃性的排名。

select *,
row_number() over(order by salary_per_hour) as row_number1,
row_number() over(partition by level_status order by salary_per_hour) as row_number2,
row_number() over(partition by level_status order by salary_per_hour,salary_medium_year) as row_number3
from company_stuff_salary
; 

percent_rabk()开窗函数

计算给定行的百分比排名，可以用来计算超过了百分之多少的人，案例为360开机小组手。

计算公式：(当前行rank值-1) / (分组内的总行数 - 1)

select *,
row_number() over(partition by level_status order by salary_per_hour) as row_number,
PERCENT_RANK() over(partition by level_status order by salary_per_hour,salary_medium_year) as PERCENT_RANK
from company_stuff_salary
;