[大数据]15道Hive SQL经典训练提升题

第1题

我们有如下的用户访问数据

要求使用SQL统计出每个用户的累积访问次数，如下表所示：

数据

insert into action values('u01','2017/1/21',5);
insert into action values('u02','2017/1/23',6);
insert into action values('u03','2017/1/22',8);
insert into action values('u04','2017/1/20',3);
insert into action values('u01','2017/1/23',6);
insert into action values('u01','2017/2/21',8);
insert into action values('u02','2017/1/23',6);
insert into action values('u01','2017/2/22',4);

1）创建表

create table action

(userId string,

visitDate string,

visitCount int)

row format delimited fields terminated by "\t";

1）修改数据格式

select

?????userId,

?????date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,

?????visitCount

from

?????action;t1

2）计算每人单月访问量

select

????userId,

????mn,

????sum(visitCount) mn_count

from

????t1

group by userId,mn;t2

3）按月累计访问量

select

????userId,

????mn,

????mn_count,

????sum(mn_count) over(partition by userId order by mn)

from t2;



最终SQL

select

????userId,

????mn,

????mn_count,

????sum(mn_count) over(partition by userId order by mn)

from

( ??select

????????userId,

????????mn,

????????sum(visitCount) mn_count

????from

?????????(select

?????????????userId,

?????????????date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn,

?????????????visitCount

?????????from

?????????????action)t1

group by userId,mn)t2;

第2题 京东

有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：

insert into  visit values(3,'女装');
insert into  visit values(4,'女装');
insert into  visit values(5,'女装');
insert into  visit values(6,'女装');
insert into  visit values(7,'女装');
insert into  visit values(8,'女装');
insert into  visit values(8,'女装');
insert into  visit values(9,'女装');
insert into  visit values(10,'女装');
insert into  visit values(11,'女装');
insert into  visit values(1,'男装');
insert into  visit values(1,'男装');
insert into  visit values(1,'男装');
insert into  visit values(1,'男装');
insert into  visit values(2,'男装');
insert into  visit values(3,'男装');
insert into  visit values(4,'男装');

建表：

create table visit(user_id string,shop string) row format delimited fields terminated by '\t';

1）每个店铺的UV（访客数）

select shop,count(distinct user_id) from visit group by shop;

2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

（1）查询每个店铺被每个用户访问次数

select shop,user_id,count(*) ct

from visit

group by shop,user_id;t1

（2）计算每个店铺被用户访问次数排名

select shop,user_id,ct,rank() over(partition by shop order by ct) rk

from t1;t2

（3）取每个店铺排名前3的

select shop,user_id,ct

from t2

where rk<=3;

（4）最终SQL

s

select shop,
       user_id,
       ct
from (select shop,
             user_id,
             ct,
             rank() over (partition by shop order by ct) rk
      from (select shop,
                   user_id,
                   count(*) ct
            from visit
            group by shop,
                     user_id) t1
     ) t2
where rk <= 3;

第3题

已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。

建表：

create table order_tab(dt string,order_id string,user_id string,amount decimal(10,2)) row format delimited fields terminated by '\t';

1）给出 2017年每个月的订单数、用户数、总成交金额。

select

???date_format(dt,'yyyy-MM'),

???count(order_id),

???count(distinct user_id),

???sum(amount)

from

???order_tab

group by date_format(dt,'yyyy-MM');

2）给出2017年11月的新客数(指在11月才有第一笔订单)

select

???count(user_id)

from

???order_tab

???group by user_id

having date_format(min(dt),'yyyy-MM')='2017-11';

第4题

有一个5000万的用户文件(user_id，name，age)?a，一个2亿记录的用户看电影的记录文件(user_id，url)?b，根据年龄段观看电影的次数进行排序？

1.先把表按照user_id分组统计减少数据量

(
    SELECT user_id,
           count(url) count
    FROM b
    GROUP BY user_id
) c

2.再把a和c关联起来

SELECT a.user_id,

       a.age,

       Ifnull(c.count, 0),

from a
         left join (
    SELECT user_id,

           count(url) count

    FROM b

    GROUP BY user_id
) c on c.user_id = a.user_id

3、分组排序

SELECT d.*,
       rank() over (PARTITION BY age ORDER BY count) rk

FROM (
         SELECT a.user_id,

                a.age,

                ifnull(c.count, 0),


         FROM a

                  LEFT JOIN (
             SELECT user_id,

                    count(url) count

             FROM b

             GROUP BY user_id
         ) c ON c.user_id = a.user_id
     ) d

第5题

有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）

日期 用户 年龄

2019-02-11,test_1,23

2019-02-11,test_2,19

