10個經典Hive-SQL面試題
10個經典Hive-SQL面試題
資料來源:https://mp.weixin.qq.com/s?__biz=MzU5NDgyMjc0OQ==&mid=2247492072&idx=2&sn=824c8e3ebe148d3f840dbcf354225591&chksm=fe79f2dac90e7bcc97ab247157f16f33f4bec8ae71f1564a44bd09f269e839e188f3b5b990d4&scene=126&sessionid=1598513547&key=bf49e72a192178bf4075b8316bc5e7392fa69123d52fbc788f01c8c2bdf472e75297263c0ffd272487acfe5f7b7da6d3d744b379762d829e6bd443cee17ed4bf61888cbc3c130b18705727a1aa92562a7bc8a31a021fd91d36bb7249e3632acda20b1c2c7b20f709a0624091a17ec10c64742f95d9952617ce55ac9ae78d963f&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10+x64&version=62090529&lang=zh_TW&exportkey=AoW2ZnsI7Y4ACA9PDAo3DZs%3D&pass_ticket=8U225q1Ia3xIzmkcuQdH71UlgbcdTwgOT45vZqTQKM%2FM89JIIhQ1QmNHme06S9UW
問題01:
我们有如下的用户访问数据
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
答案01:
CREATE TABLE test_sql.test1 (
userId string,
visitDate string,
visitCount INT )
ROW format delimited FIELDS TERMINATED BY "\t";
INSERT INTO TABLE test_sql.test1
VALUES
( '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 );
SELECT t2.userid,
t2.visitmonth,
subtotal_visit_cnt,
sum(subtotal_visit_cnt) over (partition BY userid ORDER BY visitmonth) AS total_visit_cnt
FROM
(SELECT userid,
visitmonth,
sum(visitcount) AS subtotal_visit_cnt
FROM
(SELECT userid,
date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM') AS visitmonth,
visitcount
FROM test_sql.test1) t1
GROUP BY userid,
visitmonth)t2
ORDER BY t2.userid,
t2.visitmonth
問題02
有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，
访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：
u1 a
u2 b
u1 b
u1 a
u3 c
u4 b
u1 a
u2 c
u5 b
u4 b
u6 c
u2 c
u1 b
u2 a
u2 a
u3 a
u5 a
u5 a
u5 a
请统计：
(1)每个店铺的UV（访客数）
(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
答案02
CREATE TABLE test_sql.test2 (
user_id string,
shop string )
ROW format delimited FIELDS TERMINATED BY '\t';
INSERT INTO TABLE test_sql.test2 VALUES
( 'u1', 'a' ),
( 'u2', 'b' ),
( 'u1', 'b' ),
( 'u1', 'a' ),
( 'u3', 'c' ),
( 'u4', 'b' ),
( 'u1', 'a' ),
( 'u2', 'c' ),
( 'u5', 'b' ),
( 'u4', 'b' ),
( 'u6', 'c' ),
( 'u2', 'c' ),
( 'u1', 'b' ),
( 'u2', 'a' ),
( 'u2', 'a' ),
( 'u3', 'a' ),
( 'u5', 'a' ),
( 'u5', 'a' ),
( 'u5', 'a' );
(1)方式1：
SELECT shop,
count(DISTINCT user_id)
FROM test_sql.test2
GROUP BY shop
方式2：
SELECT t.shop,
count(*)
FROM
(SELECT user_id,
shop
FROM test_sql.test2
GROUP BY user_id,
shop) t
GROUP BY t.shop
(2)
SELECT t2.shop,
t2.user_id,
t2.cnt
FROM
(SELECT t1.*,
row_number() over(partition BY t1.shop
ORDER BY t1.cnt DESC) rank
FROM
(SELECT user_id,
shop,
count(*) AS cnt
FROM test_sql.test2
GROUP BY user_id,
shop) t1)t2
WHERE rank <= 3
問題03
已知一个表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。 数据样例:2017-01-01,10029028,1000003251,33.57。 请给出sql进行统计: (1)给出 2017年每个月的订单数、用户数、总成交金额。 (2)给出2017年11月的新客数(指在11月才有第一笔订单)
答案03
CREATE TABLE test_sql.test3 (
dt string,
order_id string,
user_id string,
amount DECIMAL ( 10, 2 ) )
ROW format delimited FIELDS TERMINATED BY '\t';
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','10029028','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','10029029','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-01-01','100290288','1000003252',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','10029088','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','100290281','1000003251',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-02-02','100290282','1000003253',33.57);
INSERT INTO TABLE test_sql.test3 VALUES ('2017-11-02','10290282','100003253',234);
INSERT INTO TABLE test_sql.test3 VALUES ('2018-11-02','10290284','100003243',234);
(1)
SELECT t1.mon,
count(t1.order_id) AS order_cnt,
count(DISTINCT t1.user_id) AS user_cnt,
sum(amount) AS total_amount
FROM
(SELECT order_id,
user_id,
amount,
date_format(dt,'yyyy-MM') mon
FROM test_sql.test3
WHERE date_format(dt,'yyyy') = '2017') t1
GROUP BY t1.mon
(2)
SELECT count(user_id)
FROM test_sql.test3
GROUP BY user_id
HAVING date_format(min(dt),'yyyy-MM')='2017-11';問題
問題04
有一个5000万的用户文件(user_id，name，age)，一个2亿记录的用户看电影的记录文件(user_id，url)，根据年龄段观看电影的次数进行排序？
答案04
CREATE TABLE test_sql.test4user
(user_id string,
name string,
age int);
CREATE TABLE test_sql.test4log
(user_id string,
url string);
INSERT INTO TABLE test_sql.test4user VALUES('001','u1',10);
INSERT INTO TABLE test_sql.test4user VALUES('002','u2',15);
INSERT INTO TABLE test_sql.test4user VALUES('003','u3',15);
INSERT INTO TABLE test_sql.test4user VALUES('004','u4',20);
INSERT INTO TABLE test_sql.test4user VALUES('005','u5',25);
INSERT INTO TABLE test_sql.test4user VALUES('006','u6',35);
INSERT INTO TABLE test_sql.test4user VALUES('007','u7',40);
INSERT INTO TABLE test_sql.test4user VALUES('008','u8',45);
INSERT INTO TABLE test_sql.test4user VALUES('009','u9',50);
INSERT INTO TABLE test_sql.test4user VALUES('0010','u10',65);
INSERT INTO TABLE test_sql.test4log VALUES('001','url1');
INSERT INTO TABLE test_sql.test4log VALUES('002','url1');
INSERT INTO TABLE test_sql.test4log VALUES('003','url2');
INSERT INTO TABLE test_sql.test4log VALUES('004','url3');
INSERT INTO TABLE test_sql.test4log VALUES('005','url3');
INSERT INTO TABLE test_sql.test4log VALUES('006','url1');
INSERT INTO TABLE test_sql.test4log VALUES('007','url5');
INSERT INTO TABLE test_sql.test4log VALUES('008','url7');
INSERT INTO TABLE test_sql.test4log VALUES('009','url5');
INSERT INTO TABLE test_sql.test4log VALUES('0010','url1');
SELECT
t2.age_phase,
sum(t1.cnt) as view_cnt
FROM
(SELECT user_id,
count(*) cnt
FROM test_sql.test4log
GROUP BY user_id) t1
JOIN
(SELECT user_id,
CASE WHEN age <= 10 AND age > 0 THEN '0-10'
WHEN age <= 20 AND age > 10 THEN '10-20'
WHEN age >20 AND age <=30 THEN '20-30'
WHEN age >30 AND age <=40 THEN '30-40'
WHEN age >40 AND age <=50 THEN '40-50'
WHEN age >50 AND age <=60 THEN '50-60'
WHEN age >60 AND age <=70 THEN '60-70'
ELSE '70以上' END as age_phase
FROM test_sql.test4user) t2 ON t1.user_id = t2.user_id
GROUP BY t2.age_phase
問題05
有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户） 日期 用户 年龄 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
答案05
CREATE TABLE test5(
dt string,
user_id string,
age int)
ROW format delimited fields terminated BY ',';
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_2',19);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_3',39);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_3',39);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-11','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-12','test_2',19);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-13','test_1',23);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-15','test_2',19);
INSERT INTO TABLE test_sql.test5 VALUES ('2019-02-16','test_2',19);
SELECT sum(total_user_cnt) total_user_cnt,
sum(total_user_avg_age) total_user_avg_age,
sum(two_days_cnt) two_days_cnt,
sum(avg_age) avg_age
FROM
(SELECT 0 total_user_cnt,
0 total_user_avg_age,
count(*) AS two_days_cnt,
cast(sum(age) / count(*) AS decimal(5,2)) AS avg_age
FROM
(SELECT user_id,
max(age) age
FROM
(SELECT user_id,
max(age) age
FROM
(SELECT user_id,
age,
date_sub(dt,rank) flag
FROM
(SELECT dt,
user_id,
max(age) age,
row_number() over(PARTITION BY user_id
ORDER BY dt) rank
FROM test_sql.test5
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(*) total_user_cnt,
cast(sum(age) /count(*) AS decimal(5,2)) total_user_avg_age,
0 two_days_cnt,
0 avg_age
FROM
(SELECT user_id,
max(age) age
FROM test_sql.test5
GROUP BY user_id) t5) t6
問題06
请用sql写出所有用户中在今年10月份第一次购买商品的金额， 表ordertable字段: (购买用户：userid，金额：money，购买时间：paymenttime(格式：2017-10-01)，订单id：orderid
答案06
CREATE TABLE test_sql.test6 (
userid string,
money decimal(10,2),
paymenttime string,
orderid string);
INSERT INTO TABLE test_sql.test6 VALUES('001',100,'2017-10-01','123');
INSERT INTO TABLE test_sql.test6 VALUES('001',200,'2017-10-02','124');
INSERT INTO TABLE test_sql.test6 VALUES('002',500,'2017-10-01','125');
INSERT INTO TABLE test_sql.test6 VALUES('001',100,'2017-11-01','126');
SELECT
userid,
paymenttime,
money,
orderid
from
(SELECT userid,
money,
paymenttime,
orderid,
row_number() over (PARTITION BY userid
ORDER BY paymenttime) rank
FROM test_sql.test6
WHERE date_format(paymenttime,'yyyy-MM') = '2017-10') t
WHERE rank = 1
問題07
现有图书管理数据库的三个数据模型如下：
图书（数据表名：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_ID 总编号 文本
3 BORROW_DATE 借书日期 日期
（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_L0G_ 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语法，请通过其他办法进行数据更新）
答案07
(1)
-- 创建图书表book
CREATE TABLE test_sql.book(book_id string,
`SORT` string,
book_name string,
writer string,
OUTPUT string,
price decimal(10,2));
INSERT INTO TABLE test_sql.book VALUES ('001','TP391','信息处理','author1','机械工业出版社','20');
INSERT INTO TABLE test_sql.book VALUES ('002','TP392','数据库','author12','科学出版社','15');
INSERT INTO TABLE test_sql.book VALUES ('003','TP393','计算机网络','author3','机械工业出版社','29');
INSERT INTO TABLE test_sql.book VALUES ('004','TP399','微机原理','author4','科学出版社','39');
INSERT INTO TABLE test_sql.book VALUES ('005','C931','管理信息系统','author5','机械工业出版社','40');
INSERT INTO TABLE test_sql.book VALUES ('006','C932','运筹学','author6','科学出版社','55');
-- 创建读者表reader
CREATE TABLE test_sql.reader (reader_id string,
company string,
name string,
sex string,
grade string,
addr string);
INSERT INTO TABLE test_sql.reader VALUES ('0001','阿里巴巴','jack','男','vp','addr1');
INSERT INTO TABLE test_sql.reader VALUES ('0002','百度','robin','男','vp','addr2');
INSERT INTO TABLE test_sql.reader VALUES ('0003','腾讯','tony','男','vp','addr3');
INSERT INTO TABLE test_sql.reader VALUES ('0004','京东','jasper','男','cfo','addr4');
INSERT INTO TABLE test_sql.reader VALUES ('0005','网易','zhangsan','女','ceo','addr5');
INSERT INTO TABLE test_sql.reader VALUES ('0006','搜狐','lisi','女','ceo','addr6');
-- 创建借阅记录表borrow_log
CREATE TABLE test_sql.borrow_log(reader_id string,
book_id string,
borrow_date string);
INSERT INTO TABLE test_sql.borrow_log VALUES ('0001','002','2019-10-14');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0002','001','2019-10-13');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0003','005','2019-09-14');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0004','006','2019-08-15');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0005','003','2019-10-10');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0006','004','2019-17-13');
(2)
SELECT name,
company
FROM test_sql.reader
WHERE name LIKE '李%';
(3)
SELECT book_name,
price
FROM test_sql.book
WHERE OUTPUT = "高等教育出版社"
ORDER BY price DESC;
(4)
SELECT sort,
output,
price
FROM test_sql.book
WHERE price >= 10 and price <= 20
ORDER BY output,price ;
(5)
SELECT b.name,
b.company
FROM test_sql.borrow_log a
JOIN test_sql.reader b ON a.reader_id = b.reader_id;
(6)
SELECT max(price),
min(price),
avg(price)
FROM test_sql.book
WHERE OUTPUT = '科学出版社';
(7)
SELECT b.name,
b.company
FROM
(SELECT reader_id
FROM test_sql.borrow_log
GROUP BY reader_id
HAVING count(*) >= 2) a
JOIN test_sql.reader b ON a.reader_id = b.reader_id;
(8)
CREATE TABLE test_sql.borrow_log_bak AS
SELECT *
FROM test_sql.borrow_log;
(9)
CREATE TABLE book_hive (
book_id string,
SORT string,
book_name string,
writer string,
OUTPUT string,
price DECIMAL ( 10, 2 ) )
partitioned BY ( month_part string, day_part string )
ROW format delimited FIELDS TERMINATED BY '\\|' stored AS textfile;
(10)
方式1：配置hive支持事务操作，分桶表，orc存储格式
方式2：第一步找到要更新的数据，将要更改的字段替换为新的值，第二步找到不需要更新的数据，第三步将上两步的数据插入一张新表中。
問題08
有一个线上服务器访问日志格式如下（用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地址
答案08
CREATE TABLE test_sql.test8(`date` string,
interface string,
ip string);
INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 11:22:05','/api/user/login','110.23.5.23');
INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 11:23:10','/api/user/detail','57.3.2.16');
INSERT INTO TABLE test_sql.test8 VALUES ('2016-11-09 23:59:40','/api/user/login','200.6.5.166');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:14:23','/api/user/login','136.79.47.70');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:15:23','/api/user/detail','94.144.143.141');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 11:16:23','/api/user/login','197.161.8.206');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 12:14:23','/api/user/detail','240.227.107.145');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 13:14:23','/api/user/login','79.130.122.205');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:14:23','/api/user/detail','65.228.251.189');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:15:23','/api/user/detail','245.23.122.44');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:17:23','/api/user/detail','22.74.142.137');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:19:23','/api/user/detail','54.93.212.87');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:20:23','/api/user/detail','218.15.167.248');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:24:23','/api/user/detail','20.117.19.75');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 15:14:23','/api/user/login','183.162.66.97');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 16:14:23','/api/user/login','108.181.245.147');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:17:23','/api/user/login','22.74.142.137');
INSERT INTO TABLE test_sql.test8 VALUES('2016-11-09 14:19:23','/api/user/login','22.74.142.137');
SELECT ip,
count(*) AS cnt
FROM test_sql.test8
WHERE date_format(date,'yyyy-MM-dd HH') >= '2016-11-09 14'
AND date_format(date,'yyyy-MM-dd HH') < '2016-11-09 15'
AND interface='/api/user/login'
GROUP BY ip
ORDER BY cnt desc
LIMIT 10;
問題09
有一个充值日志表credit_log，字段如下： `dist_id` int '区组id', `account` string '账号', `money` int '充值金额', `create_time` string '订单时间' 请写出SQL语句，查询充值日志表2019年01月02号每个区组下充值额最大的账号，要求结果： 区组id，账号，金额，充值时间
答案09
CREATE TABLE test_sql.test9(
dist_id string COMMENT '区组id',
account string COMMENT '账号',
`money` decimal(10,2) COMMENT '充值金额',
create_time string COMMENT '订单时间');
INSERT INTO TABLE test_sql.test9 VALUES ('1','11',100006,'2019-01-02 13:00:01');
INSERT INTO TABLE test_sql.test9 VALUES ('1','22',110000,'2019-01-02 13:00:02');
INSERT INTO TABLE test_sql.test9 VALUES ('1','33',102000,'2019-01-02 13:00:03');
INSERT INTO TABLE test_sql.test9 VALUES ('1','44',100300,'2019-01-02 13:00:04');
INSERT INTO TABLE test_sql.test9 VALUES ('1','55',100040,'2019-01-02 13:00:05');
INSERT INTO TABLE test_sql.test9 VALUES ('1','66',100005,'2019-01-02 13:00:06');
INSERT INTO TABLE test_sql.test9 VALUES ('1','77',180000,'2019-01-03 13:00:07');
INSERT INTO TABLE test_sql.test9 VALUES ('1','88',106000,'2019-01-02 13:00:08');
INSERT INTO TABLE test_sql.test9 VALUES ('1','99',100400,'2019-01-02 13:00:09');
INSERT INTO TABLE test_sql.test9 VALUES ('1','12',100030,'2019-01-02 13:00:10');
INSERT INTO TABLE test_sql.test9 VALUES ('1','13',100003,'2019-01-02 13:00:20');
INSERT INTO TABLE test_sql.test9 VALUES ('1','14',100020,'2019-01-02 13:00:30');
INSERT INTO TABLE test_sql.test9 VALUES ('1','15',100500,'2019-01-02 13:00:40');
INSERT INTO TABLE test_sql.test9 VALUES ('1','16',106000,'2019-01-02 13:00:50');
INSERT INTO TABLE test_sql.test9 VALUES ('1','17',100800,'2019-01-02 13:00:59');
INSERT INTO TABLE test_sql.test9 VALUES ('2','18',100800,'2019-01-02 13:00:11');
INSERT INTO TABLE test_sql.test9 VALUES ('2','19',100030,'2019-01-02 13:00:12');
INSERT INTO TABLE test_sql.test9 VALUES ('2','10',100000,'2019-01-02 13:00:13');
INSERT INTO TABLE test_sql.test9 VALUES ('2','45',100010,'2019-01-02 13:00:14');
INSERT INTO TABLE test_sql.test9 VALUES ('2','78',100070,'2019-01-02 13:00:15');
WITH TEMP AS
(SELECT dist_id,
account,
sum(`money`) sum_money
FROM test_sql.test9
WHERE date_format(create_time,'yyyy-MM-dd') = '2019-01-02'
GROUP BY dist_id,
account)
SELECT t1.dist_id,
t1.account,
t1.sum_money
FROM
(SELECT temp.dist_id,
temp.account,
temp.sum_money,
rank() over(partition BY temp.dist_id
ORDER BY temp.sum_money DESC) ranks
FROM TEMP) t1
WHERE ranks = 1
問題10
有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10） dist_id string '区组id', account string '账号', gold int '金币'
答案10
CREATE TABLE test_sql.test10(
`dist_id` string COMMENT '区组id',
`account` string COMMENT '账号',
`gold` int COMMENT '金币'
);
INSERT INTO TABLE test_sql.test10 VALUES ('1','77',18);
INSERT INTO TABLE test_sql.test10 VALUES ('1','88',106);
INSERT INTO TABLE test_sql.test10 VALUES ('1','99',10);
INSERT INTO TABLE test_sql.test10 VALUES ('1','12',13);
INSERT INTO TABLE test_sql.test10 VALUES ('1','13',14);
INSERT INTO TABLE test_sql.test10 VALUES ('1','14',25);
INSERT INTO TABLE test_sql.test10 VALUES ('1','15',36);
INSERT INTO TABLE test_sql.test10 VALUES ('1','16',12);
INSERT INTO TABLE test_sql.test10 VALUES ('1','17',158);
INSERT INTO TABLE test_sql.test10 VALUES ('2','18',12);
INSERT INTO TABLE test_sql.test10 VALUES ('2','19',44);
INSERT INTO TABLE test_sql.test10 VALUES ('2','10',66);
INSERT INTO TABLE test_sql.test10 VALUES ('2','45',80);
INSERT INTO TABLE test_sql.test10 VALUES ('2','78',98);
SELECT dist_id,
account,
gold
FROM
(SELECT dist_id,
account,
gold,
row_number () over (PARTITION BY dist_id
ORDER BY gold DESC) rank
FROM test_sql.test10) t
WHERE rank <= 10
2 thoughts on “10個經典Hive-SQL面試題”
要求使用SQL統計出每個用戶的累積訪問次數
每個店舖的UV（訪客數）
每個店鋪訪問次數top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數
給出 2017年每個月的訂單數、用戶數、總成交金額。
給出2017年11月的新客數(指在11月才有第一筆訂單)
根據年齡段觀看電影的次數進行排序？
請寫出代碼求得所有用戶和活躍用戶的總數及平均年齡。 （活躍用戶指連續兩天都有訪問記錄的用戶）
寫出所有用戶中在今年10月份第一次購買商品的金額
創建圖書管理庫的圖書、讀者和借閱三個基本表的表結構。請寫出建表語句。
找出姓李的讀者姓名（NAME）和所在單位（COMPANY）。
查找“高等教育出版社”的所有圖書名稱（BOOK_NAME）及單價（PRICE），結果按單價降序排序。
查找價格介於10元和20元之間的圖書種類(SORT）出版單位（OUTPUT）和單價（PRICE），結果按出版單位（OUTPUT）和單價（PRICE）升序排序。
查找所有借了書的讀者的姓名（NAME）及所在單位（COMPANY）。
求”科學出版社”圖書的最高單價、最低單價、平均單價。
找出當前至少借閱了2本圖書（大於等於2本）的讀者姓名及其所在單位。
考慮到數據安全的需要，需定時將“借閱記錄”中數據進行備份，請使用一條SQL語句，在備份用戶bak下創建與“借閱記錄”表結構完全一致的數據表BORROW_LOG_BAK.井且將“借閱記錄”中現有數據全部複製到BORROW_L0G_ BAK中。
現在需要將原Oracle數據庫中數據遷移至Hive倉庫，請寫出“圖書”在Hive中的建表語句（Hive實現，提示：列分隔符|；數據表數據需要外部導入：分區分別以month＿part、day＿part命名）
Hive中有表A，現在需要將表A的月分區 201505 中 user＿id為20000的user＿dinner字段更新為bonc8920，其他用戶user＿dinner字段數據不變，請列出更新的方法步驟。 （Hive實現，提示：Hlive中無update語法，請通過其他辦法進行數據更新）
求11月9號下午14點（14-15點），訪問/api/user/login接口的top10的ip地址
查詢充值日誌表2019年01月02號每個區組下充值額最大的賬號，要求結果：
區組id，賬號，金額，充值時間
請寫出SQL語句，查詢各自區組的money排名前十的賬號（分組取前10）
HiveSQL的條件函數與日期函數全面匯總解析
http://jashliao.eu/wordpress/2020/08/27/10%e5%80%8b%e7%b6%93%e5%85%b8hive-sql%e9%9d%a2%e8%a9%a6%e9%a1%8c/
條件函數
assert_true(BOOLEAN condition)
解釋
如果condition不為true，則拋出異常，否則返回null
使用案例
select assert_true(12) — 抛出异常
coalesce(T v1, T v2, …)
解釋
返回第一個不為null的值，如果都為null，則返回null
使用案例
select coalesce(null,1,2,null) — 返回1
select coalesce(1,null) — 返回1
select coalesce(null,null) — 返回null
if(BOOLEAN testCondition, T valueTrue, T valueFalseOrNull)
解釋
如果testCondition條件為true，則返回第一個值，否則返回第二個值
使用案例
select if(1 is null,0,1) — 返回1
select if(null is null,0,1) — 返回0
isnotnull(a)
解釋
如果參數a不為null，則返回true，否則返回false
使用案例
select isnotnull(1) — 返回true
select isnotnull(null) — 返回false
isnull(a)
解釋
與isnotnull相反，如果參數a為null，則返回true，否則返回false
使用案例
select isnull(null) — 返回true
select isnull(1) — 返回false
nullif(a, b)
解釋
如果參數a=b，返回null，否則返回a值(Hive2.2.0版本)
使用案例
select nullif(1,2) — 返回1
select nullif(1,1) — 返回null
nvl(T value, T default_value)
解釋
如果value的值為null，則返回default_value默認值，否則返回value的值。在null值判斷時，可以使用if函數給定默認值，也可以使用此函數給定默認值，使用該函數sql特別簡潔。
使用案例
select nvl(1,0) — 返回1
select nvl(null,0) — 返回0
日期函數
add_months(DATE|STRING|TIMESTAMP start_date, INT num_months)
解釋
start_date參數可以是string, date 或者timestamp類型，num_months參數時int類型。返回一個日期，該日期是在start_date基礎之上加上num_months個月，即start_date之後null_months個月的一個日期。如果start_date的時間部分的數據會被忽略。注意：如果start_date所在月份的天數大於結果日期月的天數，則返回結果月的最後一天的日期。
使用案例
select add_months(“2020-05-20”,2); — 返回2020-07-20
select add_months(“2020-05-20”,8); — 返回2021-01-20
select add_months(“2020-05-31”,1); — 返回2020-06-30,5月有31天，6月只有30天，所以返回下一个月的最后一天
current_date
解釋
返回查詢時刻的當前日期
使用案例
select current_date() — 返回当前查询日期2020-05-20
current_timestamp()
解釋
返回查詢時刻的當前時間
使用案例
select current_timestamp() — 2020-05-20 14:40:47.273
datediff(STRING enddate, STRING startdate)
解釋
返回開始日期startdate與結束日期enddate之前相差的天數
使用案例
select datediff(“2020-05-20″,”2020-05-21”); — 返回-1
select datediff(“2020-05-21″,”2020-05-20”); — 返回1
date_add(DATE startdate, INT days)
解釋
在startdate基礎上加上幾天，然後返回加上幾天之後的一個日期
使用案例
select date_add(“2020-05-20”,1); — 返回2020-05-21,1表示加1天
select date_add(“2020-05-20”,-1); — 返回2020-05-19，-1表示减一天
date_sub(DATE startdate, INT days)
解釋
在startdate基礎上減去幾天，然後返回減去幾天之後的一個日期,功能與date_add很類似
使用案例
select date_sub(“2020-05-20”,1); — 返回2020-05-19,1表示减1天
select date_sub(“2020-05-20”,-1); — 返回2020-05-21，-1表示加1天
date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)
解釋
將date/timestamp/string類型的值轉換為一個具體格式化的字符串。支持java的SimpleDateFormat格式，第二個參數fmt必須是一個常量
使用案例
select date_format(‘2020-05-20’, ‘yyyy’); — 返回2020
select date_format(‘2020-05-20’, ‘MM’); — 返回05
select date_format(‘2020-05-20’, ‘dd’); — 返回20
— 返回2020年05月20日 00时00分00秒
select date_format(‘2020-05-20’, ‘yyyy年MM月dd日 HH时mm分ss秒’) ;
select date_format(‘2020-05-20’, ‘yy/MM/dd’) — 返回 20/05/20
dayofmonth(STRING date)
解釋
返回一個日期或時間的天,與day()函數功能相同
使用案例
select dayofmonth(‘2020-05-20’) — 返回20
extract(field FROM source)
解釋
提取day, dayofweek, hour, minute, month, quarter, second, week 或者year的值，field可以選擇day, dayofweek, hour, minute, month, quarter, second, week 或者year，source必須是一個date、timestamp或者可以轉為date 、timestamp的字符串。注意：Hive 2.2.0版本之後支持該函數
使用案例
select extract(year from ‘2020-05-20 15:21:34.467’); — 返回2020，年
select extract(quarter from ‘2020-05-20 15:21:34.467’); — 返回2，季度
select extract(month from ‘2020-05-20 15:21:34.467’); — 返回05，月份
select extract(week from ‘2020-05-20 15:21:34.467’); — 返回21，同weekofyear，一年中的第几周
select extract(dayofweek from ‘2020-05-20 15:21:34.467’); — 返回4,代表星期三
select extract(day from ‘2020-05-20 15:21:34.467’); — 返回20，天
select extract(hour from ‘2020-05-20 15:21:34.467’); — 返回15，小时
select extract(minute from ‘2020-05-20 15:21:34.467’); — 返回21，分钟
select extract(second from ‘2020-05-20 15:21:34.467’); — 返回34，秒
year(STRING date)
解釋
返回時間的年份,可以用extract函數替代
使用案例
select year(‘2020-05-20 15:21:34’); — 返回2020
quarter(DATE|TIMESTAMP|STRING a)
解釋
返回給定時間或日期的季度，1至4個季度,可以用extract函數替代
使用案例
select quarter(‘2020-05-20 15:21:34’); — 返回2，第2季度
month(STRING date)
解釋
返回時間的月份,可以用extract函數替代
使用案例
select month(‘2020-05-20 15:21:34’) — 返回5
day(STRING date),
解釋
返回一個日期或者時間的天,可以用extract函數替代
使用案例
select day(“2020-05-20”); — 返回20
select day(“2020-05-20 15:05:27.5”); — 返回20
hour(STRING date)
解釋
返回一個時間的小時,可以用extract函數替代
使用案例
select hour(‘2020-05-20 15:21:34’);– 返回15
minute(STRING date)
解釋
返回一個時間的分鐘值,可以用extract函數替代
使用案例
select minute(‘2020-05-20 15:21:34’); — 返回21
second(STRING date)
解釋
返回一個時間的秒,可以用extract函數替代
使用案例
select second(‘2020-05-20 15:21:34’); –返回34
from_unixtime(BIGINT unixtime [, STRING format])
解釋
將將Unix時間戳轉換為字符串格式的時間(比如yyyy-MM-dd HH:mm:ss格式)
使用案例
select from_unixtime(1589960708); — 返回2020-05-20 15:45:08
select from_unixtime(1589960708, ‘yyyy-MM-dd hh:mm:ss’); — — 返回2020-05-20 15:45:08
select from_unixtime(1589960708, ‘yyyy-MM-dd’); — 返回2020-05-20
from_utc_timestamp(T a, STRING timezone)
解釋
轉換為特定時區的時間
使用案例
select from_utc_timestamp(‘2020-05-20 15:21:34′,’PST’); — 返回2020-05-20 08:21:34.0
select from_utc_timestamp(‘2020-05-20 15:21:34′,’GMT’); — 返回2020-05-20 15:21:34.0
select from_utc_timestamp(‘2020-05-20 15:21:34′,’UTC’); — 返回2020-05-20 15:21:34.0
select from_utc_timestamp(‘2020-05-20 15:21:34′,’DST’); — 返回2020-05-20 15:21:34.0
select from_utc_timestamp(‘2020-05-20 15:21:34′,’CST’); — 返回2020-05-20 10:21:34.0
last_day(STRING date)
解釋
返回給定時間或日期所在月的最後一天，參數可以是’yyyy-MM-dd HH:mm:ss’ 或者’yyyy-MM-dd’類型，時間部分會被忽略
使用案例
select last_day(‘2020-05-20 15:21:34’); — 返回2020-05-31
select last_day(‘2020-05-20’); — 返回2020-05-31
to_date(STRING timestamp)
解釋
返回一個字符串時間的日期部分，去掉時間部分，2.1.0之前版本返回的是string，2.1.0版本及之後返回的是date
使用案例
select to_date(‘2020-05-20 15:21:34’); — 返回2020-05-20
select to_date(‘2020-05-20’); — 返回2020-05-20
to_utc_timestamp(T a, STRING timezone)
解釋
轉換為世界標準時間UTC的時間戳,與from_utc_timestamp類似
使用案例
select to_utc_timestamp(‘2020-05-20 15:21:34’, ‘GMT’); — 返回2020-05-20 15:21:34.0
trunc(STRING date, STRING format)
解釋
截斷日期到指定的日期精度，僅支持月（MONTH/MON/MM）或者年（YEAR/YYYY/YY）
使用案例
select trunc(‘2020-05-20’, ‘YY’); — 返回2020-01-01，返回年的1月1日
select trunc(‘2020-05-20’, ‘MM’); — 返回2020-05-01，返回月的第一天
select trunc(‘2020-05-20 15:21:34’, ‘MM’); — 返回2020-05-01
unix_timestamp([STRING date [, STRING pattern]])
解釋
參數時可選的，當參數為空時，返回當前Unix是時間戳，精確到秒。可以指定一個具體的日期，轉換為Unix時間戳格式
使用案例
— 返回1589959294
select unix_timestamp(‘2020-05-20 15:21:34′,’yyyy-MM-dd hh:mm:ss’);
— 返回1589904000
select unix_timestamp(‘2020-05-20′,’yyyy-MM-dd’);
weekofyear(STRING date)
解釋
返回一個日期或時間在一年中的第幾週，可以用extract替代
使用案例
select weekofyear(‘2020-05-20 15:21:34’); — 返回21，第21周
select weekofyear(‘2020-05-20’); — 返回21，第21周
next_day(STRING start_date, STRING day_of_week)
解釋
參數start_date可以是一個時間或日期，day_of_week表示星期幾，比如Mo表示星期一，Tu表示星期二，Wed表示星期三，Thur表示星期四，Fri表示星期五，Sat表示星期六，Sun表示星期日。如果指定的星期幾在該日期所在的周且在該日期之後，則返回當週的星期幾日期，如果指定的星期幾不在該日期所在的周，則返回下一個星期幾對應的日期
使用案例
select next_day(‘2020-05-20′,’Mon’);– 返回当前日期的下一个周一日期:2020-05-25
select next_day(‘2020-05-20′,’Tu’);– 返回当前日期的下一个周二日期:2020-05-26
select next_day(‘2020-05-20′,’Wed’);– 返回当前日期的下一个周三日期:2020-05-27
— 2020-05-20为周三，指定的参数为周四，所以返回当周的周四就是2020-05-21
select next_day(‘2020-05-20′,’Th’);
select next_day(‘2020-05-20′,’Fri’);– 返回周五日期2020-05-22
select next_day(‘2020-05-20′,’Sat’); — 返回周六日期2020-05-23
select next_day(‘2020-05-20′,’Sun’); — 返回周六日期2020-05-24
該函數比較重要：比如取當前日期所在的周一和周日，通過長用在按週進行匯總數據
select date_add(next_day(‘2020-05-20′,’MO’),-7); — 返回当前日期的周一日期2020-05-18
select date_add(next_day(‘2020-05-20′,’MO’),-1); — 返回当前日期的周日日期2020-05-24
months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)
解釋
返回date1 和date2 的月份差。如果date1大於date2，返回正值，否則返回負值，如果是相減是整數月，則返回一個整數，否則會返回小數
使用案例
select months_between(‘2020-05-20′,’2020-05-20’); — 返回0
select months_between(‘2020-05-20′,’2020-06-20’); — 返回-1
— 相差的整数月
select months_between(‘2020-06-30′,’2020-05-31’); — 返回1
— 非整数月，一个月差一天
select months_between(‘2020-06-29′,’2020-05-31’); — 返回0.93548387
小結
本文主要介紹了Hive的條件函數和日期函數，並給出了每個函數的解釋說明和使用案例，本文覆蓋了所有Hive內置的條件函數和日期函數，可以作為一個函數字典，方便工作中使用。