Hive SQL经典面试题

weixin_71116509

已于 2024-07-25 10:59:04 修改

阅读量913

点赞数 22

文章标签： sql 数据库

于 2024-06-25 18:44:55 首次发布

本文链接：https://blog.csdn.net/weixin_71116509/article/details/139933796

版权

题目一

我们有如下的用户访问数据
	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

创建数据库

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 );

查询sql

select
user_id,
dt,
sum(vcount) over(partition by userId order by dt) total
from
(
    select
        userId,
        dt,
        sum(visitcount) vcount
    from
    (
	    select
	        userId,
	        date_formate(replace(visitDate,'/','-'),'yyyy-MM') dt
            visitcount,
	    from test_sal.test1
    )t1
    group by userId,dt
)t2

题目二

有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、访问次数

查询sql

(1)
select
    user_id,
    count(distinct user_id) UV
from Visit
group by shop
(2)
select
shop,
user_id,
ct,
from(
    select
        user_id,
        ct,
        row_number() over(partition by user_id order by ct desc),
    from(
        select
            shop,
            count(*) ct
        from Visit
        group by shop,user_id
        )t1
    )t2
where rk<=3

题目三

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

(1)
select
    dt,
    count(Order_id) order_num
    count(DISTINCT user_id) user_num
    sum(amount) total
from(
    select
        Order_id,
        user_id,
        amount,
        date_format(Date,'yyyy-MM') dt
    from STG.ORDER
    where year(Date) = '2017'
    )
group by dt
(2)
select
    count(user_id)
from STG.ORDER
group by user_id
having date_format(min(Date),'yyyy-MM') = '2017-11'

题目四

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

select
    age_phase,
    sum(ct) view_ct
from(
        select
            user_id,
            age,
            case when age > 0 and age <= 10 then '0-10'
            when age > 10 and age <=20 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以上' age_phase
        from test_sql_user
        )t1 
        join
        (
        select
            user_id,
            count(*) ct
        from test_sql_log
        group by user_id
        )t2 on t1.user_id = t2.user_id
group by age_phase

题目五

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

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

题目六

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

select
    userid,
    paymenttime,
    money,
    orderid,
from(
    select
        user_id,
        paymenttime,
        orderid,
        row_number() over(partition by userid order by paymenttime) rank
    from ordertable
    where date_format(paymenttime,'yyyy-MM') = '2017-10'
    )t1
where rank = 1

题目七

现有图书管理数据库的三个数据模型如下：
图书（数据表名：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语法，请通过其他办法进行数据更新）

