Hive Sql中六种面试题型总结

一.常用函数

• explode(col): 将一列array类型或者map类型的字段，分成多列

• lateral view:

  用法：lateral view udtf(expression) tableAlias as columAlias,tableAlias产生的虚拟表，columAlias是炸开的列名
  解释：用于和explode和split等UDFT一起使用，能将一列数据拆分成多行数据，在此基础上可以对拆分后的数据进行聚合。

• CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

• CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

• COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。

• collect_list(col)：功能类似COLLECT_SET(col)但是不会去重。

• split(str, regex)- Splits str around occurances that match regex。将str按照regex切割。返回值为一个数组。split(str, regex)[index]返回分割后数组index处的值。如split('a,b,c',',')[0]得到a

• array_contains(array arr, element): Returns if the element is in the array。判断arr 是否包含element，如果包含返回true。

• get_json_object(string json_string, string path):第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用 .或 []读取对象或数组；

二、N日留存

1.思路分析

按天留存率计算 就是指用户在首日新增后，在接下来的后推第N天活跃情况，用后推第N天活跃的用户 / 首日新增用户。就得到后推第N天的新增用户留存率。 留存率计算案例 1月1日，新增用户200人; 次日留存：第2天，1月2日，这200人里面有100人活跃，则次日留存率为： 100 / 200 = 50% 2日留存：第3天，1月3日;这200名新增用户里面有80人活跃， 第3日新增留存率为:80/200 = 40%; 7日留存：第8天，1月8日，这200名新增用户里面有25人活跃， 第7日新增留存率为:25/200 = 12.5%;

• ① 首先查询时间内 的 每天新增用户 (t_new)

  -- 为了简化问题，假设有一张new_users表记录了每天的新增用户
  select 
  	user_id,--用户id
  	dt as new_dt --新增日期
  from new_users
  where dt >= '2020-05-01' and dt<= '2020-06-01'; //t_new
  

• ② 查询时间范围的 每天活跃用户数 (t_active)

  --为了简化问题，假设有一张active_users表记录了每天的活跃用户数
  select
  	user_id, --用户id
  	dt as active_dt, --活跃日期
  from active_users
  where dt >= '2020-05-01' and dt<= '2020-06-01'; // t_active
  

• ③ 统计当天注册 且 第N日活跃的用户 (t3)： 以①为主表 left join ②

  select
  	t_new.new_dt, --用户注册时间
  	count(distinct t_new.user_id) as cnt_01, --当日注册且当日活跃的用户(当日注册肯定活跃)
  	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 1,t_new.user_id,null)) as cnt_02,--当日注册 且 第二日活跃的用户
  	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 2,t_new.user_id,null)) as cnt_03,--当日注册 且 第三日活跃的用户
  	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 4,t_new.user_id,null)) as cnt_05,--当日注册 且 第五日活跃的用户
  	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 6,t_new.user_id,null)) as cnt_07,--当日注册 且 第七日活跃的用户
  	count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 14,t_new.user_id,null)) as cnt_15,--当日注册 且 第十五日活跃的用户
  	
  from  t_new
  left join
  t_active on t_new.user_id = t_active.user_id
  group by t_new.new_dt; // t3
  

• ④统计留存: 用后推第N天活跃的用户 / 首日新增用户 即 cnt_0n / cnt_01

  select
  	t.new_dt,--注册时间
  	t.cnt_01,--当日注册用户
  	round(cnt_02/ cnt_01 * 100, 2) as keep_02, -- 次日留存
  	round(cnt_03/ cnt_01 * 100, 2) as keep_03, -- 3日留存
  	round(cnt_05/ cnt_01 * 100, 2) as keep_05, -- 5日留存
  	round(cnt_07/ cnt_01 * 100, 2) as keep_07, --7日留存
  	round(cnt_15/ cnt_01 * 100, 2) as keep_15, -- 15日留存
  from t3
  

  完整结果：

  select
  t.new_dt,--注册时间
  t.cnt_01,--当日注册用户
  round(cnt_02/ cnt_01 * 100, 2) as keep_02, -- 次日留存
  round(cnt_03/ cnt_01 * 100, 2) as keep_03, -- 3日留存
  round(cnt_05/ cnt_01 * 100, 2) as keep_05, -- 5日留存
  round(cnt_07/ cnt_01 * 100, 2) as keep_07, --7日留存
  round(cnt_15/ cnt_01 * 100, 2) as keep_15, -- 15日留存
  
  from
  (
  	select
  		t_new.new_dt, --用户注册时间
  		count(distinct t_new.user_id) as cnt_01, --当日注册且当日活跃的用户
  		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 1,t_new.user_id,null)) as cnt_02,--当日注册 且 第二日活跃的用户
  		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 2,t_new.user_id,null)) as cnt_03,--当日注册 且 第三日活跃的用户
  		count(distinct if(datediff(t_active.active_dt,t_new.new_dt) = 4,t_new.user_id,null)) as cnt_05,--当日注册 且 第五日活跃的用户
  		count(distinct if(datediff(t_active