Hive 热门数据分析笔试题（干货满满，持续更新中

三、数据分析题（案例）

场景举例：北京市学生成绩分析 💥

成绩的数据格式：时间,学校,年纪,姓名,科目,成绩

样例数据如下:

2013,北大,1,裘容絮,语文,97
2013,北大,1,庆眠拔,语文,52
2013,北大,1,乌洒筹,语文,85
2012,清华,0,钦尧,英语,61
2015,北理工,3,冼殿,物理,81
2016,北科,4,况飘索,化学,92
2014,北航,2,孔须,数学,70
2012,清华,0,王脊,英语,59
2014,北航,2,方部盾,数学,49
2014,北航,2,东门雹,数学,77

create table t_score (
time int,
school string,
class int,
name string,
subjects string,
score int
)
row format delimited fields terminated by ',';

load data local inpath "/opt/data/a.csv" into table t_score

问题：

分组 TopN，选出2014年每个学校、每个年级、分数前三的科目

select t.\*
	from
(
select
	time,
	school,
	class,
	score,
	row_number() over (partition by school, class, subjects order by score desc) rank_code
from t_score
	where time = "2014"
) t
	where t.rank_code <= 3;

详解如下：
  row_number函数：row_number() 按指定的列进行分组生成行序列，从 1 开始，如果两行记录的分组列相同，则行序列 +1。
  over 函数：是一个窗口函数。
  over (order by score) 按照 score 排序进行累计，order by 是个默认的开窗函数。
  over (partition by class) 按照班级分区。
  over (partition by class order by score) 按照班级分区，并按着分数排序。
  over (order by score range between 2 preceding and 2 following) 窗口范围为当前行的数据幅度减2加2后的范围内的数据求和。

优化：

row_number() over (distribute by school, class, subjects sort by score desc) rank_code

2014年，北航，每个班级，每科的分数，及分数上下浮动 2 分的总和

select time,school, class, subjects, score,
sum(score) over (order by score range between 2 preceding and 2 following) sscore
from t_score
where time = "2014" and school="北航";

over (order by score rows between 2 preceding and 2 following)：窗口范围为当前行前后各移动2行。

where 与 having：2012年，清华 0 年级，总成绩大于 200 分的学生以及学生数

select \*,sum(score) as total_score,
count(1) over (partition by school, class) 
from t_score where school="清华" and class = 0 and time=2012
group by school, class, name,time,subjects,score having total_score > 50;

having 是分组（group by）后的筛选条件，分组后的数据组内再筛选，也就是说 HAVING 子句可以让我们筛选成组后的各组数据。
where 则是在分组，聚合前先筛选记录。也就是说作用在 GROUP BY 子句和 HAVING 子句前。

四、情景分析题

今年加入进来了 10 个学校，学校数据差异很大计算每个学校的平均分。

该题主要是考察数据倾斜的处理方式。

group by 方式很容易产生数据倾斜 ❗，需要注意一下几点：

Map 端部分聚合
hive.map.aggr=true（用于设定是否在 map 端进行聚合，默认值为真，相当于 combine） 
hive.groupby.mapaggr.checkinterval=100000（用于设定 map 端进行聚合操作的条数）

有数据倾斜时进行负载均衡
设定 hive.groupby.skewindata，当选项设定为 true 是，生成的查询计划有两个 MapReduce 任务。

（先打散数据）
第一个 MapReduce 中，map 的输出结果集合会随机分布到 reduce 中， 每个 reduce 做部分聚合操作，并输出结果。
这样处理的结果是，相同的 group by key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的；

第二个 MapReduce 任务再根据预处理的数据结果按照 group by key 分布到 reduce 中
（这个过程可以保证相同的 group by key 分布到同一个 reduce 中），最后完成最终的聚合操作。


![img](https://img-blog.csdnimg.cn/img_convert/e4a39232561cfaa20289a5daa6d96f79.png)
![img](https://img-blog.csdnimg.cn/img_convert/b1a0c178893848239debea65f7db7dbb.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**