[hive] 经典sql题及答案(一)

推荐:

经典sql题及答案(二)
经典sql题及答案(三)

题目部分

第1题
我们有如下的用户访问数据
userId visitDate visitCount
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
U02 2017/1/23 6
U01 2017/2/22 4
要求使用SQL统计出每个用户的累积访问次数，如下表所示：
用户id 月份 小计 累积
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3

第2题 京东
有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，请统计：
1）每个店铺的UV（访客数）
2）每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

第3题
已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。请给出sql进行统计:数据样例:2017-01-01,10029028,1000003251,33.57。
1）给出 2017年每个月的订单数、用户数、总成交金额。
2）给出2017年11月的新客数(指在11月才有第一笔订单)

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

第5题
有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
日期 用户 年龄
11,test_1,23
11,test_2,19
11,test_3,39
11,test_1,23
11,test_3,39
11,test_1,23
12,test_2,19
13,test_1,23

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

第7题
现有图书管理数据库的三个数据模型如下：
图书（数据表名：BOOK）
序号 字段名称 字段描述 字段类型
1 BOOK_ID 总编号 文本
2 SORT 分类号 文本
3 BOOK_NAME 书名 文本
4 WRITER 作者 文本
5 OUTPUT 出版单位 文本
6 PRICE 单价 数值（保留小数点后2位）
读者（数据表名：READER）
序号 字段名称 字段描述 字段类型
1 READER_ID 借书证号 文本
2 COMPANY 单位 文本
3 NAME 姓名 文本
4 SEX 性别 文本
5 GRADE 职称 文本
6 ADDR 地址 文本
借阅记录（数据表名：BORROW LOG）
序号 字段名称 字段描述 字段类型
1 READER_ID 借书证号 文本
2 BOOK_D 总编号 文本
3 BORROW_ATE 借书日期 日期
（1）创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。
（2）找出姓李的读者姓名（NAME）和所在单位（COMPANY）。
（3）查找“高等教育出版社”的所有图书名称（BOOK_NAME）及单价（PRICE），结果按单价降序排序。
（4）查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），结果按出版单位（OUTPUT）和单价（PRICE）升序排序。
（5）查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。
（6）求”科学出版社”图书的最高单价、最低单价、平均单价。
（7）找出当前至少借阅了2本图书（大于等于2本）的读者姓名及其所在单位。
（8）考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_1.0G_ BAK中。
（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语法，请通过其他办法进行数据更新）

第8题
有一个线上服务器访问日志格式如下（用sql答题）
时间 接口 ip地址
2016-11-09 11：22：05 /api/user/login 110.23.5.33
2016-11-09 11：23：10 /api/user/detail 57.3.2.16
…
2016-11-09 23：59：40 /api/user/login 200.6.5.166
求11月9号下午14点（14-15点），访问api/user/login接口的top10的ip地址

第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，账号，金额，充值时间
第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）

答案部分

01
u01	2017/1/21	5
u02	2017/1/23	6
u03	2017/1/22	8
u04	2017/1/20	3
u01	2017/1/23	6
u01	2017/2/21	8
u02	2017/1/23	6
u01	2017/2/22	4

create table sql01(userid string,visitdate string,visitcount int)
row format delimited fields terminated by '\t'
stored as textfile;

select userid,
date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') dt,
sum(visitcount) sum
from sql01
group by userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM');t1

