Hive查询,客服电话练习

如图片,我自己建了两个表格模拟数据库内容,
如下:
table:call

table:sep

create table call(case_id int, create_time date,deal_name string,deal_group string) row format delimited fields terminated by ‘,’;

load data local inpath ‘/opt/module/data/call.txt’ into table call;

create table sep(feed_id int, case_id int, eva_end_time date,sat_name string) row format delimited fields terminated by ‘,’;

load data local inpath ‘/opt/module/data/sep.txt’ into table sep;

1,合并两个表,join,并显示有评价的内容
select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’;t1

2,总评价量
select
deal_name,count(deal_name) zong
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name;t2

4满意评价量,
select
deal_name,sat_name,count(sat_name) countsat
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name,sat_name
having sat_name=‘m’ ; t3

5,满意率
select
t3.deal_name,t3.countsat,t2.zong,t3.countsat/t2.zong manyi
from
(select
deal_name,count(deal_name) zong
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name)t2
join
(select
deal_name,sat_name,count(sat_name) countsat
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name,sat_name
having sat_name=‘m’ )t3
on
t2.deal_name=t3.deal_name;t6

6 sep中没有被评为满意的任务

select
case_id,sat_name
from
sep
where
sat_name<>‘m’ and
sat_name<>‘null’;t1

7,call表 按照电话创建时间顺序排列,
select
case_id,creat_time,deal_name
from
call
order by creat_time;t2

8.连接两个表

select
t1.case_id,t1.sat_name,t2.create_time,t2.deal_name
from
(select
case_id,sat_name
from
sep
where
sat_name<>‘m’ and
sat_name<>‘null’)t1
join
(select
case_id,create_time,deal_name
from
call
order by create_time)t2
on t1.case_id=t2.case_id;t3

9 对每个用户没有被评为满意的数据进行排名
select
case_id,sat_name,create_time,deal_name,rank()over(partition by deal_name order by create_time asc) rank1
from
(select
t1.case_id,t1.sat_name,t2.create_time,t2.deal_name
from
(select
case_id,sat_name
from
sep
where
sat_name<>‘m’ and
sat_name<>‘null’)t1
join
(select
case_id,create_time,deal_name
from
call
order by create_time)t2
on t1.case_id=t2.case_id)t3;t4

10,找出第一个来电的任务编号

select
deal_name,case_id,rank1,create_time
from
(select
case_id,sat_name,create_time,deal_name,rank()over(partition by deal_name order by create_time asc) rank1
from
(select
t1.case_id,t1.sat_name,t2.create_time,t2.deal_name
from
(select
case_id,sat_name
from
sep
where
sat_name<>‘m’ and
sat_name<>‘null’)t1
join
(select
case_id,create_time,deal_name
from
call
order by create_time)t2
on t1.case_id=t2.case_id)t3)t4
where rank1=1;t5

11,得出结果
select
t3.deal_name,t3.countsat,t2.zong ,t6.manyi,t5.case_id
from
(select
deal_name,sat_name,count(sat_name) countsat
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name,sat_name
having sat_name=‘m’ ) t3 join (select
t3.deal_name,t3.countsat,t2.zong,t3.countsat/t2.zong manyi
from
(select
deal_name,count(deal_name) zong
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name)t2
join
(select
deal_name,sat_name,count(sat_name) countsat
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name,sat_name
having sat_name=‘m’ )t3
on
t2.deal_name=t3.deal_name) t6 on
t3.deal_name=t6.deal_name
join (select
deal_name,case_id,rank1,create_time
from
(select
case_id,sat_name,create_time,deal_name,rank()over(partition by deal_name order by create_time asc) rank1
from
(select
t1.case_id,t1.sat_name,t2.create_time,t2.deal_name
from
(select
case_id,sat_name
from
sep
where
sat_name<>‘m’ and
sat_name<>‘null’)t1
join
(select
case_id,create_time,deal_name
from
call
order by create_time)t2
on t1.case_id=t2.case_id)t3)t4
where rank1=1)t5 on
t3.deal_name=t5.deal_name
join (select
deal_name,count(deal_name) zong
from
(select
a.case_id,create_time,deal_name,feed_id,eva_end_time,sat_name
from
call a inner join sep b
on a.case_id=b.case_id
where sat_name <> ‘null’ )t1
group by deal_name)t2 on
t3.deal_name=t2.deal_name;