从小案例学习Hive——特征分析和偏移分析、企业级优化技巧

一、特征分析与偏移分析

什么是窗口函数呢？

1. Window Function又称为窗口函数、分析函数。
2. 窗口函数与聚合函数类似，但是每一行数据都生成一个结果。
3. 聚合函数(比如sum、avg、max等)可以将多行数据按照规定聚合为一
   行，一般来讲聚集后的行数要少于聚集前的行数。但是有时我们想要既
   显示聚集前的数据，又要显示聚集后的数据，这时便引入了窗口函数。
4. 窗口函数是在select时执行的，位于order by之前。

1.累计计算窗口函数

1、sum(…) over(……)

需求1-1：对2018年公司的支付总额按月度累计进行分析

SELECT a.month,a.pay_amount,
sum(a.pay_amount) over(order by a.month)
FROM
(SELECT month(dt) month,
sum(pay_amount) pay_amount
FROM user_trade
WHERE year(dt)=2018
GROUP BY month(dt))a;

需求1-2：对2017和2018年公司的支付总额按月度累计进行分析，按年度进行汇总

SELECT a.year,
a.month,
a.pay_amount,
sum(a.pay_amount) over(partition by a.year order by
a.month )
FROM
(SELECT year(dt) year,
month(dt) month,
sum(pay_amount) pay_amount
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY year(dt),
month(dt))a;

需求2：对2018年每个月的近三个月进行移动地求平均支付金额

SELECT a.month,
a.pay_amount,
avg(a.pay_amount) over(order by a.month rows between 2 preceding and current row)
FROM
(SELECT month(dt) month,sum(pay_amount) pay_amount
FROM user_trade
WHERE year(dt)=2018
GROUP BY month(dt))a;

3、语法总结
sum(…A…) over(partition by …B… order by …C… rows between …
D1… and …D2…)
avg(…A…) over(partition by …B… order by …C… rows between …D1…
and …D2…)
A：需要被加工的字段名称
B：分组的字段名称
C：排序的字段名称
D：计算的行数范围
rows between unbounded preceding and current row——包括
本行和之前所有的行
rows between current row and unbounded following——包括
本行和之后所有的行
rows between 3 preceding and current row——包括本行以内和
前三行
rows between 3 preceding and 1 following——从前三行到下一行
(5行)

2.分区排序窗口函数

row_number() over(……)、rank() over(……)、
dense_rank() over(……)
这三个函数的作用都是返回相应规则的排序序号
row_number() over(partition by …A… order by …B… )
rank() over(partition by …A… order by …B… )
dense_rank() over(partition by …A… order by …B… )
A：分组的字段名称
B：排序的字段名称
注意：row_number()的这个括号内是不加任何字段名称的，rank() 和
dense_rank() 同理。
row_number：它会为查询出来的每一行记录生成一个序号，依次排
序且不会重复。
rank&dense_rank：dense_rank函数出现相同排名时，将不跳过相
同排名号，rank值紧接上一次的rank值。在各个分组内，rank()是跳跃
排序，有两个第一名时接下来就是第三名，dense_rank()是连续排序，
有两个第一名时仍然跟着第二名。
需求3：对2019年1月份用户的购买爱好进行分析
参考实现：2019年1月，用户购买商品品类数量的排名

SELECT user_name,
count(distinct goods_category),
row_number() over(order by count(distinct goods_category)),
rank() over(order by count(distinct goods_category)),
dense_rank() over(order by count(distinct goods_category))
FROM user_trade
WHERE substr(dt,1,7)='2019-01'
GROUP BY user_name;

需求4：选出2019年支付金额排名在第10、20、30名的用户

SELECT a.user_name,a.pay_amount,a.dr
FROM
(SELECT user_name,
sum(pay_amount) pay_amount,dense_rank() over(order by sum(pay_amount)desc) dr
FROM user_trade
WHERE year(dt)=2019
GROUP BY user_name)a
WHERE a.dr in (10,20,30);

3. 分组排序窗口函数

ntile(n) over(……)
ntile(n) over(partition by …A… order by …B… )
n：切分的片数
A：分组的字段名称
B：排序的字段名称

• NTILE(n)：用于将分组数据按照顺序切分成n片，返回当前切片值
• NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY
  …… ORDER BY …… ROWS BETWEEN 3 PRECEDING AND CURRENT
  ROW)
