1.第一题：
sid name gender age academy dt chinese math english
95001 李勇 男 20 CS 2017-08-31 56 28 62
95002 刘晨 女 19 IS 2017-08-31 84 22 38
95003 王敏 女 22 MA 2017-08-31 38 80 82
95004 张立 男 19 IS 2017-08-31 39 65 4
95005 刘刚 男 18 MA 2018-08-31 18 28 79
95006 孙庆 男 23 CS 2018-08-31 12 67 16
95007 易思玲 女 19 MA 2018-08-31 27 60 84
95008 李娜 女 18 CS 2018-08-31 12 12 82
95009 梦圆圆 女 18 MA 2018-08-31 62 35 94
95010 孔小涛 男 19 CS 2017-08-31 82 97 4
95011 包小柏 男 18 MA 2019-08-31 41 53 71
95012 孙花 女 20 CS 2017-08-31 90 51 79
95013 冯伟 男 21 CS 2019-08-31 20 69 86
95014 王小丽 女 19 CS 2017-08-31 83 64 60
95015 王君 男 18 MA 2019-08-31 39 48 29
95016 钱国 男 21 MA 2019-08-31 94 7 69
95017 王风娟 女 18 IS 2019-08-31 87 56 54
95018 王一啊 女 19 IS 2019-08-31 54 36 24
95019 邢小丽 女 19 IS 2018-08-31 78 9 82
95020 赵钱 男 21 IS 2019-08-31 4 48 87
95021 周二 男 17 MA 2018-08-31 84 61 16
95022 郑明 男 20 MA 2018-08-31 64 70 90
95023 李小勇 男 20 CS 2017-08-31 52 21 19
95024 刘小晨 女 19 IS 2017-08-31 1 16 76
95025 王小敏 女 22 MA 2017-08-31 13 89 98
95026 张小立 男 19 IS 2017-08-31 85 84 80
95027 刘小刚 男 18 MA 2018-08-31 7 17 48
95028 孙小庆 男 23 CS 2018-08-31 17 94 83
95029 易小思玲 女 19 MA 2018-08-31 69 84 23
95030 李小娜 女 18 CS 2018-08-31 16 70 70
95031 梦小圆 女 18 MA 2018-08-31 82 48 37
95032 孔涛 男 19 CS 2017-08-31 83 80 12
95033 包柏 男 18 MA 2019-08-31 18 16 71
95034 孙小花 女 20 CS 2017-08-31 36 95 93
95035 冯小伟 男 21 CS 2019-08-31 31 19 13
95036 王丽 女 19 CS 2017-08-31 16 42 9
95037 王小君 男 18 MA 2019-08-31 91 3 67
95038 钱小国 男 21 MA 2019-08-31 89 59 18
95039 王娟 女 18 IS 2019-08-31 7 17 67
95040 王小一 女 19 IS 2019-08-31 49 32 45
95041 邢丽 女 19 IS 2018-08-31 30 40 40
95042 赵小钱 男 21 IS 2019-08-31 71 69 57
95043 周小二 男 17 MA 2018-08-31 18 9 1
95044 郑一明 男 20 MA 2018-08-31 64 3 44
95045 张一勇 男 20 CS 2017-08-31 10 65 97
95046 刘一丽 女 19 IS 2017-08-31 56 8 45
95047 张一敏 女 22 MA 2017-08-31 19 8 98
95048 张一立 男 19 IS 2017-08-31 49 39 92
95049 刘一刚 男 18 MA 2018-08-31 4 79 91
95050 孙一庆 男 23 CS 2018-08-31 99 71 61
95051 易一玲 女 19 MA 2018-08-31 49 53 71
95052 李一 女 18 CS 2018-08-31 22 87 79
95053 小一 女 18 MA 2018-08-31 53 58 35
95054 孔一 男 19 CS 2017-08-31 30 62 5
95055 包一 男 18 MA 2019-08-31 73 68 28
95056 孙一庆 女 20 CS 2017-08-31 68 22 33
95057 冯一 男 21 CS 2019-08-31 91 26 46
95058 王一二 女 19 CS 2017-08-31 49 81 51
95059 王一三 男 18 MA 2019-08-31 56 27 49
95060 钱一 男 21 MA 2019-08-31 97 40 24
95061 王小娟 女 18 IS 2019-08-31 96 79 34
95062 王小一 女 19 IS 2019-08-31 87 6 55
95063 邢丽 女 19 IS 2018-08-31 56 19 33
95064 赵小钱 男 21 IS 2019-08-31 70 17 85
95065 周小二 男 17 MA 2018-08-31 66 19 14
95066 郑小明 男 20 MA 2018-08-31 99 95 14
95067 李勇 男 19 CS 2017-08-31 48 40 13
95068 刘晨 女 19 IS 2017-08-31 86 28 46
95069 王敏 女 21 MA 2017-08-31 91 59 0
95070 张立 男 17 IS 2017-08-31 99 39 64
95071 刘刚 男 20 MA 2018-08-31 73 2 64
95072 孙庆 男 19 CS 2018-08-31 90 71 5
95073 易思玲 女 19 MA 2018-08-31 72 87 25
95074 李娜 女 21 CS 2018-08-31 92 23 65
95075 梦圆圆 女 17 MA 2018-08-31 37 34 5
95076 孔小涛 男 20 CS 2017-08-31 15 37 40
95077 包小柏 男 19 MA 2019-08-31 29 82 30
95078 孙花 女 19 CS 2017-08-31 6 27 75
95079 冯伟 男 21 CS 2019-08-31 4 10 29
95080 王小丽 女 17 CS 2017-08-31 70 70 36
95081 王君 男 20 MA 2019-08-31 20 74 31
95082 钱国 男 19 MA 2019-08-31 59 85 39
95083 王风娟 女 19 IS 2019-08-31 6 98 95
95084 王一 女 21 IS 2019-08-31 16 26 27
95085 邢小丽 女 17 IS 2018-08-31 73 28 4
95086 赵钱 男 20 IS 2019-08-31 69 52 59
95087 周二 男 19 MA 2018-08-31 29 40 10
95088 郑明 男 18 MA 2018-08-31 77 13 91
95089 李勇 男 23 CS 2017-08-31 49 22 8
95090 刘晨 女 19 IS 2017-08-31 12 18 25
95091 王敏 女 18 MA 2017-08-31 44 94 47
95092 张立 男 18 IS 2017-08-31 71 45 17
95093 刘刚 男 19 MA 2018-08-31 27 90 50
95094 孙庆 男 18 CS 2018-08-31 92 34 75
95095 易思玲 女 20 MA 2018-08-31 38 63 24
95096 李娜 女 21 CS 2018-08-31 94 66 37
95097 梦圆圆 女 19 MA 2018-08-31 91 35 45
95098 孔小涛 男 18 CS 2017-08-31 9 61 0
95099 包小柏 男 19 MA 2019-08-31 93 66 25
95100 孙花 女 18 CS 2017-08-31 47 22 27
95101 冯伟 男 23 CS 2019-08-31 23 99 62
95102 王小丽 女 19 CS 2017-08-31 6 34 3
95103 王君 男 18 MA 2019-08-31 0 7 8
95104 钱国 男 18 MA 2019-08-31 93 2 35
95105 王风 女 19 IS 2019-08-31 12 84 50
95106 王一 女 18 IS 2019-08-31 29 60 86
95107 邢小 女 20 IS 2018-08-31 50 51 6
95108 赵钱 男 21 IS 2019-08-31 93 54 86
95109 周二 男 19 MA 2018-08-31 57 39 93
95110 郑明 男 18 MA 2018-08-31 30 78 62
95111 张勇 男 19 CS 2017-08-31 25 5 19
95112 刘丽 女 18 IS 2017-08-31 74 96 44
95113 张敏 女 23 MA 2017-08-31 64 17 82
95114 张小 男 19 IS 2017-08-31 14 57 69
95115 刘小 男 18 MA 2018-08-31 37 84 29
95116 孙小 男 18 CS 2018-08-31 7 99 26
95117 易小 女 19 MA 2018-08-31 60 97 26
95118 李小 女 18 CS 2018-08-31 57 8 88
95119 小小 女 20 MA 2018-08-31 7 97 64
95120 孔大 男 21 CS 2017-08-31 33 27 85
95121 包大 男 19 MA 2019-08-31 96 2 38
95122 孙小 女 18 CS 2017-08-31 54 94 23
95123 冯小伟 男 19 CS 2019-08-31 26 45 1
95124 王小丽 女 18 CS 2017-08-31 65 77 43
95125 王小君 男 23 MA 2019-08-31 24 22 34
95126 钱小国 男 19 MA 2019-08-31 98 98 2
95127 王小娟 女 18 IS 2019-08-31 36 66 30
95128 王小一 女 18 IS 2019-08-31 98 78 15
95129 邢丽 女 19 IS 2018-08-31 41 41 52
95130 赵小钱 男 18 IS 2019-08-31 87 65 93
95131 周小二 男 20 MA 2018-08-31 26 56 48
95132 郑小明 男 21 MA 2018-08-31 31 96 60
95133 李勇 男 19 CS 2017-08-31 26 53 78
95134 刘晨 女 18 IS 2017-08-31 60 27 11
95135 王敏 女 19 MA 2017-08-31 63 25 60
95136 张立 男 18 IS 2017-08-31 46 86 77
95137 刘刚 男 23 MA 2018-08-31 74 6 48
95138 孙庆 男 19 CS 2018-08-31 18 66 20
95139 易思玲 女 18 MA 2018-08-31 72 87 44
95140 李娜 女 18 CS 2018-08-31 12 68 55
95141 梦圆圆 女 19 MA 2018-08-31 63 70 29
95142 孔小涛 男 18 CS 2017-08-31 14 0 15
95143 包小柏 男 20 MA 2019-08-31 29 78 3
95144 孙花 女 21 CS 2017-08-31 1 21 86
95145 冯d伟 男 19 CS 2019-08-31 51 86 85
95146 王3丽 女 18 CS 2017-08-31 88 33 11
95147 王d君 男 23 MA 2019-08-31 61 98 29
95148 钱1国 男 19 MA 2019-08-31 88 65 12
95149 王2娟 女 18 IS 2019-08-31 30 64 41
95150 王B一 女 18 IS 2019-08-31 16 93 10
95151 邢A丽 女 19 IS 2018-08-31 58 20 24
95152 赵钱D 男 18 IS 2019-08-31 77 48 92
95153 周二C 男 20 MA 2018-08-31 89 7 60
95154 郑明B 男 21 MA 2018-08-31 18 76 59
95155 李勇A 男 19 CS 2017-08-31 53 35 49
1.1 将上面的数据存储到一个分区分桶表中。要求：分区按照学院和入学日期分区，根据学号分四个桶。
先建立一张含有基础信息的表格，学院跟日期不用填写进去，放在后面用于分桶，分区，
分区（partitioned by）记得添加名称和数据类型，
分桶 （clustered by） 格式如下
create table if not exists student_info(
sid int,
name string,
gender string,
age int,
chinese int,
math int,
english int
)
partitioned by (academy string,dt date)
clustered by (sid) sorted by (sid) into 4 buckets
row format delimited fields terminated by '\t';
再建立一张临时表，将所有信息放在上面，注意顺序，跟文本文件输入书序一致就行
create table if not exists tmp_student_info(
sid int,
name string,
gender string,
age int,
academy string,
dt date,
chinese int,
math int,
english int
)
row format delimited fields terminated by '\t'
;
在此之前需要找个目录，在目录下创建文本文件，输入下面的代码，
进入vim编辑模式，复制上面的输入信息，最后保存退出，
注意文本输入正确，不要多余空格跟字段，不然导致输入数据不正确
vim student_info.txt
接着载入文件，目录是我的目录，自己根据情况修改绿色地方的目录
load data local inpath '/data/tmp_student_info.txt' into table tmp_student_info;
再设置非严格模式
set hive.exec.dynamic.partition.mode=nonstrict
下面是动态输入的语句
insert into 接目标表格，分区（partition）接select …from…临时表
这里注意！！！！！
前面几个sid,name,gender,age,chinese,math,english的数据信息要求跟目标表格顺序一致，否则报错，或者输出数据为Null,
代码输入顺序如下：
先目标表 后分区，再在from 临时表 后接分桶信息
insert overwrite table student_info partition(academy,dt)
select sid,name,gender,age,chinese,math,english,academy,dt from student_temp
distribute by (sid) sort by(sid);
insert into table student_info partition(academy,dt)
select sid,name,gender,age,chinese,math,english,academy,dt from student_temp cluster by(sid);
如果出现问题，检查表格，去vim里面查找文件问题，或者跟列明顺序有关，
这里不要用*来代替，
1.2 统计每个学院每年入学的总人数以及前一年的总人数。
先根据学院跟入学日期来分组，得到一张表 t
在根据表t 调用lag()序列函数 跟开窗函数来分区跟排序
lag(colname,num) lag返回当前数据行的前第n行的数据
select t.academy,t.dt,t.num,lag(t.num,1) over(distribute by t.academy sort by dt) `上一年的人数`
from
(select academy,dt,count(1) num
from student_info
group by academy,dt
) t;
1.3 统计每个学院的男女人数，在一行上显示，并按照学院名称升序
查询效果如下：
学院 男 女
cs xxx xxx
MA xxxx xxx
使用sum(if()) 或者case when …then …end 都可以，
然后就是根据学院分组 group by
再者根据学院名称排序 order by
select academy,
sum(if(gender='男',1,0)) `男`,
sum(if(gender='女',1,0)) `女`
from student_info
group by academy
order by academy;
1.4 查询每个学院的每年入学总成绩排名的前五名的学生信息：连续，重复效果
(1).先建一张表t 包含学生id,姓名，跟总成绩的表
(2).再通过表t ，调用dense_rank() 的排名函数 根据学院分区，再根据成绩排序 形成表n
(3).最后给这个表n 添加条件(where) 排名为前5 即可
select *
from
(select *,dense_rank() over (distribute by t.academy sort by sum) num
from
(select sid,name,academy,chinese+math+english sum
from student_info ) t
) n
where n.num <=5;
2.第二题
a_test、b_test、c_test三个hive表 每个表中都只有一列int类型且列名相同，求三个表中互不重复的数
a.txt
1
2
3
4
5
6
7
8
9
b.txt
2
3
11
12
14
15
16
18
35
6
7
8
c.txt
1
2
3
11
5
6
7
8
20
30
40
建表语句如下：
create table a_test(
id int
);
create table b_test(
id int
);
create table c_test(
id int
);
load data local inpath '/tmp/exercise/a.txt' into table a_test;
load data local inpath '/tmp/exercise/b.txt' into table b_test;
load data local inpath '/tmp/exercise/c.txt' into table c_test;
无脑的方法，用union all来去重连接三张表
统计出现次数为1的数
select A.id
from
(select id from a_test union all
select id from b_test union all
select id from c_test) A
group by A.id having count(A.id)=1;
3.第三题
需求：词频统计。
统计结果如下,如果出现次数一样，则按照content名称排序：
content cnt
i 3
china 2
good 1
like 1
love 1
is 1
content_test表：
uid contents
1 i|love|china
2 china|is|good|i|i|like
建表语句
create table login_test(
uid int,
dt string,
login_status int
)
row format delimited fields terminated by '\t';
load data local inpath '/tmp/exercise/content_test.txt' into table content_test;
解析：
通过explode()展开函数去展开，再用split()方法去根据 “ | ”来切除字符串，获取多个单词
注意： \用于转义字符 ，所以\\ = \ ,这里注意注意下就行了，
这样就得到表t
然后就是对表t 进行分组跟排序，再在select 上调用聚合函数算总数
select content,count(1) cnt
from
(select explode(split(contents,'\\|')) content from content_test) t
group by t.content
order by cnt desc,content;
解法二：同思路，调用了lateral view
select content,count(1) num
from content lateral view explode(split(contents,"\\|")) mycontent as content
group by content
order by num desc,content
4.第四题
需求：求出连续七天登陆的总人数
数据: login_test表
uid dt login_status(1登录成功,0异常)
1 2019-07-11 1
1 2019-07-12 1
1 2019-07-13 1
1 2019-07-14 1
1 2019-07-15 1
1 2019-07-16 1
1 2019-07-17 1
1 2019-07-18 1
2 2019-07-11 1
2 2019-07-12 1
2 2019-07-13 0
2 2019-07-14 1
2 2019-07-15 1
2 2019-07-16 0
2 2019-07-17 1
2 2019-07-18 0
3 2019-07-11 1
3 2019-07-12 1
3 2019-07-13 1
3 2019-07-14 1
3 2019-07-15 1
3 2019-07-16 1
3 2019-07-17 1
3 2019-07-18 1
建表语句：
create table login_test(
uid int,
dt string,
login_status int
)
row format delimited fields terminated by '\t';
load data local inpath '/tmp/exercise/login_test.txt' into table login_test;
解析：
1.首先通过 用序列函数根据uid 进行分区 然后根据dt 来排序 再用where 筛选出成功登录的信息 形成表 t1
select uid ,dt,row_number () over(distribute by uid sort by dt) rn
from login_test
where login_status =1
2.通过表t1 调用date_sub() 函数
这里有DATE_SUB(date,INTERVAL expr type) 的案例演示讲解
DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。
expr 参数是您希望添加的时间间隔。
date_sub(t1.dt,t1.rn)
通过输入的日期 dt，减去按时间排序的序号，如果最终指向同一天，那么他就是连续登录，如果不是同一天那么就是有断签的行为
3.最后根据uid，dt 进行分组，然后统计连续登录的人，并且次数大于等于7次
代码如下
select uid,dt,count(1)
from
(select t1.uid uid,date_sub(t1.dt,t1.rn) dt
from
(select uid ,dt,row_number () over(distribute by uid sort by dt) rn
from login_test
where login_status =1) t1) t2
group by uid,dt
having count(1)>=7
4.最后进行统计总人数，只能利用这样表t2 来分组count()来
最终代码如下：
select count(uid) `总人数`
from
(select uid,dt,count(1)
from
(select t1.uid uid,date_sub(t1.dt,t1.rn) dt
from
(select uid ,dt,row_number () over(distribute by uid sort by dt) rn
from login_test
where login_status =1) t1) t2
group by uid,dt
having count(1)>=7) t3;
解法二：
计算日期差值，因为实际日期包含当天所以去6
select count(distinct a.uid)
from
(select uid,login_status,
datediff(dt,lag(dt,6) over(distribute by uid sort by dt asc)) b
from login_test
where login_status=1) a
where a.b=6;
5.第五题
需求：现在要求使用hive，计算每一年的最大气温的日期+温度。
数据解析：比如：2010012325表示在2010年01月23日的气温为25度。
效果如下：
20010105 29
20070109 99
20080103 37
20100103 17
20120107 32
20130109 29
20140103 17
20150109 99
数据表如下：
2014010114
2014010216
2014010317
2014010410
2014010506
2012010609
2012010732
2012010812
2012010919
2012011023
2001010116
2001010212
2001010310
2001010411
2001010529
2013010619
2013010722
2013010812
2013010929
2013011023
2008010105
2008010216
2008010337
2008010414
2008010516
2007010619
2007010712
2007010812
2007010999
2007011023
2010010114
2010010216
2010010317
2010010410
2010010506
2015010649
2015010722
2015010812
2015010999
2015011023
建表语句：
create table if not exists temperature(
data string
)
row format delimited fields terminated by '\t'
lines terminated by '\n';
load data local inpath '/tmp/exercise/login_test.txt' into table login_test;
解析：
1.建表，通过substr( )函数进行字符串的截取，获取日期跟温度两个信息
再用dense_rank() 序列函数先对年份进行分区，再根据温度来降序排序，
得到表 t
substr()函数的实例讲解分析
select substr(data,0,8) dt,substr(data,9,11) tem,
dense_rank() over (distribute by substr(data,0,4) sort by substr(9,10) desc ) num
from temperature
2.筛选条件 num =1
就得出结果
select dt,tem
from
(select substr(data,0,8) dt,substr(data,9,11) tem,
dense_rank() over (distribute by substr(data,0,4) sort by substr(9,10) desc ) num
from temperature) t
where num=1;
解法二：
思路一致，只不过用max() 函数来解决，最后筛选条件是tem =max 就行
select dt,max
from
(select substr(data,0,8) dt,substr(data,9,11) tem,max(substr(data,9,11)) over (distribute by substr(data,0,4) ) max
from temperature) t
where tem=max;
5.第六题
需求：使用hql统计两个人的通话总时长（用户之间互相通话的时长）
有如下通话记录：
呼叫 被叫 时长
goudan haoge 01:01:01
goudan mazi 00:11:21
goudan laowang 00:19:01
goudan Jingba 00:21:01
goudan weige 01:31:17
haoge mazi 00:51:01
haoge mazi 01:11:19
haoge laowang 00:00:21
haoge laowang 00:23:01
laowang mazi 01:18:01
laowang weige 00:18:00
Jingba haoge 00:01:01
Jingba haoge 00:00:06
Jingba haoge 00:02:04
Jingba haoge 00:02:54
haoge laowang 01:00:13
haoge laowang 00:01:01
haoge goudan 00:01:01
建表语句：
create table call_test(
huname string comment '主动呼叫用户名',
huiname string comment '被呼叫用户名',
times string comment '呼叫时间'
)
row format delimited
fields terminated by ' ';
load data local inpath '/tmp/exercise/call_test.txt' into table call_test;
解析：
1.通过case when then 来实现对通话者跟呼叫者进行变化，
假如有呼叫者跟通话这角色互调的情况，重新把他们归为一类，统一通话者跟呼叫者
所以表t1 就是这样的情况
2.然后进行分组，再对时间进行累加即可
(1) 这里原来times 时间是字符串类型，所以用hour(),min(),second() 函数统一转化为秒数
(2) 调用from_unixtime() 函数，后面将时间戳转化为日期类型，
这一点与unixtime()函数输入的内容和输出的内容正好相反
因为中国处于东八区（跟你的系统设置地区有关），
from_unixtime 是根据时间戳（具体时间数值）
准确来说是输入的是子午线的时间，
就会返回一个你所在的地区的时间
所以我计算得总秒数，返回的是东八区的时间，所以我们需要减去8小时的差值，所以就有了 - 28800 的由来
3.将两个内容嵌套就行了，根据呼叫者和通话者进行分组就OK了
最终代码如下：
from_unixtime()函数的讲解跟实例解析
select huname, huiname,
from_unixtime(sum(hour(times)*3600+minute(times)*60+second(times))-28800,"HH:mm:ss" )
from
(select
case when huname>=huiname then huname else huiname end huname,
case when huname>=huiname then huiname else huname end huiname,
times
from call_test) t1
group by t1.huname,t1.huiname;
解法二：
思路相同这个会复杂一点，我推荐上面那种看得明白一点
先通过unixtime()函数得来对应时间的子午线时间，加上8小时，最终得到东八区时间，
之前说过，我们要得到的是子午线时间，所以需要减去28800，也就是后面那个unixtime()函数对应数值就是-28800
所以最终代码如下
select huname, huiname,
from_unixtime(sum(unix_timestamp(times,'HH:mm:ss')-unix_timestamp("00:00:00",'HH:mm:ss'))+unix_timestamp("00:00:00",'HH:mm:ss'),"HH:mm:ss") times
from
(select
case when huname>=huiname then huname else huiname end huname,
case when huname>=huiname then huiname else huname end huiname,
times
from call_test) t1
group by t1.huname,t1.huiname;
解法二：
连两张表分组跟条件排除，最后加的时间方法一致
select t.h1,t.h2,from_unixtime(sum(unix_timestamp(t.times,"HH:mm:ss")+28800)-28800,"HH:mm:ss") from
(
select huname h1,huiname h2,times
from call_test
union
select huiname h1,huname h2,times
from call_test
) t
where t.h1>t.h2
group by t.h1,t.h2;
扫一扫
218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
