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