select userid,dt,sum,sum(sum) over(partition by userid order by dt rows between unbounded preceding and current row) sum2
from (select userid,
date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') dt,
sum(visitcount) sum
from sql01
group by userid,date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM'))t1;

02
u01	a
u02	b
u03	b
u04	a
u01	b
u01	c
u02	b
u01	c
u01	a
u02	b
u03	c
u04	a
u01	c
u01	c
u02	a
u01	b

create table sql02(userid string,shop string)
row format delimited fields terminated by '\t'
stored as textfile;

load data local inpath '/root/in/sql02' into table sql02;

去重每个店的用户
select shop,userid from sql02 group by shop,userid;t1

计算pv
select 
shop,
count(*)
from (select shop,userid from sql02 group by shop,userid)t1
group by shop;


去重每个店的每个用户,获得数量
select
userid,
shop,
count(*) num
from 
sql02
group by userid,shop;t1

排名
select
shop,
userid,
num,
row_number() over(partition by shop order by num desc) rank
from
(select
userid,
shop,
count(*) num
from 
sql02
group by userid,shop)t1;t2

取前三
select shop,userid,num
from
(select
shop,
userid,
num,
row_number() over(partition by shop order by num desc) rank
from
(select
userid,
shop,
count(*) num
from 
sql02
group by userid,shop)t1)t2
where rank<3;

03
2016-01-01,100290212,1000325123,32
2017-01-01,100290282,1000325123,32
2017-01-01,101290281,1000003251,43
2017-01-01,102290291,1000003251,33
2017-01-01,103290281,1100000325,33
2017-02-02,104290881,1000003251,33
2017-02-02,105290281,1100000325,33
2017-02-02,106290281,1100000325,33
2017-11-02,107902821,1000032532,34
2017-11-02,108902821,1000032433,42
2017-12-02,108202821,1000032433,42

create table sql03(dt string,orderid int,userid int,amount int)
row format delimited fields terminated by ','
stored as textfile;

load data local inpath '/root/in/sql03' into table sql03;

1）给出 2017年每个月的订单数、用户数、总成交金额。
得到yyyy-MM的格式
select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid,
amount
from sql03;t1

获取每个月每个用户的订单数,成交金额
select
dtt,
userid,
count(*) order_num,
sum(amount) amount_num
from
(select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid,
amount
from sql03)t1
where substring(dtt,0,4)='2017'
group by dtt,userid;t2

每个月的订单数、用户数、总成交金额
select 
dtt,
sum(order_num) all_order_num,
count(*) all_user_num,
sum(amount_num) all_amount_num
from 
(select
dtt,
userid,
count(*) order_num,
sum(amount) amount_num
from
(select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid,
amount
from sql03)t1
where substring(dtt,0,4)='2017'
group by dtt,userid)t2
group by dtt;

2）给出2017年11月的新客数(指在11月才有第一笔订单)
得到yyyy-MM的格式
select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid,
amount
from sql03;t1

每个月的客户去重
select
dtt,
userid
from
(select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid
from sql03)t2
group by dtt,userid;t2

客户按照时间升序排序
select
userid,
dtt,
row_number() over(partition by userid order by dtt) `rank`
from
(select
dtt,
userid
from
(select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid
from sql03)t2
group by dtt,userid)t2;t3

取2017年11月第一次下订单的客户
select 
userid
from
(select
userid,
dtt,
row_number() over(partition by userid order by dtt) `rank`
from
(select
dtt,
userid
from
(select
date_format(dt,'yyyy-MM') dtt,
userid,
orderid
from sql03)t2
group by dtt,userid)t2)t3
where 
`rank`=1 and dtt='2017-11';

05
11,test_1,23
11,test_2,19
11,test_3,39
11,test_1,23
11,test_3,39
11,test_1,23
12,test_2,19
13,test_1,23

create table sql05(dt int,user_id string,age int)
row format
delimited fields terminated by","
stored as textfile;

load data local inpath '/root/in/sql05' into table sql05;

1 首先去重并加一列标记
select
dt,
user_id,
row_number() over(partition by user_id order by dt) `rank`
from
sql05
group by
dt,user_id;t1
2 如果dt-`rank`的值连续两行保持不变,那么此用户为活跃用户.
select
user_id,
dt - `rank` sub
from
(select
dt,
user_id,
row_number() over(partition by user_id order by dt) `rank`
from
sql05
group by
dt,user_id)t1;t2
3 计数,取活跃用户
select
user_id,
count(*)
from
(select
user_id,
dt - `rank` sub
from
(select
dt,
user_id,
row_number() over(partition by user_id order by dt) `rank`
from
sql05
group by
dt,user_id)t1)t2
group by
user_id,sub
having
count(*)>1;t3
4 两边关联查询,计算平均年龄
select 
avg(age)
from
(select
age,
user_id
from
sql05
group by
user_id,age)t4
join
(select
user_id,
count(*)
from
(select
user_id,
dt - `rank` sub
from
(select
dt,
user_id,
row_number() over(partition by user_id order by dt) `rank`
from
sql05
group by
dt,user_id)t2)t3
group by
user_id,sub
having
count(*)>1)t5
on
t4.user_id=t5.user_id;

06
101	44	2016-5-11	1111
101	54	2017-5-11	1112
101	64	2017-10-11	1113
101	74	2017-10-14	1114
102	14	2017-5-11	1115
102	44	2017-6-11	1116
103	44	2017-10-13	1117
104	44	2017-10-14	1118
104	144	2017-10-11	1119
105	44	2017-10-11	1121

create table sql06(userid string,money int,paymenttime string,orderid int)
row format
delimited fields terminated by '\t'
stored as textfile;

load data local inpath '/root/in/sql06' into table sql06;

1 获取所有用户今年十月份的交易记录
select
userid,
money,
paymenttime
from
sql06
where
substring(paymenttime,0,7)='2017-10';t1
2 按用户分组,时间排序
select
userid,
money,
paymenttime,
row_number() over(partition by userid order by paymenttime) `rank`
from
(select
userid,
money,
paymenttime
from
sql06
where
substring(paymenttime,0,7)='2017-10')t1;t2
3 取排第一的交易记录及金额
select
userid,
money
from
(select
userid,
money,
paymenttime,
row_number() over(partition by userid order by paymenttime) `rank`
from
(select
userid,
money,
paymenttime
from
sql06
where
substring(paymenttime,0,7)='2017-10')t1)t2
where
`rank`=1;

08
select ip ,count(*) count
from server 
where (date_format(`time`,'yyyy-MM-dd HH')='2016-11-09 14' or `time`='2016-11-09 15:00:00') and interface='/api/user/login'
group by ip
order by count desc
limit 10;

09
1	11	1000	2015-11-04
1	12	100	2015-7-09
2	21	50	2015-7-09
2	22	150	2015-7-09
3	32	350	2015-7-09

create table sql09(dist_id int,account int,money int,dt string)
row format delimited fields terminated by '\t'
stored as textfile;

load data local inpath '/root/in/sql09' into table sql09;

获得2017年7月9日每个区组最大的充值金额
select
dist_id,
max(money) money
from
sql09
where dt='2015-7-09'
group by
dist_id;t1
关联查询获得账号
select
t.dist_id,
t.account,
t.money,
t.dt
from
sql09 t
join
(select
dist_id,
max(money) money
from
sql09
where dt='2015-7-09'
group by
dist_id)t1
on
t.dist_id=t1.dist_id and t.money=t1.money;

12
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
(
	Id bigint comment ‘学号’,
	cid string comment ‘课程号’,
	score bigint comment ‘成绩’
) partitioned by(event_day string)