网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
分组 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 中),最后完成最终的聚合操作。
🚀
假设我创建了一张表,其中包含了 2016 年客户完成的所有交易的详细信息:
CREATE TABLE transaction_details
(cust_id INT, amount FLOAT, month STRING, country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ ;
现在我插入了 100 万条数据,我想知道每个月的总收入。
问:如何高效的统计出结果,写出步骤即可。 (提示:动态分区)
1.首先分析这个需求,其实并不难,但是由于题目说了,要高效.而且数据量也不小,直接写sql查询估计肯定会挂.
2.分析:
a.我们可以通过根据每个月对表进行分区来解决查询慢的问题。 因此,对于每个月我们将只扫描分区的数据,而不是整个数据集。
b.但是我们不能直接对现有的非分区表进行分区。所以我们会采取以下步骤来解决这个问题:
c.创建一个分区表,partitioned_transaction:
i.create table partitioned_transaction
(cust_id int, amount float, country string) partitioned by (month string)
row format delimited fields terminated by ‘,’ ;
d.在 Hive 中启用动态分区:
i.SET hive.exec.dynamic.partition=true;
ii.SET hive.exec.dynamic.partition.mode=nonstrict;
e.将数据从非分区表导入到新创建的分区表中:
i.insert overwrite table partitioned_transaction partition (month) select cust_id, amount, country, month from transaction_details;
f.使用新建的分区表实现需求。
欢迎扫码关注我的公众号,在这里我将专注分享数据仓库、数据建模与大数据技术的相关内容。
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
(https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!