网站怎么做才不会被墙,房屋装修设计app免费,做写手一般上什么网站,服务器建站参考链接#xff1a;hive sql面试题及答案 - 知乎
有哪些好的题目都可以给我哦 我来汇总到一起
1、编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
数据#xff1a; userid,month,visits A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,…参考链接hive sql面试题及答案 - 知乎
有哪些好的题目都可以给我哦 我来汇总到一起
1、编写sql实现每个用户截止到每月为止的最大单月访问次数和累计到该月的总访问次数
数据 userid,month,visits A,2015-01,5 A,2015-01,15 B,2015-01,5 A,2015-01,8 B,2015-01,25 A,2015-01,5 A,2015-02,4 A,2015-02,6 B,2015-02,10 B,2015-02,5 A,2015-03,16 A,2015-03,22 B,2015-03,23 B,2015-03,10 B,2015-03,1
预期结果 create table u_visit(
userid STRING ,month STRING ,visits BIGINT
) LIFECYCLE 1;
INSERT into u_visit values(A,2015-01,5)
,(A,2015-01,15)
,(B,2015-01,5)
,(A,2015-01,8)
,(B,2015-01,25)
,(A,2015-01,5)
,(A,2015-02,4)
,(A,2015-02,6)
,(B,2015-02,10)
,(B,2015-02,5)
,(A,2015-03,16)
,(A,2015-03,22)
,(B,2015-03,23)
,(B,2015-03,10)
,(B,2015-03,1);思路:
截至当前累计 over中加order by 时间
全累计 over中不加order by 时间SELECT userid,MONTH,visits,max(visits) OVER(PARTITION BY userid ) AS max_visit,max(visits) OVER(PARTITION BY userid ORDER BY MONTH ASC ) AS max_visit --截止到当月最大值,SUM(visits) OVER(PARTITION BY userid ORDER BY MONTH ASC ) AS sum_visit
FROM (SELECT userid,MONTH,sum(visits) visitsFROM u_visitGROUP BY userid,MONTH) A
;
结果
用户id 月份 月访问数 截止目前最大访问数 截止当前月最大访问数 截止当前月总访问数
userid month visits max_visit max_visit2 sum_visit A 2015-01 33 38 33 33 A 2015-02 10 38 33 43 A 2015-03 38 38 38 81 B 2015-01 30 34 30 30 B 2015-02 15 34 30 45 B 2015-03 34 34 34 79
2、求出每个栏目的被观看次数及累计观看时长
数据: vedio表
用户id 栏目id 时长 Uid channl min 1 1 23 2 1 12 3 1 12 4 1 32 5 1 342 6 2 13 7 2 34 8 2 13 9 2 134
这个好简单 pass
3、编写连续7天登录的总人数
数据: t1表 Uid dt login_status(1登录成功,0异常) 1 2019-07-11 1 1 2019-07-12 1 1 2019-07-13 1 1 2019-07-14 1 1 2019-07-15 1 1 2019-07-16 1 1 2019-07-17 1 1 2019-07-18 1 2 2019-07-11 1 2 2019-07-12 1 2 2019-07-13 0 2 2019-07-14 1 2 2019-07-15 1 2 2019-07-16 0 2 2019-07-17 1 2 2019-07-18 0 3 2019-07-11 1 3 2019-07-12 1 3 2019-07-13 1 3 2019-07-14 1 3 2019-07-15 1 3 2019-07-16 1 3 2019-07-17 1 3 2019-07-18 1
create TABLE t1(Uid bigint,dt STRING , login_status BIGINT COMMENT (1登录成功,0异常)
) LIFECYCLE 1;
INSERT INTO t1 VALUES (1, 2019-07-11, 1)
,(1, 2019-07-12, 1)
,(1, 2019-07-13, 1)
,(1, 2019-07-14, 1)
,(1, 2019-07-15, 1)
,(1, 2019-07-16, 1)
,(1, 2019-07-17, 1)
,(1, 2019-07-18, 1)
,(2, 2019-07-11, 1)
,(2, 2019-07-12, 1)
,(2, 2019-07-13, 0)
,(2, 2019-07-14, 1)
,(2, 2019-07-15, 1)
,(2, 2019-07-16, 0)
,(2, 2019-07-17, 1)
,(2, 2019-07-18, 0)
,(3, 2019-07-11, 1)
,(3, 2019-07-12, 1)
,(3, 2019-07-13, 1)
,(3, 2019-07-14, 1)
,(3, 2019-07-15, 1)
,(3, 2019-07-16, 1)
,(3, 2019-07-17, 1)
,(3, 2019-07-18, 1);
--思路1
-- 1、先按照每个人登录时间排序成如下数据结构
-- 1 07-01 1
-- 1 07-02 2
-- 1 07-03 3
--2、 时间与排序做date_sub,变成如下结果
-- 1 07-01 1 06-30
-- 1 07-02 2 06-30
-- 1 07-03 3 06-30
-- 3、根据id聚合时间差变成如下结果
--1 06-30 3 07-01 07-03 证明用户1 连续登录了3天 起始登录时间是07-01 结束连续登录是07-03
SELECT Uid,COUNT(dtadd) AS countadd,COUNT(dtsub) AS countsub,MIN(dt) mindt,MAX(dt) maxdt
FROM (SELECT Uid,dt,DATEADD(dt,-rk,dd) dtadd,date_sub(dt,rk) dtsubFROM (SELECT Uid,to_date(dt,yyyy-mm-dd) dt,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt ASC ) AS rkFROM t1WHERE login_status 1) A) B
GROUP BY uid
HAVING COUNT(dtadd) 7
;
结果
uid countadd countsub mindt maxdt 1 8 8 2019-07-11 00:00:00 2019-07-18 00:00:00 3 8 8 2019-07-11 00:00:00 2019-07-18 00:00:00
-- 思路2
-- 1、先按照每个人登录时间排序成如下数据结构
-- 1 07-01 1
-- 1 07-02 2
-- 1 07-03 3
--2、 利用lead或lag函数 上下错位,并计算时间差
-- 1 07-01 07-02 1
-- 1 07-02 07-03 1
-- 1 07-03 null
-- 3、根据id聚合时间差变成如下结果
--1 2(因为有null,得再加个1) 证明用户1 连续登录了3天
SELECT
uid
,COUNT(dtdiff)1 AS 登录总次数
FROM
(SELECT Uid,dt,LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt ASC) leadrk,DATEDIFF(dt,LEAD(dt,1) OVER (PARTITION BY uid ORDER BY dt ASC),dd) dtdiff
FROM (SELECT Uid,to_date(dt,yyyy-mm-dd) dt,ROW_NUMBER() OVER(PARTITION BY uid ORDER BY dt ASC ) AS rkFROM t1WHERE login_status 1) A
) B
where (dtdiff-1 or dtdiff is null)
group by uid
HAVING COUNT(dtdiff)17
;结果
uid 登录总次数 1 8 3 8
4、编写sql语句实现每班前三名分数一样并列同时求出前三名按名次排序的依次的分差
数据 stu表 Stu_no class score 1 1901 90 2 1901 90 3 1901 83 4 1901 60 5 1902 66 6 1902 23 7 1902 99 8 1902 67 9 1902 87
create table stu(Stu_no BIGINT , class BIGINT , score BIGINT
) LIFECYCLE 1;
INSERT INTO stu VALUES
(1 ,1901, 90)
,(2 ,1901, 90)
,(3 ,1901, 83)
,(4 ,1901, 60)
,(5 ,1902, 66)
,(6 ,1902, 23)
,(7 ,1902, 99)
,(8 ,1902, 67)
,(9 ,1902, 87); -- 思路1
-- 1、各班自行排序可并列 应使用 dense_rank 区别rankrk
-- 2、利用lag或lead函数上下错误并计算分差
SELECT stu_no,class,score,LEAD(score,1) OVER (PARTITION BY class ORDER BY score DESC ) leadscore,LAG(score,1) OVER (PARTITION BY class ORDER BY score DESC ) lagscore,score-nvl(LEAD(score,1) OVER (PARTITION BY class ORDER BY score DESC ),0) AS 分差lead,LAG(score,1) OVER (PARTITION BY class ORDER BY score DESC )-score AS 分差lag
FROM (SELECT stu_no,class,score,DENSE_RANK() OVER (PARTITION BY class ORDER BY score DESC ) denserk,RANK() OVER (PARTITION BY class ORDER BY score DESC ) rankrkFROM stu) A
WHERE denserk 3
;第一步结果
stu_no class score denserk rankrk 1 1901 90 1 1 2 1901 90 1 1 3 1901 83 2 3 4 1901 60 3 4 7 1902 99 1 1 9 1902 87 2 2 8 1902 67 3 3 5 1902 66 4 4 6 1902 23 5 5
结果 [具体向上取分差还是向下取分差按实际情况即可]
stu_no class score leadscore lagscore 分差lead 分差lag 1 1901 90 90 \N 0 \N 2 1901 90 83 90 7 0 3 1901 83 60 90 23 7 4 1901 60 \N 83 60 23 7 1902 99 87 \N 12 \N 9 1902 87 67 99 20 12 8 1902 67 \N 87 67 20
5、每个店铺的当月销售额和累计到当月的总销售额
数据 店铺,月份,金额 a,01,150 a,01,200 b,01,1000 b,01,800 c,01,250 c,01,220 b,01,6000 a,02,2000 a,02,3000 b,02,1000 b,02,1500 c,02,350 c,02,280 a,03,350 a,03,250
参考1思路
6、分析用户的行为习惯找到每个用户的第一次行为
数据user_action_log
uid time action
1 time1 Read
3 time2 Comment
1 time3 Share
2 time4 Like
1 time5 Write
2 time6 Share
3 time7 Write
2 time8 Read
思路
1、排序取第一个
2、first_value函数
7、订单及订单类型行列互换
t_order表: order_id order_type order_time 111 N 10:00 111 A 10:05 111 B 10:10 是用hql获取结果如下 order_id order_type_1 order_type_2 order_time_1 order_time_2 111 N A 10:00 10:05 111 A B 10:05 10:10
create table t_order
(order_id BIGINT , order_type STRING , order_time STRING )
LIFECYCLE 1;INSERT INTO t_order VALUES
(111, N, 10:00)
,(111, A, 10:05)
,(111, B, 10:10);--思路
-- 1、按照时间升序排列数据
-- 2、利用lead函数取下一个数据取不到的排除掉
SELECT *
FROM (SELECT order_id,order_type AS order_type_1,LEAD(order_type,1) OVER (PARTITION BY order_id ORDER BY order_time ASC ) AS order_type_2,order_time AS order_time_1,LEAD(order_time,1) OVER (PARTITION BY order_id ORDER BY order_time ASC ) AS order_time_2FROM (SELECT order_id,order_type,order_time,ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_time ASC ) rkFROM t_order) A)
WHERE order_type_2 IS NOT NULL
;
结果
order_id order_type_1 order_type_2 order_time_1 order_time_2 111 N A 10:00 10:05 111 A B 10:05 10:10
8、某APP每天访问数据存放在表access_log里面包含日期字段 ds,用户类型字段user_type用户账号user_id,用户访问时间 log_time,请使用hive的hql语句实现如下需求
(1)、每天整体的访问UV、PV? (2)、每天每个类型的访问UV、PV? (3)、每天每个类型中最早访问时间和最晚访问时间? (4)、每天每个类型中访问次数最高的10个用户?
1思路
UV count(user_id)
PV sum(user_id)
select count(user_id) over(distribute by user_id) uv, sum(user_id) over(distribute by log_time) pv from access_log (2)思路
select count(user_id) uv sum(user_id) over(distribute by log_time) pv from access_log al1 inner join access_log al2 group by user_type
3思路 select first_value(log_time) over(distribute by user_type order by log_time) first_time, last_value(log_time) over(distribute by user_type order by log_time) from access_log
4思路 select user_id from (select count(user_id) cnt row_number() over(distribute by user_type order by count(user_id)) rows from access_log) tmp where tmp.rows10 9、每个用户连续登陆的最大天数
数据: login表 uid,date 1,2019-08-01 1,2019-08-02 1,2019-08-03 2,2019-08-01 2,2019-08-02 3,2019-08-01 3,2019-08-03 4,2019-07-28 4,2019-07-29 4,2019-08-01 4,2019-08-02 4,2019-08-03 结果如下 uid cnt_days 1 3 2 2 3 1 4 3
10、使用hive的hql实现男女各自第一名及其它
id sex chinese_s math_s 0 0 70 50 1 0 90 70 2 1 80 90 1、男女各自语文第一名0:男1:女 2、男生成绩语文大于80女生数学成绩大于70
11、使用hive的hql实现最大连续访问天数
log_time uid 2018-10-01 18:00:00,123 2018-10-02 18:00:00,123 2018-10-02 19:00:00,456 2018-10-04 18:00:00,123 2018-10-04 18:00:00,456 2018-10-05 18:00:00,123 2018-10-06 18:00:00,123 12、编写sql实现行列互换 行转列
1、使用case when 查询出多列即可即可增加列。
列转行:
1、lateral view explode()使用炸裂函数可以将1列转成多行被转换列适用于array、map等类型。 lateral view posexplode(数组)如有排序需求则需要索引。将数组炸开成两行(索引 , 值),需要 as 两个别名。
2、case when 结合concat_ws与collect_set/collect_list实现。内层用case when外层用 collect_set/list收集对搜集完后用concat_ws分割连接形成列。
13、编写sql实现如下
数据 t1表 uid tags 1 1,2,3 2 2,3 3 1,2 编写sql实现如下结果 uid tag 1 1 1 2 1 3 2 2 2 3 3 1 3 2 14、用户标签连接查询
数据 T1表: Tags 1,2,3 1,2 2,3 T2表: Id lab 1 A 2 B 3 C 根据T1和T2表的数据编写sql实现如下结果 ids tags 1,2,3 A,B,C 1,2 A,B 2,3 B,C
预期结果 15、用户标签组合
数据 t1表 id tag flag a b 2 a b 1 a b 3 c d 6 c d 8 c d 8 编写sql实现如下结果 id tag flag a b 1|2|3 c d 6|8
预期结果 16、户标签行列互换
数据 t1表 uid name tags 1 goudan chihuo,huaci 2 mazi sleep 3 laotie paly 编写sql实现如下结果 uid name tag 1 goudan chihuo 1 goudan huaci 2 mazi sleep 3 laotie paly
17、hive实现词频统计
数据 t1表 uid contents 1 i|love|china 2 china|is|good|i|i|like 统计结果如下,如果出现次数一样则按照content名称排序 content cnt i 3 china 2 good 1 like 1 love 1 is 1 18、课程行转列
数据 t1表 id course 1,a 1,b 1,c 1,e 2,a 2,c 2,d 2,f 3,a 3,b 3,c 3,e 根据编写sql得到结果如下(表中的1表示选修表中的0表示未选修) id a b c d e f 1 1 1 1 0 1 0 2 1 0 1 1 0 1 3 1 1 1 0 1 0
19、兴趣行转列
t1表 name sex hobby janson 男 打乒乓球、游泳、看电影 tom 男 打乒乓球、看电影 hobby最多3个值使用hql实现结果如下: name sex hobby1 hobby2 hobby3 janson 男 打乒乓球 游泳 看电影 tom 男 打乒乓球 看电影 20、用户商品行列互换
t1表 用户 商品 A P1 B P1 A P2 B P3 请你使用hql变成如下结果:1代表购买过的商品0代表未购买 用户 P1 P2 P3 A 1 1 0 B 1 0 1
21、求top3英雄及其pick率
id names 1 亚索挖掘机艾瑞莉娅洛卡莎 2 亚索盖伦奥巴马牛头皇子 3 亚索盖伦艾瑞莉娅宝石琴女 4 亚素盖伦赵信老鼠锤石 请用 HiveSQL 计算出出场次数最多的 top3 英雄及其 pick 率(出现场数/总场数) 21、使用hive求出两个数据集的差集
数据 t1表 id name 1 zs 2 ls t2表 id name 1 zs 3 ww 结果如下 id name 2 ls 3 ww 22、两个表A 和B 均有key 和value 两个字段写一个SQL语句 将B表中的value值置成A表中相同key值对应的value值
A: key vlaue k1 123 k2 234 k3 235 B: key value k1 111 k2 222 k5 246 使用hive的hql实现结果是B表数据如下: k1 123 k2 234 k5 246 23、有用户表user(uid,name)以及黑名单表Banuser(uid)
1、用left join方式写sql查出所有不在黑名单的用户信息 2、用not exists方式写sql查出所有不在黑名单的用户信息
24、使用什么来做的cube
使用with cube 、 with rollup 或者grouping sets来实现cube。
详细解释如下
0、hive一般分为基本聚合和高级聚合
基本聚合就是常见的group by高级聚合就是grouping set、cube、rollup等。
一般group by与hive内置的聚合函数max、min、count、sum、avg等搭配使用。
1、grouping sets可以实现对同一个数据集的多重group by操作。
事实上grouping sets是多个group by进行union all操作的结合它仅使用一个stage完成这些操作。
grouping sets的子句中如果包换() 数据集则表示整体聚合。多用于指定的组合查询。
2、cube俗称是数据立方它可以时限hive任意维度的组合查询。
即使用with cube语句时可对group by后的维度做任意组合查询
如group a,b,c with cube ,则它首先group a,b,c 然后依次group by a,c 、 group by b,c、group by a,b 、group a 、group b、group by c、group by () 等这8种组合查询所以一般cube个数2^3个。2是定 值3是维度的个数。多用于无级联关系的任意组合查询。
3、rollup是卷起的意思俗称层级聚合相对于grouping sets能指定多少种聚合而with rollup则表示从左 往右的逐级递减聚合如:group by a,b,c with rollup 等价于 group by a, b, c grouping sets( (a, b, c), (a, b), (a), ( )).直到逐级递减为()为止,多适用于有级联关系的组合查询如国家、省、市级联组合查 询。
4、Grouping__ID在hive2.3.0版本被修复过修复后的发型版本和之前的不一样。对于每一列如果这列 被聚合 过则返回0否则返回1。应用场景暂时很难想到用于哪儿。
5、grouping sets/cube/rollup三者的区别 注: grouping sets是指定具体的组合来查询。 with cube 是group by后列的所有的维度的任意组合查询。
with rollup 是group by后列的从左往右逐级递减的层级组合查询。 cube/rollup 后不能加()来选择列hive是要求这样。
25、访问日志正则提取
表t1(注:数据时正常的访问日志数据,分隔符全是空格) 8.35.201.160 - - [16/May/2018:17:38:21 0800] GET /uc_server/data/avatar/000/01/54/22_avatar_middle.jpg HTTP/1.1 200 5396 使用hive的hql实现结果如下: ip dt url 8.35.201.160 2018-5-16 17:38:21 /uc_server/data/avatar/000/01/54/22_avatar_middle.jpg 26、