2019-02-11,test_3,39

2019-02-11,test_1,23

2019-02-11,test_3,39

2019-02-11,test_1,23

2019-02-12,test_2,19

2019-02-13,test_1,23

2019-02-15,test_2,19

2019-02-16,test_2,19

create table user_age(dt string,user_id string,age int)row format delimited fields terminated by ',';



insert into table user_age values ('11', 'test_1', 23);

insert into table user_age values ('11', 'test_2', 19);

insert into table user_age values ('11', 'test_3', 39);

insert into table user_age values ('11', 'test_1', 23);

insert into table user_age values ('11', 'test_3', 39);

insert into table user_age values ('11', 'test_1', 23);

insert into table user_age values ('12', 'test_2', 19);

insert into table user_age values ('13', 'test_1', 23);

1）按照日期以及用户分组，按照日期排序并给出排名

select dt,

       user_id,

       min(age)   age,

       rank() over (partition by user_id order by dt) rk

from user_age

group by dt, user_id; t1

2）计算日期及排名的差值

select user_id,

       age,

       date_sub(dt, rk) flag

from t1; t2

3）过滤出差值大于等于2的，即为连续两天活跃的用户

select user_id,

       min(age) age

from t2

group by user_id, flag

having count(*) >= 2; t3

4）对数据进行去重处理（一个用户可以在两个不同的时间点连续登录），例如：a用户在1月10号1月11号以及1月20号和1月21号4天登录。

select user_id,

       min(age) age

from t3

group by user_id;t4

5）计算活跃用户（两天连续有访问）的人数以及平均年龄

select

???count(*) ct,

???cast(sum(age)/count(*) as decimal(10,2))

from t4;

6）对全量数据集进行按照用户去重

?
select user_id,

       min(age) age

from user_age

group by user_id;t5

?

7）计算所有用户的数量以及平均年龄

select

???count(*) user_count,

???cast((sum(age)/count(*)) as decimal(10,1))

from ??t5;

8）将第5步以及第7步两个数据集进行union?all操作

select 0                                           user_total_count,

       0                                           user_total_avg_age,

       count(*)                                    twice_count,

       cast(sum(age) / count(*) as decimal(10, 2)) twice_count_avg_age

from (
         select user_id,

                min(age) age

         from (select user_id,

                      min(age) age

               from (
                        select user_id,

                               age,

                               date_sub(dt, rk) flag

                        from (
                                 select dt,

                                        user_id,

                                        min(age)                                       age,

                                        rank() over (partition by user_id order by dt) rk

                                 from user_age

                                 group by dt, user_id
                             ) t1
                    ) t2

               group by user_id, flag

               having count(*) >= 2) t3

         group by user_id
     ) t4


union all


select count(*)                                      user_total_count,

       cast((sum(age) / count(*)) as decimal(10, 1)) user_total_avg_age,

       0                                             twice_count,

       0                                             twice_count_avg_age

from (
         select user_id,

                min(age) age

         from user_age

         group by user_id
     ) t5; t6

9）计算最终结果

select sum(user_total_count),

       sum(user_total_avg_age),

       sum(twice_count),

       sum(twice_count_avg_age)

from (select 0                                           user_total_count,

             0                                           user_total_avg_age,

             count(*)                                    twice_count,

             cast(sum(age) / count(*) as decimal(10, 2)) twice_count_avg_age

      from (
               select user_id,

                      min(age) age

               from (select user_id,

                            min(age) age

                     from (
                              select user_id,

                                     age,

                                     date_sub(dt, rk) flag

                              from (
                                       select dt,

                                              user_id,

                                              min(age)                                       age,

                                              rank() over (partition by user_id order by dt) rk

                                       from user_age

                                       group by dt, user_id
                                   ) t1
                          ) t2

                     group by user_id, flag

                     having count(*) >= 2) t3

               group by user_id
           ) t4


      union all


      select count(*)                                      user_total_count,

             cast((sum(age) / count(*)) as decimal(10, 1)) user_total_avg_age,

             0                                             twice_count,

             0                                             twice_count_avg_age

      from (
               select user_id,

                      min(age) age

               from user_age

               group by user_id
           ) t5) t6;

第6题

请用sql写出所有用户中在今年10月份第一次购买商品的金额，表ordertable字段（购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid）

create table sixth (userid string,monty string ,paymenttime string,orderid string);



insert into table sixth values('001','100','2017-10-01','123123');

insert into table sixth values('001','200','2017-10-02','123124');

insert into table sixth values('002','500','2017-10-01','222222');

insert into table sixth values('001','100','2017-11-01','123123');

select

????userid,

????paymenttime,

