Hive QL场景题第六部分详解

26、请使用sql计算pv、uv？

数据:

t1表

uid dt url
1 2019-08-06 http://www.baidu.com
2 2019-08-06 http://www.baidu.com
3 2019-08-06 http://www.baidu.com
3 2019-08-06 http://www.soho.com
3 2019-08-06 http://www.meituan.com
3 2019-08-06

结果如下:

dt uv pv 
2019-08-6 3 5 

create table user_net_log(
uid int,
dt date,
url string
)
row format delimited
fields terminated by ' ';
​
load data local inpath './hivedata/user_net_log.txt' overwrite into table user_net_log;

select dt,count(distinct uid) uv,count(url) pv
from user_net_log
group by dt;
​
rusule：
Total MapReduce CPU Time Spent: 0 msec
OK
NULL    1       0
2019-08-06      3       5
​

27、hive中coalease()、nvl()、concat_ws()、collect_list()、collect_set()、regexp_replace().这几个函 数的意义？

略

28、有表如下记录了智智同学玩某moba游戏每局的英雄pick情况

pk_moba表

id	names
1	亚索,挖据机,艾瑞莉亚,洛,卡莎
2	亚索,盖伦,奥巴马,牛头,皇子
3	亚索,盖伦,艾瑞莉亚,宝石,琴女
4	亚索,盖伦,赵信,老鼠,锤石

请用HiveSQL计算出出场次数最多的top3英雄及其Pick率（=出现场数/总场数）

create table pk_moba(
id int,
names array<string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';
​
load data local inpath './hivedata/pk_moba.txt' overwrite into table pk_moba;

分析：计算出出场次数最多的英雄及pick率
select name,cnt,rn,concat(round(cnt/4*100,0),"%") lv
from
(
select name,cnt,dense_rank() over(sort by cnt desc) rn
from(
select name,count(name) cnt
from pk_moba lateral view explode(names) tmp as name
group by name
)A
)B
where rn<4;
​
ressult:
亚索    4       1       100.0%
盖伦    3       2       75.0%
艾瑞莉亚        2       3       50.0%
​

29、有三个表，分别是：

区域(district) 区域中有两个字段分别是区域Id(disid)和区域名称(disname)

城市(city) 城市有两个字段分别是城市ID(cityid)和区域ID(disid)

订单(order) 订单有四个字段分别是订单ID(orderid)、用户ID(userid)、城市ID(cityid)和消费金额(amount)。

district表:

disid disname 
1 华中
2 西南
​
​
create table district(
disid int,
disname string
)
row format delimited
fields terminated by ' ';
​
load data local inpath './hivedata/district.txt' overwrite into table district;

city表:

cityid disid 
1 1
2 1
3 2
4 2
5 2
​
create table city(
cityid int,
disid int
)
row format delimited
fields terminated by ' ';
​
load data local inpath './hivedata/city.txt' overwrite into table city;

order表：

oid userid cityid amount 
1 1 1 1223.9
2 1 1 9999.9
3 2 2 2322
4 2 2 8909
5 2 3 6789
6 2 3 798
7 3 4 56786
8 4 5 78890
​
create table order_t(
oid int,
userid int,
cityid int,
amount float
)
row format delimited
fields terminated by ' ';
​
load data local inpath './hivedata/order.txt' overwrite into table order_t;

高消费者是消费金额大于1W的用户，使用hive hql生成如下报表:

区域名 高消费者人数 消费总额

分析：
select disname,count(1),sum(amount)
from(
select disname,userid,sum(amount) amount from district A join city B on A.disid = B.disid
join order_t C on B.cityid = C.cityid
group by disname,userid
)D
where amount>10000
group by disname;
​
result:
华中    2       22454.800415039062
西南    2       135676.0
​

30、某APP每天访问数据存放在表access_log里面，包含日期字段ds,用户类型字段user_type，用户账 号user_id,用户访问时间log_time,请使用hive的hql语句实现如下需求：

(1)、每天整体的访问UV、PV?

select log_time,count(distinct user_id) uv,count(1) pv
from access_log
group by log_time;

(2)、每天每个类型的访问UV、PV?

select log_time,user_type,count(distinct user_id) uv,count(1) pv
from access_log
group by log_time;

(3)、每天每个类型中最早访问时间和最晚访问时间?

select log_time,user_type,min(log_time),max(log_time)
from access_log
group by log_time;

(4)、每天每个类型中访问次数最高的10个用户?

select dt,user_type,cnt,rn
from(
select dt,user_type,cnt,row_number() over(partition by dt,user_type order by cnt desc)
from
(select substr(log_time,1,7) dt,user_type,count(1) cnt
from access_log
group by substr(log_time,1,7),user_type
)A
)B
where rn<11;