Hive -- Hive面试题及答案（3）

小打卡面试题

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

已知，数据如下：
tb_habit圈子表：数百万行数据

user_habit_relation用户与圈子关系表：亿行数据

需求：请用hive sql计算出如下结果（同一个圈主名下，同一个用户加多个圈子只计算一次）：

答案：

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 sql编程实现如下2个小需求：

1.需求A：找出T1表中最大的版本号。
2.需求B：计算出如下格式的所有版本号排序，要求对于相同的版本号，顺序号并列：

答案：
第一题：

SELECT version,
         cast( split ( version, '\\.' ) [ 0 ] AS INT ) * 10000 
         + cast( split ( version, '\\.' ) [ 1 ] AS INT ) * 100 
         + cast( nvl ( split ( version, '\\.' ) [ 2 ], 0 ) AS INT )  AS score
FROM t_version
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 t_version) t;

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

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

已知：

用户信息表结构：

埋点访问日志表结构：

dim_tb_user : 总量数百万行级别

fact_access_log ：每天新增访问日志记录数据量数亿行

定义：

• 新用户：某一日新注册的用户
• 新用户次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计算出如下数据：
   
2. 假设本次活动拉新留存情况符合预期，公司决定后续会不定期举办拉新活动，以获得更多新用户，结合你所掌握的数仓知识，设计调度任务，使得报表按天增量更新，帮助公司长期监控平台每一天的新用户留存。（请给出完整的相关sql，将数据写入结果表中）

补充：

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

输出结果表结构：


答案：
第一题：

select  a.date_flag , 
  count(case when a.date_flag=1 then 1 end ) as save_1,
  count(case when a.date_flag=2 then 1 end ) as save_2,
  count(case when a.date_flag=3 then 1 end ) as save_3,
  count(case when a.date_flag=4 then 1 end ) as save_4,
  count(case when a.date_flag=5 then 1 end ) as save_5,
  count(case when a.date_flag=6 then 1 end ) as save_6,
  count(case when a.date_flag=7 then 1 end ) as save_7,
 '20190101'
from 
(
	select user_id, datediff(log.create_time,'2019-09-01') as date_flag
	from dim_tb_user user join fact_access_log log
	on user.user_id = log.user_id
	where user.create_time > '2019-09-01' and log.dt>= 20190901
	group by user_id,datediff(log.create_time,'2019-09-01')
) a group by a.date_flag