????monty,

????row_con

from

(

select

????paymenttime,

????userid,

????monty,

orderid,

--下面的where起到了先过滤后排序的作用

????row_number() over(partition by userid order by paymenttime) row_con

from sixth

where substring(`paymenttime`, 1,7) ?='2017-10'

) t1

where t1.row_con=1;

第7题

现有图书管理数据库的三个数据模型如下：

图书（数据表名：BOOK）

读者（数据表名：READER）

借阅记录（数据表名：BORROW LOG）

（1）创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。

（2）找出姓李的读者姓名（NAME）和所在单位（COMPANY）。

（3）查找“高等教育出版社”的所有图书名称（BOOK_NAME）及单价（PRICE），结果按单价降序排序。

（4）查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），结果按出版单位（OUTPUT）和单价（PRICE）升序排序。

（5）查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。

（6）求”科学出版社”图书的最高单价、最低单价、平均单价。

（7）找出当前至少借阅了2本图书（大于等于2本）的读者姓名及其所在单位。

?select

????????t1.name,

????????t1.company

????from

????(

????select

????????r.name name,

????????r.company company

????from borrow_log b

????join reader r on

????b.reader_id=r.reader_id

????) t1

????group by t1.name,t1.company having count(*)>=2;

（8）考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_1.0G_ BAK中。

create table if not exists borrow_log_bak

????select * from borrow_log;

（9）现在需要将原Oracle数据库中数据迁移至Hive仓库，请写出“图书”在Hive中的建表语句（Hive实现，提示：列分隔符|；数据表数据需要外部导入：分区分别以month＿part、day＿part 命名）

（10）Hive中有表A，现在需要将表A的月分区　201505　中　user＿id为20000的user＿dinner字段更新为bonc8920，其他用户user＿dinner字段数据不变，请列出更新的方法步骤。（Hive实现，提示：Hlive中无update语法，请通过其他办法进行数据更新）

hive在1.1.0版本之前不可以更新数据，在之后可以更改在建表后面添加: stored as orc TBLPROPERTIES('transactional'='true')

但update操作非常慢

第8题

有一个线上服务器访问日志格式如下（用sql答题）

时间? ? ? ? ? ? ? ? ? ? ? ? ? 接口? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ip地址

2016-11-09 14:22:05 /api/user/login? ? ? ? ? ? ? ? ? ? ? 110.23.5.33

2016-11-09 14:23:10 /api/user/detail ????????????????????57.3.2.16

2016-11-09 15:59:40 /api/user/login? ? ? ? ? ? ? ? ? ? ? ?200.6.5.166

… …

求11月9号下午14点（14-15点），访问/api/user/login接口的top10的ip地址

create table eight_log(`date` string,interface string ,ip string);



insert into table eight_log values ('2016-11-09 11:22:05','/api/user/login','110.23.5.23');

insert into table eight_log values ('2016-11-09 11:23:10','/api/user/detail','57.3.2.16');

insert into table eight_log values ('2016-11-09 23:59:40','/api/user/login','200.6.5.166');



insert into table eight_log values('2016-11-09 11:14:23','/api/user/login','136.79.47.70');

insert into table eight_log values('2016-11-09 11:15:23','/api/user/detail','94.144.143.141');

insert into table eight_log values('2016-11-09 11:16:23','/api/user/login','197.161.8.206');

insert into table eight_log values('2016-11-09 12:14:23','/api/user/detail','240.227.107.145');

insert into table eight_log values('2016-11-09 13:14:23','/api/user/login','79.130.122.205');

insert into table eight_log values('2016-11-09 14:14:23','/api/user/detail','65.228.251.189');

insert into table eight_log values('2016-11-09 14:15:23','/api/user/detail','245.23.122.44');

insert into table eight_log values('2016-11-09 14:17:23','/api/user/detail','22.74.142.137');

insert into table eight_log values('2016-11-09 14:19:23','/api/user/detail','54.93.212.87');

insert into table eight_log values('2016-11-09 14:20:23','/api/user/detail','218.15.167.248');

insert into table eight_log values('2016-11-09 14:24:23','/api/user/detail','20.117.19.75');

insert into table eight_log values('2016-11-09 15:14:23','/api/user/login','183.162.66.97');

insert into table eight_log values('2016-11-09 16:14:23','/api/user/login','108.181.245.147');



insert into table eight_log values('2016-11-09 14:17:23','/api/user/login','22.74.142.137');

insert into table eight_log values('2016-11-09 14:19:23','/api/user/login','22.74.142.137');

select
???ip,
???count(*) ct
from
???eight_log
where
???substring(`date`,1,13)>='2016-11-09 14'
???and 
???substring(`date`,1,13)<'2016-11-09 15'