• 如果切片不均匀，默认增加到第一个切片的分布
  需求5:将2019年1月的支付用户，按照支付金额分成5组

SELECT user_name,
sum(pay_amount) pay_amount,
ntile(5) over(order by sum(pay_amount) desc) level
FROM user_trade
WHERE substr(dt,1,7)='2019-01'
GROUP BY user_name;

需求6:选出2019年退款金额排名前10%的用户

SELECT a.user_name,a.refund_amount,a.level
FROM
(SELECT user_name,sum(refund_amount) refund_amount,
ntile(10) over(order by sum(refund_amount) desc) level
FROM user_refund
WHERE year(dt)=2019
GROUP BY user_name)a
WHERE a.level=1;

4.偏移分析窗口函数

Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据
(Lag)和后N行的数据(Lead)作为独立的列。
在实际应用当中，若要用到取今天和昨天的某字段差值时，Lag和Lead函数的应用就显得尤为重要。当然，这种操作可以用表的自连接实现，但是LAG和LEAD与left join、right join等自连接相比，效率更高，SQL更简洁。
lag(exp_str,offset,defval) over(partion by ……order by ……)
lead(exp_str,offset,defval) over(partion by ……order by ……)

• exp_str是字段名称。
• offset是偏移量，即是上1个或上N个的值，假设当前行在表中排在第5行，则offset 为3，则表示我们所要找的数据行就是表中的第2行（即5-3=2）。offset默认值为1。
• defval默认值，当这两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag()函数将defval这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

需求7：支付时间间隔超过100天的用户数【跨时间潜在VIP用户流失分
析】

SELECT count(distinct user_name)
FROM
(SELECT user_name,dt,
lead(dt) over(partition by user_name order by dt) as lead_dt
FROM user_trade
WHERE dt>'0' )a
WHERE a.lead_dt is not null
and datediff(a.lead_dt,a.dt)>100;

需求8：每个城市，不同性别，2018年支付金额最高的TOP3用户

SELECT c.user_name,c.city,c.sex,c.pay_amount,c.rank
FROM
(SELECT a.user_name,b.city,b.sex,a.pay_amount,
row_number() over(partition by b.city,b.sex
order by a.pay_amount desc) rank
FROM
(SELECT user_name,
sum(pay_amount) pay_amount
FROM user_trade
WHERE year(dt)=2018
GROUP BY user_name)a
LEFT JOIN user_info b
on a.user_name=b.user_name)c
WHERE c.rank<=3;

需求9：每个手机品牌退款金额前25%的用户【跨手机品牌用户退款分析】

SELECT *
FROM
(SELECT a.user_name,
extra2['phonebrand'] as phonebrand,
a.refund_amount,
ntile(4) over(partition by
extra2['phonebrand'] order by a.refund_amount desc) level
FROM
(SELECT user_name,
sum(refund_amount) refund_amount
FROM user_refund
WHERE dt>'0'
GROUP BY user_name)a
LEFT JOIN user_info b
on a.user_name=b.user_name)c
WHERE c.level=1;

企业级优化技巧

技巧1：去重技巧——用group by来替换distinct

–在2019年购买后又退款的用户–

SELECT a.user_name
FROM
(SELECT distinct user_name
FROM user_trade
WHERE year(dt)=2019)a
JOIN
(SELECT distinct user_name
FROM user_refund
WHERE year(dt)=2019)b on a.user_name=b.user_name;

优化写法：

SELECT a.user_name
FROM
(SELECT user_name
FROM user_trade
WHERE year(dt)=2019
GROUP BY user_name)a
JOIN
(SELECT user_name
FROM user_refund
WHERE year(dt)=2019
GROUP BY user_name)b on a.user_name=b.user_name;

技巧2：聚合技巧——利用窗口函数grouping sets、cube、rollup

1、grouping sets
–性别、城市、等级用户分布–

SELECT sex,
city,
level,
count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
GROUPING SETS (sex,city,level);

注意：聚合结果均在同一列，分类字段用不同列来进行区分
GROUPING SETS()：在group by查询中，根据不同的维度组合进行聚合，等价于
将不同维度的group by结果集进行union all。聚合规则在括号中进行指定。
2、cube
cube：根据group by 维度的所有组合进行聚合
–性别、城市、等级的各种组合的用户分布–

SELECT sex,
city,
level,
count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
GROUPING SETS (sex,city,level,(sex,city),(sex,level),
(city,level),(sex,city,level));

