欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Hive -- Hive面试题及答案

程序员文章站 2024-03-23 21:19:04
...

小打卡面试题

题目一:计算小打卡平台的圈主建了多少圈子,名下有多少用户

已知,数据如下:
tb_habit圈子表:数百万行数据
Hive -- Hive面试题及答案
user_habit_relation用户与圈子关系表:亿行数据
Hive -- Hive面试题及答案
需求:请用hive sql计算出如下结果(同一个圈主名下,同一个用户加多个圈子只计算一次):
Hive -- Hive面试题及答案
答案:

SELECT a.`uid`, nvl(COUNT(a.log_id), 0) AS log_cnt
	, nvl(SUM(b.like_uid_cnt), 0) AS liked_cnt
FROM user_log a
	LEFT JOIN (
		SELECT log_id, COUNT(like_uid) AS like_uid_cnt
		FROM log_like
		GROUP BY log_id
	) b
	ON a.`log_id` = b.`log_id`
GROUP BY a.`uid`

题目二:处理产品版本号

版本号信息存储在数据表中,每行一个版本号。
版本号命名规则如下:
产品版本号由三个部分组成
如:v9.11.2
第一部分9为主版本号,为1-99之间的数字;
第二部分11为子版本号,为0-99之间的数字;
第三部分2为阶段版本号,为0-99之间的数字(可选);
已知T1表有若干个版本号:
Hive -- Hive面试题及答案
请使用hive sql编程实现如下2个小需求:

1.需求A:找出T1表中最大的版本号。
2.需求B:计算出如下格式的所有版本号排序,要求对于相同的版本号,顺序号并列:
Hive -- Hive面试题及答案
答案:
第一题:

SELECT version,
         cast( split ( version,
         '\\.' ) [ 0 ] AS INT ) * 10000 
         + cast( split ( version, '\\.' ) [ 1 ] AS INT ) * 100 
         + cast( nvl ( split ( version, '\\.' ) [ 2 ], 0 ) AS INT ) * 1 AS score
FROM banben
ORDER BY  score DESC limit 1; 

第二题:

SELECT version,
         rank() over(order by score desc) rank
FROM 
    (SELECT version,
         cast( split ( version,
         '\\.' ) [ 0 ] AS INT ) * 10000 
         + cast( split ( version, '\\.' ) [ 1 ] AS INT ) * 100 
         + cast( nvl ( split ( version, '\\.' ) [ 2 ], 0 ) AS INT ) * 1 AS score
    FROM banben) t;

题目三、计算平台用户留存

用户留存是体现平台健康程度的重要数据指标之一,请用hive sql完成如下需求:

已知:

用户信息表结构:
Hive -- Hive面试题及答案
埋点访问日志表结构:
Hive -- Hive面试题及答案
dim_tb_user : 总量数百万行级别
Hive -- Hive面试题及答案
fact_access_log :每天新增访问日志记录数据量数亿行
Hive -- Hive面试题及答案
定义:

  • 新用户:某一日新注册的用户
  • 新用户次1日留存:T日成为新用户,T+1日回访平台网站任意页面(T+1即T日期的第二天)
  • 新用户次3日留存:T日成为新用户,T+3日回访平台网站任意页面
  • 新用户次n日留存:T日成为新用户,T+n日回访平台网站任意页面
  1. 假设平台2019年元旦节做了一次拉新活动,当日新增用户数约为平日的两倍,2019年1月9日,距离活动已经过去一周了,公司希望评估一下2019年1月1日当天的全部新增用户,在接下来一周的留存情况(即1月2日-1月8日每天的留存用户数),要求交付的数据结果表如下,请用一个简洁的sql计算出如下数据:
    Hive -- Hive面试题及答案
  2. 假设本次活动拉新留存情况符合预期,公司决定后续会不定期举办拉新活动,以获得更多新用户,结合你所掌握的数仓知识,设计调度任务,使得报表按天增量更新,帮助公司长期监控平台每一天的新用户留存。(请给出完整的相关sql,将数据写入结果表中)

补充:

  • 希望保留全量历史数据,方便随时对比各期活动效果,即:将历史产出的数据永久保存在当日分区中,随时可查询历史任意日期的计算结果。
  • 日期未到,留存数暂时用0补充,如:2019年1月9日那天,计算1月8日的次7日留存数,此时因为次7日时间未到而计算不出真实值,将其设置为0即可,待到了可以计算的对应日期再将数据更新。
  • 请考虑大数据的特点,合理设计sql计算方案,尽可能的降低数据计算量,避免产出时间过长,资源消耗过大。
  • 保持数据幂等性,支持任意时间段历史数据回溯:若遇到异常情况可重跑N天前的数据将其快速修复。
  • 合理设计增量更新方案,切勿做全量计算。
  • 保持sql尽可能精简易读。

输出结果表结构:
Hive -- Hive面试题及答案
Hive -- Hive面试题及答案
答案: