hive数仓sql面试题2021

99 篇文章 2 订阅
23 篇文章 0 订阅

 

 

一、求单月访问次数和总访问次数

1、数据说明

数据字段说明

用户名,月份,访问次数

数据格式

A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1

2、数据准备

(1)创建表

use myhive;
create external table if not exists t_access(
uname string comment '用户名',
umonth string comment '月份',
ucount int comment '访问次数'
) comment '用户访问表' 
row format delimited fields terminated by "," 
location "/hive/t_access"; 

(2)导入数据

load data local inpath "/home/hadoop/access.txt" into table t_access;

(3)验证数据

select * from t_access;

3、结果需求

现要求出:
每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数,结果数据格式如下

4、需求分析

此结果需要根据用户+月份进行分组

(1)先求出当月访问次数

--求当月访问次数
create table tmp_access(
name string,
mon string,
num int
); 

insert into table tmp_access 
select uname,umonth,sum(ucount)
 from t_access t group by t.uname,t.umonth;

select * from tmp_access;

(2)tmp_access进行自连接视图

create view tmp_view as 
select a.name anme,a.mon amon,a.num anum,b.name bname,b.mon bmon,b.num bnum from tmp_access a join tmp_access b 
on a.name=b.name;

select * from tmp_view;

(3)进行比较统计

select anme,amon,anum,max(bnum) as max_access,sum(bnum) as sum_access 
from tmp_view 
where amon>=bmon 
group by anme,amon,anum;

二、学生课程成绩

1、说明

use myhive;
CREATE TABLE `course` (
  `id` int,
  `sid` int ,
  `course` string,
  `score` int 
) ;
// 插入数据
// 字段解释:id, 学号, 课程, 成绩
INSERT INTO `course` VALUES (1, 1, 'yuwen', 43);
INSERT INTO `course` VALUES (2, 1, 'shuxue', 55);
INSERT INTO `course` VALUES (3, 2, 'yuwen', 77);
INSERT INTO `course` VALUES (4, 2, 'shuxue', 88);
INSERT INTO `course` VALUES (5, 3, 'yuwen', 98);
INSERT INTO `course` VALUES (6, 3, 'shuxue', 65);

2、需求

求:所有数学课程成绩 大于 语文课程成绩的学生的学号

1、使用case...when...将不同的课程名称转换成不同的列

create view tmp_course_view as
select sid, case course when "shuxue" then score else 0 end  as shuxue,  
case course when "yuwen" then score else 0 end  as yuwen from course;  

select * from tmp_course_view;

 

2、以sid分组合并取各成绩最大值

create view tmp_course_view1 as
select aa.sid, max(aa.shuxue) as shuxue, max(aa.yuwen) as yuwen from tmp_course_view aa group by sid;  

select * from tmp_course_view1;

3、比较结果

select * from tmp_course_view1 where shuxue > yuwen;

三、求每一年最大气温的那一天  + 温度

 1、说明

数据格式

2010012325

具体数据

2014010216
2014010410
2012010609
2012010812
2012011023
2001010212
2001010411
2013010619
2013010812
2013011023
2008010216
2008010414
2007010619
2007010812
2007011023
2010010216
2010010410
2015010649
2015010812
2015011023

数据解释

2010012325表示在2010年01月23日的气温为25度

2、 需求

比如:2010012325表示在2010年01月23日的气温为25度。现在要求使用hive,计算每一年出现过的最大气温的日期+温度。
要计算出每一年的最大气温。我用
select substr(data,1,4),max(substr(data,9,2)) from table2 group by substr(data,1,4);
出来的是 年份 + 温度 这两列数据例如 2015 99

但是如果我是想select 的是:具体每一年最大气温的那一天 + 温度 。例如 20150109 99
请问该怎么执行hive语句。。
group by 只需要substr(data,1,4),
但是select substr(data,1,8),又不在group by 的范围内。 
是我陷入了思维死角。一直想不出所以然。。求大神指点一下。
在select 如果所需要的。不在group by的条件里。这种情况如何去分析?

3、解析

(1)创建一个临时表tmp_weather,将数据切分

create table tmp_weather as 
select substr(data,1,4) years,substr(data,5,2) months,substr(data,7,2) days,substr(data,9,2) temp from weather;
select * from tmp_weather;

(2)创建一个临时表tmp_year_weather

create table tmp_year_weather as 
select substr(data,1,4) years,max(substr(data,9,2)) max_temp from weather group by substr(data,1,4);
select * from tmp_year_weather;

(3)将2个临时表进行连接查询

select * from tmp_year_weather a join tmp_weather b on a.years=b.years and a.max_temp=b.temp;

四、求学生选课情况

1、数据说明

(1)数据格式

id course 
1,a 
1,b 
1,c 
1,e 
2,a 
2,c 
2,d 
2,f 
3,a 
3,b 
3,c 
3,e

(2)字段含义

表示有id为1,2,3的学生选修了课程a,b,c,d,e,f中其中几门。

2、数据准备

(1)建表t_course

create table t_course(id int,course string)
row format delimited fields terminated by ",";

(2)导入数据

load data local inpath "/home/hadoop/course/course.txt" into table t_course;

3、需求

编写Hive的HQL语句来实现以下结果:表中的1表示选修,表中的0表示未选修

id    a    b    c    d    e    f
1     1    1    1    0    1    0
2     1    0    1    1    0    1
3     1    1    1    0    1    0

4、解析

第一步:

select collect_set(course) as courses from id_course;

第二步:

set hive.strict.checks.cartesian.product=false;

create table id_courses as select t1.id as id,t1.course as id_courses,t2.course courses 
from 
( select id as id,collect_set(course) as course from id_course group by id ) t1 
join 
(select collect_set(course) as course from id_course) t2;

启用严格模式:hive.mapred.mode = strict // Deprecated
hive.strict.checks.large.query = true
该设置会禁用:1. 不指定分页的orderby
         2. 对分区表不指定分区进行查询 
         3. 和数据量无关,只是一个查询模式

hive.strict.checks.type.safety = true
严格类型安全,该属性不允许以下操作:1. bigint和string之间的比较
                  2. bigint和double之间的比较

hive.strict.checks.cartesian.product = true
该属性不允许笛卡尔积操作

第三步:得出最终结果:
思路:
拿出course字段中的每一个元素在id_courses中进行判断,看是否存在。

select id,
case when array_contains(id_courses, courses[0]) then 1 else 0 end as a,
case when array_contains(id_courses, courses[1]) then 1 else 0 end as b,
case when array_contains(id_courses, courses[2]) then 1 else 0 end as c,
case when array_contains(id_courses, courses[3]) then 1 else 0 end as d,
case when array_contains(id_courses, courses[4]) then 1 else 0 end as e,
case when array_contains(id_courses, courses[5]) then 1 else 0 end as f 
from id_courses;

五、求月销售额和总销售额

1、数据说明

(1)数据格式

a,01,150
a,01,200
b,01,1000
b,01,800
c,01,250
c,01,220
b,01,6000
a,02,2000
a,02,3000
b,02,1000
b,02,1500
c,02,350
c,02,280
a,03,350
a,03,250

(2)字段含义

店铺,月份,金额

2、数据准备

(1)创建数据库表t_store

use class;
create table t_store(
name string,
months int,
money int
) 
row format delimited fields terminated by ",";

(2)导入数据

load data local inpath "/home/hadoop/store.txt" into table t_store;

3、需求

编写Hive的HQL语句求出每个店铺的当月销售额和累计到当月的总销售额

4、解析

(1)按照商店名称和月份进行分组统计

create table tmp_store1 as 
select name,months,sum(money) as money from t_store group by name,months;

select * from tmp_store1;

(2)对tmp_store1 表里面的数据进行自连接

create table tmp_store2 as 
select a.name aname,a.months amonths,a.money amoney,b.name bname,b.months bmonths,b.money bmoney from tmp_store1 a 
join tmp_store1 b on a.name=b.name order by aname,amonths;

select * from tmp_store2;

(3)比较统计

select aname,amonths,amoney,sum(bmoney) as total from tmp_store2 where amonths >= bmonths group by aname,amonths,amoney;

分类: Hive

一、课程简介<br /> <br /> 随着技术的飞速发展,经过多年的数据积累,各互联网公司已保存了海量的原始数据和各种业务数据,所以数据仓库技术是各大公司目前都需要着重发展投入的技术领域。数据仓库是面向分析的集成化数据环境,为企业所有决策制定过程,提供系统数据支持的战略集合。通过对数据仓库中数据的分析,可以帮助企业改进业务流程、控制成本、提高产品质量等。<br /> <br /> 二、课程内容<br /> <br /> 本次精心打造的数仓项目的课程,从项目架构的搭建,到数据采集模块的设计、数仓架构的设计、实战需求实现、即席查询的实现,我们针对国内目前广泛使用的Apache原生框架和CDH版本框架进行了分别介绍,Apache原生框架介绍中涉及到的技术框架包括Flume、Kafka、Sqoop、MySql、HDFS、Hive、Tez、Spark、Presto、Druid等,CDH版本框架讲解包括CM的安装部署、Hadoop、Zookeeper、Hive、Flume、Kafka、Oozie、Impala、HUE、Kudu、Spark的安装配置,透彻了解不同版本框架的区别联系,将大数据全生态系统前沿技术一网打尽。在过程中对大数据生态体系进行了系统的讲解,对实际企业数仓项目中可能涉及到的技术点都进行了深入的讲解和探讨。同时穿插了大量数仓基础理论知识,让你在掌握实战经验的同时能够打下坚实的理论基础。<br /> <br /> <br /> 三、课程目标<br /> <br /> 本课程以国内电商巨头实际业务应用场景为依托,对电商数仓的常见实战指标以及难点实战指标进行了详尽讲解,具体指标包括:每日、周、月活跃设备明细,留存用户比例,沉默用户、回流用户、流失用户统计,最近连续3周活跃用户统计,最近7天内连续3天活跃用户统计,GMV成交总额分析,转化率及漏斗分析,品牌复购率分析、订单表拉链表的设计等,让学生拥有更直观全面的实战经验。通过对本课程的学习,对数仓项目可以建立起清晰明确的概念,系统全面的掌握各项数仓项目技术,轻松应对各种数仓难题。<br /> <br /> 四、课程亮点<br /> 本课程结合国内多家企业实际项目经验,特别加入了项目架构模块,从集群规模的确定到框架版本选型以及服务器选型,手把手教你从零开始搭建大数据集群。并且总结大量项目实战中会遇到的问题,针对各个技术框架,均有调优实战经验,具体包括:常用Linux运维命令、Hadoop集群调优、Flume组件选型及性能优化、Kafka集群规模确认及关键参数调优。通过这部分学习,助学生迅速成长,获取前沿技术经验,从容解决实战问题。<br /> <br /> <br /> <div> <br /> </div>
参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:岁月 设计师:pinMode 返回首页

打赏作者

Hero.Lin

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值