优化写法
–性别、城市、等级的各种组合的用户分布–

SELECT sex,
city,
level,
count(distinct user_id)
FROM user_info
GROUP BY sex,city,level
with cube;

3、rollup
rollup：以最左侧的维度为主，进行层级聚合，是cube的子集。
举例：
如果我想同时计算出，每个月的支付金额，以及每年的总支付金额，该怎
么办？

SELECT a.dt,
sum(a.year_amount),
sum(a.month_amount)
FROM
(SELECT substr(dt,1,4) as dt,
sum(pay_amount) year_amount,
0 as month_amount
FROM user_trade
WHERE dt>'0'
GROUP BY substr(dt,1,4)
UNION ALL
SELECT substr(dt,1,7) as dt,
0 as year_amount,
sum(pay_amount) as month_amount
FROM user_trade
WHERE dt>'0'
GROUP BY substr(dt,1,7)
)a
GROUP BY a.dt;

优化写法

SELECT year(dt) as year,
month(dt) as month,
sum(pay_amount)
FROM user_trade
WHERE dt>'0'
GROUP BY year(dt),
month(dt)
with rollup;

技巧3：换个思路解题

条条大路通罗马，写SQL亦是如此，能达到同样效果的SQL有很多种，要学
会思路转换，灵活应用。

技巧4：union all时可以开启并发执行

参数设置：set hive.exec.parallel=true
可以并行的任务较多时，开启并发执行，可以提高执行效率。
–每个用户的支付和退款金额汇总–

SELECT a.user_name,sum(a.pay_amount),
sum(a.refund_amount)
FROM
(
SELECT user_name,
sum(pay_amount) as pay_amount,
0 as refund_amount
FROM user_trade
WHERE dt>'0'
GROUP BY user_name
UNION ALL
SELECT user_name,
0 as pay_amount,
sum(refund_amount) as refund_amount
FROM user_refund
WHERE dt>'0'
GROUP BY user_name
)a
GROUP BY a.user_name;

技巧5：利用lateral view进行行转列

–每个品类的购买用户数–

SELECT b.category,
count(distinct a.user_name)
FROM user_goods_category a
lateral view explode(split(category_detail,',')) b as
category
GROUP BY b.category;

split()：字符串分割函数
explode：行转列函数
列转行函数：concat_ws(’,’,collect_set(column))

技巧6：表连接优化

• 小表在前，大表在后

Hive假定查询中最后的一个表是大表，它会将其它表缓存起来，然后扫描
最后那个表。

• 使用相同的连接键

当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接
键的话，那么只会产生一个MapReduce job。

• 尽早的过滤数据

减少每个阶段的数据量，对于分区表要加分区，同时只选择需要使用到的
字段。

• 逻辑过于复杂时，引入中间表

技巧7：如何解决数据倾斜

数据倾斜的表现：
任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少
量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他
reduce差异过大。
数据倾斜的原因与解决办法：

• 空值产生的数据倾斜

解决：如果两个表连接时，使用的连接条件有很多空值，建议在连接条件
中增加过滤
例如：on a.user_id=b.user_id and a.user_id is not null

• 大小表连接(其中一张表很大，另一张表非常小)

解决：将小表放到内存里，在map端做Join
例如：

• 两个表连接条件的字段数据类型不一致

解决：将连接条件的字段数据类型转换成一致的
例如：on a.user_id=cast(b.user_id as string)

技巧8：如何计算按月累计去重

–2017、2018年按月累计去重的购买用户数–

SELECT b.year,
b.month,
sum(b.user_num) over(partition by b.year order by
b.month)
FROM
(SELECT a.year,
a.month,
count(distinct a.user_name) user_num
FROM
(SELECT year(dt) as year,
user_name,
min(month(dt)) as month
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY year(dt),
user_name)a
GROUP BY a.year,
a.month)b
ORDER BY b.year,
b.month
limit 24;

方法2：

set hive.mapred.mode=nonstrict;
SELECT b.month,
count(distinct a.user_name)
FROM
(SELECT substr(dt,1,7) as month,
user_name
FROM user_trade
WHERE year(dt) in (2017,2018)
GROUP BY substr(dt,1,7),
user_name)a
CROSS JOIN
(SELECT month
FROM dim_month)b
WHERE b.month>=a.month and
substr(a.month,1,4)=substr(b.month,1,4)
GROUP BY b.month;