group by
???ip
order by
???ct desc
limit 10;

第9题

有一个充值日志表如下：

CREATE TABLE `credit_log`

(

????`dist_id` int（11）DEFAULT NULL COMMENT '区组id',

????`account` varchar（100）DEFAULT NULL COMMENT '账号',

????`money` int(11) DEFAULT NULL COMMENT '充值金额',

????`create_time` datetime DEFAULT NULL COMMENT '订单时间'

)ENGINE=InnoDB DEFAUILT CHARSET-utf8

请写出SQL语句，查询充值日志表2015年7月9号每个区组下充值额最大的账号，要求结果：

区组id，账号，金额，充值时间

create table nine_log(

????dist_id int,

????account string,

????money int,

????create_time string

)


insert into table nine_log values (1,'001',100,'2015-07-09');

insert into table nine_log values (1,'002',500,'2015-07-09');

insert into table nine_log values (2,'001',200,'2015-07-09');

select

????t1.dist_id,

????t1.account,

????t1.money,

????t1.create_time

from

(

select

????dist_id,

????account,

????create_time,

????money,

????rank() over(partition by dist_id order by money desc) rank

????from nine_log

where create_time='2015-07-09'

)t1

where rank=1;

create table nine_log(

????dist_id int,

????account string,

????money int,

????create_time string

)

select

????t1.dist_id,

????t1.account,

????t1.money,

????t1.create_time

from

(

select

????dist_id,

????account,

????create_time,

????money,

????rank() over(partition by dist_id order by money desc) rank

????from nine_log

where create_time='2015-07-09'

)t1

where rank=1;

第10题

有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10）

CREATE TABIE `account`