(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.reader
where name like '李%'

(3)
select
    book_name,
    price,
from test.book
where OUTPUT = '高等教育出版社'
order by price desc

(4)
select
    sort,
    OUTPUT,
    PRICE,
from test.book
where price >=10 and price <=20
order by price,OUTPUT

(5)
select
    r.name,
    r.company,
from test.reader r
join test.borrow_lag b
on r.reader_id = b.reader_id 

(6)
select
    price,
    max(price),
    min(price),
    avg(price),
from test.book
where OUTPUT = '科学出版社'

(7)
select
    b.name,
    b.company,
from(
    select
        reader_id,
        count(*) ct
    from test.borrow_log
    group by reader_id
    having ct >= 2
    )t1
join test.reader b
on t1.reader_id = b.reader_id

(8)
CREATE TABLE test.borrow_log_bak AS
SELECT *
FROM test.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：第一步找到要更新的数据，将要更改的字段替换为新的值，第二步找到不需要更新的数据，第三步将上两步的数据插入一张新表中。

题目八

有一个线上服务器访问日志格式如下（用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地址

select
    ip,
    substring(interface,-5) interface
from test
where interface = 'login'
    and date_format(date,'yyyy-MM-dd HH') >= '2016-11-09 14'
    and date_format(date,'yyyy-MM-dd HH') < '2016-11-09 15'
order by date
limit 10

题目九

有一个充值日志表credit_log，字段如下：

`dist_id` int  '区组id',
`account` string  '账号',
`money` int   '充值金额',
`create_time` string  '订单时间'

请写出SQL语句，查询充值日志表2019年01月02号每个区组下充值额最大的账号，要求结果：
区组id，账号，金额，充值时间

select
    dist_id,
    account,
    total,
    create_time
from(
    select
        total,
        dist_id
        rank() over(partition by dist_id order by total desc) rk
    from(
        select
            account,
            dist_id,
            sum(money) total
        from credit_log
        where date_format(create_time,'yyyy-MM-dd') = '2019-01-02'
        group by dist_id,account
        )t1
    )t2
where rk = 1

题目十

有一个账号表如下，请写出SQL语句，查询各自区组的money排名前十的账号（分组取前10）
dist_id string  '区组id',
account string  '账号',
gold     int    '金币'

select
    dist_id,
    account,
    money,
from(
    select
        dist_id,
        account,
        money,
        row_number() over(partition by dist_id order by money desc) rk
    from test
    )t1
where rk <= 10

题目十一

如下为某直播平台主播开播及关播时间，根据该数据计算出平台最高峰同时在线的主播人数。

id in_time out_time
1001   2021-06-14 12:12:12       2021-06-14 18:12:12
1003   2021-06-14 13:12:12       2021-06-14 16:12:12
1004   2021-06-14 13:15:12       2021-06-14 20:12:12
1002   2021-06-14 15:12:12       2021-06-14 16:12:12
1005   2021-06-14 15:18:12       2021-06-14 20:12:12
1001   2021-06-14 20:12:12       2021-06-14 23:12:12
1006   2021-06-14 21:12:12       2021-06-14 23:15:12
1007   2021-06-14 22:12:12       2021-06-14 23:10:12

select
    max(user_count) max_user_count
from(
    select
        id,
        time,
        sum(online_num) over(partition by id order by time) user_count
    from(
        select
            id,
            in_time time,
            1 online_num
        from test
        union all
        select
            id,
            out_time,
            -1
        from test
        )t1--将进入时间与离开时间拼接在一起，union all实现上下拼接，进入赋值为1，离开赋值为-1
    )t2--使用窗口函数按照时间升序排序，并计算截至当前行的直播间人数
group by id
--获取直播间人数的最大值

题目十二

现有页面访问表，表中呈现了每名用户的每次页面访问记录。现规定若同一用户的相邻2次访问记录的时间间间隔小于60s，则认为2次访问记录属于同一会话。现有如下需求，为属于同一会话的访问记录增加1个相同的会话id(session_id)列

id view_timestamp
1001 17523641234
1001 17523641256
1002 17523641278
1001 17523641334
1002 17523641434
1001 17523641534
1001 17523641544
1002 17523641634
1001 17523641638
1001 17523641654
时间间隔小于60秒，则分为同一个组
1001 17523641234 1
1001 17523641256 1
1001 17523641334 2
1001 17523641534 3
1001 17523641544 3
1001 17523641638 4
1001 17523641654 4
1002 17523641278 1
1002 17523641434 2
1002 17523641634 3

select
    id,
    view_timestamp,
    sum(flag) over(partition by id order by view_timestamp) session
from(
    select
        id,
        view_timestamp,
        if(view_timestamp-dt >= 60,1,0) point
    from(
        select
            id,
            view_timestamp,
            lag(view_timestamp,1,0) over(partition by id order by view_timestamp) dt
        from test
        )t1--使用窗口函数lag获取上一条数据的view_timestamp值，得到dt列
    )t2--超过60秒即为另一个session
--flag求和的到session号

题目十三

某游戏公司记录的用户每日登录数据

id login_datetime
1001 2021-12-12
1001 2021-12-13
1001 2021-12-14
1001 2021-12-16
1001 2021-12-19
1001 2021-12-20
1002 2021-12-12
1002 2021-12-16
1002 2021-12-17
计算每个用户最大的连续登录天数，可以间隔一天。解释：如果一个用户在 1,3,5,6 登录游戏，则视为连续 6 天登录。

select
    id,
    max(c_days) max_c_days
from(
    select
        id,
        flag,
        datediff(max(login_date),min(login_date)) + 1 c_days
    from(
        select
            id,
            login_date,
            lag_date,
            sum(id(datediff(login_date,lag_date) > 2,1,0)) over(partition by id order by login_date) flag
        from(
            select
                id,
                login_date,
                lag(login_date,1,'1970-01-01') over(partition by id order by login_date)
            from(
                select
                    id,
                    date_format(login_datetime,'yyyy-MM-dd') login_date
                from test
                group by id,date_format(login_datetime,'yyyy-MM-dd')
                )t1--用窗口函数lag从当前行向上一行获取用户登录日期
            )t2--将天数相减找出超过两天的日期，每超过两天后的日期分为一组
        )t3--然后按照组内最大天数减去最小天数就得到用户没超过两天的持续登录天数
    group by id,flag
    )t4
group by id--求出用户登录天数最大值

题目十四

如下为平台商品促销数据：字段为品牌，打折开始日期，打折结束日期

brand start_date   end_date
oppo   2021-06-05   2021-06-09
oppo   2021-06-11   2021-06-21
vivo   2021-06-05   2021-06-15
vivo   2021-06-09   2021-06-21
redmi   2021-06-05   2021-06-21
redmi   2021-06-09   2021-06-15
redmi   2021-06-17   2021-06-26
huawei   2021-06-05   2021-06-26
huawei   2021-06-09   2021-06-15
huawei   2021-06-17   2021-06-21

计算每个品牌总的打折销售天数，注意其中的交叉日期，比如 vivo 品牌，第一次活动时间为 2021-06-05 到 2021-06-15，第二次活动时间为 2021-06-09 到 2021-06-21 其中 9 号到 15 号为重复天数，只统计一次，即 vivo 总打折天数为 2021-06-05 到 2021-06-21 共计 17 天。

select
    brand,
    sum(datediff(end_date,start_date) + 1) day_count
from(
    select
        brand,
        max_end_date,
        if(max_end_date is null or start_date > max_end_date,start_date,date_add(max_end_date,1)) 
    from(
        select
            brand,
            start_date,
            end_date,
            max(end_date) over(partition by band order by start_date rows between unbounded preceding and 1 preceding) max_end_date
        from test
        )t1--使用窗口函数将窗口分区第一行到当前行的前一行获得max_end_date
    )t2--若max_end_date>start_date,则说明存在区间交叉，应将start_date改为max_end_date+1
where end_date > start_date
group by brand

题目十五

如下数据为蚂蚁森林中用户领取的减少碳排放量

id dt lowcarbon
1001 2021-12-12 123
1002 2021-12-12 45
1001 2021-12-13 43
1001 2021-12-13 45
1001 2021-12-13 23
1002 2021-12-14 45
1001 2021-12-14 230
1002 2021-12-15 45
1001 2021-12-15 23
.......

找出连续3天及以上减少碳排放量在100以上的用户

select
    id,
    flag,
    count(*) ct
from(
    select
        id,
        dt,
        lowcarbon,
        date_sub(dt,rk) flag
    from(
        select
            id,
            dt,
            lowcarbon,
            rank() over(partition by id order by dt) rk
        from(
            select
                id,
                dt,
                sum(lowcarbon) lowcarbon
            from test
            group by id,dt
            having lowcarbon > 100
            )t1--根据每个用户id与dt分组，计算每个用户单日的lowcarbon
        )t2--使用等差数列法，两个等差数列如果等差相同,则相同位置的数据相减到的结果相同，将数据赋予rank值
    ）t3--dt与rank值相减得到差值，差值相同即为连续三天
group by id,flag
having ct >= 3
--筛选超过三天的数据

题目十六

select
    name,
    sum(case course when '语文' then score else 0 end) as 语文 
    sum(case course when '数学' then score else 0 end) as 数学
    sum(case course when '英语' then score else 0 end) as 英语 
from test
group by name

参考：https://blog.csdn.net/weixin_43161811/article/details/123947852

https://jiamaoxiang.top/2019/10/15/%E7%BB%8F%E5%85%B8Hive-SQL%E9%9D%A2%E8%AF%95%E9%A2%98/