(

????`dist_id` int（11）

????DEFAULT NULL COMMENT '区组id'，

????`account` varchar（100）DEFAULT NULL COMMENT '账号' ,

????`gold` int（11）DEFAULT NULL COMMENT '金币'

????PRIMARY KEY （`dist_id`，`account_id`），

）ENGINE=InnoDB DEFAULT CHARSET-utf8

第11题

1）有三张表分别为会员表（member）销售表（sale）退货表（regoods）

（1）会员表有字段memberid（会员id，主键）credits（积分）；

（2）销售表有字段memberid（会员id，外键）购买金额（MNAccount）；

（3）退货表中有字段memberid（会员id，外键）退货金额（RMNAccount）；

2）业务说明：

（1）销售表中的销售记录可以是会员购买，也可是非会员购买。（即销售表中的memberid可以为空）

（2）销售表中的一个会员可以有多条购买记录

（3）退货表中的退货记录可以是会员，也可是非会员

（4）一个会员可以有一条或多条退货记录

查询需求：分组查出销售表中所有会员购买金额，同时分组查出退货表中所有会员的退货金额，把会员id相同的购买金额-退款金额得到的结果更新到会员表中对应会员的积分字段（credits）

create table member(

memberid int,

credits double

);



create table sale(

memberid int,

MNAccount double

);



insert into sale values(1,345.9);

insert into sale values(1,3435.9);

insert into sale values(1,3245.9);

insert into sale values(2,3435.9);

insert into sale values(3,2345.9);

insert into sale values(3,3345.9);

insert into sale values(null,345.9);



create table regoods(

memberid int,

RMNAccount double

);

insert into regoods values(1,256.9);

insert into regoods values(1,2526.9);

insert into regoods values(1,2516.9);

insert into regoods values(2,2546.9);

insert into regoods values(3,2156.9);

insert into regoods values(3,2256.9);

insert into regoods values(null,256.9);

insert into table member

select t1.memberid                  memberid,

       t1.MNAccount - t2.RMNAccount credits

from (select memberid,

             sum(MNAccount) MNAccount

      from sale

      where memberid is not null

      group by memberid) t1

         join

     (select memberid,

             sum(RMNAccount) RMNAccount

      from regoods

      where memberid is not null

      group by memberid) t2
     on t1.memberid = t2.memberid

第12题 百度

现在有三个表student（学生表）、course(课程表)、score（成绩单），结构如下：

create table student

(

id bigint comment ‘学号’，

name string comment ‘姓名’,

age bigint comment ‘年龄’

);

create table course

(

cid string comment ‘课程号，001/002格式’,

cname string comment ‘课程名’

);

Create table score

(

uid?bigint comment ‘学号’,

cid string comment ‘课程号’,

score bigint comment ‘成绩’

) partitioned by(event_day string)



insert into student values(1,'zhangsan1',18);

insert into student values(2,'zhangsan2',19);

insert into student values(3,'zhangsan3',16);

insert into student values(4,'zhangsan4',13);

insert into student values(5,'zhangsan5',20);

insert into student values(6,'zhangsan6',24);

insert into student values(7,'zhangsan7',23);

create table course

(

cid string ,

cname string

);



insert into course values ('001','math');

insert into course values ('002','english');

insert into course values ('003','chinese');



DROP table if exists score;

Create table score

(

Id bigint,

cid string ,

score bigint

)

partitioned by(event_day string)



insert into score partition(event_day ='20190301') values (1,'001',50);

insert into score partition(event_day ='20190301') values (1,'002',55);

insert into score partition(event_day ='20190301') values (1,'003',60);

insert into score partition(event_day ='20190301') values (2,'001',null);

insert into score partition(event_day ='20190301') values (2,'002',60);

insert into score partition(event_day ='20190301') values (2,'003',70);

insert into score partition(event_day ='20190301') values (3,'001',80);

insert into score partition(event_day ='20190301') values (3,'002',65);

insert into score partition(event_day ='20190301') values (3,'003',80);

insert into score partition(event_day ='20190301') values (4,'001',70);

insert into score partition(event_day ='20190301') values (4,'002',75);

insert into score partition(event_day ='20190301') values (4,'003',90);

insert into score partition(event_day ='20190301') values (5,'001',null);

insert into score partition(event_day ='20190301') values (5,'002',65);

insert into score partition(event_day ='20190301') values (5,'003',75);

insert into score partition(event_day ='20190301') values (6,'001',65);

insert into score partition(event_day ='20190301') values (6,'002',85);

insert into score partition(event_day ='20190301') values (6,'003',84);

insert into score partition(event_day ='20190301') values (7,'001',65);

insert into score partition(event_day ='20190301') values (7,'002',59);

insert into score partition(event_day ='20190301') values (7,'003',94);

其中score中的id、cid，分别是student、course中对应的列请根据上面的表结构，回答下面的问题

1）请将本地文件（/home/users/test/20190301.csv）文件，加载到分区表score的20190301分区中，并覆盖之前的数据

load data local inpath '/home/users/test/20190301.csv' overwrite into table score partition (event_day='20190301');

2）查出平均成绩大于60分的学生的姓名、年龄、平均成绩

select ?id,s.name,s.age,avg(score)

from score

group by id having avg(score)>60

join

student s

on s.id=score.id

3）查出没有‘001’课程成绩的学生的姓名、年龄

select t2.name,

       t2.age

from (select Id

      from score

      where cid = '001'
        and score is null) t1

         join student t2
              on t1.Id = t2.id;

4）查出有‘001’\’002’这两门课程下，成绩排名前3的学生的姓名、年龄

select t2.id,
???????student.age
from (select id
??????from (select *,
???????????????????rank() over (partition by cid order by score desc) rank
????????????from score
????????????where cid = '001'
???????????????or cid = '002') t1
??????where rank <= 3) t2
?????????join student
??????????????on t2.id = student.id
group by t2.id, student.age;

--最后的group by是为了去重

5）创建新的表score_20190317，并存入score表中20190317分区的数据

create table if not exists score_20190317 as select * from score where event_dayk='20190317';

6）如果上面的score表中，uid存在数据倾斜，请进行优化，查出在20190101-20190317中，学生的姓名、年龄、课程、课程的平均成绩

##map数据倾斜时负载均衡

set?hive.map.aggr = true

##groupby数据倾斜时负载均衡

set hive.groupby.skewindata = true

select

????uid,

????cid,

????avg(score),

????s.age

from score

where event_day>='20190101' and event_day<='20190317'

group by uid,cid

join

student s

on s.id=score.uid

Hive中的Predicate Pushdown简称谓词下推，主要思想是把过滤条件下推到map端，提前执行过滤，以减少map到reduce的传输数据，提升整体性能

所谓下推，即谓词过滤在map端执行；所谓不下推，即谓词过滤在reduce端执行

inner join时，谓词任意放都会下推

left join时，左表的谓词应该写在where后，右表的谓词应写在join后

right join时，左表的谓词应该写在join后，右表的谓词应写在where后

7）描述一下union和union all的区别，以及在mysql和HQL中用法的不同之处？

8）简单描述一下lateral view语法在HQL中的应用场景，并写一个HQL实例

?比如一个学生表为：

需要实现效果：

create table student(

`id` string,

`name` string,

`age` int,

`scores` array<string>

)

row format delimited fields terminated by '\t'

collection items terminated by ',';



select

????id,

????score

from

student lateral view explode(scores) tmp_score as score;

第13题

第14题

?第15题